search

crazyjohn / crazyjohn.github.io

crazyjohn's blog
9 stars 3 forks source link

mysql调优-innodb篇 #32

Open crazyjohn opened 8 years ago

crazyjohn commented 8 years ago

1. sysbench

  1. prepare

    [liujinguang@VM_221_168_centos sysbench-0.4.12.7]$ sysbench --test=oltp --oltp-table-size=80000000 --db-driver=mysql --mysql-socket=/var/lib/mysql/mysql.sock --mysql-user=root --mysql-password='' --mysql-db=test prepare
sysbench 0.4.13:  multi-threaded system evaluation benchmark

Creating table 'sbtest'...
Creating 80000000 records in table 'sbtest'...

  2. run

    [root@VM_221_168_centos sysbench-0.4.12.7]# vim result_1.log 
[root@VM_221_168_centos sysbench-0.4.12.7]# ./sysbench/sysbench --test=oltp --oltp-table-size=80000000 --oltp-read-only=off --init-rng=on --num-threads=16  --max-requests=0 --oltp-dist-type00000 --oltp-read-only=off --init-rng=on --num-threads=16  --max-requests=0 --oltp-dist-type=uniform --max-time=3600 --db-driver=mysql --mysql-user=root --mysql-password='' --mysql-db=test  --mysql-socket=/var/lib/mysql/mysql.sock  run > result_1.log
[root@VM_221_168_centos sysbench-0.4.12.7]# ls
acinclude.m4  autogen.sh      ChangeLog  config.log     configure     COPYING  INSTALL     libtool   Makefile.am  missing        README          result_1.log  TODO
aclocal.m4    autom4te.cache  config     config.status  configure.ac  doc      install-sh  Makefile  Makefile.in  mkinstalldirs  README-WIN.txt  sysbench
[root@VM_221_168_centos sysbench-0.4.12.7]#

  3. mysql参数

    mysql> show variables like '%size%';
+--------------------------------------------------------+----------------------+
| Variable_name                                          | Value                |
+--------------------------------------------------------+----------------------+
| binlog_cache_size                                      | 32768                |
| binlog_stmt_cache_size                                 | 32768                |
| bulk_insert_buffer_size                                | 8388608              |
| delayed_queue_size                                     | 1000                 |
| host_cache_size                                        | 279                  |
| innodb_additional_mem_pool_size                        | 8388608              |
| innodb_buffer_pool_size                                | 134217728            |
| innodb_change_buffer_max_size                          | 25                   |
| innodb_ft_cache_size                                   | 8000000              |
| innodb_ft_max_token_size                               | 84                   |
| innodb_ft_min_token_size                               | 3                    |
| innodb_ft_total_cache_size                             | 640000000            |
| innodb_log_buffer_size                                 | 8388608              |
| innodb_log_file_size                                   | 50331648             |
| innodb_online_alter_log_max_size                       | 134217728            |
| innodb_page_size                                       | 16384                |
| innodb_purge_batch_size                                | 300                  |
| innodb_sort_buffer_size                                | 1048576              |
| innodb_sync_array_size                                 | 1                    |
| join_buffer_size                                       | 262144               |
| key_buffer_size                                        | 8388608              |
| key_cache_block_size                                   | 1024                 |
| large_page_size                                        | 0                    |
| max_binlog_cache_size                                  | 18446744073709547520 |
| max_binlog_size                                        | 1073741824           |
| max_binlog_stmt_cache_size                             | 18446744073709547520 |
| max_heap_table_size                                    | 16777216             |
| max_join_size                                          | 18446744073709551615 |
| max_relay_log_size                                     | 0                    |
| metadata_locks_cache_size                              | 1024                 |
| myisam_data_pointer_size                               | 6                    |
| myisam_max_sort_file_size                              | 9223372036853727232  |
| myisam_mmap_size                                       | 18446744073709551615 |
| myisam_sort_buffer_size                                | 8388608              |
| optimizer_trace_max_mem_size                           | 16384                |
| performance_schema_accounts_size                       | 100                  |
| performance_schema_digests_size                        | 5000                 |
| performance_schema_events_stages_history_long_size     | 1000                 |
| performance_schema_events_stages_history_size          | 10                   |
| performance_schema_events_statements_history_long_size | 1000                 |
| performance_schema_events_statements_history_size      | 10                   |
| performance_schema_events_waits_history_long_size      | 1000                 |
| performance_schema_events_waits_history_size           | 10                   |
| performance_schema_hosts_size                          | 100                  |
| performance_schema_session_connect_attrs_size          | 512                  |
| performance_schema_setup_actors_size                   | 100                  |
| performance_schema_setup_objects_size                  | 100                  |
| performance_schema_users_size                          | 100                  |
| preload_buffer_size                                    | 32768                |
| profiling_history_size                                 | 15                   |
| query_alloc_block_size                                 | 8192                 |
| query_cache_size                                       | 1048576              |
| query_prealloc_size                                    | 8192                 |
| range_alloc_block_size                                 | 4096                 |
| read_buffer_size                                       | 131072               |
| read_rnd_buffer_size                                   | 262144               |
| slave_pending_jobs_size_max                            | 16777216             |
| sort_buffer_size                                       | 262144               |
| thread_cache_size                                      | 9                    |
| tmp_table_size                                         | 16777216             |
| transaction_alloc_block_size                           | 8192                 |
| transaction_prealloc_size                              | 4096                 |
+--------------------------------------------------------+----------------------+

    62 rows in set (0.02 sec)

  4. 国内merger server测试oltp的数据：

    sysbench 0.4.13:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 16
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored

Doing OLTP test.
Running mixed OLTP test
Using Uniform distribution
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Using 1 test tables
Threads started!
Time limit exceeded, exiting...
(last message repeated 15 times)
Done.

OLTP test statistics:
   queries performed:
       read:                            3964912
       write:                           1416040
       other:                           566416
       total:                           5947368
   transactions:                        283208 (78.67 per sec.)
   deadlocks:                           0      (0.00 per sec.)
   read/write requests:                 5380952 (1494.66 per sec.)
   other operations:                    566416 (157.33 per sec.)

Test execution summary:
   total time:                          3600.1084s
   total number of events:              283208
   total time taken by event execution: 57599.8189
   per-request statistics:
        min:                                  3.57ms
        avg:                                203.38ms
        max:                               1183.50ms
        approx.  95 percentile:             498.73ms

Threads fairness:
   events (avg/stddev):           17700.5000/35.07
   execution time (avg/stddev):   3599.9887/0.03

  5. 海外merger server测试oltp的数据：

    sysbench 0.4.13:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 16
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored

Doing OLTP test.
Running mixed OLTP test
Using Uniform distribution
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Using 1 test tables
Threads started!
Time limit exceeded, exiting...
(last message repeated 15 times)
Done.

OLTP test statistics:
   queries performed:
       read:                            2008804
       write:                           717430
       other:                           286972
       total:                           3013206
   transactions:                        143486 (39.85 per sec.)
   deadlocks:                           0      (0.00 per sec.)
   read/write requests:                 2726234 (757.23 per sec.)
   other operations:                    286972 (79.71 per sec.)

Test execution summary:
   total time:                          3600.2908s
   total number of events:              143486
   total time taken by event execution: 57602.0610
   per-request statistics:
        min:                                 64.60ms
        avg:                                401.45ms
        max:                               6108.75ms
        approx.  95 percentile:             573.04ms

Threads fairness:
   events (avg/stddev):           8967.8750/22.93
   execution time (avg/stddev):   3600.1288/0.08

  6. 海外2次测试oltp（修改innodb_flush_method=O_DIRECT）:

    sysbench 0.4.13:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 16
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored

Doing OLTP test.
Running mixed OLTP test
Using Uniform distribution
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Using 1 test tables
Threads started!
Time limit exceeded, exiting...
(last message repeated 15 times)
Done.

OLTP test statistics:
   queries performed:
       read:                            537600
       write:                           192000
       other:                           76800
       total:                           806400
   transactions:                        38400  (42.66 per sec.)
   deadlocks:                           0      (0.00 per sec.)
   read/write requests:                 729600 (810.49 per sec.)
   other operations:                    76800  (85.32 per sec.)

Test execution summary:
   total time:                          900.1910s
   total number of events:              38400
   total time taken by event execution: 14401.4218
   per-request statistics:
        min:                                131.79ms
        avg:                                375.04ms
        max:                                871.31ms
        approx.  95 percentile:             517.90ms

Threads fairness:
   events (avg/stddev):           2400.0000/5.15
   execution time (avg/stddev):   900.0889/0.05

  7. 海外3次测试(file_format=BARRACUDA，file_per_talbe=on, data_file_path=10M)：

    sysbench 0.4.13:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 16
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored

Doing OLTP test.
Running mixed OLTP test
Using Uniform distribution
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Using 1 test tables
Threads started!
Time limit exceeded, exiting...
(last message repeated 15 times)
Done.

OLTP test statistics:
   queries performed:
       read:                            545762
       write:                           194915
       other:                           77966
       total:                           818643
   transactions:                        38983  (43.31 per sec.)
   deadlocks:                           0      (0.00 per sec.)
   read/write requests:                 740677 (822.81 per sec.)
   other operations:                    77966  (86.61 per sec.)

Test execution summary:
   total time:                          900.1752s
   total number of events:              38983
   total time taken by event execution: 14401.2252
   per-request statistics:
        min:                                100.85ms
        avg:                                369.42ms
        max:                                860.14ms
        approx.  95 percentile:             509.75ms

Threads fairness:
   events (avg/stddev):           2436.4375/6.77
   execution time (avg/stddev):   900.0766/0.05

~                                                                                                                                                                                            
"result_5.log" 45L, 1514C

    2. sysbench

  8. fileio。先看下相关的参数:

    [liujinguang@VM_221_168_centos sysbench-0.4.12.7]$ sysbench --test=fileio help
sysbench 0.4.13:  multi-threaded system evaluation benchmark

fileio options:
 --file-num=N                  number of files to create [128];测试要创建的文件数目
 --file-block-size=N           block size to use in all IO operations [16384]；测试中使用的文件块的大小：16k
 --file-total-size=SIZE        total size of files to create [2G];测试中要创建的文件总大小
 --file-test-mode=STRING       test mode {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw}；测试模式：顺序写，顺序rewrite,顺序读，随机读，随机写，随机rewrite?
 --file-io-mode=STRING         file operations mode {sync,async,fastmmap,slowmmap} [sync]
 --file-extra-flags=STRING     additional flags to use on opening files {sync,dsync,direct} []
 --file-fsync-freq=N           do fsync() after this number of requests (0 - don't use fsync()) [100]
 --file-fsync-all=[on|off]     do fsync() after each write operation [off]
 --file-fsync-end=[on|off]     do fsync() at the end of test [on]
 --file-fsync-mode=STRING      which method to use for synchronization {fsync, fdatasync} [fsync]
 --file-merged-requests=N      merge at most this number of IO requests if possible (0 - don't merge) [0]
 --file-rw-ratio=N             reads/writes ratio for combined test [1.5];读写比例，没用过

    3. innodb flush method

innodb_flush_method

Command-Line Format --innodb_flush_method=name
System Variable Name    innodb_flush_method
Variable Scope  Global
Dynamic Variable    No
Permitted Values (Windows)  Type    string
Default async_unbuffered
Valid Values    normal
unbuffered
Permitted Values (Unix) Type    string
Default NULL
Valid Values    fsync
littlesync
nosync
O_DSYNC
O_DIRECT
Defines the method used to flush data to the InnoDB data files and log files, which can affect I/O throughput.

If innodb_flush_method=NULL on a Unix-like system, the fsync option is used by default. If innodb_flush_method=NULL on Windows, the async_unbuffered option is used by default.

The innodb_flush_method options for Unix-like systems include:

fsync: InnoDB uses the fsync() system call to flush both the data and log files. fsync is the default setting.

O_DSYNC: InnoDB uses O_SYNC to open and flush the log files, and fsync() to flush the data files. InnoDB does not use O_DSYNC directly because there have been problems with it on many varieties of Unix.

littlesync: This option is used for internal performance testing and is currently unsupported. Use at your own risk.

nosync: This option is used for internal performance testing and is currently unsupported. Use at your own risk.

O_DIRECT: InnoDB uses O_DIRECT (or directio() on Solaris) to open the data files, and uses fsync() to flush both the data and log files. This option is available on some GNU/Linux versions, FreeBSD, and Solaris.

The innodb_flush_method options for Windows systems include:

async_unbuffered: InnoDB uses Windows asynchronous I/O and non-buffered I/O. async_unbuffered is the default setting on Windows systems.

normal: InnoDB uses a simulated asynchronous I/O and buffered I/O. This option is used for internal performance testing and is currently unsupported. Use at your own risk.

unbuffered: InnoDB uses a simulated asynchronous I/O and non-buffered I/O. This option is used for internal performance testing and is currently unsupported. Use at your own risk.

How each settings affects performance depends on hardware configuration and workload. Benchmark your particular configuration to decide which setting to use, or whether to keep the default setting. Examine the Innodb_data_fsyncs status variable to see the overall number of fsync() calls for each setting. The mix of read and write operations in your workload can affect how a setting performs. For example, on a system with a hardware RAID controller and battery-backed write cache, O_DIRECT can help to avoid double buffering between the InnoDB buffer pool and the operating system's file system cache. On some systems where InnoDB data and log files are located on a SAN, the default value or O_DSYNC might be faster for a read-heavy workload with mostly SELECT statements. Always test this parameter with hardware and workload that reflect your production environment. For general I/O tuning advice, see Section 8.5.7, “Optimizing InnoDB Disk I/O”.

Prior to MySQL 5.1.24, the default innodb_flush_method option was named fdatasync. When fdatasync was specified, InnoDB used the fsync() system call to flush both the data and log files. To avoid confusing the fdatasync option name with the fdatasync() system call, the option name was changed to fsync in MySQL 5.1.24.

4. 优化Limit查询

  1. 目前拼的sql:

    mysql> explain select id,playerId,type,mailId,title,content,params,classification,effectTime,createTime,updateTime,invalid from email  limit 6200000,100000;
+----+-------------+-------+------+---------------+------+---------+------+----------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | Extra |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------+
|  1 | SIMPLE      | email | ALL  | NULL          | NULL | NULL    | NULL | 19179630 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------+
1 row in set (0.00 sec)

  2. order by 优化使用索引：

    mysql> explain select id,playerId,type,mailId,title,content,params,classification,effectTime,createTime,updateTime,invalid from email order by id limit 6200000,100000;
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows    | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------+
|  1 | SIMPLE      | email | index | NULL          | PRIMARY | 4       | NULL | 6300000 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------+
1 row in set (0.03 sec)
  3. 优化执行合服结果： emial从昨天的2438变为2286，节省152s。

  4. 尝试禁用查询缓存，query_cache_size=0, query_cache_type=off：结果email的合并变为了：2370s，比上次长了一些时间，看来禁用查询缓存还是有负面影响。

    接着调整query_cache-size=1G,看效果

  5. 尝试从sending data这里入手：先对比下查询数据

    [查询对比]

mysql> explain select * from email order by id limit 10000000, 100000;
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows    | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------+
|  1 | SIMPLE      | email | index | NULL          | PRIMARY | 4       | NULL | 10100000 |       |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------+
1 row in set (0.00 sec)

100000 rows in set (30.45 sec)

【韩国merger服务器】

【1】
mysql> explain select id,playerId,type,mailId,title,content,params,classification,effectTime,createTime,updateTime,invalid from email order by id limit 0,100000;
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows   | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------+
|  1 | SIMPLE      | email | index | NULL          | PRIMARY | 4       | NULL | 100000 |       |
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------+

[实际耗时]
100000 rows in set (1.00 sec)

【2】
mysql> explain select id,playerId,type,mailId,title,content,params,classification,effectTime,createTime,updateTime,invalid from email order by id limit 5000000,100000;
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows    | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------+
|  1 | SIMPLE      | email | index | NULL          | PRIMARY | 4       | NULL | 5100000 |       |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------+

[实际耗时]
100000 rows in set (20.76 sec)

【3】
mysql> explain select id,playerId,type,mailId,title,content,params,classification,effectTime,createTime,updateTime,invalid from email order by id limit 10000000,100000;
+----+-------------+-------+-------+---------------+---------+---------+------+----------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows     | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+----------+-------+
|  1 | SIMPLE      | email | index | NULL          | PRIMARY | 4       | NULL | 10100000 |       |
+----+-------------+-------+-------+---------------+---------+---------+------+----------+-------+

[实际耗时]
100000 rows in set (36.81 sec)

【使用国内merge】

[1]

mysql> explain select id,playerId,type,mailId,title,content,params,classification,effectTime,createTime,updateTime,invalid from email order by id limit 0,100000;
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows   | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------+
|  1 | SIMPLE      | email | index | NULL          | PRIMARY | 4       | NULL | 100000 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------+
1 row in set (0.00 sec)

100000 rows in set (0.41 sec)

[2]

mysql> explain select id,playerId,type,mailId,title,content,params,classification,effectTime,createTime,updateTime,invalid from email order by id limit 5000000,100000;
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows    | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------+
|  1 | SIMPLE      | email | index | NULL          | PRIMARY | 4       | NULL | 5100000 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------+

[实际耗时]
100000 rows in set (3.84 sec)

ERROR: 
No query specified

[3]

mysql> explain select id,playerId,type,mailId,title,content,params,classification,effectTime,createTime,updateTime,invalid from email order by id limit 10000000,100000;
+----+-------------+-------+-------+---------------+---------+---------+------+----------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows     | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+----------+-------+
|  1 | SIMPLE      | email | index | NULL          | PRIMARY | 4       | NULL | 10100000 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+------+----------+-------+

[实际耗时]
100000 rows in set (6.73 sec)

    5. mysql自带优化工具

  6. 慢查询日志。

  7. show profiles。展示所有性能收集。set profiling=on打开性能收集。show profile for query QUERY_ID。查看制定查询的性能分析数据。

    mysql> show profiles;
+----------+-------------+--------------------------------------------+
| Query_ID | Duration    | Query                                      |
+----------+-------------+--------------------------------------------+
|        1 |  0.00010900 | set variables slow_query_log = on          |
|        2 |  0.00008100 | set variable slow_query_log = on           |
|        3 |  0.00010500 | set  slow_query_log = on                   |
|        4 |  0.02771200 | set global  slow_query_log = on            |
|        5 |  0.00054000 | show variables like '%query%'              |
|        6 |  0.00010000 | set global long_query_time=1000000         |
|        7 |  0.00052400 | show variables like '%query%'              |
|        8 |  0.00010300 | set long_query_time=1000000                |
|        9 |  0.00052700 | show variables like '%query%'              |
|       10 |  0.00020300 | select count(*),min(id),max(id) from emial |
|       11 | 16.03535000 | select count(*),min(id),max(id) from email |
|       12 |  0.00020000 | show profiling                             |
|       13 |  0.01365000 | show status                                |
+----------+-------------+--------------------------------------------+
13 rows in set (0.00 sec)

mysql> show profile for query 11;
+--------------------------------+-----------+
| Status                         | Duration  |
+--------------------------------+-----------+
| starting                       |  0.000029 |
| checking query cache for query |  0.000054 |
| checking permissions           |  0.000011 |
| Opening tables                 |  0.000030 |
| System lock                    |  0.000043 |
| init                           |  0.000028 |
| optimizing                     |  0.011490 |
| statistics                     |  0.000016 |
| preparing                      |  0.000011 |
| executing                      |  0.000008 |
| Sending data                   | 16.023329 |
| end                            |  0.000022 |
| query end                      |  0.000007 |
| freeing items                  |  0.000246 |
| storing result in query cache  |  0.000018 |
| logging slow query             |  0.000004 |
| cleaning up                    |  0.000004 |
+--------------------------------+-----------+
17 rows in set (0.00 sec)

mysql>

    查看上面说到的韩国服的慢查询性能分析数据：

    Query_ID: 10
Duration: 38.72581100
  Query: select id,playerId,type,mailId,title,content,params,classification,effectTime,createTime,updateTime,invalid from email order by id limit 10000000,100000
10 rows in set (0.00 sec)

ERROR: 
No query specified

mysql> show profile for query 10;
+--------------------------------+-----------+
| Status                         | Duration  |
+--------------------------------+-----------+
| starting                       |  0.000022 |
| checking query cache for query |  0.000077 |
| checking permissions           |  0.000009 |
| Opening tables                 |  0.000021 |
| System lock                    |  0.000047 |
| init                           |  0.000024 |
| optimizing                     |  0.000012 |
| statistics                     |  0.000012 |
| preparing                      |  0.000012 |
| executing                      |  0.000004 |
| Sorting result                 |  0.000015 |
| Sending data                   | 38.725246 |
| end                            |  0.000014 |
| query end                      |  0.000005 |
| freeing items                  |  0.000277 |
| logging slow query             |  0.000006 |
| logging slow query             |  0.000004 |
| cleaning up                    |  0.000004 |
+--------------------------------+-----------+
18 rows in set (0.00 sec)

    给出的还不是很详细，只是说到sending data要很长时间。

  8. show status。

    6. Percona

  9. 安装percona。
    • 安装perl。yum install perl。
    • 接着执行以下命令：yum install -y perl-CPAN perl-Time-HiResyum install -y perl-CPAN perl-Time-HiRes
    • 然后到percona.com下载最新的percona-toolkit。然后tar 解压。
    • 编译安装。make && make install。
  10. 使用pt-query-digest来分析mysql的slow log。 pt-query-digest /data/mysql/data/mysql/3306/slow_query_log.log > slow_report.log