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