… is one bug, or “missing feature” in MySQL Optimizer which may give you hard time causing performance problems which may be hard… can see in this case estimate becomes closer to the truth and this query runs about 50 times faster and easy on buffer… for second query is accessing subset of rows from the first plan so estimation for number of rows for the first query should…
Post: How to estimate query completion time in MySQL
April 22, 2008 By 16 Comments
… estimate how long it’ll take for the query to complete, depending on what the query is. One of the simplest is to estimate how many rows the query needs…’ll write more about that later) I convinced MySQL to perform the query as a table scan of the fact table, then…
Post: Find unused indexes
June 30, 2012 By Leave a Comment
… about how to find duplicate indexes. This time we’ll learn how to find unused …usage reads the slow query log and execute every query with EXPLAIN to ask MySQL which indexes would it…estimation with real data. EXPLAIN, as I said before, is an estimation from the optimizer and sometimes the real query…
Post: Cache Performance Comparison
August 9, 2006 By 74 Comments
… also needs to be factored in estimating improvement for real application. So instead… multiple times. Examining MySQL full query logs from many applications seeing several exactly same queries executed… Cache (TCP/IP) 12200 MySQL Query Cache (TCP/IP) 9900 MySQL Query Cache (Unix Socket) 13500 …
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: How does MySQL Replication really work?
January 9, 2013 By 9 Comments
… on estimating replication capacity, I don’t think we have one that covers the very basics of how MySQL replication … want to look at diskstats or even do a query review for SQL thread to get a good picture. … will only ever read from one disk at a time. That being said, one solution to fix IO-bound …
Post: Using LVM for MySQL Backup and Replication Setup
August 21, 2006 By 53 Comments
…copy files. Fast Recovery Recovery time is as fast as putting data back and standard MySQL crash recovery, and it…hot backup, but bad thing it is hard to estimate when it is hot and when it is not …quite a while to complete on systems with long queries. Problems with data on multiple volumes If you …
Post: MySQL Server Memory Usage
May 17, 2006 By 21 Comments
… often people ask me the question how should they estimate memory consumption by MySQL Server in given configuration. What is the formula… key caches. Multiple key caches can be created to accomodate query executions Query Parsing and optimization needs memory. This is usually small… unbound memory requirements retriving result sets from remove queries. Blobs may require 3x time of memory. This is important if you’re…
Post: Percona Server 5.1.59-13.0
October 16, 2011 By Leave a Comment
…ANALYZE TABLE is executed. #857590 (Alexey Kopytov). QUERY_RESPONSE_TIME doesn’t respect QUERY_RESPONSE_TIME_STATS . #855312 (Oleg Tsarev). The …KEY. #851674 (Alexey Kopytov). Backported fix for MySQL bug #53761 (Wrong estimate for RANGE query with compound indexes). #832528 (Alexey Kopytov)….
Post: Percona Server and XtraBackup weekly news, March 5th
March 6, 2011 By Leave a Comment
… our repositories on the next release based on MySQL 5.1.55, which we estimate will be released on March 9th. This… severe bottleneck especially for complex queries, which need to check the number of records in ranges many times. If you have ever seen a server with dozens of queries in “statistics” state, that’s…
