Here are some common performance tuning concepts that I frequently run into. Please note that this really is only a basic introduction to performance tuning. For more in-depth tuning, it strongly depends on your systems, data and usage. Server Variables For tuning InnoDB performance, your primary variable is innodb_buffer_pool_size. This is the chunk of memory […]
Tag Archives: MySQL
Don’t Quote Your Numbers
It’s a fairly simple rule, and something that should be obeyed for your health and sanity. There are a couple of bugs which you could run into, when quoting large numbers. First of all, Bug #34384. This is concerning quoting large INTs in the WHERE condition of an UPDATE or DELETE. It seems that this […]
The \G modifier in the MySQL command line client
A little publicized, but exceedingly useful feature of the MySQL command line client is the \G modifier. It formats the query output nicely, so you can read through it easier. To use it, you just replace the semi-colon at the end of the query with ‘\G’. For example, checking the master status: mysql> SHOW MASTER […]
Upgrading MySQL with minimal downtime through Replication
Problem With the release of MySQL 5.1, many DBAs are going to be scheduling downtime to upgrade their MySQL Server. As with all upgrades between major version numbers, it requires one of two upgrade paths: Dump/reload: The safest method of upgrading, but it takes out your server for quite some time, especially if you have […]
Permissions by interface on the local server
I had an issue come up recently that involved some confusion over permissions for the same user, connecting through different interfaces. For example, say you have a server with the public IP address of 192.168.0.1. You could connect to it from the local machine using the following commands: shell> mysql -h localhost # Connects through […]