… is “7″. This is also confirmed by looking at the graph of the N! function. Indeed, after 7, this function grows very fast – 7! = 5040, while 8! = 40320. The worst-case complexity of MySQL‘s join optimizer is N!. The goal… guarantees that any query can be optimized quickly no matter how bad the pruning heuristics works. With heuristics, things can go…
Post: How fast can MySQL Process Data
April 9, 2008 By 3 Comments
… lot in their product is about beating data processing limitations of current systems. This raises valid question how fast can MySQL process (filter) data… 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, or rather return top 10 rows from sorted result set which is the… now increase sort_buffer_size and see how performance is affected: set sort_buffer_size=100000; mysql> select * from gt order by i…
Post: How fast is FLUSH TABLES WITH READ LOCK?
April 24, 2010 By 28 Comments
… Expo Hall. I started to chat with them about their MySQL backup product. One of the representatives told me that their… of what the system can look like while this process is ongoing: mysql> show processlist; +—-+——+———–+——+————+——+——————-+———————————————————————-+ | Id | User | Host | db | Command | Time | State…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… (0.00 sec) Now let’s see how performance is affected when turning expand_fast_index_creation on. Here and in later examples… for InnoDB tables and thus, is just a special case of the previous one: mysql> SET expand_fast_index_creation=OFF; Query OK… index as the clustered one. References: Peter’s post MySQL bug #57583 MySQL bug #49120 Fast Index Creation page in Percona Server documentation
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… I check what the MySQL version is, and I check (with the ‘file’ command) whether /usr/libexec/mysqld is a 64-bit build…
Post: How to estimate query completion time in MySQL
April 22, 2008 By 14 Comments
… what the query is. One of the simplest is to estimate how many rows the query needs to examine, measure how fast it’s working… only its status, but MySQL doesn’t currently let you do that.) The solution was to measure how fast the query was scanning rows in the table scan of the fact table. This is shown by the…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 63 Comments
… Storage would benefit a lot from fast compression, such as QuickLZ. Results sets for most queries is highly compressible and light compression… sure if it is worth doing or if delayed invalidation is enough. Improve Manageability What is in the MySQL Query Cache ? How many hits did… now none of such information is available which makes it hard to understand how exactly MySQL Query Cache is working in many cases. This…
Post: How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 7 Comments
… is easy with MyISAM but if your tables are InnoDB the process is a little bit different story. With Oracle’s stock MySQL… post. This post will be focus on how to recover a single tablespace using stock MySQL server. First, you must meet certain… be dependent of the shared tablespace ibdata. That is, shutting down with innodb_fast_shutdown=0. We’ll use XtraBackup to avoid…
Post: Is your server's performance about to degrade?
May 18, 2010 By 13 Comments
…soon. In the meantime, how do you know if this is happening to you? Here’s how I diagnosed it …was able to see queries that have mostly very fast performance, and occasionally have much worse, and — here’… it — and that’s where improvements to the MySQL source code are necessary. But in many cases …
