MySQL provides optimizer control through system variables that affect how query plans are evaluated, switchable optimizations, optimizer and index hints, and the optimizer cost model. The server maintains histogram statistics about column values in the columnstatistics data dictionary table (see Section 8.9.6, “Optimizer Statistics” ).
- Introduction to MySQL Optimize Table. There is a component in Mysql named query optimizer that optimizes the complex queries and decides the flow of the execution and the index to be used of the tables involved in the query statement to make as much optimization in.
- MySQL Optimizer is an effective app that gives you recommendations to optimize MySQL databases. The app provides a graphical interface simplify the process of viewing performance metrics, apply changes based on our recommendations and all mysql slow query logs which are grouped by occurrence number.
If your application is performing lot of deletes and updates on MySQL database, then there is a high possibility that your MySQL data files are fragmented.
This will result in lot of unused space, and also might affect performance.
So, it is highly recommended that you defrag your MySQL tables on an ongoing basis.
This tutorial explains how to optimize MySQL to defrag tables and reclaim unused space.
1. Identify Tables for Optimization
The first step is to identify whether you have fragmentation on your MySQL database.
Connect to your MySQL database, and execute the following query, which will display how much unused space are available in every table.
In the above output:
- This will display list of all tables that has minimum of 500MB of unused space. As we see above, in this example, there are 3 tables that has more than 500MB of unused space.
- data_length_mb column displays the total table size in MB. For example, EMPLOYEE table size is around 21GB.
- data_free_mb column displays the total unused space in that particular table. For example, EMPLOYEE table has around 19GB of unused space in it.
- All these three tables (EMPLOYEE, DEPARTMENT AND BENEFITS) are heavily fragmented and it needs to be optimized to reclaim the unused space.
From the filesystem level, you can see the size of the individual table files as shown below.
The file size will be the same as what you see under “data_length_mb” column in the above output.
In this example, the EMPLOYEE.MYD file is taking up around 22GB at the filesystem level, but it has lot of unused space in it. If we optimize this table, the size of this file should go down dramatically.
2. Defrag using OPTIMIZE TABLE command
There are two ways to optimize a table.
The first method is to use Optimize table command as shown below.
The following example will optimize EMPLOYEE table.
You can also optimize multiple tables in a single command as shown below.
Few points to keep in mind about optimize table:
- Optimize table can be performed for InnoDB engine, or MyISAM engine, or ARCHIVE tables.
- For MyISAM tables, it will analyze the table, it will defragment the corresponding MySQL datafile, and reclaim the unused space.
- For InnoDB tables, optimize table will simply perform an alter table to reclaim the space.
- If you have indexes, it will also resort the index pages, and update the statistics.
During optimization, MySQL will create a temporary table for the table, and after the optimization it will delete the original table, and rename this temporary table to the original table.
In the above optimization, the EMPLOYEE table is an MyISAM table.
For this example, before the optimization, you’ll see the following .MYD file for the table.
When the “OPTIMIZE TABLE” command is running, you can see that it has created a temporary file for this table with extension .TMD. The size of this temporary file will keep growing until the optimize table is running.
After the optimize table command finishes, you’ll not see the temporary table. Instead, you’ll see the original EMPLOYEE.MYD file that is optimized and with reduced file size.
3. Defrag using mysqlcheck command
The second method to optimize a table is using mysqlcheck command as shown below.
The following example will optimize the DEPARTMENT table. You’ll execute this command from the Linux prompt (and not on MySQL prompt).
Note: Internally mysqlcheck command uses “OPTIMIZE TABLE” command.
In the above example:
- mysqlcheck is the command that is executed from the Linux prompt.
- -o option is to indicate that mysqlcheck should perform “optimize table” operation.
- thegeekstuff is the database
- DEPARTMENT is the table inside thegeekstuff database that should be optimized
- -u root indicates that mysqlcheck command should use “root” as the mysql user to connect
- -p indicates the password for the root account of mysql. Please note that there is no space between -p option and the password.
Apart from optimization, you can also use mysqlcheck command to check, analyze and repair tables in your mysql database.
4. Defrag All Tables or All Databases
If you want to optimize all the tables in a particular MySQL database, use the following command.
The following command will optimize all the tables located in thegeekstuff database.
If you have multiple database running on your system, you can optimize all the tables located under all the database on your system using the following command.
The following will optimize ALL database on your system.
5. After Optimization
After the optimization, using the following query, check the total-size and unused-space-size for the three tables that we optimized in this example.
As we see from the above output, the data_length_mb is drastically reduced for these tables after the optimization. Also, the data_free_mb is now at 0, as there are no fragmentation anymore.
The file size for these tables are now lot less when compared to the original size. We have now reclaimed lot of unused space at the filsystem level for these tables.
In this example, we’ve reclaimed around 37GB of unused space after optimizing these three tables.
-->displays mysql metrics and slow queries
Intuitive
MySQL Optimizer App provides an intuitive interface designed to simplify the process of viewing database metrics and issues.
Flexible
MySQL Optimizer is designed to function with either a dedicated server or a virtual private server. If you don't remember your mysql root password you can reset with new one.
Complete
Completely remote system so you don't have to install any software on your server. We provide the tools you need.
Safe
MySQL Optimizer applies recommendations safe as the app makes a backup every step so you can easily revert to previous mysql cofiguration .
everything you need to speed up your web sites
Cutting Costs & Saving Time
MySQL Optimizer lowers system administrator workload therefore saving you money. MySQL Optimizer is the perfect solution to have your database optimized and let you dedicate your energy to company growth.
User friendly
The app look and feel is taken as important as its feature set. The navigation feels like second nature along with visual appearance, interactive behavior and assistive capabilities.
Select Features
Everything you need to view database metrics, network traffic, memory usage, slow query logs and apply our recommendations. We're working continuously on many more features.
Reliable
We are a young team working to improve your experience, listen to your feedback along with your feature requests that should be included for next versions updates. We'll always have your back.
'This app is simple and does the job, exactly what I needed so I don't have to install 10 tools to tell me same thing - keep it up, I also purchased your server manager app :) .'
05.07.2016 - Patrick Lee
- MySQL Optimizer App
- One time payment, unlimited servers, lifetime access.
- $5.99
- Dashboard with server and mysql info
- Setup up unlimited servers
- See General, InnoDB and myISAM metrics
- See out recommendations of issues and where applicable you can just set the values and apply settings
- View slow queries, sort them by occurences and time
- Apply changes safe as config files are automatically backed up so you can revert if you need to
- Download from App Store
What should you know before you make your purchase
Mysql Optimizer Not Using Index
The app only works on MAC OSX Systems - El Capitan(10.11) or newer
MySQL Optimizer is an effective app that gives you recommendations to optimize MySQL databases. The app provides a graphical interface simplify the process of viewing performance metrics, apply changes based on our recommendations and all mysql slow query logs which are grouped by occurrence number.
Mysql Like Optimization
Main Features:
- Performance Metrics (very useful info about your server and mysql)
- Slow Query logs that are already grouped so you can fix slow queries that have highest occurrence first.
- Recommendations tab is the app main function, where you see a list of issues and recommendations, where applicable you can set the value and just tick to apply new values.
- When you apply changes MySQL service restarts so your web sites will be offline for couple seconds
- Make sure you understand the changes you make as in some cases they might not be suitable
- You can always revert changes by going to Backup/restore tab and select the version you want to revert to
- Once you apply some changes MySQL needs time to collect data again for new statistics and recommendations and while it's recommended to wait 24hours before applying more changes, the app will allow you to do it after 6hours
Read our FAQ and User Guide for more details.
Unlimited servers, domains, emails only means that the app functionality is not limited or restricted in any way, you may still be limited by your server resources.