On Friday I gave a presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars. If you missed it, you can still register… SQL can produce an infinite variety of queries. The query optimizer analyzes queries for common patterns the MySQL designers know can be improved. But… over the range 1..MAX(id)? Yes, there’s a risk if you have irregular distribution of matching rows that you’ll…
Post: The small improvements of MySQL 5.6: Duplicate Index Detection
May 31, 2013 By Leave a Comment
… INDEX (col2); Query OK, 0 rows affected (0.25 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE test\G *************************** 1. row *************************** Table: test… mysql> ALTER TABLE test ADD INDEX (col2); Query OK, 0 rows affected, 1 warning (0.56 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> SHOW WARNINGS\G *************************** 1. row…
Post: Implementing SchemaSpy in your MySQL environment
June 4, 2013 By 2 Comments
…the relationships between tables versus observing queries only. An additional benefit is that…MySQL driver=com.mysql.jdbc.Driver connectionSpec=jdbc:mysql://127.0.0.1:5610/schemaspy driverPath=/usr/share/java/mysql…, number of children, parents, count of columns, row counts, and comments — a great …
Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency
May 22, 2013 By Leave a Comment
… are common in OLAP analysis. Each query in each flight (Q1.1 for example) is tested with a … better when a larger number of rows must be joined together (Q4.1, Q4.2) but worse when… datadir=/mnt/mysql56/data basedir=/usr/local/mysql socket=/var/lib/mysql/mysql.sock user=justin innodb_buffer_pool_size…
Comment: Derived Tables and Views Performance
May 21, 2013 By 16 Comments
…mysql=mysql_connect(’127.0.0.1‘,’root’,”); mysql_select_db(‘test’,$mysql); function display_menu($parent, $level) { $result = mysql_query…
Post: Choosing a MySQL HA Solution - Post-Webinar Q&A
June 7, 2013 By Leave a Comment
… a two-NIC bonded pair you’ll get roughly 1.67x the throughput of a single port. [Don’t …simple query, but it’s always possible to build something more involved if that’s what you need. Q: What MySQL …server up with –skip-grant-tables and insert a few rows into an InnoDB table, but trying to run a …
Comment: INSERT INTO ... SELECT Performance with Innodb tables.
May 22, 2013 By 22 Comments
… InnoDB 334 mysql tables in use 4, locked 4 3264 lock struct(s), heap size 440760, 799203 row lock(s) MySQL thread id 38, OS thread handle 0x2b3c1c776940, query id 16838101 localhost root Copying to tmp table create table t as SELECT ld.label_id, cai.inventory_id, COUNT(1…
Post: Disconnecting a replication slave is easier with MySQL 5.5+ (RESET SLAVE vs. RESET SLAVE ALL)
April 17, 2013 By 2 Comments
…: mysql> stop slave; Query OK, 0 rows affected (0.00 sec) mysql> reset slave; Query OK, 0 rows affected (0.03 sec) mysql> show slave status\G *************************** 1. row *************************** Slave…: mysql> stop slave; Query OK, 0 rows affected (0,01 sec) mysql > reset slave; Query OK, 0 rows affected (0,11 sec) mysql> show slave status\G *************************** 1. row *************************** Slave…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…: single SELECT queries doing PRIMARY KEY lookups (aka QPS sysbench mode); same MySQL queries executed inside single-…reportedly Dimitri was using 8 tables with 1M rows each (about 2.1GB) for his tests,…_SELECT + UPDATE QPS test sysbench –num-threads=<1..1024> –test=oltp.lua –oltp_tables_count=…
Post: Is your MySQL buffer pool warm? Make it sweat!
April 16, 2013 By 6 Comments
…3307/slow | percona-playback –mysql-host 127.0.0.1 –mysql-username playback –mysql-password PaSSwOrd –mysql-schema schema_name –query-log-stdin –dispatcher-plugin … applying the captured query traffic. This is an important step because update statements that match no rows or insert statements …
