MariaDB 调优

MariaDB 调优

默认情况下mariadb为了保持通用性和方便升级等因素，很多优化策略默认是关闭状态

开启优化策略

具有SUPER权限的用户才能修改

(jlive)[isfdb]>SET optimizer_switch="mrr=on";

Query OK, 0 rows affected (0.00 sec)

(jlive)[isfdb]>SET optimizer_switch="mrr_cost_based=on";

Query OK, 0 rows affected (0.00 sec)

(jlive)[isfdb]>SET optimizer_switch="mrr_sort_keys=on";

Query OK, 0 rows affected (0.00 sec)

(jlive)[isfdb]>SELECT @@optimizer_switch \G

*************************** 1. row ***************************

@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on

1 row in set (0.01 sec)

对于optimizer_switch中的值，SET语句只会修改指定的值没有指定的保持原样

注意：上面的SET只是当前会话有效，会话断开的重连是则双恢复原样

加上GLOBAL参数后，可以一直有效直到数据库重启

SET GLOBAL optimizer_switch="mrr=on";

写入配置文件会永久生效

[mysqld]

optimizer_switch = "mrr=on, mrr_cost_based=on,mrr_sort_keys=on" 

启用extended keys(InnoDB,XtraDB)

The extended key's optimization improves the index lookups for InnoDB and XtraDB tables 

SET GLOBAL optimizer_switch='extended_keys=on';

[mysqld]

 

optimizer_switch = "extended_keys=on" 

two-step deadlock detection(Aria)

https://mariadb.com/kb/en/aria-two-step-deadlock-detection/

http://en.wikipedia.org/wiki/Deadlock

http://en.wikipedia.org/wiki/Wait-for_graph

当两个actions互相争抢同一个资源并且在等待对方结束，结果两个都没有结束而造成死锁

 SET GLOBAL deadlock_search_depth_short = 3; 

 SET GLOBAL deadlock_search_depth_long = 10; 

 SET GLOBAL deadlock_timeout_long = 10000000; 

 SET GLOBAL deadlock_timeout_short = 5000; 

[mysqld] 

 deadlock_search_depth_short = 3 

 deadlock_search_depth_long = 10 

 deadlock_timeout_long = 10000000 

 deadlock_timeout_short = 5000 

注意:

deadlock_timeout_{long,short}的单位是microseconds，10000000microseconds=10S

(jlive)[isfdb]>SHOW VARIABLES LIKE 'deadlock%';

+-----------------------------+----------+

| Variable_name               | Value    |

+-----------------------------+----------+

| deadlock_search_depth_long  | 10       |

| deadlock_search_depth_short | 3        |

| deadlock_timeout_long       | 10000000 |

| deadlock_timeout_short      | 5000     |

+-----------------------------+----------+

 

4 rows in set (0.00 sec)

segment key cache(MyISAM)

https://mariadb.com/kb/en/segmented-key-cache/

SET GLOBAL key_cache_segments = 64; 

[mysqld] 

key_cache_segments = 64 

key_cache_segments等于0是表示关闭该功能，也是默认值，当等于非零时表示开启并将值赋值给该key_cache_segments

,相关的变量还有key_buffer_size, key_cache_age_ threshold, key_cache_block_size, and key_cache_division_limit

配置thread pool

https://mariadb.com/kb/en/thread-pool/

[mysqld] 

 thread_handling = pool-of-threads 

需要重启mariadb,相关参数还有

thread_pool_stall_limit  #判定激活的线程处于stalled状态的时间，默认是500milliseconds

thread_pool_max_threads #如果判定某个线程stalled,则会生成一个新的线程，直到达到最大的线程数为止，所以一旦达到了线程池上线，管理员也无法接入mariad进行应急管理，解决办法是通过extra_port单独指定一个管理端口

Aria pagecache

[mysqld]

aria_pagecache_buffer_size = 536870912  #单位byte

aria_pagecache_age_threshold = 400

aria_pagecache_division_limit = 90 

需要重启mariadb,下面两个值可以动态加载

SET GLOBAL aria_pagecache_age_threshold = 400;  #The value is a ratio of the number of times the pagecache is accessed to the number of blocks in the pagecache.

SET GLOBAL aria_pagecache_division_limit = 90;  #the minimum percentage of the pagecache that must be warm

(jlive)[isfdb]>SHOW VARIABLES LIKE 'aria_pagecache%';

+-------------------------------+-----------+

| Variable_name                 | Value     |

+-------------------------------+-----------+

| aria_pagecache_age_threshold  | 300       |

| aria_pagecache_buffer_size    | 134217728 |

| aria_pagecache_division_limit | 100       |

| aria_pagecache_file_hash_size | 512       |

+-------------------------------+-----------+

4 rows in set (0.00 sec)

(jlive)[isfdb]>SHOW STATUS LIKE 'aria_pagecache%';

+-----------------------------------+--------+

| Variable_name                     | Value  |

+-----------------------------------+--------+

| Aria_pagecache_blocks_not_flushed | 0      |

| Aria_pagecache_blocks_unused      | 15706  |

| Aria_pagecache_blocks_used        | 1394   |

| Aria_pagecache_read_requests      | 409947 |

| Aria_pagecache_reads              | 6      |

| Aria_pagecache_write_requests     | 138592 |

| Aria_pagecache_writes             | 1394   |

+-----------------------------------+--------+

 

7 rows in set (0.00 sec)

subquery cache

这是mariadb独有特性相对于其它mysql产品而言，默认是开启的

(jlive)[isfdb]>SHOW STATUS LIKE 'subquery%';

+---------------------+-------+

| Variable_name       | Value |

+---------------------+-------+

| Subquery_cache_hit  | 0     |

| Subquery_cache_miss | 237   |

+---------------------+-------+

 

2 rows in set (0.00 sec)

semijoin subqueries

SET GLOBAL optimizer_switch='exists_to_in=on'; 

[mysqld]

optimizer_switch = 'exists_to_in=on'; 

默认情况下，semijoin=on

创建index

(jlive)[isfdb]>CREATE INDEX email ON emails(email_address(50));

Query OK, 1600 rows affected (0.01 sec)

Records: 1600  Duplicates: 0  Warnings: 0

如果已经确认某列一定唯一，则可以创建UNIQUE INDEX

CREATE UNIQUE INDEX index_name ON table_name(column_name (length)); 

(jlive)[isfdb]>SHOW INDEX FROM emails\G

*************************** 1. row ***************************

        Table: emails

   Non_unique: 0

     Key_name: PRIMARY

 Seq_in_index: 1

  Column_name: email_id

    Collation: A

  Cardinality: 1600

     Sub_part: NULL

       Packed: NULL

         Null: 

   Index_type: BTREE

      Comment: 

Index_comment: 

*************************** 2. row ***************************

        Table: emails

   Non_unique: 1

     Key_name: email

 Seq_in_index: 1

  Column_name: email_address

    Collation: A

  Cardinality: 1600

     Sub_part: 50

       Packed: NULL

         Null: YES

   Index_type: BTREE

      Comment: 

Index_comment: 

 

2 rows in set (0.00 sec)

full-text index

(jlive)[isfdb]>CREATE FULLTEXT INDEX note ON notes(note_note);

Query OK, 417609 rows affected (13.17 sec)

Records: 417609  Duplicates: 0  Warnings: 0

(jlive)[isfdb]>SHOW INDEX FROM notes\G

*************************** 1. row ***************************

        Table: notes

   Non_unique: 0

     Key_name: PRIMARY

 Seq_in_index: 1

  Column_name: note_id

    Collation: A

  Cardinality: 417609

     Sub_part: NULL

       Packed: NULL

         Null: 

   Index_type: BTREE

      Comment: 

Index_comment: 

*************************** 2. row ***************************

        Table: notes

   Non_unique: 1

     Key_name: note

 Seq_in_index: 1

  Column_name: note_note

    Collation: NULL

  Cardinality: 1

     Sub_part: NULL

       Packed: NULL

         Null: YES

   Index_type: FULLTEXT

      Comment: 

Index_comment: 

 

2 rows in set (0.00 sec)

SELECT * FROM notes WHERE MATCH(note_note) AGAINST('+artificial,+intelligence' IN BOOLEAN MODE);

删除index

(jlive)[isfdb]>DROP INDEX note ON notes;

Query OK, 417609 rows affected (0.80 sec)

 

Records: 417609  Duplicates: 0  Warnings: 0

说明：DROP INDEX实际上是调用ALTER TABLE来删除index的

(jlive)[isfdb]>SHOW INDEX FROM notes\G

*************************** 1. row ***************************

        Table: notes

   Non_unique: 0

     Key_name: PRIMARY

 Seq_in_index: 1

  Column_name: note_id

    Collation: A

  Cardinality: 417609

     Sub_part: NULL

       Packed: NULL

         Null: 

   Index_type: BTREE

      Comment: 

Index_comment: 

1 row in set (0.00 sec)

启用user statistics

SET GLOBAL userstat = 1;

[mysqld]

userstat = 1 

(jlive)[isfdb]>SET GLOBAL userstat = 1;

Query OK, 0 rows affected (0.00 sec)

(jlive)[isfdb]>SHOW VARIABLES LIKE 'userstat';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| userstat      | ON    |

+---------------+-------+

1 row in set (0.00 sec)

(jlive)[isfdb]>SHOW INDEX_STATISTICS;

+--------------+--------------+-------------+-----------+

| Table_schema | Table_name   | Index_name  | Rows_read |

+--------------+--------------+-------------+-----------+

| zabbix       | autoreg_host | PRIMARY     |         1 |

| zabbix       | hosts        | PRIMARY     |         6 |

| zabbix       | drules       | drules_1    |         2 |

| zabbix       | hosts        | hosts_2     |        48 |

| zabbix       | triggers     | PRIMARY     |       448 |

| zabbix       | functions    | functions_2 |       448 |

| zabbix       | items        | items_1     |      1956 |

+--------------+--------------+-------------+-----------+

 

7 rows in set (0.00 sec)

JOIN

(jlive)[isfdb]>SELECT author_canonical, email_address FROM authors INNER JOIN emails ON authors.author_id = emails.author_id LIMIT 3;

+----------------------+------------------------+

| author_canonical     | email_address          |

+----------------------+------------------------+

| Lois McMaster Bujold | lmbujold@mn.uswest.net |

| Orson Scott Card     | orsoncard@aol.com      |

| Joe Haldeman         | haldeman@mit.edu       |

+----------------------+------------------------+

3 rows in set (0.01 sec)

(jlive)[isfdb]>SELECT author_canonical, email_address FROM emails LEFT JOIN authors ON authors.author_id = emails.author_id LIMIT 3;

+----------------------+------------------------+

| author_canonical     | email_address          |

+----------------------+------------------------+

| Lois McMaster Bujold | lmbujold@mn.uswest.net |

| Orson Scott Card     | orsoncard@aol.com      |

| Joe Haldeman         | haldeman@mit.edu       |

+----------------------+------------------------+

 

3 rows in set (0.00 sec)

SELECT * FROM awards CROSS JOIN award_types LIMIT 10; 

日期使用微秒提高精度

(jlive)[isfdb]>CREATE DATABASE IF NOT EXISTS test;

Query OK, 1 row affected (0.00 sec)

(jlive)[isfdb]>USE test;

Database changed

(jlive)[test]>CREATE TABLE times (id int NOT NULL AUTO_INCREMENT,dt datetime(6), PRIMARY KEY (id));

Query OK, 0 rows affected (0.01 sec)

(jlive)[test]>INSERT INTO times (dt) VALUES (NOW()), (NOW(6));

Query OK, 2 rows affected (0.01 sec)

Records: 2  Duplicates: 0  Warnings: 0

(jlive)[test]>SELECT * FROM times;

+----+----------------------------+

| id | dt                         |

+----+----------------------------+

|  1 | 2016-04-04 22:40:09.000000 |

|  2 | 2016-04-04 22:40:09.730399 |

+----+----------------------------+

 

2 rows in set (0.00 sec)

自动更新日期和时间戳

CREATE TABLE dtts (

         id int(11) NOT NULL AUTO_INCREMENT,

         name varchar(25),

         dt datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),

         ts timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),

         PRIMARY KEY (id)

 

);

INSERT INTO dtts (name) VALUES

         ('Thomass'),('Gordon'),('Howard'),('Ezra');

UPDATE dtts SET name = 'Thomas'

         WHERE name = 'Thomass';

SELECT * FROM dtts;

(jlive)[test]>SELECT * FROM dtts;

+----+--------+----------------------------+-------------------------+

| id | name   | dt                         | ts                      |

+----+--------+----------------------------+-------------------------+

|  1 | Thomas | 2016-04-04 22:45:46.711699 | 2016-04-04 22:46:07.471 |

|  2 | Gordon | 2016-04-04 22:45:46.711699 | 2016-04-04 22:45:46.711 |

|  3 | Howard | 2016-04-04 22:45:46.711699 | 2016-04-04 22:45:46.711 |

|  4 | Ezra   | 2016-04-04 22:45:46.711699 | 2016-04-04 22:45:46.711 |

+----+--------+----------------------------+-------------------------+

 

4 rows in set (0.00 sec)