… batch jobs, reports and other non response time critical activities are overloading the system causing …know it is not MySQL problem, might be even not problem with your MySQL configuration, queries and hardware, …. This is true if you have system completely at your disposal. If you however need …
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 6 Comments
…read less than 1000 rows and doing it completely in memory. The plan optimizer picked was … this change I could bring optimization, and full query execution time to less than 50ms. Low values, … the following explanation from Timour Katchaounov in MySQL mailing list archives I have some recollection …
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
…queries you can be potentially waiting for hours for this statement to complete. Here is example how it can look: mysql…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… query time for MySQL 5.6, increase of 0.02s. But the query times for MariaDB 5.5 are greater than both MySQL 5.5 and MySQL… though both MariaDB and MySQL 5.6 are reporting high numbers for Innodb_rows_read, which is completely in line with how… MRR, and the query time for MySQL 5.6 and MariaDB 5.5 jumps to ~11min (this is the query time for MySQL 5.5) While…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
… > dump.sql SESSION1: (before dump has completed) mysql> alter table C add i int not null; Query OK, 1 row affected (0.65… ALTER TABLE `C` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014… SQL_NOTES=@OLD_SQL_NOTES */; — Dump completed on 2012-03-23 7:27:18 SESSION1: mysql> select * from C; +——+—+ | t | i | +——+—+ | test…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 63 Comments
… different from completely transparent cache and you’ll see why below. So what I think could be done with MySQL Query Cache so… all queries have to be invalidated from the query cache… which can take a lot of time if you have millions of queries cache… complex expensive queries, for which tracking exact rows used from query syntax may be hard. Query Cache Storage Engines MySQL Query Cache now stores queries in…
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 this experience… for the query to complete, depending on what the query is. One of the simplest is to estimate how many rows the query needs… you get 3488 seconds to completion, or a little less than an hour. And indeed the query completed in about 55 minutes. This…
Post: Logging MySQL queries from the client instead of the server
February 2, 2011 By 2 Comments
The “slow query log” is the single most valuable way to examine query execution on your MySQL server. Queries are logged with timing information, and… for logging queries with their execution times. I will list three. One is to make the application time the queries and write a query log… of accuracy and completeness; you can use tcpdump and some quick shell commands, for example. But with mk-query-digest’s built…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
… about of time: mysql> call flexviews.enable( -> flexviews.get_id(‘demo’,’complete_example2′)); Query OK, 0 rows affected (42 min 42.14 sec) mysql> call… the complete refresh method takes a very long time to refresh: mysql> call flexviews.refresh( -> flexviews.get_id(‘demo’,’complete_example2′),’BOTH’,NULL); Query OK…
