Hello.
> server version: 4.0.18-log ( I know it's old but we can't update for >the moment ) MySQL shouldn't die even under huge load, it is bad that you can't update, and work with old version which has lots of bugs. Do you have a stack trace in the error log? Please, resolve and send it to the list in case you have it. See: http://dev.mysql.com/doc/refman/5.0/en/using-stack-trace.html Dilipan Sebastiampillai wrote: > Hi all, > > We have a MySQL server used for a queueing system. It worked fine so far > but now we are experiencing some load problems couple with crashes. > > The load ( around 5-7 ) is due, according to vmstat, to processes > waiting for the CPU. And it makes sense because the CPU is used 100% > most of the time. > Nearly all tables are Innodb ( 3 tables of 1kb are MyIsam ). Around 500 > machines open a connection, make a query and disconnect. > How can we tune MySQL so that it stops crashing and stays at a > reasonnable load? > Your help would be greatly appreciated. > > Dilipan > > > FOLLOWING: > system info > my.cnf > show status > show innodb status > > ------------------------------------------------------------------------------------------------------------------------------------------------------------- > > SYSTEM INFO > ------------------------------------------------------------------------------------------------------------------------------------------------------------- > > > OS : Red Hat Enterprise Linux ES release 3 > RAM : 4 Gig > Hard disk : 15k rpm > CPU : 2 x Intel Xeon 3.60GHz CPU with 1M cache (Hyperthreading Enabled) > server version: 4.0.18-log ( I know it's old but we can't update for the > moment ) > database size : 1.9Gig > > > ------------------------------------------------------------------------------------------------------------------------------------------------------------- > > MY.CNF > ------------------------------------------------------------------------------------------------------------------------------------------------------------- > > > [mysqld] > skip-locking > skip-grant-table > > # If you have a very slow DNS and many hosts, you can get more > performance by either disabling DNS lookup with > #skip-name-resolve > > log-error > log-slow-queries > > set-variable = join_buffer_size=256K > set-variable = tmp_table_size=32M > set-variable = max_tmp_tables=500 set-variable = long_query_time=2 > > set-variable = max_allowed_packet=1M > set-variable = table_cache=2048 > set-variable = sort_buffer=1M > set-variable = read_buffer_size=512K > set-variable = read_rnd_buffer_size=512K > set-variable = myisam_sort_buffer_size=64M > set-variable = back_log=256 > set-variable = thread_cache_size=500 > set-variable = max_connections=1000 > set-variable = key_buffer_size=8M > set-variable = max_connect_errors=5000 > # Try number of CPU's*2 for thread_concurrency > set-variable = thread_concurrency=4 > > server-id = 1 > set-variable = query_cache_size=20M > > innodb_data_home_dir = /var/lib/mysql/MySQL4.0.18/var/ > innodb_data_file_path=ibdata1:1000M;ibdata2:10M:autoextend > innodb_log_group_home_dir = /var/lib/mysql/MySQL4.0.18/var/ > innodb_log_arch_dir = /var/lib/mysql/MySQL4.0.18/var/ > > # You can set .._buffer_pool_size up to 50 - 80 % > # of RAM but beware of setting memory usage too high > set-variable = innodb_buffer_pool_size=1924M # previous was 1024M > set-variable = innodb_additional_mem_pool_size=30M # previous was 20M > > # Set .._log_file_size to 25 % of buffer pool size > set-variable = innodb_log_file_size=256M > set-variable = innodb_log_buffer_size=8M > set-variable = innodb_lock_wait_timeout=300 #=5min > innodb_flush_log_at_trx_commit=2 > set-variable =innodb_thread_concurrency=8 > tmpdir = /tmp/ > > > [mysqldump] > quick > set-variable = max_allowed_packet=16M > > [mysql] > no-auto-rehash > > [isamchk] > set-variable = key_buffer=512M > set-variable = sort_buffer=512M > set-variable = read_buffer=2M > set-variable = write_buffer=2M > > [myisamchk] > set-variable = key_buffer=512M > set-variable = sort_buffer=512M > set-variable = read_buffer=2M > set-variable = write_buffer=2M > > [mysqlhotcopy] > interactive-timeout > > [safe_mysqld] > err-log=/var/lib/mysql/MySQL4.0.18/var/safe_mysqld_mysqld4.0.18.log > pid-file=/var/run/mysqld4.0.18.pid > > > > > ------------------------------------------------------------------------------------------------------------------------------------------------------------- > > SHOW STATUS > ------------------------------------------------------------------------------------------------------------------------------------------------------------- > > This MySQL server has been running for 0 days, 2 hours, 47 minutes and > 55 seconds. It started up on Dec 15, 2005 at 08:53 AM. > > mysql> show status ; > +--------------------------+------------+ > | Variable_name | Value | > +--------------------------+------------+ > | Aborted_clients | 2 | > | Aborted_connects | 36 | > | Bytes_received | 2126078417 | > | Bytes_sent | 1070759771 | > | Com_admin_commands | 0 | > | Com_alter_table | 0 | > | Com_analyze | 0 | > | Com_backup_table | 0 | > | Com_begin | 311 | > | Com_change_db | 43 | > | Com_change_master | 0 | > | Com_check | 0 | > | Com_commit | 312 | > | Com_create_db | 0 | > | Com_create_function | 0 | > | Com_create_index | 0 | > | Com_create_table | 0 | > | Com_delete | 18 | > | Com_delete_multi | 12 | > | Com_drop_db | 0 | > | Com_drop_function | 0 | > | Com_drop_index | 0 | > | Com_drop_table | 0 | > | Com_flush | 0 | > | Com_grant | 0 | > | Com_ha_close | 0 | > | Com_ha_open | 0 | > | Com_ha_read | 0 | > | Com_insert | 46633 | > | Com_insert_select | 0 | > | Com_kill | 0 | > | Com_load | 0 | > | Com_load_master_data | 0 | > | Com_load_master_table | 0 | > | Com_lock_tables | 0 | > | Com_optimize | 26 | > | 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 | 3477135 | > | Com_set_option | 115790 | > | Com_show_binlog_events | 0 | > | Com_show_binlogs | 3 | > | Com_show_create | 1 | > | Com_show_databases | 10 | > | Com_show_fields | 63 | > | Com_show_grants | 0 | > | Com_show_keys | 8 | > | Com_show_logs | 0 | > | Com_show_master_status | 0 | > | Com_show_new_master | 0 | > | Com_show_open_tables | 0 | > | Com_show_processlist | 14681 | > | Com_show_slave_hosts | 0 | > | Com_show_slave_status | 0 | > | Com_show_status | 23976 | > | Com_show_innodb_status | 9505 | > | Com_show_tables | 79 | > | Com_show_variables | 530 | > | Com_slave_start | 0 | > | Com_slave_stop | 0 | > | Com_truncate | 0 | > | Com_unlock_tables | 426 | > | Com_update | 2408550 | > | Connections | 444434 | > | Created_tmp_disk_tables | 108778 | > | Created_tmp_tables | 280390 | > | Created_tmp_files | 2 | > | Delayed_insert_threads | 0 | > | Delayed_writes | 0 | > | Delayed_errors | 0 | > | Flush_commands | 1 | > | Handler_commit | 312 | > | Handler_delete | 0 | > | Handler_read_first | 277779 | > | Handler_read_key | 90929262 | > | Handler_read_next | 1152511422 | > | Handler_read_prev | 0 | > | Handler_read_rnd | 7821452 | > | Handler_read_rnd_next | 57375083 | > | Handler_rollback | 424513 | > | Handler_update | 29812487 | > | Handler_write | 25867776 | > | Key_blocks_used | 7793 | > | Key_read_requests | 25104538 | > | Key_reads | 0 | > | Key_write_requests | 204156 | > | Key_writes | 0 | > | Max_used_connections | 143 | > | Not_flushed_key_blocks | 0 | > | Not_flushed_delayed_rows | 0 | > | Open_tables | 225 | > | Open_files | 24 | > | Open_streams | 0 | > | Opened_tables | 331 | > | Questions | 7214176 | > | Qcache_queries_in_cache | 555 | > | Qcache_inserts | 3221408 | > | Qcache_hits | 674342 | > | Qcache_lowmem_prunes | 0 | > | Qcache_not_cached | 256785 | > | Qcache_free_memory | 20158008 | > | Qcache_free_blocks | 317 | > | Qcache_total_blocks | 1439 | > | Rpl_status | NULL | > | Select_full_join | 1545 | > | Select_full_range_join | 251 | > | Select_range | 1850013 | > | Select_range_check | 0 | > | Select_scan | 275729 | > | Slave_open_temp_tables | 0 | > | Slave_running | OFF | > | Slow_launch_threads | 0 | > | Slow_queries | 149 | > | Sort_merge_passes | 1 | > | Sort_range | 17305 | > | Sort_rows | 2904469 | > | Sort_scan | 101391 | > | Table_locks_immediate | 9383249 | > | Table_locks_waited | 1590 | > | Threads_cached | 131 | > | Threads_created | 144 | > | Threads_connected | 13 | > | Threads_running | 6 | > | Uptime | 10138 | > +--------------------------+------------+ > 132 rows in set (0.00 sec) > > > > ------------------------------------------------------------------------------------------------------------------------------------------------------------- > > INNODB STATUS > ------------------------------------------------------------------------------------------------------------------------------------------------------------- > > > mysql> show innodb status\G > *************************** 1. row *************************** > Status: > ===================================== > 051215 11:27:05 INNODB MONITOR OUTPUT > ===================================== > Per second averages calculated from the last 0 seconds > ---------- > SEMAPHORES > ---------- > OS WAIT ARRAY INFO: reservation count 2314454, signal count 1923933 > Mutex spin waits 1057261556, rounds 2350226711, OS waits 896528 > RW-shared spins 1526452, OS waits 194614; RW-excl spins 3582238, OS > waits 148956 > ------------------------ > LATEST DETECTED DEADLOCK > ------------------------ > 051215 10:05:42 > *** (1) TRANSACTION: > TRANSACTION 0 112269141, ACTIVE 0 sec, process no 24526, OS thread id > 163851 starting index read > mysql tables in use 8, locked 8 > LOCK WAIT 4 lock struct(s), heap size 320 > ** > *** (1) WAITING FOR THIS LOCK TO BE GRANTED: > RECORD LOCKS space id 0 page no 34769 n bits 88 table fcq/commands index > commandId trx id 0 112269141 lock_mode X locks rec but not gap waiting > Record lock, heap no 7 RECORD: info bits 0 0: len 4; hex 83fdfeb5; asc > ....;; 1: len 6; hex 000006b1164c; asc .....L;; > *** (2) TRANSACTION: > TRANSACTION 0 112268876, ACTIVE 0 sec, process no 24530, OS thread id > 229391 fetching rows, thread declared inside InnoDB 332 > mysql tables in use 1, locked 1 > 643 lock struct(s), heap size 44352, undo log entries 1924 > MySQL thread id 155698, query id 2843769 Searching rows for update > UPDATE ** > *** (2) HOLDS THE LOCK(S): > RECORD LOCKS space id 0 page no 34769 n bits 88 table fcq/commands index > commandId trx id 0 112268876 lock_mode X locks rec but not gap > Record lock, heap no 2 RECORD: info bits 0 0: len 4; hex 83fdfeb0; asc > ....;; 1: len 6; hex 000006b1164c; asc .....L;; > Record lock, heap no 3 RECORD: info bits 0 0: len 4; hex 83fdfeb1; asc > ....;; 1: len 6; hex 000006b10fcf; asc ......;; > Suppressing further record lock prints for this page > *** (2) WAITING FOR THIS LOCK TO BE GRANTED: > RECORD LOCKS space id 0 page no 78520 n bits 136 table fcq/tries index > tryId trx id 0 112268876 lock_mode X locks rec but not gap waiting > Record lock, heap no 36 RECORD: info bits 0 0: len 4; hex 82d869b2; asc > ..i.;; 1: len 6; hex 000006b08b2e; asc ......;; > *** WE ROLL BACK TRANSACTION (1) > ------------ > TRANSACTIONS > ------------ > Trx id counter 0 115265959 > Purge done for trx's n:o < 0 115265364 undo n:o < 0 0 > Total number of lock structs in row lock hash table 0 > LIST OF TRANSACTIONS FOR EACH SESSION: > ---TRANSACTION 0 115265954, not started, process no 8253, OS thread id > 1278031 > ** > ---TRANSACTION 0 115265950, not started, process no 8197, OS thread id > 1097796 starting index read, thread declared inside InnoDB 500 > mysql tables in use 1, locked 1 > UPDATE ** > ---TRANSACTION 0 115265888, not started, process no 25549, OS thread id > 950331 > ** > ---TRANSACTION 0 0, not started, process no 24601, OS thread id 507936 > ** > ---TRANSACTION 0 115265701, not started, process no 10195, OS thread id > 2261131 > ** > ---TRANSACTION 0 113682384, not started, process no 25550, OS thread id > 966716 > ** > ---TRANSACTION 0 115265264, not started, process no 24529, OS thread id > 213006 > ** > ---TRANSACTION 0 0, not started, process no 24531, OS thread id 245776 > ** > -------- > FILE I/O > -------- > I/O thread 0 state: waiting for i/o request (insert buffer thread) > I/O thread 1 state: waiting for i/o request (log thread) > I/O thread 2 state: waiting for i/o request (read thread) > I/O thread 3 state: waiting for i/o request (write thread) > Pending normal aio reads: 0, aio writes: 0, > ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 > Pending flushes (fsync) log: 0; buffer pool: 0 > 43408 OS file reads, 980605 OS file writes, 12706 OS fsyncs > 0.00 reads/s, 0 avg bytes/read, 149000.00 writes/s, 4000.00 fsyncs/s > ------------------------------------- > INSERT BUFFER AND ADAPTIVE HASH INDEX > ------------------------------------- > Ibuf for space 0: size 1, free list len 3, seg size 5, > 1546 inserts, 1546 merged recs, 483 merges > Hash table size 7985381, used cells 2683537, node heap has 3207 buffer(s) > 99400000.00 hash searches/s, 18064000.00 non-hash searches/s > --- > LOG > --- > Log sequence number 2 632102335 > Log flushed up to 2 632051578 > Last checkpoint at 2 392421868 > 0 pending log writes, 0 pending chkp writes > 970504 log i/o's done, 142000.00 log i/o's/second > ---------------------- > BUFFER POOL AND MEMORY > ---------------------- > Total memory allocated 2210424456; in additional pool allocated 4874624 > Buffer pool size 123136 > Free buffers 37398 > Database pages 82531 > Modified db pages 8719 > Pending reads 0 > Pending writes: LRU 0, flush list 0, single page 0 > Pages read 75602, created 6929, written 48273 > 0.00 reads/s, 0.00 creates/s, 15000.00 writes/s > Buffer pool hit rate 1000 / 1000 > -------------- > ROW OPERATIONS > -------------- > 0 queries inside InnoDB, 0 queries in queue > Main thread process no. 24523, id 114696, state: sleeping > Number of rows inserted 64577, updated 1169552, deleted 1617684, read > 1105845782 > 3000.00 inserts/s, 185000.00 updates/s, 0.00 deletes/s, 111256000.00 > reads/s > ---------------------------- > END OF INNODB MONITOR OUTPUT > ============================ > > > > -- 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]
