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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
