Bench marking
the example below shows that the divide takes 10 times longer to run than the addition
select benchmark(10000000, 1+1);
select benchmark(10000000, 1/1);
show global status
The show global status command can display one important variable regarding performance and that is the slow_queries variable
mysqltuner
You can use the utility mysqltuner which is a Perl script which can give the following information
General statistics
Storage engine statistics
Performance metrics
mysqlreport (MySQL server status with mysqlreport report script)
use it regularly against my databases to highlight any problems, it is very similar to mysqltuner in that it is a perl script and it uses show status to
gather an overall picture of a servers health, however it does not provide any recommendations.
Q. How to find out more information about MySQL server status?
A. The MySQL server maintains many status variables that provide information about its operation. You can see upto date variables lists and explanation of its usages online. For example you can connect to MySQL server and execute SHOW STATUS
$ mysql -u vivek -p
mysql> show status;
Output:
+——————————–+————+
| Variable_name | Value |
+——————————–+————+
| Aborted_clients | 0 |
| Aborted_connects | 4 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 208711192 |
| Bytes_sent | 2331972589 |
| Com_admin_commands | 1 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
….
…..
.
| Threads_connected | 1 |
| Threads_created | 53 |
| Threads_running | 1 |
| Uptime | 143182 |
+——————————–+————+
186 rows in set (0.00 sec)
However it takes some time to understand these variables. I recommend using mysqlreport which makes a friendly report of important status variables.
From the project home page:
It makes a friendly report of nearly every status value from SHOW STATUS. Unlike SHOW STATUS which simply dumps over 100 values to screen in one long list, mysqlreport interprets and formats the values and presents the basic values and many more inferred values in a human-readable format. Numerous example reports are available at the mysqlreport web page.
The benefit of mysqlreport is that it allows you to very quickly see a wide array of performance indicators for your MySQL server which would otherwise need to be calculated by hand from all the various SHOW STATUS values
How do I use mysqlreport?
First, download mysqlreport perl script:
$ wget http://hackmysql.com/scripts/mysqlreport
Now, execute mysqlreport
$ ./mysqlreport –user root –host localhost –port 3306 –password
Password for database user vivek:
mysqlreport –user root –password -host
