… know) about Linux swappiness setting, which basically controls how important is the file cache for Linux. Basically… explained this here and here. In summary, you need to interleave the allocation of memory for the MySQL …at 10:03:36. Also, the server is reading a lot (~150MB/s) but doing very …
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…sent: 0 Rows_examined: 10000000 Rows_affected: 0 Rows_read: 0 # Bytes_sent: 213 Tmp_tables: 0 Tmp_disk…where a.id=5 and b.id=a.k; mysql> explain select * from sbtest a,sbtest b where a.id…with “yes or no” answer but rather helps to understand how much optimization is possible. For example I might have…
Post: How to Monitor MySQL with Percona's Nagios Plugins
February 22, 2012 By 11 Comments
… MySQL monitoring plugins we created for Nagios, and explain their features and intended purpose. I want to …, and their causes and preventions. You can read more about that research in our white …server has been restarted recently. It is surprising how often a server crashes, perhaps even cycling …
Post: How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 10 Comments
… Oracle’s stock MySQL you cannot move your ibd files freely from one server to another or from one database to another. The… be focus on how to recover a single tablespace using stock MySQL server. First, you must meet certain prerequisites to be able to restore a… process to work. You can read more about this feature in Percona Server Documentation In the next blog post I’ll explain how to do…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… also elect to use in some cases): vmstat 5 5 iostat -dx 5 5 If you don’t know how to read these, there’s a full explanation in our book, High Performance MySQL 2nd Edition (it’s… the query plan. This is where you need to really know how to write queries and how EXPLAIN works. At Percona, we have peer training…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… have LIMIT which restricts how many rows will be examined MySQL will still print full number. Here is example: mysql> explain select * from lt… enough to perform sorting or use temporary table with partial result set (partial rows) and than compose full rows by reading data… at least an alternative view. If you want to find more information about understanding EXPLAIN output, MySQL manual EXPLAIN page is good place to start.
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
…” width=”300″] Catch the webinar: “Learn How MySQL 5.6 Makes Query Optimization Easier”… Let’s have a look at the EXPLAIN output: mysql> EXPLAIN SELECT * FROM cast_info WHERE role…MySQL 5.5. For example, in this case, the actual number of “Handler_read_next” calls was reduced from 8346769 (5.5) to…
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
… log to my laptop, which is across the continent. I used a variation on MySQL Sandbox to set up a little throw-away MySQL… SSH tunnel to query EXPLAIN and find out the index usage on the remote server, and store the results in my MySQL sandbox… | … snip … 14 rows in set (0.00 sec) Here’s how to read this — the first row says that query 1262633894049058504 has two…
Post: Upgrading MySQL
January 5, 2010 By 24 Comments
… 4.1 to 5.1 just because so much more people have followed that road. So how do…to run comparison. The tool will run SELECT queries on both new and old MySQL installations and check result set, explain… also a safe way to test things out – you can move read traffic to such slave to ensure new version …
Post: Percona MySQL Webinar: Really Large Queries: Advanced Optimization Techniques, Feb. 27
February 18, 2013 By 2 Comments
…will discuss filling the gap between reading explain output and optimizing really large queries. Practical techniques used to address real-world situations … organizations often make. The talk will cover topics including: • How to start optimizing large queries • The use of denormalization • The …
