|
|
| Line 3: |
Line 3: |
| == MySQL == | | == MySQL == |
|
| |
|
| [[MySQL#Performance_Tuning]] | | [[MySQL#Performance_Tuning MySQL Performance Tuning]] |
| | |
| | |
| | |
| === Performance Tuning ===
| |
| | |
| '''1. First, Check innodb and myisam settings'''
| |
| | |
| Run the below to get the size of data in INNODB and MYISAM tables:<br />
| |
| <pre>mysql -e 'SELECT SQL_NO_CACHE Engine, count(*) "# of Tables", concat(round(sum(table_rows)/1000,2),"K") "Rows", concat(round(sum(data_length)/(1024*1024),2),"M") "Size of Data", concat(round(sum(index_length)/(1024*1024),2),"M") "Size of Indexes", concat(round(sum(data_length+index_length)/(1024*1024),2),"M") "Total Size", concat(round(sum(index_length)/sum(data_length)*(100),2), "%") "% Data in Indexes" FROM information_schema.TABLES GROUP BY engine ORDER BY sum(data_length+index_length) DESC LIMIT 10;'</pre>
| |
| | |
| In /etc/my.cnf, change:<br />
| |
| | |
| <code>innodb_buffer_pool_size</code> - set this to the value from the Total column, innodb row.<br />
| |
| <br />
| |
| '''Note:''' if server has lots of RAM, and lots of data, set the innodb_buffer_pool_size = 1G, then set innodb_buffer_pool_instances = 4 (or smaller, or larger, depending on RAM) Also note that this will set the buffer pool size as 1G, and break that 1G into 4 chunks (It will NOT create 4, 1GB pools.)
| |
| | |
| <code>key_buffer_size</code> - set this to the value from the Index column, myisam row.
| |
| | |
| | |
| '''2. Opens'''
| |
| | |
| a.) Get the total number of tables on the server:<br />
| |
| <code>find /var/lib/mysql -name '*.frm' | wc -l</code> - Example output: 4568
| |
| | |
| b.) Set table_open_cache variable in /etc/my.cnf to 30% of that number<br />
| |
| Example, based on the above number: <code>table_open_cache = 1371</code>
| |
| | |
| c.) Set open_files_limit to three times the corrected value of table_open_cache<br />
| |
| Example: <code>open_files_limit = 3951</code>
| |
| | |
| You can check to see if this value is good by running <code>mysqladmin stat</code>. That command will show the number of Opens, and the number of Open tables. Ideally, these numbers should be identical.
| |
| | |
| === MySQL Error log ===
| |
| | |
| To check the MySQL error log, run:<br />
| |
| <code>mysql -e "show variables like '%log_error%';"</code>
| |
| | |
| This will show the location of the MySQL error log, which you can then grep, tail, etc. (You're looking for stuff like innodb errors, disk space issues, connection messages, etc)
| |
| | |
| | |
| === View MySQL Variables ===
| |
| | |
| You can view the variables that MySQL is using with:
| |
| | |
| <code>mysql -e "show variables like 'max_connections%';" </code> - This shows the max_connections variable. You can substitute any MySQL variable here.
| |
| | |
| This is helpful for database connection errors, or if a change to /etc/my.cnf isn't 'sticking' (In that case, look for other my.cnf files)
| |
| | |
| | |
| === View Currently Running Queries ===
| |
| | |
| <code>mysqladmin process stat</code> - This will show the queries that are running, how many threads, Open tables, slow queries, and a bunch of other helpful stuff. (Note that you can "tail -f" by running: <code>watch mysqladmin process stat</code> )
| |