求救：mysql服务器负载超高,请问如何解决

大浪淘沙2023 2013-08-14 11:57:58

OS:CentOS release 6.3 (Final)
CPU:6核24CPU 内存：128GB
mysql版本：5.5.24
mysql采用双主架构，每个主机上8个实例，互为主备！
主库内存的分配： innodb_buffer_pool_size=14G
备库内存的分配： innodb_buffer_pool_size=10G

目前看到的负载如下：经常在100多，不小心就冲上220
top - 11:30:14 up 138 days, 20:40, 6 users, load average: 184.49, 179.63, 186.20[/color]
Tasks: 632 total, 1 running, 630 sleeping, 1 stopped, 0 zombie
Cpu(s): 7.2%us, 1.7%sy, 0.0%ni, 43.9%id, 46.6%wa, 0.0%hi, 0.7%si, 0.0%st
Mem: 132112432k total, 131598292k used, 514140k free, 261508k buffers
Swap: 16777208k total, 919504k used, 15857704k free, 9088716k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1111 mysql 20 0 32.1g 17g 5536 S 124.4 13.8 1216:44 mysqld
2222 mysql 20 0 32.1g 18g 5488 S 302.0 14.7 732:48.27 mysqld
....

（1）11111进程对应CPU利用率一直在100多，并发数和连接数如下(这是峰值)，并发数平均下来再260-300左右，连接数一直在800之上；
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 338 |
| Threads_connected | 935 |
| Threads_created | 14247 |
| Threads_running | 435 |
+-------------------+-------+

查看慢查询日志，有些SQL是笔记慢，3-5s的查询语句比较多，但是用expalin看了一下，都用了索引！

（2）2222进程对应CPU利用率一直在15%，但是会周期性的冲到300%多（周期一般是30S）；
并发数和连接数如下(这是峰值)，并发数平均下来再150左右，连接数一直在500之上；：
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 498 |
| Threads_connected | 745 |
| Threads_created | 40085 |
| Threads_running | 454 |
+-------------------+-------+

但是这个数据库最大的问题提，慢查询日志中的insert和update语句，会到达70多秒，如下，为一个insert和update的慢查询日志
# User@Host: coolyun[coolyun] @ [192.168.0.139]
# Thread_id: 151341 Schema: coolcloud Last_errno: 0 Killed: 0
# Query_time: 76.443678 Lock_time: 0.000088 Rows_sent: 0 Rows_examined: 0 Rows_affected: 1 Rows_read: 0
# Bytes_sent: 19 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# InnoDB_trx_id: A29CBB2C
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# Filesort: No Filesort_on_disk: No Merge_passes: 0
# InnoDB_IO_r_ops: 0 InnoDB_IO_r_bytes: 0 InnoDB_IO_r_wait: 0.000000
# InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000
# InnoDB_pages_distinct: 19
SET timestamp=1376447034;
insert into AAAAAA values (XXXX);
# User@Host: coolyun[coolyun] @ [192.168.0.70]
# Thread_id: 150487 Schema: coolcloud Last_errno: 0 Killed: 0
# Query_time: 75.643280 Lock_time: 0.000079 Rows_sent: 0 Rows_examined: 1 Rows_affected: 1 Rows_read: 0
# Bytes_sent: 52 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# InnoDB_trx_id: A29CBC2E
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# Filesort: No Filesort_on_disk: No Merge_passes: 0
# InnoDB_IO_r_ops: 0 InnoDB_IO_r_bytes: 0 InnoDB_IO_r_wait: 0.000000
# InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000
# InnoDB_pages_distinct: 7

哪位大侠能指点一下，能如何优化，谢谢了！

...全文