RE: AMD64 Linux MySQL Error 1041 Out of Memory
On Thu, 2005-06-16 at 21:06, Richard Dale wrote: So we have recently started stress testing Mysql on an Opteron dual CPU machine running Ubuntu Hoary. We are using the 64-bit GCC 4.0.24-standard binary from mysql. The stress test that I'm currently running on it involves inserting a large database (from a mysqldump) ... Which linux kernel are you running? 2.6.10, specifically the ubuntu compile: 2.6.10-5-amd64-k8-smp Why not consider MySQL 4.1? It's now a stable production release. Unfortunately when we tried to run our application on MySQL 4.1, we were bit by a number of features of MySQL 4.1. MySQL 4.1 now silently returns Long objects instead of Integers for most functions (COUNT), causing class cast exceptions in Java... Also a number of our queries which used to compare binary and non-binary fields, now nolonger cast automatically... Requiring some additional SQL which our database abstraction layer is not aware of needing. A lengthy porting process ensued... and has been completed, but our application is still quite a ways from being fully certified to run on MySQL 4.1. :( The MySQL 4.1 upgrade was 10x nastier than our 3.23-4.0 upgrade. Also, consider visiting: http://hashmysql.org/index.php?title=Opteron_HOWTO We would welcome a section on there about Ubuntu and x86-64. Yes, I will be updating that wiki once I've got it running stably. I ran into some libc issues with Ubuntu already, as seen here: http://bugzilla.ubuntu.com/show_bug.cgi?id=11730 However I've compiled my own replacement libc with patches applied to fix the issues, which I will make available to all. Except for this memory problem, the machine is ready for our burnin testing. -Joe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: AMD64 Linux MySQL Error 1041 Out of Memory
On Thu, 2005-06-16 at 21:06, Richard Dale wrote: So we have recently started stress testing Mysql on an Opteron dual CPU machine running Ubuntu Hoary. We are using the 64-bit GCC 4.0.24-standard binary from mysql. The stress test that I'm currently running on it involves inserting a large database (from a mysqldump) ... So I don't think this has anything to do memory consumption anymore. I've been able to get the problem to repeat with mysqld's memory footprint being about 800+megs (545M resident). I also was able to get it to happen with the ubuntu stock mysqld my.cnf aswell... which uses ridiculously conservative memory settings. (The key cache is so small it takes 4x as long to run the test). With an identical configuration as my normal setup, running the 32bit binary doesn't appear to trigger the problem. I'll try 64bit with linuxthreads next. Somebody asked for a show processlist and show status... Here's the data pretty close to when it happened... +--++ | Variable_name| Value | +--++ | Aborted_clients | 4 | | Aborted_connects | 0 | | Bytes_received | 3638957151 | | Bytes_sent | 18427266 | | Com_admin_commands | 5 | | Com_alter_table | 10996 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin| 0 | | Com_change_db| 9 | | Com_change_master| 0 | | Com_check| 193| | Com_commit | 0 | | Com_create_db| 57 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_table | 5505 | | Com_delete | 0 | | Com_delete_multi | 0 | | Com_drop_db | 57 | | Com_drop_function| 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_flush| 3 | | Com_grant| 0 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_insert | 334612 | | Com_insert_select| 0 | | Com_kill | 0 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table| 0 | | Com_lock_tables | 5505 | | Com_optimize | 0 | | Com_purge| 0 | | Com_rename_table | 0 | | Com_repair | 0 | | Com_replace | 0 | | Com_replace_select | 0 | | Com_reset| 0 | | Com_restore_table| 0 | | Com_revoke | 0 | | Com_rollback | 0 | | Com_savepoint| 0 | | Com_select | 1 | | Com_set_option | 0 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 0 | | Com_show_create | 0 | | Com_show_databases | 2 | | Com_show_fields | 0 | | Com_show_grants | 0 | | Com_show_keys| 0 | | Com_show_logs| 0 | | Com_show_master_status | 0 | | Com_show_new_master | 0 | | Com_show_open_tables | 0 | | Com_show_processlist | 2 | | Com_show_slave_hosts | 0 | | Com_show_slave_status| 0 | | Com_show_status | 1 | | Com_show_innodb_status | 0 | | Com_show_tables | 9 | | Com_show_variables | 0 | | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_truncate | 0 | | Com_unlock_tables| 5491 | | Com_update | 0 | | Com_update_multi | 0 | | Connections | 182| | Created_tmp_disk_tables | 0 | | Created_tmp_tables | 0 | | Created_tmp_files| 43 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Delayed_errors | 0 | | Flush_commands | 1 | | Handler_commit | 0 | | Handler_delete | 0 | | Handler_read_first | 1 | | Handler_read_key | 0 | | Handler_read_next| 0 | | Handler_read_prev| 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next| 9 | | Handler_rollback | 0 | | Handler_update | 0 | | Handler_write| 617450860 | | Key_blocks_used | 478697 | |
Re: AMD64 Linux MySQL Error 1041 Out of Memory
Hello. MySQL on 64-bit AMD rises lots of questions. There is an Opteron HOWTO at: http://hashmysql.org/index.php?title=Opteron_HOWTO I can't give any advice except sending to the list the output of SHOW STATUS and SHOW PROCESSLIST statements performed when MySQL uses a lot of memory. If you able check your test on MySQL 4.1.12. Joe Kislo [EMAIL PROTECTED] wrote: So we have recently started stress testing Mysql on an Opteron dual CPU machine running Ubuntu Hoary. We are using the 64-bit GCC 4.0.24-standard binary from mysql. The stress test that I'm currently running on it involves inserting a large database (from a mysqldump) from three separate windows (so three imports running simultaneously). The database dump is about 3.7 gigs uncompressed, or 580megs compressed. It was dumped using the following dump parameters: --add-locks --extended-insert --quick --lock-tables --all --disable-keys Each window has as script that creates a database, imports the data, dumps the database, and repeats. After about 12 cycles (each take about an hour) mysql starts spewing these errors: ERROR 1041 at line 195: Out of memory; Check if mysqld or some other process uses all available memory. If not you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space I, unfortunately, have not been at the server when this actually happens, however when I come in in the morning top is reporting mysqld taking up between 2.7 gigs and 3.2 gigs of memory. I have had a vmstat running all night, and at no point saw the system run out of swap space (it did over the course of the 15 hours or so, slowly hit swap up for about 60megs out of 2 gigs though). Obviously checking ulimit was my first stop, however I believe MySQLd does it's own setuid... And I'm not sure it uses PAM to get it's initial ulimits. Either way, I do this: su mysql -s /bin/sh sh-3.00$ ulimit -a core file size(blocks, -c) 0 data seg size (kbytes, -d) unlimited file size (blocks, -f) unlimited max locked memory (kbytes, -l) unlimited max memory size (kbytes, -m) unlimited open files(-n) 8192 pipe size (512 bytes, -p) 8 stack size(kbytes, -s) 8192 cpu time (seconds, -t) unlimited max user processes(-u) unlimited virtual memory(kbytes, -v) unlimited Which implies it should be able to alloc quite a bit of memory without problem. So my first question, is it appears to be konking out around 4 gigs of memory. Is there some reason why mysqld can't allocate more than 4gigs? I confirmed I *am* running the 64-bit binary: file /usr/sbin/mysqld /usr/sbin/mysqld: ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.4.0, dynamically linked (uses shared libs), stripped This system has 4 gigs of memory in it. So if it tried to allocate 4gigs, it would have had to hit swap up harder than 60megs. It seems like mysqld is hitting the 32bit allocation limit, but that doesn't particularly make sense to me. Anybody have their mysqld allocating 4gigs? Anything else I can try here? The second thing is, I have no idea why mysql is taking up so much memory. For the first 5 runs or so, mysql only allocates about 800 megs. Sometime during the night, is when it jumps up in memory.. I don't really understand why if it didn't need 3+ gigs of memory after the first 5 complete runs (x3 of course... since there's 3 running in parallel), it would suddenly need more later. Either way, lets do some math. Mysql is 2.7 gigs this morning, which is about half a gig less than yesterday morning. 2.7 gigs Key buffer: 512m Tmp Table: 128m sort buffer size: 512m join buffer size: 512m query cache: 256m KeyBuffer=512m, I could see that possibly not being returned.. So lets assume 512M there. There are no threads connected at the moment because I have shutdown the test, so tmptable should take up 0, but lets say it didn't return 3x128M (384M). Sort buffer size is 512M, well it may have used that for the alter table activate keys... and never returned it, so 512M there. Join buffer size, not a single select query was used ever, 0M. Query cache, 0M. 3x16M max packet. So I see 512M+384+512M+48M=1.4gigs. I have no idea why mysql is using this much memory... especially after it successfully performs 5 cycles with considerably less. Any ideas? here's the my.cnf [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language= /usr/share/mysql/english skip-external-locking key_buffer =
RE: AMD64 Linux MySQL Error 1041 Out of Memory
So we have recently started stress testing Mysql on an Opteron dual CPU machine running Ubuntu Hoary. We are using the 64-bit GCC 4.0.24-standard binary from mysql. The stress test that I'm currently running on it involves inserting a large database (from a mysqldump) ... Which linux kernel are you running? Why not consider MySQL 4.1? It's now a stable production release. Also, consider visiting: http://hashmysql.org/index.php?title=Opteron_HOWTO We would welcome a section on there about Ubuntu and x86-64. Also, perhaps this bug is related: http://bugs.mysql.com/bug.php?id=10829 Best regards, Richard Dale. Norgate Investor Services - Premium quality Stock, Futures and Foreign Exchange Data for markets in Australia, Asia, Canada, Europe, UK USA - www.premiumdata.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
