Have you ever run a query in MySQL and wondered how long it’ll take to complete? Many people have had … of the simplest is to estimate how many rows the query needs to examine, measure how fast it’s working, and do the… only its status, but MySQL doesn’t currently let you do that.) The solution was to measure how fast the query was scanning…
Post: How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 10 Comments
… post. This post will be focus on how to recover a single tablespace using stock MySQL server. First, you must meet certain… option runs a recovery process on the backup with innodb_fast_shutdown=0 and therefore merging all the insert buffers. # innobackupex… /var/lib/mysql/data/employees/ Import the new tablespace: mysql> set FOREIGN_KEY_CHECKS=0; mysql> ALTER TABLE salaries IMPORT TABLESPACE; mysql> set FOREIGN…
Post: A micro-benchmark of stored routines in MySQL
August 16, 2009 By 7 Comments
Ever wondered how fast stored routines are in MySQL? I just ran a quick micro-benchmark to compare the … poisoning the results here, your comments welcome — is to see how fast the SQL procedure code is at doing basically the same… the subquery and basically rewrote it as a stored function. mysql> delimiter // mysql> create function speaks_english(c char(3)) returns integer…
Post: Filtered MySQL Replication
November 7, 2007 By 8 Comments
… not a big fan of filtered or partial MySQL Replication (as of version MySQL 5.0) – there is enough gotchas with replication… not much because query execution on such slave is very fast) and what is more important you will get into more…. P.S It is in my todo to see how well MySQL 5.1 row based replication works and performs and I…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… traversed in both directions for different queries. This will use fast primary key for some queries and use key K as… the two tables? A: The question in this case is how MySQL will execute the join. If it will fist lookup Table2… more details. Q: how mysql use index for group by? A: If you have Index on the column MySQL can avoid temporary table…
Post: How network can impact MySQL Operations ?
September 1, 2008 By 5 Comments
… lot of data copying around between MySQL boxes. We had prepared well and had measured how fast we could copy the data between…. As I mentioned you can get even better with using fast parallel compression like LZO or QuickLZ but there was no…
Post: MySQL Upgrade Webinar Questions Followup
June 28, 2012 By 7 Comments
… getting any significant development since MySQL 4.1, so in-place upgrade of MySQL 5.0 to MySQL 5.5 should be rather… importance I would recommend using Percona Server with enabled expand_fast_index_creation feature, as I just wrote this option can… which is maximally close to production. Q: How safe is it to upgrade from MySQL 5.5 to Percona Server 5.5…
Post: Three ways to know when a MySQL slave is about to start lagging
October 8, 2008 By 8 Comments
…how fast it can catch up. This is sort of related to Method One. The goal here is to explicitly see how…USER_STATISTICS table. mysql> SELECT * FROM INFORMATION_SCHEMA.USER_STATISTICS WHERE USER=’#mysql_system#’\G *************************** 1. row *************************** USER: #mysql_system# …
Post: Percona Server Fast-Restart White Paper Posted
January 10, 2011 By 2 Comments
… the implementation and benefits of Percona Server’s fast-restart capabilities. Briefly, after shutting down and restarting … when you don’t have to obsess over how long it’s going to take to get … but you still get terrible I/O performance so MySQL restarts take an eternity. Read the white paper …
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
…’t running queries anymore, or it runs such fast queries that you can’t capture them in… and port ${port} > /root/tcpdumps/`date +%s`-tcpdump & mysql -e ‘show innodb status\Gshow full processlist’ > /root/tcpdumps/`…open and I am still stumped as to how a SELECT-only transaction can possibly have undo…
