… ago, when I was just hired at MySQL. The assumptions about what are typical MySQL may have been true then in 2004… 7, this function grows very fast – 7! = 5040, while 8! = 40320. The worst-case complexity of MySQL‘s join optimizer is N… guarantees that any query can be optimized quickly no matter how bad the pruning heuristics works. With heuristics, things can go…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 63 Comments
… and have multiple MySQL Query Cache instances hashed by query hash. It would help to reduce complexity dramatically. Fast Invalidation Right now… such information is available which makes it hard to understand how exactly MySQL Query Cache is working in many cases. This however… INFORMATION_SCHEMA tables. Self Refresh MySQL Query Cache causes not uniform performance as you get very fast responses for hits but you…
Post: How fast can MySQL Process Data
April 9, 2008 By 3 Comments
… data processing limitations of current systems. This raises valid question how fast can MySQL process (filter) data using it current architecture ? I decided… rows and wrote little stored procedure to make timing easier: mysql> DELIMITER // mysql> CREATE PROCEDURE test_read(pl INT) -> BEGIN -> DECLARE t…
Post: How fast can you sort data with MySQL ?
August 18, 2007 By 26 Comments
… the same table as I used for MySQL Group by Performance Tests to see how much MySQL can sort 1.000.000 rows… lot of sort merge passes are required for sort completion: mysql> show status like “sort%”; +——————-+——-+ | Variable_name | Value | +——————-+——-+ | Sort_merge_passes… increase sort_buffer_size and see how performance is affected: set sort_buffer_size=100000; mysql> select * from gt order by i…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… spindle speed. I need to know how many physical spindles are under the box, and how fast they are. I can also get information about the all-important battery-backed write cache, how it’s configured…=/var/db/mysql –user=mysql –pid-file=/var/run/mysqld/mysqld.pid –skip-external-locking –port=3306 –socket=/var/db/mysql/mysql.sock consult…
Post: How to estimate query completion time in MySQL
April 22, 2008 By 14 Comments
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 7 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 7 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: 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…
