June 18, 2013

Post: Be productive with the MySQL command line

December 21, 2012 By 14 Comments

…mysql> pager grep sequence PAGER set to ‘grep sequence’ mysql> show engine innodb status\Gselect sleep(60);show engine innodb status\G Log sequence number…mysql> tee queries.log Logging to file ‘queries.log’ mysql> use sakila Reading table information for completion of table and column…

Filed Under: Insight for DBAs, MySQL

Post: How to debug long-running transactions in MySQL

March 8, 2011 By 6 Comments

…in the processlist, and you can track down where it’s coming from in the source code…Host column; there you’ll see a hostname followed by a port number. That is the port number …and port ${port} > /root/tcpdumps/`date +%s`-tcpdump & mysql -e ‘show innodb status\Gshow full processlist’ > /root/tcpdumps…

Filed Under: Insight for DBAs, MySQL

Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5

March 21, 2012 By 10 Comments

…_column, pk_column FROM tbl WHERE key_column=x ORDER BY key_column (Note that secondary keys in InnoDB contain primary key columns) Buffer each pk_column… seen by the numbers reported for the status counter Innodb_rows_read. While MySQL 5.6 is also reporting increased numbers for the…

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , , , , , , , , , , ,

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

…third installment. For now, the important number here is to note that …ID column, however, MySQL apparently has no problem with either of the following statements: mysql> insert into dir_test_innodb3 (fts_doc_id, full_name) values (1, ‘john smith’); mysql> select * from dir_test_innodb3 where…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: Multi Column indexes vs Index Merge

September 19, 2009 By 31 Comments

… see among MySQL users is how indexes are created. Quite commonly people just index individual columns as they are referenced in where clause… and correlation. If where clause from first column selects 5% of the rows and applying where clause on second column brings it down to… combined index but single index on another column. This is because MySQL is able to estimate number of rows it will find using…

Filed Under: Insight for DBAs, MySQL Tagged With:

Post: A rule of thumb for choosing column order in indexes

June 5, 2009 By 15 Comments

… sometimes use to decide which columns should come first in an index. This is not specific to MySQL, it’s generally applicable…. EXPLAIN says there are no possible_keys. SELECT * FROM tbl WHERE status=’waiting’ AND source=’twitter’ AND no_send_before

Filed Under: Insight for DBAs Tagged With: ,

Post: Identifying the load with the help of pt-query-digest and Percona Server

December 29, 2011 By 3 Comments

… atomically, not just for new connections as in MySQL. This is very helpful for measurement as otherwise…*/ SELECT option_name, option_value FROM wp_options WHERE autoload = ‘yes’\G This is the actual part…number of times this query was executed. Now let’s take a look at the values in the 95% column…

Filed Under: Insight for DBAs, MySQL

Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5

April 4, 2012 By 4 Comments

… MySQL can employ many other optimizations like for example if columns other then the secondary key columns… orders, lineitem FORCE INDEX (i_l_orderkey) where c_mktsegment = ‘AUTOMOBILE’ and c_custkey = o…become sequential. The two other most important numbers are values for Innodb_buffer_pool_reads…

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , , , , , , , , , , ,

Post: Find and remove duplicate indexes

June 20, 2012 By 9 Comments

… on the same column This is the easiest one. You can create multiple indexes on the same column and MySQL won’t complain. Let’s see this example: mysql> alter table t add index(name); mysql> alter table…) the execution plan changes to Using where. There is also a big difference in the number of Handler_read_next requests, so…

Filed Under: Insight for DBAs, Insight for Developers, MySQL, Percona Software

Post: Shard-Query turbo charges Infobright community edition (ICE)

May 6, 2011 By 23 Comments

…is the flight dimension, which contains the flight numbers and air carrier hierarchies. Only the largest … $ cat shards.ini [default] user=mysql db=ontime password= port=5029 column=date_id mapper=hash gearman=127…(date_id) JOIN dim_flight using (flight_id) WHERE Year BETWEEN 2009 and 2011 GROUP BY …

Filed Under: MySQL
« Older Posts
Newer Posts »