Hi. I have a perl script that uses many temporary tables (HEAP mostly) and when running the script many times in parallel some of the script instances fail. The query uses temporary tables and recently I discovered that some of the queries uses filesort (disk access) due to GROUP BY making the response time for every concurrent script longer than expected.
Here is the error I get: DBD::mysql::db do failed: Got error 12 from table handler at /home/travis/tr.pl ... Looking at the log files for MySQL I cant figure out what queries cause the failure but generally speaking I assume that the problem is the temporary tables. Looking up the error code received by DBI/DBD I get: 'perror 12' Error code 12: Cannot allocate memory Can't allocate memory? Why is that? Below is my server info and my default my-huge.cnf settings. I have tried to set the "SET SQL_BIG_RESULT=1" resulting in all temp tables getting written to disk, but the error still exists. I have tried to modify the parameters in my.cnf, primarily adjusting key_buffer and sort_buffer, but the default my-huge.cnf configuration seems to work the best. Could someone please tell me what is wrong? SERVER INFO: ----------------------------- * MySQL v 3.23.52 on FreeBSD 4.7, dual 1200 MHz Intel CPU, 1Gb RAM, 10Gb Free swap space * and lots of free space on all partitions, especially in /var * no limits on CPU, Memory, open files etc. in FreeBSD kernel * No errors shown in /var/messages * No errors shown in /var/db/mysql/host.err * The queries involve the use of temporary tables and a lot of READ queries on regular tables. * No locks have been issued and no locks are needed. * No other processes on the server uses MySQL while the program runs. MySQL info: ------------- Compiled with static, no innodb, no BDB for full performance /etc/my.cnf - copied 100% from my-huge.cnf ----------------------- [client] #password = your_password port = 3306 socket = /tmp/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking skip-innodb skip-bdb set-variable = key_buffer=384M set-variable = max_allowed_packet=1M set-variable = table_cache=512 set-variable = sort_buffer=2M set-variable = record_buffer=2M set-variable = thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable = thread_concurrency=8 #set-variable = join_buffer_size=512000 set-variable = myisam_sort_buffer_size=64M #log-bin server-id = 1 --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
