Increase MySQL buffered query memory limit

Question

I couldn't find an answer online so I try my luck to ask here.

How to increase MySQL buffered query memory limit?

I try to execute a PDO query using PHP, but it bugged with error Out of memory (allocated 1444937728). After search for a while, it has nothing to do with PHP but with MySQL limits.

Although changing mysql config is still ineffective to solve the error. Here is my config

[mysqld]
port = 3306
socket = C:/neard/tmp/mariadb.sock
key_buffer_size = 64M
max_allowed_packet = 64M
bulk_insert_buffer_size = 2048M
table_open_cache = 1000
sort_buffer_size = 64M
read_buffer_size = 64M
read_rnd_buffer_size = 64M
innodb_sort_buffer_size = 32M
myisam_sort_buffer_size = 32M
basedir=C:/neard/bin/mariadb/mariadb10.3.8
log-error=C:/neard/logs/mariadb.log
datadir=C:/neard/bin/mariadb/mariadb10.3.8/data

long_query_time=3
slow_query_log_file=C:/neard/logs/slow_query.log

skip-external-locking

server-id = 1

innodb_buffer_pool_size = 128M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M

Clearly this number 1444937728 is defined or calculate somewhere, but I counln't find a doc about this.

Note: Most of the table engine is MyISAM

@YourCommonSense I can, but I want to try a simplier solution with configs instead of modifying the code source, then handle the left over problem such as free results or close current cursor. — Thanh Trung, Dec 04 '19 at 14:24
you don't have to free results or close cursor with unbuffered query (given you are fetching all rows returned) — Your Common Sense, Dec 04 '19 at 14:27
https://dba.stackexchange.com/questions/39467/mysql-performance-impact-of-increasing-innodb-buffer-pool-size — Shafiqul Islam, Dec 04 '19 at 14:30
https://dba.stackexchange.com/questions/27328/how-large-should-be-mysql-innodb-buffer-pool-size — Shafiqul Islam, Dec 04 '19 at 14:30
Well either way you've got but a little choice - either increase the already blow-my-mind memory limit or use unbuffered queries for a few leviathan datasets. Also consider using LIMIT when applicable. — Your Common Sense, Dec 04 '19 at 15:03

score -1 · Answer 1 · answered Dec 04 '19 at 14:12

-1

Change the setting in php.ini.

Section: sqlsrv

Directive: sqlsrv.ClientBufferMaxKBSize.

client_buffer_max_kb_size = '50240'
sqlsrv.ClientBufferMaxKBSize = 50240

answered Dec 04 '19 at 14:12

Blank

90
7

1

I don't think this apply to MySQL. Please refer a doc about this. – Thanh Trung Dec 04 '19 at 14:25

Increase MySQL buffered query memory limit

1 Answers1