Hi! We are operating several MySQL servers and most are running just fine even under high load. However we have one site (High traffic PHP based site) where we currently is experiencing problems with some strange lock up that causes MySQL to spawn new threads and have a huge number of threads in "ready to run" mode. The load goes from below 1 (on a dual PIII machine, running Slackware 8 with kernel 2.4.18 and MySQL 3.23.51-max (binary from mysql.com).
From what I have read, this is most likely due to an internal error that can't be delt with as a result of a specific query. Here's a snip from vmstat 5: 38 0 0 0 5472 912 294552 0 0 0 51 365 625 96 4 0 56 0 0 0 8016 912 294588 0 0 0 26 519 805 96 4 0 36 0 1 0 8904 912 294624 0 0 0 0 466 884 95 5 0 63 0 1 0 8672 912 294656 0 0 0 0 433 867 97 3 0 And a mysqladmin processlist shows a bunch of connections in state locked: ...snip... | 5362 | public | localhost | onside1 | Query | 3 | Locked | select id from banners_new where (id=131 OR parent_id=131) AND pic!='' | | 5363 | public | localhost | onside1 | Query | 0 | System lock | select id from banners_new where (id=131 OR parent_id=131) AND pic!='' | | 5364 | public | localhost | onside1 | Query | 0 | Locked | select id from banners_new where (id=131 OR parent_id=131) AND pic!='' | | 5365 | public | localhost | onside1 | Query | 0 | Locked | select id,pic from banners_new where (id=110 OR parent_id=110) AND pic!='' limit 0,1 | ...snip... The config (mostly a my-huge.cnf file): [mysqld] datadir = /data/db user = mysqld port = 3306 socket = /tmp/mysql.sock skip-locking set-variable = max_connections=256 set-variable = key_buffer=256M set-variable = max_allowed_packet=1M set-variable = table_cache=256 set-variable = sort_buffer=1M set-variable = record_buffer=1M set-variable = myisam_sort_buffer_size=64M set-variable = thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable = thread_concurrency=8 #log-bin server-id = 1 Any ideas on how to debug this? Logging all queries is almost not an option since the traffic (when the error occurs) is several hundred queries / second (according to mysqladmin status). Thanks in advance! Brgds /Stefan -- DISCUSSION, n. A method of confirming others in their errors. --------------------------------------------------------------------- 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
