capturing, analyzing and optimizing mysql

ODTUG – June 2010

Capturing, Analyzing & Optimizing your SQL

Padraig O'Sullivan - http://posulliv.com/Ronald Bradford - http://ronaldbradford.com/

http://posulliv.com/

http://ronaldbradford.com/

ODTUG – June 2010

Capturing your SQL

ODTUG – June 2010

MySQL Provided Options

Slow Query Log

General Query Log

Binary Log

Connectors

Processlist

Engine status

Status variables

ODTUG – June 2010

Other Techniques

MySQL Proxy

TCP/IP Capture

Dtrace/SystemTap

Application Management

ODTUG – June 2010

Slow Query LogNot enabled by default

log-slow-queries = /path/to/file (5.0)

slow_query_log/slow_query_log_file (5.1)

Granularity of seconds

Microsecond patch by percona

Default slow time is 10 seconds

long_query_time = 2

Log output to file or table (5.1)

log_output = FILE | TABLE | BOTHhttp://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html

http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html

ODTUG – June 2010

Slow Query Log Example# Time: 100518 19:34:31# User@Host: user[db] @ localhost []# Query_time: 4 Lock_time: 0 Rows_sent: 1155 Rows_examined: 1663671select distinct visitorid from log where timestamp like '20100518%' and accountid!=1 and accountid!=37 and file not like '%admin%';# Time: 100518 19:35:37# User@Host: user[db] @ localhost []# Query_time: 4 Lock_time: 0 Rows_sent: 6 Rows_examined: 1662525select distinct accountid from log where timestamp>='20100518132033' and accountid NOT IN ('0','1','37','1635');# Time: 100518 19:35:41# User@Host: user[db] @ localhost []# Query_time: 4 Lock_time: 0 Rows_sent: 2477 Rows_examined: 1664996select timestamp, file from log where timestamp>='20100518000000' and timestamp<='end' and accountid!=1 and accountid!=37 order by timestamp desc;# Time: 100518 19:35:44# User@Host: user[db] @ localhost []# Query_time: 3 Lock_time: 0 Rows_sent: 1157 Rows_examined: 1663676select distinct visitorid from log where timestamp like '20100518%' and accountid!=1 and accountid!=37 and file not like '%admin%';

ODTUG – June 2010

General Query Log

Not enabled by default

log = /path/to/file

general_log/general_log_file (5.1)

Log output to file or table (5.1)

log_output = FILE | TABLE | BOTH

Never use in production

Ideal for development, unit testing environments

http://dev.mysql.com/doc/refman/5.1/en/query-log.html

http://dev.mysql.com/doc/refman/5.1/en/query-log.html

ODTUG – June 2010

General Query Log Example

26 Query SELECT * FROM wp_users WHERE user_login = 'xxxx'26 Query SELECT meta_key, meta_value FROM wp_usermeta WHERE user_id = 226 Query SELECT option_value FROM wp_options WHERE option_name = 'sidebars_widgets' LIMIT 126 Query SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') ORDER BY wp_posts.post_date DESC LIMIT 0, 1026 Query SELECT FOUND_ROWS()26 Query SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category', 'post_tag') ANDtr.object_id IN (2849, 2842, 2836, 2824, 2812, 2680, 2813, 2800, 2770, 2784) ORDER BY t.name ASC26 Query SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (2849,2842,2836,2824,2812,2680,2813,2800,2770,2784)

Output using Wordpress

ODTUG – June 2010

General Query Log Example (2)

Framework new connection

23 Connect user@host 23 Query SELECT 1 23 Query SET NAMES utf8 23 Query SET character_set_results = NULL 23 Query SHOW VARIABLES 23 Query SHOW COLLATION 23 Query SET autocommit=1 23 Query SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED 23 Query SET autocommit=0

ODTUG – June 2010

General Query Log Example (3)

Finding framework overhead

ROLLBACK after every SELECT

SELECT 1 – connection ping test 23 Query select count(*) from collection where account_name = 'xxx' and user_id = '313' 23 Query rollback 23 Query select 1 23 Query select count(*) from collection where account_name = 'xxx' and user_id = '346' 23 Query rollback 23 Query select 1 23 Query select name,user_id,date_format(`register_time`,'%Y%m%d%H%i%S') as createTime from account where name = 'xxx' and user_id = '346' and `status` = '1' 23 Query rollback 23 Query select 1 23 Query select max(mdate) from received where account_name = 'xxx' And user_id = '346' order by mdate desc 23 Query rollback

ODTUG – June 2010

Binary Log

Not enabled by default

log-bin = /path/to/file

expire_log_days = 5

View with mysqlbinlog

http://dev.mysql.com/doc/refman/5.1/en/binary-log.html

http://dev.mysql.com/doc/refman/5.1/en/binary-log.html

ODTUG – June 2010

Binary Log Example

# at 2461#100604 10:49:43 server id 1 end_log_pos 2632 Query thread_id=6 exec_time=0 error_code=0use blog/*!*/;SET TIMESTAMP=1275662983/*!*/;UPDATE `wp_postmeta` SET `meta_value` = '1275662983' WHERE `meta_key` = '_edit_lock' AND `post_id` = 2849/*!*/;

# at 21107#100604 10:58:49 server id 1 end_log_pos 21469 Query thread_id=62 exec_time=0 error_code=0use monitoringmysql/*!*/;SET TIMESTAMP=1275663529/*!*/;SET @@session.time_zone='SYSTEM'/*!*/;INSERT INTO wp_redirection_logs (url,sent_to,created,agent,redirection_id,ip,referrer,module_id,group_id) VALUES ('/mysqlmonitoring/dbtuna',NULL,NOW(),'Mozilla/5.0 (compatible; Yahoo! Slurp/3.0; http://help.yahoo.com/help/us/ysearch/slurp)',NULL, '67.195.114.227', NULL, 3, NULL)/*!*/;

mysqlbinlog /path/to/log-bin.000000

ODTUG – June 2010

Connector/J

logSlowQueries

slowQueryThresholdMillis

SlowQueryThresholdNanos

http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-configuration-properties.html

http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-configuration-properties.html

ODTUG – June 2010

Processlist

Current connection/thread

No filtering options

Ideal for locking

http://dev.mysql.com/doc/refman/5.1/en/show-processlist.html

mysql> SHOW [FULL] PROCESSLIST$ mysqladmin [v] processlist

mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST (5.1))

http://dev.mysql.com/doc/refman/5.1/en/show-processlist.html

ODTUG – June 2010

Processlist Example

mysql [localhost] {root} ((none)) > show processlist;+++++++++| Id | User | Host | db | Command | Time | State | Info |+++++++++| 7 | root | localhost | NULL | Query | 0 | NULL | show processlist || 20017 | root | localhost | oltp | Query | 0 | creating table | create table bench1 (a int NOT NULL,i int,s char(10),primary key (a)) || 20020 | root | localhost | oltp | Query | 1 | copy to tmp table | ALTER TABLE bench ADD i919 integer,ADD i920 integer,ADD i921 integer,ADD i922 integer,ADD i923 |+++++++++3 rows in set (0.00 sec)

ODTUG – June 2010

Processlist Example (2)

| Id | User | Host | db | Command | Time | State | Info

| 2692 | app_usr | db:44376 | db1 | Query | 3 | Sending data | SELECT ...| 2805 | app_usr | db:44703 | db1 | Query | 0 | statistics | SELECT ... FOR UPDATE| 2829 | app_usr | db:44812 | db1 | Query | 0 | init | UPDATE ...| 2845 | app_usr | db:44866 | db1 | Sleep | 2 | | NULL | 2846 | app_usr | db:44866 | db1 | Sleep | 2 | | NULL | 2915 | app_usr | db:56751 | db1 | Query | 0 | Updating | UPDATE ...| 2847 | app_usr | db:44868 | db1 | Query | 0 | query end | SELECT * FROM ...| 2928 | app_usr | db:56792 | db1 | Query | 0 | logging slow query | INSERT INTO ...

ODTUG – June 2010

Engine Status

Provides engine specific information

Vendor implemented

InnoDB

Running threads

Last deadlock

Last foreign key error

Other engine types include

NDB, PBXT, TOKUDB

ODTUG – June 2010

Engine Status

mysql> SHOW ENGINE INNODB STATUS;

http://dev.mysql.com/doc/refman/5.1/en/show-engine.html

http://dev.mysql.com/doc/refman/5.1/en/show-engine.html

ODTUG – June 2010

Engine Status ExampleTRANSACTIONSTrx id counter 0 480206585Purge done for trx's n:o < 0 480205682 undo n:o < 0 0History list length 5LIST OF TRANSACTIONS FOR EACH SESSION:TRANSACTION 0 0, not started, process no 27230, OS thread id 1182685536MySQL thread id 427, query id 123563 10.8.160.241 dbaSHOW ENGINE INNODB STATUSTRANSACTION 0 0, not started, process no 27230, OS thread id 1169639776MySQL thread id 44, query id 123445 10.8.160.241 dbaTRANSACTION 0 480206305, ACTIVE 67 sec, process no 27230, OS thread id 1192270176mysql tables in use 1, locked 11 lock struct(s), heap size 368, 0 row lock(s), undo log entries 1MySQL thread id 370, query id 123171 10.120.229.37 user query endINSERT INTO `table` ....TRANSACTION 0 480206290, ACTIVE 70 sec, process no 27230, OS thread id 1178159456mysql tables in use 1, locked 11 lock struct(s), heap size 368, 0 row lock(s), undo log entries 1MySQL thread id 372, query id 123150 10.120.229.37 user query endINSERT INTO `table` ....TRANSACTION 0 480206289, ACTIVE 70 sec, process no 27230, OS thread id 1178425696mysql tables in use 1, locked 11 lock struct(s), heap size 368, 0 row lock(s), undo log entries 1MySQL thread id 373, query id 123149 10.120.229.37 user query endINSERT INTO `table` ........

ODTUG – June 2010

Engine Status Example (2)------------------------LATEST DETECTED DEADLOCK------------------------090624 14:32:18*** (1) TRANSACTION:TRANSACTION 0 39639074, ACTIVE 0 sec, process no 27807, OS thread id 1176562016 insertingmysql tables in use 1, locked 1LOCK WAIT 7 lock struct(s), heap size 1216MySQL thread id 1225, query id 4695283 10.120.19.204 db updateINSERT INTO table VALUES(254381,638,1,57)*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 0 page no 6371457 n bits 248 index `PRIMARY` of table `db/table` trx id 0 39639074 lock_mode X insert intention waitingRecord lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:TRANSACTION 0 39639079, ACTIVE 0 sec, process no 27807, OS thread id 1179224416 inserting, thread declared inside InnoDB 500mysql tables in use 1, locked 17 lock struct(s), heap size 1216MySQL thread id 1229, query id 4695295 prodgos23.eao.abn-iad.ea.com 10.120.19.204 madden_2010 updateINSERT INTO table VALUES(254382,746,1,57)*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 0 page no 6371457 n bits 248 index `PRIMARY` of table `madden_2010_franchise_xbl2/franchise_draft_board` trx id 0 39639079 lock_mode XRecord lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 0 page no 6371457 n bits 248 index `PRIMARY` of table `db/table` trx id 0 39639079 lock_mode X insert intention waitingRecord lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

ODTUG – June 2010

Status Variables

Shows incrementing internal counters

Volume of SQL

Read/Write ratio

ODTUG – June 2010

Status Variables

mysql> SHOW GLOBAL STATUS;

$ mysqladmin extendedstatus

mysql> SELECT * FROM GLOBAL_STATUS (5.1)

http://dev.mysql.com/doc/refman/5.1/en/show-status.html


ODTUG – June 2010

Status Variables Example

| Com_select | 553971 |

| Com_insert | 64068 || Com_insert_select | 6521 |

| Com_update | 190627 || Com_update_multi | 4809 |

| Com_delete | 95197 || Com_delete_multi | 1408 |

| Com_replace | 1549 || Com_replace_select | 0 |

ODTUG – June 2010

Status Variables Example (2)

| Bytes_received | 635778091 || Bytes_sent | 4207810294 |

| Threads_connected | 22 || Threads_running | 3 |

Throughput

| Bytes_received | 635778091 || Bytes_sent | 4207810294 |

Load

| Threads_connected | 22 || Threads_running | 3 |

Server Running

| Uptime | 5951 |

ODTUG – June 2010

Status Variables Example (3)

●$ mysqladmin u p r s 1 extended status | grep v “| 0”

+++| Variable_name | Value |+++| Bytes_received | 8888 || Bytes_sent | 151482 || Com_select | 114 || Com_show_status | 1 || Created_tmp_tables | 2 || Handler_read_key | 194 || Handler_read_next | 273 || Handler_read_rnd | 165 || Handler_read_rnd_next | 47416 || Handler_write | 289 || Key_read_requests | 1555 || Questions | 116 || Select_range | 23 || Select_scan | 3 || Sort_rows | 165 || Sort_scan | 3 || Table_locks_immediate | 115 || Threads_connected | 1 || Threads_running | 1 || Uptime | 1 || Uptime_since_flush_status | 1 |+++

ODTUG – June 2010

MySQL Proxy

Included histogram.lua

Gives read/write breakdown per table

Shows top executing queries by frequency

Shows max and avg execution time

http://dev.mysql.com/doc/refman/5.1/en/mysql-proxy.html

https://launchpad.net/mysql-proxy

http://ronaldbradford.com/blog/sql-query-analysis-with-mysql-proxy-2009-09-02/

http://dev.mysql.com/doc/refman/5.1/en/mysql-proxy.html

https://launchpad.net/mysql-proxy

http://ronaldbradford.com/blog/sql-query-analysis-with-mysql-proxy-2009-09-02/

ODTUG – June 2010

MySQL Proxy Examplemysql> select * from histogram.tables; ++++ | table | reads | writes | ++++ | schema1.dotCommLookup | 991 | 0 | | schema2xxxxxx.dotCommClient | 6 | 0 | | schema2xxxxxx.ReleaseNumbers | 3 | 6 | | schema2xxxxxx.PAYCYCLE | 1 | 0 | | schema1.dotCommClient | 1505 | 0 | | schema1.Job | 131 | 0 | | schema2xxxxxx.CondMod | 0 | 28 | | schema2xxxxxx.SPINFO | 3 | 0 | | schema1.Poll | 1184 | 633 | | schema2xxxxxx.LaborSchedules | 0 | 96 | | schema1.JobRequest | 192 | 0 | | schema1.PollTasks | 454 | 1048 | | schema1.Tasks | 1048 | 0 | | schema1.auth | 141 | 59 | | schema2xxxxxx.dotComm | 2 | 0 | | schema2xxxxxx.MenuItem | 0 | 2446 | | schema1.Location | 19 | 118 | | schema2xxxxxx.CONCASH | 2 | 0 | | schema1.dotComm | 156 | 0 | | schema1.Client | 47 | 0 | ++++ 20 rows in set (0.00 sec)

ODTUG – June 2010

MySQL Proxy Example (2)mysql> select * from histogram.queries; *************************** 8. row *************************** query: SELECT `type` , COUNT( * ) AS `count` FROM `schema1` . `table1` à` WHERE `clientID` = ? AND `bocDate` = ? AND `locationID` IN( ? ) GROUP BY ? ORDER BY ? count: 8 max_query_time: 12932 avg_query_time: 4154.125 *************************** 35. row *************************** query: SELECT èmpl_first_nam` , èmpl_last_nam` , èmpl_mid_initial` FROM `schema2` . `table2` WHERE `clientID` = ? AND èmpl_ssn` = ? AND `locationID` = ? LIMIT ? count: 84 max_query_time: 61974 avg_query_time: 2501.3095238095 *************************** 305. row *************************** query: INSERT INTO `schema3` . `table3` SET èventTime` = NOW( ) , èvent` = ? , ùserID` = ? , `details` = ? count: 59 max_query_time: 433610 avg_query_time: 38760.983050847 *************************** 302. row *************************** query: SELECT * FROM `table4` WHERE `jobID` = ? AND `taskID` = ? count: 1056 max_query_time: 17564 avg_query_time: 672.67708333333

ODTUG – June 2010

TCP/IP Capture

Captures network packets

http://www.tcpdump.org/tcpdump_man.html

http://www.tcpdump.org/tcpdump_man.html

ODTUG – June 2010

TCP/IP Example

20100518 19:08:09.291455 IP 999.67.245.14.3338 > 999.174.90.144.3306: tcp 00x0000: 4500 0030 fdd0 4000 7106 e466 4b43 f50e [email protected]: 8cae 5a90 0d0a 0cea e1cd 4d71 0000 0000 ..Z.......Mq....0x0020: 7002 ffff 125c 0000 0204 05b4 0101 0402 p....\..........20100518 19:08:09.291519 IP 999.174.90.144.3306 > 999.67.245.14.3338: tcp 00x0000: 4500 0030 0000 4000 4006 1338 8cae 5a90 E..0..@[email protected]: 4b43 f50e 0cea 0d0a 69af 73bf e1cd 4d72 KC......i.s...Mr0x0020: 7012 16d0 1e0c 0000 0204 05b4 0101 0402 p...............20100518 19:08:09.316990 IP 999.67.245.14.3338 > 999.174.90.144.3306: tcp 00x0000: 4500 0028 fdd1 4000 7106 e46d 4b43 f50e E..([email protected]: 8cae 5a90 0d0a 0cea e1cd 4d72 69af 73c0 ..Z.......Mri.s.0x0020: 5010 ffff 61a0 0000 P...a...20100518 19:08:09.322483 IP 999.174.90.144.3306 > 999.67.245.14.3338: tcp 670x0000: 4508 006b 7eed 4000 4006 9407 8cae 5a90 E..k~.@[email protected]: 4b43 f50e 0cea 0d0a 69af 73c0 e1cd 4d72 KC......i.s...Mr0x0020: 5018 16d0 574b 0000 3f00 0000 0a35 2e30 P...WK..?....5.00x0030: 2e35 3161 2d63 6f6d 6d75 6e69 7479 0036 .51acommunity.60x0040: 9800 0057 7462 6f28 732f 2e00 2ca2 0802 ...Wtbo(s/..,...0x0050: 0000 0000 0000 0000 0000 0000 0000 386f ..............8o0x0060: 4858 615f 3838 5274 7055 00 HXa_88RtpU.

tcpdump -i any -s 65535 -x -nn -q -tttt port 3306

ODTUG – June 2010

DTrace/SystemTap

Observability technologies

Enable the behavior of running systems and applications to be investigated

Zero probe effect when not enabled

http://www.sun.com/bigadmin/content/dtrace/index.jsp

http://sourceware.org/systemtap/

http://www.sun.com/bigadmin/content/dtrace/index.jsp

http://sourceware.org/systemtap/

ODTUG – June 2010

SystemTap Example# stap query_trace.sty /home/posulliv/repos/mysql/mysql-5.5.2-m2/5.5.2/libexec/mysqldselect @@version_comment limit 1Total: 287us Locks: 0us Engine: 0us Network: 6us Filesort: 0usselect USER()Total: 127us Locks: 0us Engine: 0us Network: 6us Filesort: 0usSELECT DATABASE()Total: 562us Locks: 0us Engine: 0us Network: 21us Filesort: 0usshow databasesTotal: 642us Locks: 0us Engine: 5us Network: 6us Filesort: 0usshow tablesTotal: 1148us Locks: 0us Engine: 6us Network: 6us Filesort: 0usshow tablesTotal: 6201us Locks: 0us Engine: 19us Network: 23us Filesort: 0usSELECT DATABASE()Total: 564us Locks: 0us Engine: 0us Network: 21us Filesort: 0usshow databasesTotal: 81us Locks: 0us Engine: 5us Network: 6us Filesort: 0usshow tablesTotal: 293us Locks: 0us Engine: 5us Network: 6us Filesort: 0usselect * from t1Total: 1202us Locks: 32us Engine: 20us Network: 23us Filesort: 0usselect * from t1 where a = 1Total: 1134us Locks: 30us Engine: 56us Network: 21us Filesort: 0usselect * from t1 order by a descTotal: 4727us Locks: 26us Engine: 20us Network: 22us Filesort: 3182usinsert into t1 values (3, 'tomas')Total: 3406us Locks: 146us Engine: 858us Network: 30us Filesort: 0usselect * from t1 order by a descTotal: 1340us Locks: 27us Engine: 20us Network: 21us Filesort: 228us^C

ODTUG – June 2010

Analyzing your SQL

ODTUG – June 2010

Bulk Analysis Tools

Your eyes

Maatkit

mk-query-digest

ODTUG – June 2010

Slow Query Log

mk-query-digest --type slowlog /path/to/slow.log

Best practice to roll/analyze daily

http://ronaldbradford.com/blog/the-correct-approach-to-rolling-mysql-logs-2010-02-22/

http://ronaldbradford.com/blog/the-correct-approach-to-rolling-mysql-logs-2010-02-22/

ODTUG – June 2010

Slow Query Analysis Example●$ mkquerydigest type slowlog /var/log/mysql/slow.log●

# Query 1: 0.00 QPS, 2.74x concurrency, ID 0x036A4959F0EBB3BD at byte 1 _# pct total min max avg 95% stddev median# Count 1 395# Exec time 28 478528s 11s 4733s 1211s 4168s 1431s 511s# Lock time 20 31777s 0 2769s 80s 685s 291s 0# Rows sent 0 3.86k 10 10 10 10 0 10# Rows exam 3 193.22M 500.73k 500.96k 500.90k 485.50k 0 485.50k# Users 1 xxx# Hosts 5 app5.xxxxx... (128), app1.xxxx... (85)... 3 more# Databases 1 xxxx# Time range 20090518 10:23:18 to 20090520 10:50:14# bytes 0 184.67k 476 479 478.73 463.90 0 463.90# Query_time distribution# 1us# 10us# 100us# 1ms# 10ms# 100ms# 1s# 10s+ ################################################################

ODTUG – June 2010

Slow Query Analysis Example

# Tables# SHOW TABLE STATUS FROM `xxxx` LIKE 'gg'\G# SHOW CREATE TABLE `xxxx`.`gg`\G# SHOW TABLE STATUS FROM `xxxx ` LIKE 'gr'\G# SHOW CREATE TABLE `xxxx `.`gr`\G# SHOW TABLE STATUS FROM `xxxx ` LIKE 'tt'\G# SHOW CREATE TABLE `xxxx `.`tt`\G# EXPLAINSELECT tt.*, (SELECT COUNT(*) FROM gg WHERE gg.tt_user_id = tt.id) AS g_c, (SELECT COUNT(*) FROM WHERE gr.tt_user_id = tt.id ) AS r_c FROM tt AS tt WHERE 1 AND tt.user_type = 'CUSTOMER' GROUP BY tt.id HAVING g_c > 1 OR r_c > 1 ORDER BY tt.id desc, g_c DESC , r_c DESC LIMIT 10 OFFSET 0\G

ODTUG – June 2010

General Query Log●$ mkquerydigest type genlog reportformat profile /opt/mysql_sandboxes/latest/data/msandbox.log

# 2.4s user time, 40ms system time, 37.40M rss, 78.70M vsz# Current date: Fri Jun 11 14:17:22 2010# Files: /opt/mysql_sandboxes/latest/data/msandbox.log# Overall: 17.11k total, 2.64k unique, 0.71 QPS, 0x concurrency __________# total min max avg 95% stddev median# Exec time 0 0 0 0 0 0 0# Time range 20100611 07:35:44 to 20100611 14:17:01# bytes 793.75k 11 26.50k 47.52 54.21 295.84 38.53

# Profile# Rank Query ID Response time Calls R/Call Item# ==== ================== ================ ===== ======== ================# 1 0xC9EEB2D51C356A33 0.0000 0.0% 3622 0.0000 SET# 2 0x6C099B0B73EA7633 0.0000 0.0% 608 0.0000 USE# 3 0xAC5C18B304F9DE87 0.0000 0.0% 595 0.0000 SELECT# 4 0x30FE5C2032672827 0.0000 0.0% 595 0.0000 SHOW TABLE STATUS# 5 0x1D7CBA0BE5BAA758 0.0000 0.0% 595 0.0000 SHOW TRIGGERS# 6 0xAEA9A980826923AF 0.0000 0.0% 595 0.0000 SET# 7 0x10AB55EA465447D0 0.0000 0.0% 565 0.0000 ADMIN FIELD# 8 0x5D51E5F01B88B79E 0.0000 0.0% 522 0.0000 ADMIN CONNECT# 9 0xAA353644DE4C4CB4 0.0000 0.0% 438 0.0000 ADMIN QUIT# 10 0xE3A3649C5FAC418D 0.0000 0.0% 410 0.0000 SELECT# 11 0x28FC5B5D583E2DA6 0.0000 0.0% 402 0.0000 SHOW STATUS# 12 0x3AEAAD0E15D725B5 0.0000 0.0% 107 0.0000 SET# 13 0x3607184B9D9C3A96 0.0000 0.0% 107 0.0000 SELECT# 14 0x44001A2E53C1AB70 0.0000 0.0% 107 0.0000 SET# 15 0xA2750AF24EA2AEE6 0.0000 0.0% 107 0.0000 SHOW COLLATION# 16 0x38B3D80280BBFA2A 0.0000 0.0% 107 0.0000 SET# 17 0xC69B6ED2C47380A4 0.0000 0.0% 107 0.0000 SHOW VARIABLES# 18 0xE4CF7146873CCC28 0.0000 0.0% 107 0.0000 SET# 19 0x5CBA2034458B5BC9 0.0000 0.0% 95 0.0000 SHOW DATABASES# 20 0x2DE40AAFA6F4B715 0.0000 0.0% 46 0.0000 INSERT SELECT TEMP.task

ODTUG – June 2010

General Query Log Analysis$ mkquerydigest type genlog /opt/mysql_sandboxes/latest/data/msandbox.log

# Query 20: 0 QPS, 0x concurrency, ID 0x2DE40AAFA6F4B715 at byte 56687 ___# This item is included in the report because it matches limit.# pct total min max avg 95% stddev median# Count 0 46# Exec time 0 0 0 0 0 0 0 0# Databases 1 anonymous# Time range 20100611 09:33:04 to 20100611 09:33:04# bytes 3 25.72k 404 1.12k 572.57 833.10 157.30 511.45# Query_time distribution# 1us# 10us# 100us# 1ms# 10ms# 100ms# 1s# 10s+# Tables# SHOW TABLE STATUS FROM `TEMP` LIKE 'task'\G# SHOW CREATE TABLE `TEMP`.`task`\Ginsert into TEMP.task( task_type, state, user_table_schema, user_table_table, user_table_depth, artifact_schema, artifact_table, command ) values( 'GenerateFinalBySort', 'waiting', 'drupal', 'node_revisions', '0', 'TEMP', 'node_revisions$final', 'create table `TEMP`.`node_revisions$final`(`nid` int, `vid` int, `uid` int, `title` varchar(255), `body` longtext, `teaser` longtext, `log` longtext, `timestamp` int, `format` int) select `nid`, `vid`, `uid`, `title`, `body`, `teaser`, `log`, `timestamp`, `format` from `drupal`.`node_revisions` order by `vid`')\G

ODTUG – June 2010

Binary Log

mysql> SHOW GLOBAL VARIABLES LIKE 'log_bin';mysql> SHOW MASTER STATUS;mysql> SHOW MASTER LOGS;

ODTUG – June 2010

Aggregating Binlog Output

33389 update e_acc 17680 insert into r_b 17680 insert into e_rec 14332 insert into rcv_c 13543 update e_rec 10805 update loc 3339 insert into r_att 2781 insert into o_att

mysqlbinlog /path/to/mysqlbin.000999 | \ grep i e "ûpdate" e "însert" e "^delete" e "^replace" e "âlter" | \ cut c1100 | tr '[AZ]' '[az]' | \ sed e "s/\t/ /g;s/\`//g;s/(.*$//;s/ set .*$//;s/ as .*$//" | \ sed e "s/ where .*$//" | \ sort | uniq c | sort nr

ODTUG – June 2010

Maatkit$ mysqlbinlog /opt/mysql_sandboxes/latest/data/msandbox.000001 \> | ./mkquerydigest type binlog reportformat profile# Profile# Rank Query ID Response time Calls R/Call Item# ==== ================== ================ ===== ======== ================# 1 0xBE1EAF34D33DE48B 0.0000 0.0% 3 0.0000 CREATE TABLE coitest_?k.customers `bulkload`.`customers$final`# 2 0x0A44B866EBFF94FC 0.0000 0.0% 3 0.0000 DROP# 3 0x7B747B0DEABCA018 0.0000 0.0% 3 0.0000 CREATE TABLE coitest_?k.orders `bulkload`.òrders$final`# 4 0xB4E2D6D6475B00FF 0.0000 0.0% 3 0.0000 DROP# 5 0xF5DAC1762A3DE61F 0.0000 0.0% 3 0.0000 DROP# 6 0x42EF521F930E2DB7 0.0000 0.0% 3 0.0000 CREATE TABLE coitest_?k.orders `bulkload`.òrders$parent`# 7 0xE4D87F83229BB741 0.0000 0.0% 18 0.0000 UPDATE bulkload.task# 8 0x5A7960FD3F39E5F8 0.0000 0.0% 3 0.0000 CREATE TABLE bulkload.progress# 9 0x813031B8BBC3B329 0.0000 0.0% 60141 0.0000 COMMIT# 10 0x854DE5DBCC259DFB 0.0000 0.0% 30000 0.0000 INSERT bulkload.items$parent# 11 0x3FBD919BC7E69704 0.0000 0.0% 87 0.0000 INSERT bulkload.progress# 12 0xE50052BA71B4D17C 0.0000 0.0% 3 0.0000 DROP TABLE if# 13 0xD645DADD65C8DD01 0.0000 0.0% 18 0.0000 INSERT SELECT bulkload.task# 14 0x5B6EB50967F9BAB8 0.0000 0.0% 18 0.0000 UPDATE bulkload.task# 15 0x277902B2380B46B4 0.0000 0.0% 1 0.0000 DROP# 16 0x15174D089E600489 0.0000 0.0% 3 0.0000 CREATE TABLE bulkload.task# 17 0x6F1EE7DAFC74D9AF 0.0000 0.0% 3 0.0000 CREATE TABLE coitest_?k.items `bulkload`.ìtems$child`# 18 0x85FFF5AA78E5FF6A 0.0000 0.0% 60141 0.0000 BEGIN# 19 0x9EE6FD43C5546BA1 0.0000 0.0% 3 0.0000 CREATE TABLE bulkload.items$parent `bulkload`.ìtems$parent`# 20 0x51DCCB95FA917A6F 0.0000 0.0% 3 0.0000 DROP TABLE if

ODTUG – June 2010

Status variables Analysis

Gathering global status per time interval

Reporting options

statpack

http://ronaldbradford.com/blog/using-statpack-with-show-status-2009-06-18/

http://ronaldbradford.com/blog/using-statpack-with-show-status-2009-06-18/

ODTUG – June 2010

statpack Example

==================================================================================================== Uptime: 17 days 17 hours 22 mins Snapshot Period 1: 59 minute interval==================================================================================================== Variable Delta/Percentage Per Second Total=================================================================================...

==================================================================================================== Statement Activity====================================================================================================

SELECT: 13,503,876 3,798.56 4,298,170,239 (94.25%) INSERT: 91,101 25.63 25,327,062 (0.56%) UPDATE: 782,004 219.97 220,640,296 (4.84%) DELETE: 9,674 2.72 2,485,643 (0.05%) COMMIT: 46,422 13.06 13,700,478 (0.30%) ROLLBACK: 0 0.00 13 (0.00%)

...

==================================================================================================== Temporary Space====================================================================================================

tmp_table_size Efficiency: 2.10% Memory Temp Tables: 26,467 7.45 9,335,030 Disk Temp Tables: 25,951 7.30 9,138,705 Temp Files: 2 0.00 601

ODTUG – June 2010

TCP/IP Dump Analysis

Non-intrusive

Close to full sample

Near realtime

$ tcpdump i eth0 port 3306 s 65535 x \> n q tttt c 1000 > tcpdump.out$ cat tcpdump.out | mkquerydigest type tcpdump

ODTUG – June 2010

TCP/IP Dump Summary# 4.1s user time, 85.9ms system time, 17.85M rss, 18.46M vsz# Overall: 1.99k total, 86 unique, 321.13 QPS, 0.21x concurrency _________# total min max avg 95% stddev median# Exec time 1s 39us 70ms 649us 839us 4ms 152us# Time range 20100330 17:52:42.569092 to 20100330 17:52:48.772254# bytes 186.80k 13 1.61k 96.03 166.51 196.84 40.45# Rows affe 59 0 2 0.03 0 0.18 0# Warning c 5 0 2 0.00 0 0.07 0# 4% (98) No_index_used

# Profile# Rank Query ID Response time Calls R/Call Item# ==== ================== ================ ===== ======== ================# 3 0xC9ECBBF2C88C2336 0.0875 7.5% 568 0.0002 SELECT r_c# 4 0x448E4AEB7E02AF72 0.0785 6.7% 566 0.0001 SELECT r_t# 5 0xCF3A86CFCC509800 0.0596 5.1% 5 0.0119 SELECT h_c_i# 6 0x8D86ED007917BCE6 0.0480 4.1% 131 0.0004 SELECT loc# 7 0xDB8614598CD1ED36 0.0416 3.6% 1 0.0416 SELECT lml r# 8 0x9BA140CF9CE950C8 0.0316 2.7% 1 0.0316 SELECT bsc# 9 0x633F85619F198C33 0.0219 1.9% 23 0.0010 SELECT hlm mt # 10 0xADCCD37E265C8300 0.0197 1.7% 79 0.0002 SELECT h_l_l

ODTUG – June 2010

TCP/IP Dump Query Review# Query 6: 23.02 QPS, 0.01x concurrency, ID 0x8D86ED007917BCE6 at byte 2# This item is included in the report because it matches limit.# pct total min max avg 95% stddev median# Count 6 131# Exec time 3 48ms 160us 5ms 366us 690us 477us 247us# Hosts 5 10.0.0.77 (86), 10.0.0.36 (21), 10.0.0.79 (20)... 2 more# Time range 20100330 17:52:43.034021 to 20100330 17:52:48.724815# bytes 8 15.93k 123 125 124.54 124.25 1 124.25# Errors 1 none# Rows affe 0 0 0 0 0 0 0 0# Warning c 0 0 0 0 0 0 0 0# Query_time distribution# 1us# 10us# 100us ################################################################# 1ms ### 10ms# 100ms# 1s# 10s+# Tables# SHOW TABLE STATUS LIKE 'loc'\G# SHOW CREATE TABLE `loc`\G# EXPLAINSELECT l.* FROM loc p, loc l WHERE p.id=2090 AND l.lft BETWEEN p.lft AND p.rgt AND l.depth=p.depth+1 ORDER BY l.lft\G

ODTUG – June 2010

mycheckpoint

SQL orientated monitoring utility for MySQL

Measures various metrics

Stores measurements in database

Metrics can be accessed through SQL

Can generate HTML reports and Google charts easily

http://code.openark.org/forge/mycheckpoint/documentation

http://code.openark.org/forge/mycheckpoint/documentation

ODTUG – June 2010

SQL Analysis Tools

EXPLAIN

mk-visual-explain

SHOW CREATE TABLE

SHOW INDEXES

SHOW TABLE STATUS

I_S Table/Index size

SHOW SESSION STATUS

SHOW PROFILE

ODTUG – June 2010

Explain

Returns information on query plan

Query not actually executed (unless query contains sub-query)

1 row in output per table

EXPLAIN EXTENDED shows query actually executed

http://dev.mysql.com/doc/refman/5.1/en/explain.html

http://dev.mysql.com/doc/refman/5.1/en/explain.html

ODTUG – June 2010

Explain Examples

mysql> explain > select > avg(length(w.variables)) / 1048576 > from > watchdog w > \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: w type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 69248 Extra: 1 row in set (0.00 sec)

mysql>

ODTUG – June 2010

mk-visual-explain

Reverse engineers explain output from MySQL

Formats MySQL explain output as a left-deep tree

Can take piped input or can process explain plans from input file

http://www.maatkit.org/doc/mk-visual-explain.html

http://www.maatkit.org/doc/mk-visual-explain.html

ODTUG – June 2010

mk-visual-explain Examples

Filesort+ TEMPORARY table temporary(b,r) + JOIN + Distinct/NotExists | + Filter with WHERE | + Index lookup | key r>PRIMARY | possible_keys PRIMARY | key_len 292 | ref drupal.b.module,drupal.b.delta | rows 3 + Filter with WHERE + Bookmark lookup + Table | table b | possible_keys tmd,list + Index lookup key b>tmd possible_keys tmd,list key_len 194 ref con

echo "explain SELECT DISTINCT b.* FROM blocks b LEFT JOIN blocks_roles r ON b.module = r.module AND b.delta = r.delta WHERE b.theme = 'garland' AND b.status = 1 AND (r.rid IN (2) OR r.rid IS NULL) ORDER BY b.region, b.weight, b.module" | \mysql u root drupal | \mkvisualexplain

ODTUG – June 2010

Show Create Table

Shows full definition

Includes all keys

Shows storage engine

http://dev.mysql.com/doc/refman/5.1/en/show-create-table.html

SHOW CREATE TABLE [schema.]tablename

http://dev.mysql.com/doc/refman/5.1/en/show-create-table.html

ODTUG – June 2010

Show Create Table Example

mysql> SHOW CREATE TABLE wp_postmeta\G*************************** 1. row *************************** Table: wp_postmetaCreate Table: CREATE TABLE `wp_postmeta` ( `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `post_id` bigint(20) unsigned NOT NULL DEFAULT '0', `meta_key` varchar(255) DEFAULT NULL, `meta_value` longtext, PRIMARY KEY (`meta_id`), KEY `post_id` (`post_id`), KEY `meta_key` (`meta_key`)) ENGINE=MyISAM AUTO_INCREMENT=1745 DEFAULT CHARSET=utf81 row in set (0.00 sec)

ODTUG – June 2010

Show Indexes

Provides cardinality

http://dev.mysql.com/doc/refman/5.1/en/show-index.html

SHOW INDEX[ES] [FROM|IN] [schema.]tablename

http://dev.mysql.com/doc/refman/5.1/en/show-index.html

ODTUG – June 2010

Table Status

SHOW TABLE STATUS [IN schema] LIKE 'tablename'

http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

ODTUG – June 2010

Table Status Example

mysql> SHOW TABLE STATUS LIKE 'wp_postmeta'\G*************************** 1. row *************************** Name: wp_postmeta Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 1351 Avg_row_length: 46 Data_length: 62960Max_data_length: 281474976710655 Index_length: 58368 Data_free: 76 Auto_increment: 1745 Create_time: 20090908 09:57:51 Update_time: 20100604 15:06:47 Check_time: 20100601 11:12:54 Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)

ODTUG – June 2010

Information Schema

INFORMATION_SCHEMA

TABLES

COLUMNS

Meta data

Simple view of DBA_TABLES/COLUMNS

http://dev.mysql.com/doc/refman/5.1/en/information-schema.html

http://dev.mysql.com/doc/refman/5.1/en/information-schema.html

ODTUG – June 2010

Information Schema Example

SELECT table_schema, table_name, engine, row_format, table_rows, avg_row_length, (data_length+index_length)/1024/1024 as total_mb, (data_length)/1024/1024 as data_mb, (index_length)/1024/1024 as index_mb, CURDATE() AS todayFROM information_schema.tables WHERE table_schema=DATABASE()ORDER BY 7 DESC;

http://ronaldbradford.com/mysql-dba/#perschema

http://ronaldbradford.com/mysql-dba/#perschema

ODTUG – June 2010

Information Schema Example

+++++++++| table_name | engine | row_format | table_rows | avg_row_length | total_mb | data_mb | index_mb |+++++++++| xxxxxxx | InnoDB | Compact | 1778523 | 314 | 658.39062500 | 533.84375000 | 124.54687500 || xxxxxxxxx | InnoDB | Compact | 553266 | 846 | 472.25000000 | 446.75000000 | 25.50000000 || xxxxxxx | InnoDB | Compact | 435892 | 884 | 392.25000000 | 367.81250000 | 24.43750000 || xxxxxxxxxxxxxx | InnoDB | Compact | 1106547 | 65 | 133.26562500 | 68.59375000 | 64.67187500 || xxxxxxxxxxxxxxxx | InnoDB | Compact | 58281 | 531 | 30.34375000 | 29.51562500 | 0.82812500 || xxxxxxxxxx | InnoDB | Compact | 68721 | 298 | 28.12500000 | 19.54687500 | 8.57812500 |

...

ODTUG – June 2010

Show Session Status

MySQL Internal counters



ODTUG – June 2010

Show Profile

Provides internal message calls

Descriptions not always accurate

http://dev.mysql.com/doc/refman/5.1/en/show-profiles.html

SET PROFILING = 1;SHOW PROFILES;SHOW PROFILE [FOR QUERY n];

http://dev.mysql.com/doc/refman/5.1/en/show-profiles.html

ODTUG – June 2010

Show Profile Example

mysql> SHOW PROFILES\G*************************** 1. row ***************************Query_ID: 1Duration: 0.00081600 Query: SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category', 'post_tag') AND tr.object_id IN (2487) ORDER BY t.name ASC

ODTUG – June 2010

Show Profile Example (2)mysql> SHOW PROFILE SOURCE FOR QUERY 1;++++++| Status | Duration | Source_function | Source_file | Source_line |++++++| starting | 0.000049 | NULL | NULL | NULL || checking query cache for query | 0.000074 | unknown function | sql_cache.cc | 1446 || Opening tables | 0.000013 | unknown function | sql_base.cc | 4495 || System lock | 0.000005 | unknown function | lock.cc | 258 || Table lock | 0.000032 | unknown function | lock.cc | 269 || init | 0.000038 | unknown function | sql_select.cc | 2484 || optimizing | 0.000014 | unknown function | sql_select.cc | 820 || statistics | 0.000135 | unknown function | sql_select.cc | 1011 || preparing | 0.000017 | unknown function | sql_select.cc | 1033 || Creating tmp table | 0.000155 | unknown function | sql_select.cc | 1524 || executing | 0.000002 | unknown function | sql_select.cc | 1755 || Copying to tmp table | 0.000142 | unknown function | sql_select.cc | 1902 || Sorting result | 0.000026 | unknown function | sql_select.cc | 2176 || Sending data | 0.000051 | unknown function | sql_select.cc | 2309 || end | 0.000002 | unknown function | sql_select.cc | 2530 || removing tmp table | 0.000063 | unknown function | sql_select.cc | 10776 || end | 0.000003 | unknown function | sql_select.cc | 10801 || query end | 0.000002 | unknown function | sql_parse.cc | 4978 || freeing items | 0.000017 | unknown function | sql_parse.cc | 6004 || storing result in query cache | 0.000005 | unknown function | sql_cache.cc | 983 || logging slow query | 0.000001 | unknown function | sql_parse.cc | 1679 || cleaning up | 0.000002 | unknown function | sql_parse.cc | 1647 |++++++22 rows in set (0.00 sec)

ODTUG – June 2010

Show Profile Example (3)

mysql> SHOW PROFILES;+++| 1 | 0.00084800 | SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t ...| 2 | 0.00006000 | SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t ....+++2 rows in set (0.00 sec)

mysql> SHOW PROFILE SOURCE FOR QUERY 2;++++++| starting | 0.000034 | NULL | NULL | NULL || checking query cache for query | 0.000008 | unknown function | sql_cache.cc | 1446 || checking privileges on cached | 0.000004 | unknown function | sql_cache.cc | 1534 || sending cached result to clien | 0.000010 | unknown function | sql_cache.cc | 1629 || logging slow query | 0.000002 | unknown function | sql_parse.cc | 1679 || cleaning up | 0.000002 | unknown function | sql_parse.cc | 1647 |++++++

ODTUG – June 2010

Optimizing your SQL

ODTUG – June 2010

About the MySQL Optimizer

Cost based optimizer

No way to pin QEP

One index per table rule (with exceptions)

The lack of good hints

No statistics granularity

No parallelismhttp://forge.mysql.com/wiki/How_does_the_MySQL_Optimizer_workhttp://forge.mysql.com/wiki/How_does_the_MySQL_Optimizer_work

http://forge.mysql.com/wiki/How_does_the_MySQL_Optimizer_work

ODTUG – June 2010

SQL Optimization Goals

Identify the best indexes

Reduce unnecessary data access

Minimize kernel internals

ODTUG – June 2010

Explain Signs

No index used

Large rows

Excessive possible indexes

SQL for composite/covering indexes

Using Temporary

Using filesort

Derived tables

ODTUG – June 2010

MySQL Index Types

Column indexes

Composite indexes

Covering indexes

Partial indexes

Spatial indexes

Fulltext indexes

Clustered indexes

Hash indexeshttp://ronaldbradford.com/blog/understanding-different-mysql-index-implementations-2009-07-22/http://ronaldbradford.com/blog/understanding-different-mysql-index-implementations-2009-07-22/

http://ronaldbradford.com/blog/understanding-different-mysql-index-implementations-2009-07-22/

ODTUG – June 2010

Column Index – Sub-optimal Design

CREATE TABLE `content` ( `ContentID` int(11) NOT NULL auto_increment,.... `Promo` tinyint(1) NOT NULL, `Theme` float NOT NULL, `ForDelete` tinyint(1) NOT NULL, PRIMARY KEY (`ContentID`), KEY `CategoryID` (`CategoryID`), KEY `Verified` (`Verified`), KEY `FileName` (`FileName`(10)), KEY `FileDuration` (`FileDuration`), KEY `Votes` (`Votes`), KEY `FileDir` (`FileDir`), KEY `ForDelete` (`ForDelete`), KEY `UserID` (`UserID`), KEY `Type` (`Type`), KEY `CategoryType` (`CategoryType`), KEY `DateAdded` (`DateAdded`))

ODTUG – June 2010

Composite Indexes Example

CREATE TABLE relationship ( relation_id bigint NOT NULL auto_increment, type_id bigint default NULL, user_1 bigint NOT NULL, user_2 bigint NOT NULL,... PRIMARY KEY (relation_id), KEY user_1 (user_1), KEY user_2 (user_2),);

ODTUG – June 2010

Covering Indexes ExampleCREATE TABLE logging ( source_addr int(10) unsigned NOT NULL, source_port int(9) unsigned NOT NULL, destn_addr int(10) unsigned NOT NULL, destn_port int(9) unsigned NOT NULL, protocol int(9) unsigned NOT NULL, log_time int(11) unsigned NOT NULL, size int(11) unsigned NOT NULL, KEY log_time (log_time), KEY size (size), KEY source_port (src_port), KEY destn_port (destn_port), KEY source_addr (source_addr), KEY destn_addr (dstn_addr)) ENGINE=MyISAM;

ODTUG – June 2010

Partial IndexesCREATE TABLE parts( ... brand_id INT UNSIGNED NOT NULL, part_number VARCHAR(30) NOT NULL, manufacturer_name VARCHAR(100) NOT NULL,... INDEX (brand_id, part_number,manufacturer_name))

ALTER TABLE parts DROP INDEX brand_id,ADD KEY (brand_id, interchange_part_number, manufacturer_name(8));

Reduced Index size by 30%.

ODTUG – June 2010

MySQL Index Sizes

NULL columns

Bigint vs int

InnoDB secondary index PK value

ODTUG – June 2010

NULL Index Columns (2)

mysql> SELECT parent_id FROM CHILD PROCEDURE ANALYSE(5,2000)\G*************************** 1. row *************************** Field_name: odtug.CHILD.parent_id Min_value: 1 Max_value: 13 Min_length: 1 Max_length: 2 Empties_or_zeros: 0 Nulls: 0Avg_value_or_avg_length: 7.0000 Std: 3.7417 Optimal_fieldtype: TINYINT(2) UNSIGNED NOT NULL1 row in set (0.00 sec)

ODTUG – June 2010

NULL Index Columns (3)

ALTER TABLE child MODIFY parent_id INT UNSIGNED NOT NULL;

+++++++++++| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+++++++++++| 1 | SIMPLE | child | ref | parent_id | parent_id | 4 | const | 1 | |+++++++++++

ODTUG – June 2010

BIGINT vs INTexplain SELECT user_2 FROM relationship WHERE user_1 = '1580980221' AND user_2 IN (508534334,521894993);


ALTER TABLE relationship MODIFY user_1 INT NOT NULL, MODIFY user_2 INT NOT NULL);

explain SELECT user_2 FROM relationship WHERE user_1 = '1580980221' AND user_2 IN (508534334,521894993);


ODTUG – June 2010

MySQL Index Sizes

NULL columns

Bigint vs int

InnoDB secondary index PK value

ODTUG – June 2010

When Indexes Don't HelpCREATE TABLE ìtems` ( ìtemid` int(11) NOT NULL auto_increment, `productid` int(11) NOT NULL default '0',... ìtemlive` enum('true','false') NOT NULL default 'true',... PRIMARY KEY (ìtemid`), KEY `productid` (`productid`)KEY `productid` (`productid`), KEY `packaging_cost` (`packaging_cost`))

ODTUG – June 2010

Optimizing SQL for Locking

DML is a blocking statement for MyISAM

Long running cleanup scripts

3+ minutes to run SELECT

DELETE FROM fts_datacacheWHERE productid NOT IN (SELECT productidFROM xcart_products_categoriesWHERE categoryid IN (297,1356,1366,1361,1202,...)GROUP BY productid

ODTUG – June 2010

Optimizing SQL for Locking

Rewriting statement into 2 statements can reduce locking time to ~10 ms

CREATE TEMPORARY TABLE delete_cache (productid INT NOT NULL, primary key (productid)) ENGINE=MEMORYSELECT DISTINCT productidFROM retailbl_xcart.xcart_products_categoriesWHERE categoryid IN (297,1356,1366,1361,1202,...);

DELETE FROM fts_datacacheWHERE productid NOT IN (SELECT productid FROM delete_cache);

ODTUG – June 2010

Remove Duplicate IndexesCREATE TABLE àccounts` ( àccountid` int(11) NOT NULL auto_increment,... `retail_date` date NOT NULL, `customer_survey` tinyint(1) NOT NULL default '1', PRIMARY KEY (àccountid`), KEY àccountid` (àccountid`)KEY àccountid` (àccountid`));

CREATE TABLE `vehicle_applications` (

PRIMARY KEY (ìd`), KEY `mcp_index` (`product_id`,`make`,`model`,`year`), KEY `status_idx` (`status`), KEY `sid_idx` (`status_identifier`), KEY `product_id_idx` (`product_id`),KEY `product_id_idx` (`product_id`),)

ODTUG – June 2010

The Most Optimal SQL is the SQL You

Can Eliminate

ODTUG – June 2010

Eliminating SQL – Example 1

SELECT pages_id, pages_livestats_code, pages_title, pages_parent, pages_exhibid, pages_theme, pages_accession_num FROM pages WHERE pages_id = 0

Following executed 6,000 times in 5 min period

0 is an invalid pages_id

ODTUG – June 2010


5 Query SELECT * FROM àrtist`5 Query SELECT * FROM àrtist`5 Query SELECT * FROM àrtist`5 Query SELECT * FROM àrtist`5 Query SELECT * FROM àrtist`5 Query SELECT * FROM àrtist` WHERE (ArtistID = 196 )5 Query SELECT * FROM àrtist` WHERE (ArtistID = 2188)5 Query SELECT * FROM àrtist`5 Query SELECT * FROM àrtist`5 Query SELECT * FROM àrtist`

Following executed on single page load

ODTUG – June 2010


Row at a time (RAT) processing

SELECT * FROM activities_theme WHERE theme_parent_id=0SELECT * FROM activities_theme WHERE theme_parent_id=1SELECT * FROM activities_theme WHERE theme_parent_id=2SELECT * FROM activities_theme WHERE theme_parent_id=11SELECT * FROM activities_theme WHERE theme_parent_id=16

Chunk at a time (CAT) processing

SELECT * FROM activities_theme WHERE theme_parent_id in (0,1,2,11,16)

ODTUG – June 2010

Conclusion

ODTUG – June 2010

Capture/Analysis/Optimization

Improvement is a reoccurring process

Ideally you should analyze all SQL

Changes in data size and result set size over time can affect SQL performance

capturing, analyzing and optimizing mysql

Technology

log output

slow query log example

default logslow

wordpress26 query

xxxx26 query

asc26 query

rows26 query

default logbin