… problem, might be even not problem with your MySQL configuration, queries and hardware, even though fixing these does help in many… too much in this case throttling by having relatively short queries and introducing “sleeps” between them can be a good idea…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… memory. There are implicit MEMORY tables which are allocated for query execution, which size can be controlled by tmp_table_size… of the lines below. The most likely cause for Innodb run away memory allocation is Dictionary cache though there could be other… in MySQL. Most suspected memory leaks end up being some run away resource usage, though these can happen. Some memory leaks might…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… | Query | 0 | NULL | show processlist | 0 | 0 | 1 | +——-+——+———–+———-+————+——+——————————+——————————+———–+—————+———–+ 5 rows in set (0.00 sec) As result this means single run away select… TABLES WITH READ LOCK and fail backup or kill long running SELECT queries to let backup to proceed, but resolving server gridlock…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
….6 and MariaDB 5.5. Note that query cache is disabled during these benchmark runs and that the disks are 4 5….o_orderkey 2 100.00 Using where So during cold query runs the optimizer would switch to using plan ‘a’, which does… able to do many read_aheads. Another thing to take away is that MariaDB is just a bit slower as compared…
Post: Is it query which needs to be optimized ?
March 6, 2007 By 9 Comments
… matching profiles ? Yet another approach is to throw away count and run main query with LIMIT 1000 or so, cache results for all… explain for this one. So frequently I see dynamic query generation when some query variants fall back to use filesort, and it… of troubles. So designing schema make sure for queries you’re going to run ORDER BY .. LIMIT can be performed using the…
Post: Reasons for run-away main Innodb Tablespace
June 10, 2010 By 1 Comment
… transactions this is a very good indicator. If you’re running Percona Server the following can be helpful: mysql> select * from… as it internally commits every 10000 rows. Running Very Long Transaction If you’re running very long transaction, be it even SELECT… other a lot of people use slave for very long queries which may make long transactions reasons a lot more likely…
Comment: How to Identify Bad Queries in MySQL
January 11, 2011 By 16 Comments
… be determined easily. You can get away with a slow system, but when customers start to run away, you’re too late for tuning. “In databases, we care most about the work the database is doing. That is, queries… much load to the system, or 3. they block other queries from running ” is useless to me. What is your definition of…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… of openness. It also shocks people sometimes — “you’re giving away the golden goose!” Not really. What you hire us for… ORM system actually runs the query, the server could have died. The ORM should just be running the query, and if the query fails, then… time. For each query, I run EXPLAIN if possible (rewriting non-SELECT queries if needed, and using careful judgment for queries with subqueries in…
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
…on MySQL Sandbox to set up a little throw-away MySQL instance — that took ten seconds. And then …usage | +———————–+ | index_alternatives | | index_usage | | indexes | | queries | | tables | +———————–+ Now let’s run some queries! From the documentation, you can copy-…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 12 Comments
… using some silly like query to illustrate some search is required, so count(*) can’t be optimized away for MyISAM Tables. To… where clause. Most Simple Way Number One is to simply run two queries: mysql> select grp, count(*) cnt from dt where slack… with performance penalty. This approach is however still faster than running 2 queries completing in 30 seconds rather than 40. Though fetching…
