MySQL, like most database managers, can easily modify the parameters that control memory sizes engaged in certain tasks, resource utilization, concurrency limits, etc.
Properly adjusting these parameters can be obtained many performance improvements, especially if the server / s of the database is not about resources, and if the SQL optimization can not be improved more.
I've recently made some basic settings in a MySQL database, so I take this opportunity to explain some of the process I followed for those who seek an easy way to make a first optimization of parameters in the database. This is not to say that this is the best way to do just that to me has worked out well;)
The first comment that can be very helpful to look in phpMyAdmin to the sections 'Show information about MySQL runtime' and 'Show MySQL system variables', normally accessible from the home page of the application.
The first displays information and statistics collected and maintained the system since its beginning. Pay special attention to the values of variables that are displayed in red, and the advice provided to the right of these values.
Please refer to the variables used to determine the current values of the parameters that could later be modified to improve performance.
Once this is done and we certainly have no clue where to attack, but what matters is something more concrete than suggesting we could play exactly what parameters to improve the performance of our database. To do this I have used mysqltunner.pl, which is a perl script that analyzes the performance of the BD and the values of the parameters and makes suggestions after the analysis of changes in certain of these values.
If you meet the requerimientos, use the script on a Linux / Unix is as easy as running from the command line
> wget mysqltuner.pl > chmod 755 mysqltuner.pl > perl mysqltuner.pl
Once done, the script you may return results similar to these:
MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net> Bug reports, feature requests, and downloads at https://mysqltuner.com/Run with '--help' for additional options and output filtering ---------------------------------------- -------- General Statistics ---------- [-] Skipped version check for MySQLTuner script [OK] Currently running version 5.0.45 MySQL Supported [OK] Operating on 32-bit architecture with less Than 2GB RAM --------------------------------------- -------- Storage Engine Statistics ---- [-] Status:-Archive-BDB-Federated + InnoDB-ISAM-ndbcluster [-] Data in MyISAM tables: 156m (Tables: 390) [-] Data in InnoDB tables: 2M (Tables: 126) [!] Totally fragmented tables: 46 ---------------------------------------- -------- Performance Metrics --------- [-] Up for: 18m 8s (296K q [272,847 qps], 689 conn, TX: 1B, RX: 39M) [-] Reads / Writes: 94% / 6% [-] Total buffers: global + 2.7M 35.0M per Thread (100 max threads) [OK] Maximum possible memory usage: 303.7M (33% of installed RAM) [OK] Slow queries: 0% (0/296K) [OK] Highest usage of available connections: 8% (8 / 100) [OK] Key buffer size / total MyISAM indexes: 8.0M/52.0M [OK] Key buffer hit rate: 99.2% (2M cached / 21K reads) [OK] Query cache Efficiency: 81.6% (cached 237K / 291K selects) [!] Query cache prunes per day: 3650717 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 5K sorts) [!] Joins Performed Without indexes: 33 [!] Temporary tables created on disk: 26% (1K on disk / total 7K) [!] Thread cache is disabled [OK] Table cache hit rate: 48% (128 open / 265 opener) [OK] Open file limit used: 11% (229/2K) [OK] Table locks Acquired Immediately: 99% (73K immediate / locks 73K) [!] InnoDB data size / buffer pool: 2.0M/2.0M Recommendations ----------------------------------------- -------- ------------ General Recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started last Within 24 hours - Recommendations May be inaccurate Enable the slow query log to troubleshoot bad queries Adjust your join queries to Always use Index When making adjustments, make tmp_table_size / max_heap_table_size equal Reduce your SELECT DISTINCT queries Without LIMIT clauses Thread_cache_size September to 4 as a starting value Variables to adjust: query_cache_size (> 8M) join_buffer_size (> 128.0K, or Always use indexes with joins) tmp_table_size (> 60M) max_heap_table_size (> 16M) thread_cache_size (start at 4) innodb_buffer_pool_size (> = 2M)
On issues of optimization should never touch the things cheerfully, so now he touches is to study the information and recommendations proposed by applying the script and go that we feel most appropriate.
The first recommendation is to run the OPTIMIZE TABLE on multiple tables in which it has detected that there is fragmentation. To this we must find out what tables may require a defragmentation and apply this command on them.
The following are recommendations and suggestions on the parameters of the BD. The application shows home values in cases where the proposal is to increase the value. We will go up gradually and be validated if we get improvements, and also that we do not use more memory than you have available.
To change the values of the parameters you can edit the my.cnf file, usually found in the directory / etc
In this case, for example, could have inserted in our my.cnf lines
#Parameters changed by recommendations sqltuner query-cache-size = 16M join-buffer-size = 256K tmp-table-size = 90M max-heap-table-size = 90M thread-cache-size = 4 innodb-buffer-pool-size = 4M # Amendements end optimization
Then you have to restart the database for these changes to take effect.
Finally comment that if the information you provide the script to be reliable, the database should have been operating continuously for at least 24 hours to go so adjusting the values sqltuner executions of no use to change the parameters and return run the script immediately.
After restarting the database to apply the changes you have to wait a day or two before returning to seek help from the script to tune our database.
