I recently read an article (on PHPBuilder; it's fairly old) that spoke of MySQL dying under high load.
Recently (since I built 3.23.51 and on my test build of 4.0.2) I have had problems with MySQL dying (got signal 11, et al.) under extreme high load. For example, if I set up a 60-thread spider, and run it in our test environment (1 front end web server, 1 database server, mysql 4.0.2 alpha compiled with a custom glib-2.2.5 + MySQL patch to linuxthreads)(heavily database driven), I can crash MySQL after only 30 minutes or so. The sad part is, we haven't hit the 2500 connection limit that I've set. I've tried checking the physical resources on the boxen, and MySQL doesn't appear to have consumed all the resources on the box at this point, although the load average when MySQL dies is usually around 15. I've been tweaking values, and found that increasing the table_cache from our previous setting of 400 to 3000 has caused *vastly* improved stability, however I cannot imagine that a table_cache adjustment should prevent a crash... I digress... at this point, the question that plagues me is: Is it normal for MySQL to die under high load? Additionally, I'd love to know: Is it likely that something in my build is causing mysqld to crash (given that there are no other problems whatsoever with the build)? Since I increased the table cache it has become more stable -- is this the expected behaviour? Should the increased table_cache have made any difference at all? *** I don't want to ask leading questions, but... is it possible that we are hitting, say 200 processes, and when they all start using their 5M sort buffers (see below) that we're running out of memeory and causing MySQL to die? If so, is there any way to avoid having MySQL die? I'd rather a query take 3 minutes than kill the daemon... *** Below is my MySQL config, and some other pertinent data. Please let me know if I can provide any further information to help answer above questions. Also, if anyone has advice on how to make MySQL more stable, please let me know. Thanks in advance. --Other stuff-- MySQL built with: root@local-dbmaster-test:/usr/local/src/mysql-4.0.2-alpha/BUILD# ./compile-pentium --with-other-libc=/usr/local/mysql-glibc-2.2.5 --prefix=/usr/local glibc-2.2.5 built with: root@local-dbmaster-test:/usr/local/src/glibc-2.2.5# ./configure --enable-add-ons=linuxthreads --prefix=/usr/local/mysql-glibc-2.2.5 --enable-kernel=2.4.18 gcc-2.95.4 (debian stable package) was used to build both kernel is running patched to enable a higher open file limit per process (up to 4096) ---- --/etc/my.cnf-- [mysqld] skip-locking set-variable = max_connections=2500 set-variable = query_cache_size=64M set-variable = wait_timeout=3600 set-variable = key_buffer=384M set-variable = max_allowed_packet=1M set-variable = table_cache=3000 # NOTE: Record buffer and Sort buffer are NOT shared among threads # They are per-thread memory buffers. set-variable = sort_buffer=5M set-variable = record_buffer=5M set-variable = thread_cache=8 set-variable = thread_concurrency=4 set-variable = myisam_sort_buffer_size=64M set-variable = long_query_time=4 tmpdir=/data/tmp user=mysql #log=/usr/local/var/mysqld.log log-slow-queries=/usr/local/var/slow-queries.log pid-file=/usr/local/var/mysqld.pid server-id=1 #master-host=host #master-user=user #master-password=secret #log-bin #binlog-do-db=db [mysql] set-variable = max_allowed_packet=1M [mysqldump] set-variable = max_allowed_packet=1M [isamchk] set-variable = key_buffer=256M set-variable = sort_buffer=256M set-variable = read_buffer=2M set-variable = write_buffer=2M [myisamchk] set-variable = key_buffer=256M set-variable = sort_buffer=256M set-variable = read_buffer=2M set-variable = write_buffer=2M [mysqlhotcopy] interactive-timeout [safe_mysqld] open-files-limit=8192 err-log=/usr/local/var/mysqld.err ---- -- Shane Allen <[EMAIL PROTECTED]> sell.com : Buy & Sell Anything http://www.sell.com --------------------------------------------------------------------- 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
