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 XXXX 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 = 512M > tmp_table_size = 128M > max_connections = 2000 > max_connect_errors = 999999999 > table_cache = 1024 > myisam_max_sort_file_size=2048M > myisam_sort_buffer_size =512M > join_buffer_size =512M > sort_buffer =512M > > max_allowed_packet = 16M > thread_stack = 128K > > query_cache_limit = 1M > query_cache_size = 256M > query_cache_type = 1 > > skip-innodb > > and a show variables from a running server after a night's testing: > mysql> show variables; > +---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ > | Variable_name | > Value > > | > +---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ > | back_log | > 50 > > | > | basedir | > /usr/ > > | > | binlog_cache_size | > 32768 > > | > | bulk_insert_buffer_size | > 8388608 > > | > | character_set | > latin1 > > | > | character_sets | latin1 big5 czech euc_kr gb2312 gbk > latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 > cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek > win1250 croat cp1257 latin5 | > | concurrent_insert | > ON > > | > | connect_timeout | > 5 > > | > | convert_character_set > | > > | > | datadir | > /var/lib/mysql/ > > | > | default_week_format | > 0 > > | > | delay_key_write | > ON > > | > | delayed_insert_limit | > 100 > > | > | delayed_insert_timeout | > 300 > > | > | delayed_queue_size | > 1000 > > | > | flush | > OFF > > | > | flush_time | > 0 > > | > | ft_boolean_syntax | + > -><()~*:""&| > > | > | ft_min_word_len | > 4 > > | > | ft_max_word_len | > 254 > > | > | ft_max_word_len_for_sort | > 20 > > | > | ft_stopword_file | > (built-in) > > | > | have_bdb | > NO > > | > | have_crypt | > YES > > | > | have_innodb | > DISABLED > > | > | have_isam | > YES > > | > | have_raid | > NO > > | > | have_symlink | > YES > > | > | have_openssl | > NO > > | > | have_query_cache | > YES > > | > | init_file > | > > | > | innodb_additional_mem_pool_size | > 1048576 > > | > | innodb_autoextend_increment | > 8 > > | > | innodb_buffer_pool_size | > 8388608 > > | > | innodb_data_file_path > | > > | > | innodb_data_home_dir > | > > | > | innodb_file_io_threads | > 4 > > | > | innodb_force_recovery | > 0 > > | > | innodb_thread_concurrency | > 8 > > | > | innodb_flush_log_at_trx_commit | > 1 > > | > | innodb_fast_shutdown | > ON > > | > | innodb_flush_method > | > > | > | innodb_lock_wait_timeout | > 50 > > | > | innodb_log_arch_dir > | > > | > | innodb_log_archive | > OFF > > | > | innodb_log_buffer_size | > 1048576 > > | > | innodb_log_file_size | > 5242880 > > | > | innodb_log_files_in_group | > 2 > > | > | innodb_log_group_home_dir > | > > | > | innodb_mirrored_log_groups | > 1 > > | > | innodb_max_dirty_pages_pct | > 90 > > | > | innodb_max_purge_lag | > 0 > > | > | innodb_table_locks | > ON > > | > | interactive_timeout | > 28800 > > | > | join_buffer_size | > 536866816 > > | > | key_buffer_size | > 536870912 > > | > | language | > /usr/share/mysql/english/ > > | > | large_files_support | > ON > > | > | license | > GPL > > | > | local_infile | > ON > > | > | locked_in_memory | > OFF > > | > | log | > OFF > > | > | log_update | > OFF > > | > | log_bin | > OFF > > | > | log_slave_updates | > OFF > > | > | log_slow_queries | > OFF > > | > | log_warnings | > 1 > > | > | long_query_time | > 10 > > | > | low_priority_updates | > OFF > > | > | lower_case_file_system | > OFF > > | > | lower_case_table_names | > 0 > > | > | max_allowed_packet | > 16776192 > > | > | max_binlog_cache_size | > 4294967295 > > | > | max_binlog_size | > 1073741824 > > | > | max_connections | > 2000 > > | > | max_connect_errors | > 999999999 > > | > | max_delayed_threads | > 20 > > | > | max_insert_delayed_threads | > 20 > > | > | max_heap_table_size | > 16777216 > > | > | max_join_size | > 18446744073709551615 > > | > | max_relay_log_size | > 0 > > | > | max_seeks_for_key | > 4294967295 > > | > | max_sort_length | > 1024 > > | > | max_user_connections | > 0 > > | > | max_tmp_tables | > 32 > > | > | max_write_lock_count | > 4294967295 > > | > | myisam_max_extra_sort_file_size | > 268435456 > > | > | myisam_max_sort_file_size | > 2147483648 > > | > | myisam_repair_threads | > 1 > > | > | myisam_recover_options | > OFF > > | > | myisam_sort_buffer_size | > 536870912 > > | > | net_buffer_length | > 16384 > > | > | net_read_timeout | > 30 > > | > | net_retry_count | > 10 > > | > | net_write_timeout | > 60 > > | > | new | > OFF > > | > | open_files_limit | > 10010 > > | > | pid_file | > /var/run/mysqld/mysqld.pid > > | > | log_error > | > > | > | port | > 3306 > > | > | protocol_version | > 10 > > | > | query_alloc_block_size | > 8192 > > | > | query_cache_limit | > 1048576 > > | > | query_cache_size | > 268435456 > > | > | query_cache_type | > ON > > | > | query_cache_wlock_invalidate | > OFF > > | > | query_prealloc_size | > 8192 > > | > | range_alloc_block_size | > 2048 > > | > | read_buffer_size | > 131072 > > | > | read_only | > OFF > > | > | read_rnd_buffer_size | > 262144 > > | > | rpl_recovery_rank | > 0 > > | > | server_id | > 0 > > | > | slave_net_timeout | > 3600 > > | > | skip_external_locking | > ON > > | > | skip_networking | > OFF > > | > | skip_show_database | > OFF > > | > | slow_launch_time | > 2 > > | > | socket | > /var/run/mysqld/mysqld.sock > > | > | sort_buffer_size | > 536870904 > > | > | sql_mode | > 0 > > | > | table_cache | > 1024 > > | > | table_type | > MYISAM > > | > | thread_cache_size | > 0 > > | > | thread_stack | > 131072 > > | > | tx_isolation | > REPEATABLE-READ > > | > | timezone | > EDT > > | > | tmp_table_size | > 134217728 > > | > | tmpdir | > /tmp/ > > | > | transaction_alloc_block_size | > 8192 > > | > | transaction_prealloc_size | > 4096 > > | > | version | > 4.0.24-standard > > | > | version_comment | Official MySQL-standard > binary > > | > | version_compile_os | > unknown-linux-gnu > > | > | wait_timeout | > 28800 > > | > +---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ > 135 rows in set (0.01 sec) > > Thanks, > -Joe > > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
