Percona Toolkit in Launchpad. Pt-query-advisor — Percona Toolkit Documentation. Pt-query-advisor - Analyze queries and advise on possible problems.
Usage pt-query-advisor [OPTION...] [FILE] pt-query-advisor analyzes queries and advises on possible problems. It can read queries from several types of log files, or you can use the –query or –review options. To analyze all queries in a MySQL slow query log file: pt-query-advisor /path/to/slow-query.log The following section is included to inform users about the potential risks, whether known or unknown, of using this tool. Pt-query-advisor simply reads queries and examines them, and is thus very low risk. The authoritative source for updated information is always the online issue tracking system. See also “BUGS” for more information on filing bugs and getting help. How to calculate a good InnoDB log file size - MySQL Performance Blog. Choosing innodb_buffer_pool_size - MySQL Performance Blog. What To Tune In MySQL Server After Installation. Choosing innodb_buffer_pool_size - MySQL Performance Blog. Optimize MySQL Database for Increased InnoDB Performance.
Tuning MySQL Server Settings. The default configuration file for MySQL is intended not to use many resources, because its a general purpose sort of a configuration file.
The default configuration does enough to have MySQL running happily with limited resources and catering to simple queries and small data-sets. The configuration file would most definitely need to be customized and tuned if you intend on using complex queries and when you have good amount of data. Most of the tunings mentioned in this post are applicable to the MyISAM storage engine, I will soon be posting tunings applicable to the Innodb storage engine. Getting started Before you start changing configuration settings, you should keep in mind the fact that “more is not always better”. Now let’s get our hands dirty. Key buffer size The the most important option if you are using lots of MyISAM tables is key_buffer. Thread cache The thread cache holds threads that are not being used by any connection, but are available to be used.
Table cache. Reduce your server's resource usage by moving MySQL temporary directory to tmpfs. For sites that have lots of slow queries, disk access is often the bottleneck.
For these slow queries, MySQL writes temporary tables to disk, populates them with intermediate results, then query them again for the final result. We all know that the disk is the slowest part in a computer, because it is limited by being mechanical, rather than electronic. One way of mitigating this is to tell MySQL to use memory rather than disk for temporary tables. This is often done by creating either a RAM Disk, or the easier to use tmpfs. Both are a portion of the server's RAM memory made to emulate a disk with slightly different details: RAM disk has a file system on it that can be ext3 or anything, while tmpfs is its own file system type. Since memory access is much faster than a disk, this improves performance, and decreases load on the server by not causing pile up bottlenecks on disks. We describe here methods to achieve this goal. Method 1: Using an existing tmpfs directory Add this to the file:
~matt-day32/mysql-tuning-primer/trunk : contents of tuning-primer.sh at revision 28. MySQL Tuning Primer Script in Launchpad. 8.4.3.1 How MySQL Opens and Closes Tables. MySQL Innodb Performance Tuning For Disk-Bound Workloads On CMT Servers (Jenny Chen's Weblog) MySQL is one of the world's most popular open source databases, and it is widely used and becoming the database-of-choice for many leading Web 2.0 sites.
Like most database servers, the most common bottleneck in the enterprise environment encountered by MySQL is disk I/O. To maximize the performance of MySQL for disk I/O bound workloads on the Solaris operating system on CMT servers (e.g. the Sun Fire T2000 Server), configuration and tuning of MySQL server variables is critical and can make a big difference to performance, as does the optimization of the Solaris filesystem for MySQL, and the configuration of storage arrays. Configuration Issues MySQL server's performance can be optimized using various configuration settings. The first step is to read the configuration and system variables by running the command: mysql> show variables; Once you have done this, you can take appropriate action to configure/tune the variables for better performance. 1. 2.