Dbms. DimitriK's (dim) Weblog : May 2010. MySQL Performance: Using Performance Schema Two weeks ago I was very lucky to meet in Paris Marc Allf (one of developers of MySQL Performance Schema) and get all details about Performance Schema directly from the source :-) I would say I'm very excited about this feature giving so enormous potential to observe any activity inside of MySQL, as well by a great flexibility of the proposed solution! :-) However, discussing with Marc and going over a day more and more in depth, I've got some remarks that I needed to group, summarize and then express (and vacation time helping here a lot ;-)) My points will not be about adding some new extensions, but rather fixing some core issues which I consider important (and, please, correct me if you're not agree, express your ideas, etc - it's one of the cases where a quantity of opinions will be easily transformed into the quality solution for sure :-)) Performance Impact Following cases were tested as a start point: Read-Only: As you can see: Read+Write:
HeidiSQL - MySQL made easy. Sequel Pro — MySQL database management app for Mac OS X. MySQL Tutorial. Tuning MySQL Performance with MySQLTuner. Version 1.0 Author: Falko Timme <ft [at] falkotimme [dot] com> Last edited 08/28/2008 MySQLTuner is a Perl script that analyzes your MySQL performance and, based on the statistics it gathers, gives recommendations which variables you should adjust in order to increase performance. That way, you can tune your my.cnf file to tease out the last bit of performance from your MySQL server and make it work more efficiently. This document comes without warranty of any kind! I do not issue any guarantee that this will work for you! 1 Using MySQLTuner You can download the MySQLTuner script as follows: wget In order to run it, we must make it executable: chmod +x mysqltuner.pl Afterwards, we can run it. . server1:~# . -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB +ISAM -NDBCluster [--] Data in MyISAM tables: 301M (Tables: 2074) [--] Data in HEAP tables: 379K (Tables: 9) [!!]
Server1:~# 2 Links. Rails SQL Optimization. In order to automate the optimization of SQL queries in Ruby on Rails, I wrote this script to parse the output of the logs. It finds all the unique SQL queries made by different controllers, and runs an SQL EXPLAIN for each. This is really good for finding missing indexes and large table scans. I have only tested this on Mac/MySQL, so your mileage may vary (it uses *NIX file paths). This script has saved me MUCH time. Of course, when optimizing queries, don't go overboard on eager loading.
ActiveRecord eager loading can be slow if you use multiple nested includes. Just run this script within your Rails application root directory, and it will create a file named explained.html. Oh, and there needs to be at least 2 pageloads in your log. So here is the script. UPDATE: I came across some good tips for additional optimization here. DBA Dojo: MySQL Backups using LVM snapshots. There are a couple of options available to get consistent backups from MySQL. Use mysqldump with FLUSH TABLES WITH READ LOCKUse a slave with STOP SLAVE and your favourite backup tool.For innodb, use the commerical backup tool ibbackupUse LVM (Logical Volume Manager) snapshots with FLUSH TABLES WITH READ LOCKShutdown the database. We are going to demostrate how to use LVM snapshots to reduce time required to hold MySQL consistent. I used these two good articles here and here as a basis for this one.
To use LVM on EC2, you need to umount the /mnt partition and create a physical volume (pvcreate) and then a bunch of logical volumes (lvcreate). Once you have the use of LVM, you can use lvcreate -s to create a snapshot of the logical volume. The ease of taking backups like this means the time that the mysql database must be unable to handle writes (due to the read lock) is as short as the time takes for the lvcreate to finish. Here is the procedure to take a backup using LVM. Notes: Have Fun Paul #! Online Solutions with MySQL.