【讨论中】如何提高并发写入性能

ayalastrike

服务器版本 5.0.67-community-log
内存32G，8核cpu
数据库进程数保持在200-300之间

io负载并不大，保持在15%左右，内存吃了85%, innodb buffer pool使用率100%(innodb_buffer_pool_size = 24G), free 0

现在遇到的问题是在高并发下查询和写入性能不高，表使用的都是InnoDB
数据库构架是master带2个slave，慢查询基本集中在master上
慢查询基本上都是insert、update、select（因为有些业务要求实时性，所以从master上select），都是单表操作（已经做了32个分表，每个分表数据量级在1千万到4千万之间），查询时间集中在1-2秒之间

各位看一下还有哪些优化的思路
1. 改进InnoDB的配置
2. 继续在每个数据库实例上再进行分表
3. master改为MyISAM，slave用InnoDB，这样数据、索引损坏的概率大吗？索引损坏在master上修复貌似对服务有很大影响

下面是my.cnf的配置
我只列出了我觉得可能影响并发性能的一些参数
key_buffer              = 1024M
max_allowed_packet      = 1M
table_cache             = 2048
join_buffer_size        = 8M
sort_buffer_size        = 16M
read_buffer_size        = 16M
read_rnd_buffer_size    = 24M
myisam_sort_buffer_size = 128M
query_cache_size        = 128M
query_cache_limit       = 2M
max_tmp_tables          = 64
tmp_table_size          = 192M
max_heap_table_size     = 64M
thread_cache            = 16
thread_concurrency      = 4
max_connections         = 1536
max_user_connections    = 1024
back_log                = 300
delayed_insert_limit    = 256
max_delayed_threads     = 256
wait_timeout            = 30
interactive_timeout     = 30
long_query_time         = 1
delay_key_write         = ALL
low_priority_updates

innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql

innodb_buffer_pool_size = 24G
innodb_log_buffer_size = 12M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_lock_wait_timeout = 20
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
transaction-isolation=READ-COMMITTED
innodb_file_io_threads = 4
innodb_thread_concurrency = 18
innodb_additional_mem_pool_size = 20M
innodb_max_dirty_pages_pct         = 50

ayalastrike

__  Questions  ___________________________________________________________
Total                  117.31G        1.2k/s
    DMS                    82.26G      870.4/s    %Total:    70.12
    Com_                  37.22G      393.9/s                      31.73
    -Unknown            2.34G        24.8/s                        1.99
    COM_QUIT        146.71M          1.6/s                        0.13
    QC  Hits            23.83M          0.3/s                        0.02
Slow  1  s              14.43M          0.2/s                        0.01    %DMS:      0.02    Log:    ON
DMS                        82.26G      870.4/s                      70.12
    UPDATE              54.24G      574.0/s                      46.24                  65.94
    SELECT              14.00G      148.2/s                      11.94                  17.02
    INSERT                9.12G        96.5/s                        7.77                  11.09
    DELETE                4.89G        51.7/s                        4.17                    5.94
    REPLACE              1.48k          0.0/s                        0.00                    0.00
Com_                      37.22G      393.9/s                      31.73
    change_db        34.58G      365.9/s                      29.48
    admin_comma      2.34G        24.8/s                        2.00
    begin              211.31M          2.2/s                        0.18
       
__  InnoDB  Data,  Pages,  Rows  ____________________________________________
Data
    Reads              152.68M          1.6/s
    Writes                5.08G        53.7/s
    fsync              361.05M          3.8/s
    Pending
        Reads                      0
        Writes                    0
        fsync                      0

Pages
    Created            44.85M          0.5/s
    Read                165.66M          1.8/s
    Written              7.83G        82.8/s

Rows
    Deleted              4.61G        48.8/s
    Inserted            8.67G        91.7/s
    Read                357.11G        3.8k/s
    Updated            50.96G      539.2/s

cenalulu

业务级：
1. 从楼主描述来看，SQL集中在1-2 秒是对 DML并发频率不高的一个主要原因。建议把select 语句控制在95% 200ms级。
2. 严格控制表上的索引，尽可能的删除冗余的索引。用复合索引替代单列索引。避免在更新频繁的字段上的索引（status类）

参数级：
Query_cache:如果频繁写入量很大的话，Query Cache建议就不要了，QC的mutex对于高并发的写入是个杀手。
innodb_max_dirty_pages_pct ： 50稍微有点小，不过鉴于你目前的IO就在15左右，不是瓶颈。

ayalastrike

[root mysql]# iostat
Linux 2.6.18-92.el5  06/28/2012

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           3.10    0.00    1.09    1.67    0.00   94.14

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda              80.73      1943.14      3607.63 181959494381 337824930802

ayalastrike

现在每个分表上基本就一个主键，没有别的索引了，并且update，select都走主键，从SQL角度似乎没有优化的空间了

另外，我这里的Query cache配的不大，也影响并发性能吗？
key_buffer                         = 1024M

query_cache_size                   = 128M
query_cache_limit                  = 2M

是不是意味着
1. 改进InnoDB的配置 （改进空间不大了？）
是不是只能从下面两个入手优化呢
2. 继续在每个数据库实例上再进行分表
3. master改为MyISAM，slave用InnoDB，这样数据、索引损坏的概率大吗？索引损坏在master上修复貌似对服务有很大影响

cenalulu

1. select 也走主键的话，怎么会有超过1s的查询？这点我比较疑惑
2. QC 的存在与否对性能影响比较大，配置多大大小影响不大。
3. master改为myisam反而会增加你的压力，不建议这种方案
4. 再分表，如果开发成本可以接受，那当然也是一种不错的选择

我的观点还是SQL优化是最有效果的，PK的查询在100ms以内是绝对没有问题的

ayalastrike

等待事件 如何观察监控啊？
用innotop？还是什么工具？

是不是innodb lock有些高？
__  InnoDB  Lock  _________________________________________________________
Waits                  4751710          0.1/s
Current                          0
Time  acquiring
    Total          212284314  ms
    Average                  446  ms
    Max              184467440  ms

cenalulu

把long-query-time 设置成0s 然后用pt-query-digest 出分析报告。
看下占用时长最久的那些语句。