|
|
In my previous post, I’ve showed how to use Dtrace to find queries creating disk temporary tables (only available for OS with dtrace: solaris, freebsd, etc).
In MySQL 5.6 (which is not released yet, use “labs” version for now) we can use new performance_schema table events_statements_history or events_statements_history_long to find all performance metrics for all queries including created disk/memory tables, use of index, etc. WOW! This is what I have been waiting for a long time!
To illustrate, I have grabbed mysql-5.6.3-labs-performance-schema-linux2.6-x86_64.tar.gz from labs.mysql.com (this feature is only in labs version) and run sysbench readonly test (you need to disable prepared statements in sysbench, seems to be not working with prepared statements, I will check it later).
Here are the results:
mysql> select * from events_statements_history_long where CREATED_TMP_DISK_TABLES > 0 limit 10\G
*************************** 10. row ***************************
THREAD_ID: 74
EVENT_ID: 3295633
EVENT_NAME: statement/sql/select
SOURCE: sql_parse.cc:935
TIMER_START: 633828149000000
TIMER_END: 633843868000000
TIMER_WAIT: 15719000000
LOCK_TIME: 53000000
SQL_TEXT: SELECT DISTINCT c from sbtest where id between 847399 and 847499 order by c
CURRENT_SCHEMA: sbtest
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: NULL
MESSAGE_TEXT: NULL
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 1
ROWS_EXAMINED: 103
CREATED_TMP_DISK_TABLES: 1
CREATED_TMP_TABLES: 1
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 1
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 0
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 1
SORT_SCAN: 1
NO_INDEX_USED: 0
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
10 rows in set (0.00 sec)
Or if you need only list of queries:
mysql> select sql_text, count(*) as cnt from events_statements_history_long
where CREATED_TMP_DISK_TABLES > 0
group by sql_text order by cnt desc limit 10;
+-----------------------------------------------------------------------------+-----+
| sql_text | cnt |
+-----------------------------------------------------------------------------+-----+
| SELECT DISTINCT c from sbtest where id between 242012 and 242112 order by c | 2 |
| SELECT DISTINCT c from sbtest where id between 797388 and 797488 order by c | 2 |
| SELECT DISTINCT c from sbtest where id between 973150 and 973250 order by c | 1 |
| SELECT DISTINCT c from sbtest where id between 478783 and 478883 order by c | 1 |
| SELECT DISTINCT c from sbtest where id between 967035 and 967135 order by c | 1 |
| SELECT DISTINCT c from sbtest where id between 602102 and 602202 order by c | 1 |
| SELECT DISTINCT c from sbtest where id between 123827 and 123927 order by c | 1 |
| SELECT DISTINCT c from sbtest where id between 980527 and 980627 order by c | 1 |
| SELECT DISTINCT c from sbtest where id between 450354 and 450454 order by c | 1 |
| SELECT DISTINCT c from sbtest where id between 674804 and 674904 order by c | 1 |
+-----------------------------------------------------------------------------+-----+
10 rows in set (0.04 sec)
We can filter and order by rows_examined, SORT_MERGE_PASSES, NO_INDEX_USED, NO_GOOD_INDEX_USED, etc.
Links:
With the standard data warehousing queries we have a fact table and dimension tables and we join them.
For example, the fact table (Table size: 5M rows, ~2G in size) from my previous Loose index scan vs. covered indexes in MySQL post:
CREATE TABLE `ontime_2010` (
`YearD` int(11) DEFAULT NULL,
`MonthD` tinyint(4) DEFAULT NULL,
`DayofMonth` tinyint(4) DEFAULT NULL,
`DayOfWeek` tinyint(4) DEFAULT NULL,
`Carrier` char(2) DEFAULT NULL,
`Origin` char(5) DEFAULT NULL,
`DepDelayMinutes` int(11) DEFAULT NULL,
`AirlineID` int(11) DEFAULT NULL,
`Cancelled` tinyint(4) DEFAULT NULL,
... more fields here ...
) ENGINE=InnoDB DEFAULT CHARSET=latin1
(this is not the best possible fact table as the data is not aggregated by I’ll use it for now).
And we have those dimensions tables:
CREATE TABLE `airlines` (
`AirlineID` int(11) NOT NULL DEFAULT '0',
`AirlineName` varchar(255) DEFAULT NULL,
PRIMARY KEY (`AirlineID`),
KEY `AirlineName` (`AirlineName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
CREATE TABLE `date_dayofweek` (
`code` int(11) NOT NULL DEFAULT '0',
`description` varchar(255) DEFAULT NULL,
PRIMARY KEY (`code`),
KEY `description` (`description`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> select * from date_dayofweek order by code;
+------+-------------+
| code | description |
+------+-------------+
| 1 | Monday |
| 2 | Tuesday |
| 3 | Wednesday |
| 4 | Thursday |
| 5 | Friday |
| 6 | Saturday |
| 7 | Sunday |
| 9 | Unknown |
+------+-------------+
8 rows in set (0.00 sec)
So here is the example query (find sum of cancelled flights on Sundays for the given airline group by day):
select sum(Cancelled), FlightDate, AirlineName
from ontime_2010 o, date_dayofweek dow, airlines a
where o.dayofweek=dow.code and dow.description = 'Sunday'
and a.AirlineID = o.AirlineID and a.AirlineName = 'Delta Air Lines Inc.: DL'
group by FlightDate order by FlightDate desc limit 10\G
To fix the query we can add a covered index for ontime_2010, so that all fields for ontime_2010 table will be covered:
alter table ontime_2010 add key cov2(AirlineID, dayofweek, FlightDate, Cancelled);
However we will still have “temporary table and filesort”:
mysql> explain select sum(Cancelled), FlightDate
from ontime_2010 o, date_dayofweek dow, airlines a
where o.dayofweek=dow.code and dow.description = 'Sunday'
and a.AirlineID = o.AirlineID and a.AirlineName = 'Delta Air Lines Inc.: DL'
group by FlightDate order by FlightDate desc limit 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: dow
type: ref
possible_keys: PRIMARY,description
key: description
key_len: 258
ref: const
rows: 1
Extra: Using where; Using index; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ref
possible_keys: PRIMARY,AirlineName
key: AirlineName
key_len: 258
ref: const
rows: 1
Extra: Using where; Using index
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: o
type: ref
possible_keys: DayOfWeek,covered,AirlineID,cov2
key: cov2
key_len: 7
ref: ontime.a.AirlineID,ontime.dow.code
rows: 24417
Extra: Using where; Using index
3 rows in set (0.00 sec)
To avoid filesort we can re-write this query with "subqueries":
mysql> explain select sum(Cancelled), FlightDate from ontime_2010 o
where o.dayofweek= (select code from date_dayofweek where description = 'Sunday')
and AirlineID = (select AirlineID from airlines where AirlineName = 'Delta Air Lines Inc.: DL')
group by FlightDate limit 10\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: o
type: ref
possible_keys: DayOfWeek,covered,AirlineID,cov2
key: cov2
key_len: 7
ref: const,const
rows: 152510
Extra: Using where; Using index
*************************** 2. row ***************************
id: 3
select_type: SUBQUERY
table: airlines
type: ref
possible_keys: AirlineName
key: AirlineName
key_len: 258
ref:
rows: 1
Extra: Using where; Using index
*************************** 3. row ***************************
id: 2
select_type: SUBQUERY
table: date_dayofweek
type: ref
possible_keys: description
key: description
key_len: 258
ref:
rows: 1
Extra: Using where; Using index
3 rows in set (0.00 sec)
As MySQL will use indexes when we have "field = (select .. )" and now all fields in the index belong to the single table, MySQL will use index and avoid filesort. Please note: this will not work with "field in (select ...)" and also make sure that the subselect part will return only 1 row.
Lets say we have a table storing mail messages and we need to show user’s mailbox: messages sent “from” and “to” the specified user.
Here is our table:
CREATE TABLE `internalmail` (
`mail_id` int(10) NOT NULL AUTO_INCREMENT,
`senderaddress_id` int(10) NOT NULL,
`recipientaddress_id` int(10) NOT NULL,
`mail_timestamp` timestamp NULL DEFAULT NULL,
... message body, etc ...
PRIMARY KEY (`mail_id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1
And our query:
select * from internalmail
where (senderaddress_id = 247 or recipientaddress_id = 247 or recipientaddress_id = 0)
and mail_timestamp > '2010-08-01 12:30:47'
order by mail_timestamp desc
In this query we show all messages from and to user_id = 247 plus all messages to system user (user_id=0). We need to show only messages for the last 3 months and show the most recent messages first.
To speed up the query we can try creating indexes:
KEY `recipientaddress_id` (`recipientaddress_id`),
KEY `senderaddress_id` (`senderaddress_id`),
KEY `mail_timestamp` (`mail_timestamp`),
However, as the query uses “OR”, MySQL will use a filesort.
mysql> explain select * from internalmail
where (senderaddress_id = 247 or recipientaddress_id = 247 or recipientaddress_id = 0)
and mail_timestamp > '2010-08-01 12:30:47'
order by mail_timestamp desc\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: internalmail
type: ALL
possible_keys: recipientaddress_id,senderaddress_id,mail_timestamp
key: NULL
key_len: NULL
ref: NULL
rows: 4843257
Extra: Using where; Using filesort
1 row in set (0.00 sec)
UPDATE: even if we will create combined indexes on (recipientaddress_id,mail_timestamp) and/or (senderaddress_id,mail_timestamp) those indexes will not be used, as the query contains “OR” in the where clause.
And original query runs for 3 seconds. To fix this query we can do 2 things:
- Rewrite query with UNION instead of OR
- Create combined indexes
First, we rewrite query with UNION:
(select * from internalmail where senderaddress_id = 247 and mail_timestamp > ‘2010-08-19 12:30:47′)
union
(select * from internalmail where recipientaddress_id = 247 and mail_timestamp > ‘2010-08-19 12:30:47′)
union
(select * from internalmail where recipientaddress_id = 0 and mail_timestamp > ‘2010-08-19 12:30:47′)
order by mail_timestamp desc;
Second, we create 2 indexes:
mysql> alter table internalmail add key send_dt(senderaddress_id, mail_timestamp);
mysql> alter table internalmail add key recieve_dt(recipientaddress_id, mail_timestamp);
After that, MySQL will be able to fully utilize index for each of the 3 queries in union:
mysql> explain
(select * from internalmail where senderaddress_id = 247 and mail_timestamp > '2010-08-19 12:30:47')
union
(select * from internalmail where recipientaddress_id = 247 and mail_timestamp > '2010-08-19 12:30:47')
union
(select * from internalmail where recipientaddress_id = 0 and mail_timestamp > '2010-08-19 12:30:47')
order by mail_timestamp desc\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: internalmail
type: range
possible_keys: senderaddress_id,mail_timestamp,send_dt
key: send_dt
key_len: 9
ref: NULL
rows: 5
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: UNION
table: internalmail
type: range
possible_keys: recipientaddress_id,mail_timestamp,recieve_dt
key: recieve_dt
key_len: 9
ref: NULL
rows: 11
Extra: Using where
*************************** 3. row ***************************
id: 3
select_type: UNION
table: internalmail
type: range
possible_keys: recipientaddress_id,mail_timestamp,recieve_dt
key: recieve_dt
key_len: 9
ref: NULL
rows: 1
Extra: Using where
*************************** 4. row ***************************
id: NULL
select_type: UNION RESULT
table:
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Using filesort
4 rows in set (0.00 sec)
Although this query has to perform a final filesort it is much faster: now it runs in 0 sec compared to 3 seconds originally.
Loose index scan in MySQL can really help optimizing “group by” queries in some cases (for example, if you have only min() and/or max() as your aggregate functions). For example, if you have this query (to find maximum delay for all US flights with departure on Sundays in 2010):
select max(DepDelayMinutes), carrier, dayofweek
from ontime_2010
where dayofweek = 7
group by Carrier, dayofweek
the usual case will be adding a covered index on (dayofweek, Carrier, DepDelayMinutes). And MySQL will use this index fine (using index mean it will use the covered index):
mysql> explain select max(DepDelayMinutes), Carrier, dayofweek from ontime_2010
where dayofweek =7 group by Carrier, dayofweek\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ontime_2010
type: ref
possible_keys: covered
key: covered
key_len: 2
ref: const
rows: 905138
Extra: Using where; Using index
1 row in set (0.00 sec)
However, as the dayofweek part has low number of unique values, mysql will have to scan a lots of index entries (estimated rows: 905138).
Continue reading Loose index scan vs. covered indexes in MySQL
First of all: mysqldump is not converting tables. It is something else. Here is the story:
One of my clients had a case when they were migrating to a new mysql server: they used mysqldump to export data from the old server (all InnoDB) and imported it to the new server. When finished, all the tables became MyISAM on the new server. So they asked me this question:
“Why mysqldump is converting my tables from InnoDB to MyISAM?”
Continue reading Why mysqldump is converting my tables from InnoDB to MyISAM?
I’ll be speaking on MySQL Users Conference 2010. Talk: MySQL Architecture Design Patterns for Performance, Scalability, and Availability, 11:55am Thursday, 04/15/2010. Details.
I’ll be speaking at the data retrieval miniconf at Linux Conference in Wellington, New Zealand (Full Text Search with MySQL, Program)
I’ll cover some new sphinx search features (online updates)
Sometimes we have a lots of small and rather fast queries which use group by/order by, thus creating temporary tables. Some of those queries are retrieving text fields and mysql have to use disk (myisam) temporary tables. Those queries usually run for less than 1-2 seconds, so they did not get into slow query log, however, they sometimes add serious load on the system.
Here is the stat example:
bash-3.00$ /usr/local/mysql/bin/mysqladmin -uroot -p -i 2 -r extended-status|grep tmp_disk
...
| Created_tmp_disk_tables | 109 |
| Created_tmp_disk_tables | 101 |
| Created_tmp_disk_tables | 122 |
...
40-50 tmp_disk_tables created per second
So, how can we grab those queries? Usually we have to temporary enable general log, filter out queries with “group by/order by” and profile them all. On solaris/mac we can use dtrace instead.
Here is the simple script, which will find the list of queries creating tmp_disk_tables:
#pragma D option quiet
dtrace:::BEGIN
{
printf("Tracing... Hit Ctrl-C to end.\n");
}
pid$target::*mysql_parse*:entry
{
self->query = copyinstr(arg1);
}
pid$target::*create_myisam_tmp_table*:return
{
@query[self->query] = count();
}
put it into tmpdisktable.d, chmod +x tmpdisktable.d and run it with
./tmpdisktable.d -p `pgrep -x mysqld`
Ctrl+C after 5 seconds whatever and you will see the queries:
# ./tmpdisktable.d -p `pgrep -x mysqld`
Tracing... Hit Ctrl-C to end.
^C
Queries are stripped by the “strsize”, which is can be tweaked:
#pragma D option strsize=N
We can increase the “strsize” length now and run the script again to get the real queries examples.
Please note: running dtrace for a while can decrease performance, so do not run it for more than couple minutes on production systems.
Reporting queries (I will use this term here) are the queries which summaries and groups data over the certain period of time. For example, in Social Network site we want to know how many messages have been sent for the given period of time, group by region and status (sent, received, etc), order by number of messages sent.
As an example I will take a table which is used to send SMS (text messages).
SQL: select concat('+', substring(region_code,1 ,2), 'xxx') as reg, status, count(*) as cnt
from messages
where submition_date between '2009-01-01' and '2009-04-01' group by reg, status
having cnt>100 order by cnt desc, status limit 100;
This query will do a range scan over the submition_date and perform a filesort. There are common well known approaches which can be used to optimize table (“covered index”, “summary tables”, using external data warehouse, etc). Sometimes those approaches do not work or too complex.
Yet another approach is to use external search/index solution, for example Sphinx Search (http://www.sphinxsearch.com). In this case, data will be stored in MySQL and sphinx will be used as an external indexer/searcher, with SQL protocol support.
Using Sphinx
Starting with version 0.9.9-rc2, Sphinx searchd daemon supports MySQL binary network protocol and can be accessed with regular MySQL API. For instance, ‘mysql’ CLI client program works well. Here’s an example of querying Sphinx using MySQL client:
$ mysql -P 3307
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 0.9.9-dev (r1734)
As Sphinx can use attributes (“fields”) and group/sort then, it can be used for our report. Also, an application can simply connect to Sphinx server with MySQL protocol: an application will think it will work with MySQL (there are minor differences in Sphinx SQL, like “@count” and support of timestamps only instead of datetime)
Here is the example of the above query in Sphinx:
mysql> select *
from messages_dw
where
submition_date > 1230793200
and submition_date < 1238569200
group by region_code
order by @count desc
limit 0,10;
10 rows in set (0.19 sec)
Same query in MySQL 5.1 runs much slower:
select region_code, count(*) as cnt
from messages_dw
where
submition_date > '2009-01-01'
and submition_date < '2009-04-01'
group by region_code
order by cnt desc
limit 0,10;
10 rows in set (14.47 sec)
2 import notes:
- For now, Sphinx can’t group by more than one field. However, we can combine 2 fields in 1 and then group by this new field. Here the example of how we can do it:
- In the configuration file (in searchd section) we need to set max_matches to very large number (max_matches = 10000000 for example). By default, Sphinx will not generate exact counts (and all other average functions); this was done for the purpose of speed. However, setting max_matches to large number fixes this issue.
mysql> select BIGINT(region_code)*4*1024*1024*1024+status_code
as reg_status, *
from messages_dw
where date_added > 1230793200
and date_added < 1238569200
group by reg_status
order by @count desc, region_code
limit 0,10;
More speed comparison, group by 2 fields:
Sphinx:
mysql> select BIGINT(region_code)*4*1024*1024*1024+status_code as reg_status, * from messages_dw where date_added > 1230793200 and date_added < 1238569200 group by reg_status order by @count desc, region_code limit 0,10;
10 rows in set (0.98 sec)
MySQL:
mysql> select region_code, status+0, count(*) as cnt from messages_dw where submition_date between '2009-01-01' and '2009-04-01' group by region_code, status order by cnt desc, region_code limit 0,10;
10 rows in set (14.47 sec)
Conclusion
If you need fast ad-hock reporting queries, SphinxSearch can be a good option.
Advantages:
- Faster sorting and grouping (which is very important for reporting queries)
- No need to use external API for queries, Sphinx now supports mysql protocol
Disadvantages:
- Need to run additional Sphinx daemon
- Need to re-index data when it is changing
Sphinx config file
source src1
{
type = mysql
sql_host = 127.0.0.1
sql_user = root
sql_pass =
sql_db = dw
sql_port = 3309 # optional, default is 3306
sql_query = \
SELECT msg_id, region_code, status+0 as status_code, UNIX_TIMESTAMP(submition_date) AS date_added, 't' as content \
FROM messages_dw
sql_attr_uint = region_code
sql_attr_uint = status_code
sql_attr_timestamp = date_added
sql_query_info = SELECT * FROM messages_dw WHERE msg_id=$id
}
index messages_dw
{
source = src1
path = /data1/arubin/sphinx_new//var/data/test1
docinfo = extern
charset_type = sbcs
}
indexer
{
mem_limit = 32M
}
searchd
{
listen = localhost:3312:mysql41
log = /data1/arubin/sphinx_new//var/log/searchd.log
query_log = /data1/arubin/sphinx_new//var/log/query.log
read_timeout = 30
max_children = 30
pid_file = /data1/arubin/sphinx_new//var/log/searchd.pid
max_matches = 10000000
seamless_rotate = 1
preopen_indexes = 0
unlink_old = 1
}
I’ve started my new blog on MySQL. I’ll focus on MySQL full text search, performance tuning and High Availability (HA)
|
|
