About MySQL

Performance_schema success stories : replication SQL thread tuning

Posted on March 27, 2014 by aadant

A lot of customers have lagging slaves. It could be one of the top issues at support, due to the infamous row based replication without primary key issue :

Bug #53375	RBR + no PK => High load on slave (table scan/cpu) => slave failure

If you use binlog_format = statement or mixed , there are several ways of monitoring the SQL thread. The most ancient is the log-slow-slave-statements variable. From 5.6.11, it is a dynamic variable, before that you had to restart the slave mysqld to enable it.

Once on, you can trace what’s going on in the SQL thread and analyze the slow query log. Of course, as the SQL thread could be running long write queries or a lot of fast queries, so it is crucial to set long_query_time = 0 for say 60 seconds to catch the fast writes. Fast does not mean that they can not be optimized :-).

From MySQL 5.6.14, there is even better. After this bug fix : Bug 16750433 – THE STATEMENT DIGEST DOES NOT SHOW THE SLAVE SQL THREAD STATEMENTS, the Performance_schema statement digests are showing the SQL thread statements as well !

So that ps_helper / mysql_sys can be used to see what’s going on in the SQL thread.

Now an real example :

If you see this kind of things : show global status like ‘Handler%’ or in the show engine innodb status :

Handler_read_next: 1,573,551,696 (551,543/sec)
Number of rows inserted 172099, updated 685966, deleted 137734, read 1574302296
32.14 inserts/s, 208.76 updates/s, 28.84 deletes/s, 547765.56 reads/s

Handler_read_next: 1,573,551,696 (551,543/sec)

Number of rows inserted 172099, updated 685966, deleted 137734, read 1574302296

32.14 inserts/s, 208.76 updates/s, 28.84 deletes/s, 547765.56 reads/s

500k index records scan per second is a bit too much for a (single) thread. No wonder that

Seconds_Behind_Master: 76162

is large and increasing.

mysql> select * from statement_analysis                             ;
+-------------------------------------------------------------------+-----------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+---------------+---------------+-------------------+------------+-----------------+-------------+-------------------+----------------------------------+---------------------+---------------------+
| query                                                             | db        | full_scan | exec_count | err_count | warn_count | total_latency | max_latency | avg_latency | lock_latency | rows_sent | rows_sent_avg | rows_examined | rows_examined_avg | tmp_tables | tmp_disk_tables | rows_sorted | sort_merge_passes | digest                           | first_seen          | last_seen           |
+-------------------------------------------------------------------+-----------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+---------------+---------------+-------------------+------------+-----------------+-------------+-------------------+----------------------------------+---------------------+---------------------+
| UPDATE t ...  c1 = ? AND c2 = ?                                   |     prod  |           |      13904 |         0 |       4896 | 00:06:06.71   | 4.75 s      | 26.37 ms    | 1.26 s       |         0 |             0 |       1492198 |               107 |          0 |               0 |           0 |                 0 | a36a0c617b2f696cb4889ee015541b65 | 2014-03-05 08:02:25 | 2014-03-05 08:13:30 |
| UPDATE t ...  c1 = ? AND c2 = ?                                   |     prod  |           |       2024 |         0 |          0 | 00:01:34.49   | 2.57 s      | 46.69 ms    | 147.32 ms    |         0 |             0 |        373785 |               185 |          0 |               0 |           0 |                 0 | 8dd4ee22a216fe5fcc03b03a34f3a3fd | 2014-03-05 08:02:36 | 2014-03-05 08:13:30 |
| UPDATE t ...  c1 = ? AND c2 = ?                                   |     prod  |           |       1662 |         0 |          0 | 00:01:31.54   | 3.31 s      | 55.08 ms    | 65.96 ms     |         0 |             0 |        317512 |               191 |          0 |               0 |           0 |                 0 | 27a13609455481e156ca9eb2e5c54d6d | 2014-03-05 08:02:53 | 2014-03-05 08:13:29 |
| UPDATE t ...  ? , c3  = IF ( ...                                  |     prod  |           |       1987 |         0 |       1064 | 41.06 s       | 1.11 s      | 20.66 ms    | 206.36 ms    |         0 |             0 |        206869 |               104 |          0 |               0 |           0 |                 0 | f812e322c0e3dfaf905fe1d624978564 | 2014-03-05 08:02:42 | 2014-03-05 08:13:29 |
| UPDATE t ...  c1 = ? AND c2 = ?                                   |     prod  |           |        680 |         0 |          0 | 39.11 s       | 1.91 s      | 57.51 ms    | 62.61 ms     |         0 |             0 |        144791 |               213 |          0 |               0 |           0 |                 0 | a9266204238e66806ca4c073650b72e5 | 2014-03-05 08:05:37 | 2014-03-05 08:13:22 |

mysql> select * from io_global_by_file_by_latency                   ;
+-------------------------------------------------------------------------------------+------------+---------------+------------+--------------+-------------+---------------+------------+--------------+
| file                                                                                | count_star | total_latency | count_read | read_latency | count_write | write_latency | count_misc | misc_latency |
+-------------------------------------------------------------------------------------+------------+---------------+------------+--------------+-------------+---------------+------------+--------------+
| @@datadir/prod/t.ibd                                                                |     244505 | 00:05:58.94   |     244319 | 00:05:58.83  |         114 | 4.56 ms       |         72 | 105.85 ms    |

mysql> select * from statement_analysis ;

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

| UPDATE t ... c1 = ? AND c2 = ? | prod | | 13904 | 0 | 4896 | 00:06:06.71 | 4.75 s | 26.37 ms | 1.26 s | 0 | 0 | 1492198 | 107 | 0 | 0 | 0 | 0 | a36a0c617b2f696cb4889ee015541b65 | 2014-03-05 08:02:25 | 2014-03-05 08:13:30 |

| UPDATE t ... c1 = ? AND c2 = ? | prod | | 2024 | 0 | 0 | 00:01:34.49 | 2.57 s | 46.69 ms | 147.32 ms | 0 | 0 | 373785 | 185 | 0 | 0 | 0 | 0 | 8dd4ee22a216fe5fcc03b03a34f3a3fd | 2014-03-05 08:02:36 | 2014-03-05 08:13:30 |

| UPDATE t ... c1 = ? AND c2 = ? | prod | | 1662 | 0 | 0 | 00:01:31.54 | 3.31 s | 55.08 ms | 65.96 ms | 0 | 0 | 317512 | 191 | 0 | 0 | 0 | 0 | 27a13609455481e156ca9eb2e5c54d6d | 2014-03-05 08:02:53 | 2014-03-05 08:13:29 |

| UPDATE t ... ? , c3 = IF ( ... | prod | | 1987 | 0 | 1064 | 41.06 s | 1.11 s | 20.66 ms | 206.36 ms | 0 | 0 | 206869 | 104 | 0 | 0 | 0 | 0 | f812e322c0e3dfaf905fe1d624978564 | 2014-03-05 08:02:42 | 2014-03-05 08:13:29 |

| UPDATE t ... c1 = ? AND c2 = ? | prod | | 680 | 0 | 0 | 39.11 s | 1.91 s | 57.51 ms | 62.61 ms | 0 | 0 | 144791 | 213 | 0 | 0 | 0 | 0 | a9266204238e66806ca4c073650b72e5 | 2014-03-05 08:05:37 | 2014-03-05 08:13:22 |

mysql> select * from io_global_by_file_by_latency ;

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

| @@datadir/prod/t.ibd | 244505 | 00:05:58.94 | 244319 | 00:05:58.83 | 114 | 4.56 ms | 72 | 105.85 ms |

The slave was up for 12 min and nearly 10 min was spent on the updates ! 18000 updates in 10 minutes, that’s fast 🙂 So do we need to buy better hardware ?

No. Average rows examined is between 100 – 200. Maybe we can optimize it ?

mysql> explain update t set c3 = 1 where c1=6577849127655212045 and c2 = '2014-03-04'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: index_merge
possible_keys: idx_c1, idx_c2
          key: idx_c1, idx_c2
      key_len: 8,3
          ref: NULL
         rows: 3
        Extra: Using intersect(idx_c1,idx_c2); Using where
1 row in set (0.00 sec)

mysql> explain update t set c3 = 1 where c1=6577849127655212045 and c2 = '2014-03-04'\G

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

id: 1

select_type: SIMPLE

table: t

type: index_merge

possible_keys: idx_c1, idx_c2

key: idx_c1, idx_c2

key_len: 8,3

ref: NULL

rows: 3

Extra: Using intersect(idx_c1,idx_c2); Using where

1 row in set (0.00 sec)

Indeed, adding an index on (c1,c2) fixed the issue. And cut by 100 the rows examined average. After one hour, the slave that was lagging 1 day had caught up.

This is the MEM graph for the rows_examined.

Thank you to Hauns Froehlingsdorf who worked with me on this issue.

Things to note here :

the performance_schema rocks for statement replication. I truly hope that row based replication will get the instrumentation it deserves. Maybe row events could appear as statements ? That’s how they are processed by the parser when you do point-in-time recovery for example …
index merge used for updates is much more expensive that it seems : it is not certainly not 3 rows_examined, not 100-200 much more here. Index entries are locked for 2 index ranges, so more locks in memory in REPEATABLE READS isolation at least. Here are the 2 bugs for completeness :
Bug 14226987 – ROWS_EXAMINED IS NOT CORRECT FOR INDEX MERGE QUERIES
Bug 14226171 – EXCESSIVE ROW LOCKING WITH UPDATE IN 5.5.25

Posted in Uncategorized | Leave a comment

Performance_schema success stories : host summary tables

Posted on March 19, 2014 by aadant

This question was asked at support by a customer to solve a difficult issue.

How to identify a sporadic burst of queries coming from one of the hosts accessing the database ?

If there are hundreds of hosts, it can be challenging, especially if the queries are fast. No chance for them to get logged in the famous slow query log !

Here is the solution using the performance_schema in MySQL 5.6 :

SELECT
host,
SUM(essbben.count_star) AS total_statements,
format_time(SUM(essbben.sum_timer_wait)) AS total_latency,
format_time(SUM(essbben.sum_timer_wait) / SUM(count_star))
AS avg_latency
FROM
performance_schema.events_statements_summary_by_host_by_event_name essbben
GROUP BY
host
ORDER BY
SUM(sum_timer_wait) DESC;

SELECT

host,

SUM(essbben.count_star) AS total_statements,

format_time(SUM(essbben.sum_timer_wait)) AS total_latency,

format_time(SUM(essbben.sum_timer_wait) / SUM(count_star))

AS avg_latency

FROM

performance_schema.events_statements_summary_by_host_by_event_name essbben

GROUP BY

host

ORDER BY

SUM(sum_timer_wait) DESC;

Here is the result :

+---------------+------------------+---------------+-------------+
| host          | total_statements | total_latency | avg_latency |
+---------------+------------------+---------------+-------------+
|     localhost |            30791 |         1.22h |   143.19 ms | 
|         host1 |          6576077 |         1.14h |   622.17 us |
|         host2 |            53531 |   00:28:56.92 |   324.46 us |
...

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

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

| localhost | 30791 | 1.22h | 143.19 ms |

| host1 | 6576077 | 1.14h | 622.17 us |

| host2 | 53531 | 00:28:56.92 | 324.46 us |

...

Note that if you use the thread pool plugin, you need to upgrade to 5.6.15 or later because of this bug : Bug 17049691 : PROCESSLIST_USER AND PROCESSLIST_HOST ARE ALWAYS NULL WITH THREAD_POOL.

If you want to determine when the burst occurs, you can create an event or a cron job to load the results in a table like this :

create table log_host
(snapshot_time datetime, host varchar(60),  total_statements bigint, total_latency bigint) engine=InnoDB;
-- script to run to archive the snapshot
set @now = (select NOW());
insert into log_host(snapshot_time, host, total_statements, total_latency) SELECT @now, host,
   SUM(essbben.count_star) AS total_statements,
   SUM(essbben.sum_timer_wait) AS total_latency
   FROM
   performance_schema.events_statements_summary_by_host_by_event_name essbben
   WHERE 1 = 1
   GROUP BY host
   ORDER BY SUM(sum_timer_wait) DESC;

create table log_host

(snapshot_time datetime, host varchar(60), total_statements bigint, total_latency bigint) engine=InnoDB;

-- script to run to archive the snapshot

set @now = (select NOW());

insert into log_host(snapshot_time, host, total_statements, total_latency) SELECT @now, host,

SUM(essbben.count_star) AS total_statements,

SUM(essbben.sum_timer_wait) AS total_latency

FROM

performance_schema.events_statements_summary_by_host_by_event_name essbben

WHERE 1 = 1

GROUP BY host

ORDER BY SUM(sum_timer_wait) DESC;

I created a pull request for mysql-sys, so that host based P_S tables can be accessed using sys views and possibly from MySQL Workbench 6.1.

Posted in performance_schema, success stories, tip, Uncategorized | Leave a comment

50 tips to boost MySQL Performance Webinar follow up

Posted on March 7, 2014 by aadant

Thank you for attending the webinar ! Here are the ppt slides.

If you missed it, you can still join the archived event by clicking the URL below.
http://w.on24.com/r.htm?e=748845&s=1&k=171F8C0CECD105B0F4ED721CA6F2C704

There were a lot of attendees and a lot of questions. I could not answer everything during the limited time. But here are finally the answers !

Question	Answer
Can MEM be used on community version?	Yes, of course.
any known problems for MySQL running on an overprovisioned VMWare environment….for instance, the VMWare admins over allocate CPU for all the Vms?	Yes, a busy MySQL server needs cores, memory and fast storage and network. If these ressources are shared by busy servers then performance drops dramatically.
what makes XFS better over EXT4 ?	XFS works better with O_DIRECT. XFS had historically better support for SSD (TRIM). See http://dimitrik.free.fr/blog/archives/2012/01/mysql-performance-linux-io.html
Does sync_binlog=1 effect on write performance or read performance	Write performance only (fsyncs)
What are the disadvantage of setting wait_timeout and interactive_timeout value to 1 ?	Any idle query sleeping longer than 1s will be interrupted.
You focus on innoDB, is there no case where MyISAM would be preferable?	Not really. MyISAM uses the OS to cache the data and requires a lot of system calls. MySQL 5.6 introduced read only transactions and full text indexes for InnoDB. It can only think of spatial indexes.
what monitor instruments you recommend in mac os x system.	On Mac OS X, you can install MEM to monitor the database. The performance_schema and ps_helper will also work on any platform. DTrace can be helpful too.
How do i check Qcache_free_blocks?	show global status like ‘Qcache_free_blocks’;
How Table Partitioning effect the performance on read and write ?	Partitioning can improve read and write performance. But it requires more tuning than a normal table. The table and query designs are critical. I would recommend to use as little partitions as possible, use pruning for all queries. Partitioning is the best way to delete useless rows (drop or truncate partitions). If a query does not use pruning, then it is more expensive in terms of locking and table access, especially range index scans. Partitioning is good for big data when the insertion rate is limited by the table size.
Are there any recommendations for running MySQL on ESX?	see http://www.vmware.com/files/pdf/Virtualization-for-MySQL-on-VMware.pdf
what is the scope of mysql dba in market as per current trends?	I can not answer this question. I know that there is a high demand for MySQL DBAs worldwide
Sir I have E-5 family server getting slow while fetching 1 row from table
any tips to make it more faster, Using MYISAM	It is hard to tell. Again I recommend InnoDB. If you can, please open a support request or submit your question to http://forums.mysql.com/

Posted in conference, tip, Uncategorized | 2 Comments

How to calculate a specific InnoDB index size ?

Posted on February 4, 2014 by aadant

MySQL provides commands to see the overall index size versus the data size.

One of them is “show table status” :

mysql> show table status like 't'\G
*************************** 1. row ***************************
           Name: t
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 4186170
 Avg_row_length: 34
    Data_length: 143310848
Max_data_length: 0
   Index_length: 146030592
      Data_free: 6291456
 Auto_increment: NULL
    Create_time: 2014-02-04 15:40:54
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> show table status like 't'\G

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

Name: t

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 4186170

Avg_row_length: 34

Data_length: 143310848

Max_data_length: 0

Index_length: 146030592

Data_free: 6291456

Auto_increment: NULL

Create_time: 2014-02-04 15:40:54

Update_time: NULL

Check_time: NULL

Collation: latin1_swedish_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

So here, we have these “estimations”, run ANALYZE TABLE before to get more accurate estimates :

Data_length: 143310848, 136Mb clustered index size.

Index_length: 146030592, 139Mb secondary index size.

In this example, I have 3 indexes : 1 auto-generated clustered index and 2 secondary indexes.

 CREATE TABLE `t` (
  `a` smallint(6) DEFAULT NULL,
  `b` smallint(6) DEFAULT NULL,
  `c` smallint(6) DEFAULT NULL,
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `t` (

`a` smallint(6) DEFAULT NULL,

`b` smallint(6) DEFAULT NULL,

`c` smallint(6) DEFAULT NULL,

KEY `a` (`a`),

KEY `b` (`b`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

In this case, having the index_length, it is easy to guess the index size of each secondary index. In the general case, it is possible to drop secondary indexes one by one, optimize and see the change in index_length …

From 5.6, there is a better way : this is the hidden 5.6 gem I want to share with you :

ANALYZE table t;
SELECT
       sum(stat_value) pages,
       index_name,
       sum(stat_value) * @@innodb_page_size size
FROM
       mysql.innodb_index_stats
WHERE
           table_name = 't'
       AND database_name = 'test'
       AND stat_description = 'Number of pages in the index'
GROUP BY
       index_name;

+-------+-----------------+-----------+
| pages | index_name      | size      |
+-------+-----------------+-----------+
|  8747 | GEN_CLUST_INDEX | 143310848 |
|  4456 | a               |  73007104 |
|  4457 | b               |  73023488 |
+-------+-----------------+-----------+
3 rows in set (0.00 sec)

ANALYZE table t;

SELECT

sum(stat_value) pages,

index_name,

sum(stat_value) * @@innodb_page_size size

FROM

mysql.innodb_index_stats

WHERE

table_name = 't'

AND database_name = 'test'

AND stat_description = 'Number of pages in the index'

GROUP BY

index_name;

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

| pages | index_name | size |

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

| 8747 | GEN_CLUST_INDEX | 143310848 |

| 4456 | a | 73007104 |

| 4457 | b | 73023488 |

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

3 rows in set (0.00 sec)

Using default options, MySQL 5.6 now computes table and index statistics “on the fly” and persist them in these 2 tables : mysql.innodb_table_stats and mysql.innodb_index_stats.

If you need exact values, run ANALYZE TABLE before running the query ! The stats could be out of date due to a (dynamic) configuration change or if the table is just being heavily updated.

Regarding index stats, each index has a stat called ‘Number of pages in the index‘. The index size can be obtained by multiplying this value by the InnoDB page size.

More details on index stats can be found in this MySQL optimizer blog by my colleague Oystein : http://oysteing.blogspot.co.uk/2011/05/innodb-persistent-statistics-save-day.html. The MySQL manual should also soon be updated with this useful information.

Note that the tip also works with partitioned table :

mysql> alter table t partition by key(c) partitions 4;
Query OK, 4194308 rows affected (44.03 sec)
Records: 4194308  Duplicates: 0  Warnings: 0

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` smallint(6) DEFAULT NULL,
  `b` smallint(6) DEFAULT NULL,
  `c` smallint(6) DEFAULT NULL,
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (c)
PARTITIONS 4 */
1 row in set (0.01 sec)

ANALYZE TABLE t;

SELECT
       sum(stat_value) pages,
       index_name,
       sum(stat_value) * @@innodb_page_size size
FROM
       mysql.innodb_index_stats
WHERE
           table_name LIKE 't#P%'
       AND database_name = 'test'
       AND stat_description LIKE 'Number of pages in the index'
GROUP BY
       index_name;

+-------+-----------------+-----------+
| pages | index_name      | size      |
+-------+-----------------+-----------+
|  8848 | GEN_CLUST_INDEX | 144965632 |
|  5004 | a               |  81985536 |
|  5004 | b               |  81985536 |
+-------+-----------------+-----------+
3 rows in set (0.00 sec)

mysql> SELECT
       sum(stat_value) pages,
       table_name part,
       index_name,
       sum(stat_value) * @@innodb_page_size size
FROM
       mysql.innodb_index_stats
WHERE
           table_name LIKE 't#P#%'
       AND database_name = 'test'
       AND stat_description LIKE 'Number of pages in the index'
GROUP BY
       table_name, index_name;

+-------+--------+-----------------+----------+
| pages | part   | index_name      | size     |
+-------+--------+-----------------+----------+
|  2212 | t#P#p0 | GEN_CLUST_INDEX | 36241408 |
|  1251 | t#P#p0 | a               | 20496384 |
|  1251 | t#P#p0 | b               | 20496384 |
|  2212 | t#P#p1 | GEN_CLUST_INDEX | 36241408 |
|  1251 | t#P#p1 | a               | 20496384 |
|  1251 | t#P#p1 | b               | 20496384 |
|  2212 | t#P#p2 | GEN_CLUST_INDEX | 36241408 |
|  1251 | t#P#p2 | a               | 20496384 |
|  1251 | t#P#p2 | b               | 20496384 |
|  2212 | t#P#p3 | GEN_CLUST_INDEX | 36241408 |
|  1251 | t#P#p3 | a               | 20496384 |
|  1251 | t#P#p3 | b               | 20496384 |
+-------+--------+-----------------+----------+
12 rows in set (0.00 sec)

mysql> alter table t partition by key(c) partitions 4;

Query OK, 4194308 rows affected (44.03 sec)

Records: 4194308 Duplicates: 0 Warnings: 0

mysql> show create table t\G

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

Table: t

Create Table: CREATE TABLE `t` (

`a` smallint(6) DEFAULT NULL,

`b` smallint(6) DEFAULT NULL,

`c` smallint(6) DEFAULT NULL,

KEY `a` (`a`),

KEY `b` (`b`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/*!50100 PARTITION BY KEY (c)

PARTITIONS 4 */

1 row in set (0.01 sec)

ANALYZE TABLE t;

SELECT

sum(stat_value) pages,

index_name,

sum(stat_value) * @@innodb_page_size size

FROM

mysql.innodb_index_stats

WHERE

table_name LIKE 't#P%'

AND database_name = 'test'

AND stat_description LIKE 'Number of pages in the index'

GROUP BY

index_name;

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

| pages | index_name | size |

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

| 8848 | GEN_CLUST_INDEX | 144965632 |

| 5004 | a | 81985536 |

| 5004 | b | 81985536 |

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

3 rows in set (0.00 sec)

mysql> SELECT

sum(stat_value) pages,

table_name part,

index_name,

sum(stat_value) * @@innodb_page_size size

FROM

mysql.innodb_index_stats

WHERE

table_name LIKE 't#P#%'

AND database_name = 'test'

AND stat_description LIKE 'Number of pages in the index'

GROUP BY

table_name, index_name;

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

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

| 2212 | t#P#p0 | GEN_CLUST_INDEX | 36241408 |

| 1251 | t#P#p0 | a | 20496384 |

| 1251 | t#P#p0 | b | 20496384 |

| 2212 | t#P#p1 | GEN_CLUST_INDEX | 36241408 |

| 1251 | t#P#p1 | a | 20496384 |

| 1251 | t#P#p1 | b | 20496384 |

| 2212 | t#P#p2 | GEN_CLUST_INDEX | 36241408 |

| 1251 | t#P#p2 | a | 20496384 |

| 1251 | t#P#p2 | b | 20496384 |

| 2212 | t#P#p3 | GEN_CLUST_INDEX | 36241408 |

| 1251 | t#P#p3 | a | 20496384 |

| 1251 | t#P#p3 | b | 20496384 |

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

12 rows in set (0.00 sec)

Posted in tip | Leave a comment

A small optimizer change worth noticing

Posted on December 20, 2013 by aadant

MySQL uses internal temporary tables to execute some queries. Usually the tables are stored in memory and on disk if some conditions are met :

Some conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:

Presence of a BLOB or TEXT column in the table
Presence of any string column in a GROUP BY or DISTINCT clause larger than 512 bytes
Presence of any string column with a maximum length larger than 512 (bytes for binary strings, characters for nonbinary strings) in the SELECT list, if UNION or UNION ALL is used

http://dev.mysql.com/doc/refman/5.6/en/internal-temporary-tables.html

This was true until MySQL 5.6.15. A voluntary side effect of this bug fix :

Bug 17566396 – MATERIALIZATION IS NOT CHOSEN FOR LONG UTF8 VARCHAR JOINS

was to loosen the condition on the second item. You can now use up to varchar(512) whatever the character set (utf8, utf8mb4) in group by / distinct and of course in joins. I often see varchar(255), usually in latin1 and more and more in utf8.

Here is an example :

USE test;
DROP TABLE IF EXISTS t;
CREATE TABLE t(txt varchar(255)) ENGINE=InnoDB CHARSET = utf8;
INSERT INTO t(txt) VALUES (repeat('0', 255));
FLUSH STATUS;
SHOW SESSION STATUS LIKE '%tmp%';
SELECT DISTINCT txt FROM t;
SHOW SESSION STATUS LIKE '%tmp%';

USE test;

DROP TABLE IF EXISTS t;

CREATE TABLE t(txt varchar(255)) ENGINE=InnoDB CHARSET = utf8;

INSERT INTO t(txt) VALUES (repeat('0', 255));

FLUSH STATUS;

SHOW SESSION STATUS LIKE '%tmp%';

SELECT DISTINCT txt FROM t;

SHOW SESSION STATUS LIKE '%tmp%';

Result in 5.6.14 :

mysql> show session status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     |   <==== goes to disk ...
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 1     | 
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> show session status like '%tmp%';

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

| Variable_name | Value |

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

| Created_tmp_disk_tables | 1 | <==== goes to disk ...

| Created_tmp_files | 0 |

| Created_tmp_tables | 1 |

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

3 rows in set (0.00 sec)

Result in 5.6.15 :

mysql> show session status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     | <=== goes to memory !
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> show session status like '%tmp%';

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

| Variable_name | Value |

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

| Created_tmp_disk_tables | 0 | <=== goes to memory !

| Created_tmp_files | 0 |

| Created_tmp_tables | 1 |

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

3 rows in set (0.00 sec)

This is the internal bug for the documentation change request :

Bug 17935006 – INTERNAL TEMPORARY TABLE LIMITATIONS CHANGED IN 5.6.15

A big thank to the Optimizer team for fixing this very ancient restriction !

Posted in tip | Leave a comment

Poor man’s Online Optimize in 5.6

Posted on September 30, 2013 by aadant

Table space fragmentation has generally 2 origins :

File System fragmentation : the data file is spread physically on many non contiguous locations on the disk.
Internal Fragmentation : the data and index pages have “holes” : this happens when rows are deleted or updated, especially at random.

As a result, performance is affected by table space fragmentation. Data typically takes more space on disk and in memory. The disk is more busy than it should.

File System fragmentation can be detected using the filefrag command on Linux (and similar on different OS). When using MyISAM, MYI files are usually very fragmented on the FS, much more than the MYD files.

 ls -al frag
total 883304
drwx------  2 aadant common      4096 Sep 30 18:41 .
drwxr-xr-x 17 aadant common      4096 Sep 30 18:59 ..
-rw-rw----  1 aadant common        65 Sep 30 18:40 db.opt
-rw-rw----  1 aadant common      8608 Sep 30 18:41 t.frm
-rw-rw----  1 aadant common 551944192 Sep 30 19:41 t.MYD
-rw-rw----  1 aadant common 426150912 Sep 30 19:41 t.MYI
filefrag frag/t.MYD
frag/t.MYD: 23 extents found
filefrag frag/t.MYI
frag/t.MYI: 4949 extents found

ls -al frag

total 883304

drwx------ 2 aadant common 4096 Sep 30 18:41 .

drwxr-xr-x 17 aadant common 4096 Sep 30 18:59 ..

-rw-rw---- 1 aadant common 65 Sep 30 18:40 db.opt

-rw-rw---- 1 aadant common 8608 Sep 30 18:41 t.frm

-rw-rw---- 1 aadant common 551944192 Sep 30 19:41 t.MYD

-rw-rw---- 1 aadant common 426150912 Sep 30 19:41 t.MYI

filefrag frag/t.MYD

frag/t.MYD: 23 extents found

filefrag frag/t.MYI

frag/t.MYI: 4949 extents found

To measure internal InnoDB fragmentation, there is no general formula. It is possible to compute the theoretical size and compare it to the actual size on disk. It only works on fixed length data types. I have this trick : it is mentioned in 50 Tips for Boosting MySQL Performance [CON2655]. The idea is to compare the fragmented table and a much smaller table having the same table definition and sample data.
It works well when the average row length can be estimated from the sample data even for variable length rows.

create table t_defrag like t; 
insert into t_defrag select * from t limit 20000;

1 2	create table t_defrag like t; insert into t_defrag select * from t limit 20000;

Here 20000 rows 15-20 Mb on disk gives a good idea. Inserting the sample data the same empty table actually defragments the sample, since it contains non empty rows.

The table is likely internally fragmented

if Avg_row_length(t) > Avg_row_length(t_defrag)

where Avg_row_length comes from show table status.

Example : some random data is created, uniformly distributed random row length : 1M rows, 640Mb. Internal fragmentation is created by deleting the even rows (one every two rows).

drop database test_case2;
create database test_case2;
use test_case2;
set global query_cache_size = 0;
set global innodb_flush_log_at_trx_commit = 2;
drop table if exists t1;
create table t1(
id int primary key auto_increment,
k  varchar(50),
value varchar(1000), unique key(k)) engine=InnoDB;
set @t1 =(select now());
set @i := 1;
insert into t1(k,value) values(@i:=@i+1,repeat('a',rand()*1000)); 
insert into t1(k,value) values(@i:=@i+1,repeat('a',rand()*1000)); 
replace into t1(k,value) select @i:=@i+1,repeat('a',rand()*1000) from t1 t1, t1 t2, t1 t3, t1 t4, t1 t5, t1 t6, t1 t7, t1 t8, t1 t9, t1 t10, t1 t11, t1 t12, t1 t13, t1 t14, t1 t15, t1 t16, t1 t17,
t1 t18, t1 t19, t1 t20;
set @t2 =(select now());
select @@version,timediff(@t2,@t1) duration;

analyze table t1\G
show table status like 't1'\G
delete from t1 where mod(id,2) = 0; 
show table status like 't1'\G
drop table if exists t1_defrag;
create table t1_defrag like t1; 
insert into t1_defrag select * from t1 limit 20000;
analyze table t1\G
analyze table t1_defrag\G
show table status like 't1'\G
show table status like 't1_defrag'\G

drop database test_case2;

create database test_case2;

use test_case2;

set global query_cache_size = 0;

set global innodb_flush_log_at_trx_commit = 2;

drop table if exists t1;

create table t1(

id int primary key auto_increment,

k varchar(50),

value varchar(1000), unique key(k)) engine=InnoDB;

set @t1 =(select now());

set @i := 1;

insert into t1(k,value) values(@i:=@i+1,repeat('a',rand()*1000));

replace into t1(k,value) select @i:=@i+1,repeat('a',rand()*1000) from t1 t1, t1 t2, t1 t3, t1 t4, t1 t5, t1 t6, t1 t7, t1 t8, t1 t9, t1 t10, t1 t11, t1 t12, t1 t13, t1 t14, t1 t15, t1 t16, t1 t17,

t1 t18, t1 t19, t1 t20;

set @t2 =(select now());

select @@version,timediff(@t2,@t1) duration;

analyze table t1\G

show table status like 't1'\G

delete from t1 where mod(id,2) = 0;

show table status like 't1'\G

drop table if exists t1_defrag;

create table t1_defrag like t1;

insert into t1_defrag select * from t1 limit 20000;

analyze table t1\G

analyze table t1_defrag\G

show table status like 't1'\G

show table status like 't1_defrag'\G

mysql> show table status like 't1'\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 476676
 Avg_row_length: 1293
    Data_length: 616562688
Max_data_length: 0
   Index_length: 37257216
      Data_free: 5242880
 Auto_increment: 1114098
    Create_time: 2013-09-30 15:19:01
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.01 sec)

mysql> show table status like 't1_defrag'\G
*************************** 1. row ***************************
           Name: t1_defrag
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 19773
 Avg_row_length: 610
    Data_length: 12075008
Max_data_length: 0
   Index_length: 1589248
      Data_free: 4194304
 Auto_increment: 40000
    Create_time: 2013-09-30 15:23:38
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.01 sec)

mysql> show table status like 't1'\G

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

Name: t1

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 476676

Avg_row_length: 1293

Data_length: 616562688

Max_data_length: 0

Index_length: 37257216

Data_free: 5242880

Auto_increment: 1114098

Create_time: 2013-09-30 15:19:01

Update_time: NULL

Check_time: NULL

Collation: latin1_swedish_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.01 sec)

mysql> show table status like 't1_defrag'\G

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

Name: t1_defrag

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 19773

Avg_row_length: 610

Data_length: 12075008

Max_data_length: 0

Index_length: 1589248

Data_free: 4194304

Auto_increment: 40000

Create_time: 2013-09-30 15:23:38

Update_time: NULL

Check_time: NULL

Collation: latin1_swedish_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.01 sec)

ls -al data/test_case2/t1*
-rw-rw---- 1 aadant common      8612 Sep 30 20:39 data/test_case2/t1_defrag.frm
-rw-rw---- 1 aadant common  22020096 Sep 30 20:39 data/test_case2/t1_defrag.ibd
-rw-rw---- 1 aadant common      8612 Sep 30 20:40 data/test_case2/t1.frm
-rw-rw---- 1 aadant common 671088640 Sep 30 20:44 data/test_case2/t1.ibd

ls -al data/test_case2/t1*

-rw-rw---- 1 aadant common 8612 Sep 30 20:39 data/test_case2/t1_defrag.frm

-rw-rw---- 1 aadant common 22020096 Sep 30 20:39 data/test_case2/t1_defrag.ibd

-rw-rw---- 1 aadant common 8612 Sep 30 20:40 data/test_case2/t1.frm

-rw-rw---- 1 aadant common 671088640 Sep 30 20:44 data/test_case2/t1.ibd

As you can see, the table t1 is fragmented since its average row length is 2 times larger than t1_defrag.

Fortunately, there is a command that fixes both FS and internal fragmentation on most storage engines :

optimize table t1;

1	optimize table t1;

For InnoDB, optimize does rebuild the table and analyze it. So this command also works :

alter table t1 engine=InnoDB;

1	alter table t1 engine=InnoDB;

mysql> alter table t1 engine=InnoDB;
Query OK, 524289 rows affected (5 min 58.99 sec)
Records: 524289  Duplicates: 0  Warnings: 0

mysql> show table status like 't1'\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 500516
 Avg_row_length: 611
    Data_length: 305971200
Max_data_length: 0
   Index_length: 18399232
      Data_free: 5242880
 Auto_increment: 1048578
    Create_time: 2013-09-30 19:53:47
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.26 sec)

mysql> alter table t1 engine=InnoDB;

Query OK, 524289 rows affected (5 min 58.99 sec)

Records: 524289 Duplicates: 0 Warnings: 0

mysql> show table status like 't1'\G

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

Name: t1

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 500516

Avg_row_length: 611

Data_length: 305971200

Max_data_length: 0

Index_length: 18399232

Data_free: 5242880

Auto_increment: 1048578

Create_time: 2013-09-30 19:53:47

Update_time: NULL

Check_time: NULL

Collation: latin1_swedish_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.26 sec)

After the table rebuild, the average row length is back to normal and the table size is minimal on disk, here 2 times smaller, 324Mb.

 ls -al data/test_case2/t1*
-rw-rw---- 1 aadant common      8612 Sep 30 20:35 data/test_case2/t1.frm
-rw-rw---- 1 aadant common 339738624 Sep 30 20:37 data/test_case2/t1.ibd

ls -al data/test_case2/t1*

-rw-rw---- 1 aadant common 8612 Sep 30 20:35 data/test_case2/t1.frm

-rw-rw---- 1 aadant common 339738624 Sep 30 20:37 data/test_case2/t1.ibd

Note that the optimize and alter table engine=InnoDB commands take an exclusive lock on the table.

Update : from MySQL 5.6.17, optimize and alter table engine=InnoDB are online operations. See this blog and the MySQL manual for more information.

http://mysqlserverteam.com/mysql-5-6-17-improved-online-optimize-table-for-innodb-and-partitioned-innodb-tables/

It means that if you use these commands before MySQL 5.6.17, your application : reads, writes, other DDL will be blocked during the table rebuild. So most of the time these commands were run in maintenance windows. Or using special tricks such as pt-online-schema-change or Online Schema Change at Facebook.

In MySQL 5.6, this is no longer required in most cases thanks to InnoDB online DDL. Online DDL is a major 5.6 feature. Even though optimize table is not an online operation, in practice, you can use this online DDL :

alter table t1 row_format = <row format>;

1	alter table t1 row_format = <row format>;

where <row_format> is given by show table status. Usually :

alter table t1 row_format = Compact;

1	alter table t1 row_format = Compact;

Make sure :

you have enough disk space, otherwise you may run into this : Bug #68895 Various assertions and crashes when running out of space
innodb_online_alter_log_max_size is large enough

Check also : innodb_sort_buffer_size that can speed up online DDL.

 alter table t1 row_format=Compact;
Query OK, 0 rows affected (5 min 26.53 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show table status like 't1'\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 502312
 Avg_row_length: 613
    Data_length: 308068352
Max_data_length: 0
   Index_length: 9977856
      Data_free: 0
 Auto_increment: 1114098
    Create_time: 2013-09-30 20:53:41
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: row_format=COMPACT
        Comment:
1 row in set (0.02 sec)

alter table t1 row_format=Compact;

Query OK, 0 rows affected (5 min 26.53 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> show table status like 't1'\G

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

Name: t1

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 502312

Avg_row_length: 613

Data_length: 308068352

Max_data_length: 0

Index_length: 9977856

Data_free: 0

Auto_increment: 1114098

Create_time: 2013-09-30 20:53:41

Update_time: NULL

Check_time: NULL

Collation: latin1_swedish_ci

Checksum: NULL

Create_options: row_format=COMPACT

Comment:

1 row in set (0.02 sec)

The disk usage is even slightly smaller, 320Mb, than the classical rebuild and it is also slightly faster.

 ls -al data/test_case2/t1*
-rw-rw---- 1 aadant common      8612 Sep 30 20:51 data/test_case2/t1.frm
-rw-rw---- 1 aadant common 318767104 Sep 30 20:53 data/test_case2/t1.ibd

ls -al data/test_case2/t1*

-rw-rw---- 1 aadant common 8612 Sep 30 20:51 data/test_case2/t1.frm

-rw-rw---- 1 aadant common 318767104 Sep 30 20:53 data/test_case2/t1.ibd

With innodb_sort_buffer_size= 32M, the alter is faster :

 alter table t1 row_format=Compact;
Query OK, 0 rows affected (2 min 51.96 sec)
Records: 0  Duplicates: 0  Warnings: 0

alter table t1 row_format=Compact;

Query OK, 0 rows affected (2 min 51.96 sec)

Records: 0 Duplicates: 0 Warnings: 0

Posted in online DDL, tip | 2 Comments

MySQL Connect slides

Posted on September 25, 2013 by aadant

Thank you for attending MySQL Connect 2013.

The Saturday session 50 Tips for Boosting MySQL Performance [CON2655] was sold out. It shows the interest in practical recipes to solve performance problems. Maybe the topic of a book as suggested by the audience ?

On Monday, the tutorial Enhancing Productivity with MySQL 5.6 New Features [TUT8131] was less crowded due to MySQL 5.6 Replication Tips and Tricks [TUT8133] happening at the same time, again tips and tricks are more popular :-).

Here are the slides :

The ppt files are here (additional notes) :

Feel free to comment, report problems or ask questions if unclear !

Posted in conference | Leave a comment

I am speaking at MySQL Connect 2013

Posted on September 9, 2013 by aadant

I open this blog to announce that I will be speaking at MySQL Connect in 2 weeks.

I will present a conference session :

50 Tips for Boosting MySQL Performance [CON2655], Saturday 21 Sept, 1PM

and a tutorial session :

Enhancing Productivity with MySQL 5.6 New Features [TUT8131], Monday, Sep 23, 1:15 PM

I am very happy to be part of this great event and to be able to meet the MySQL Community, our customers and my colleagues there. Looking forward to seeing you !

It is not too late to register !!

Posted in conference | Leave a comment

Performance_schema success stories : replication SQL thread tuning

Performance_schema success stories : host summary tables

50 tips to boost MySQL Performance Webinar follow up

How to calculate a specific InnoDB index size ?

A small optimizer change worth noticing

Poor man’s Online Optimize in 5.6

MySQL Connect slides

I am speaking at MySQL Connect 2013

Recent Posts

Recent Comments

Archives

Categories

Meta