Basic server-side parameters
Ideally, we can split mysql server-side options in two group:
- options that, while influencing server speed, don't affect its reliability (in other words: that don't increase data loss probability);
- options that affect speed and reliability also.
In the first group, we can find options that mostly deal with reads: for example, the amount of buffer memory to use, the query cache state and size, and similar things. On the other hand, in the second group we can find options that mostly deal with writes: for example, the disk flush policy, the disk access methods, and so on.
The key point to understand is that, while for reads we are basically dealing with read-only caches that, for their very nature, are unable to cause data loss, for writes there is no free lunch: as write speed is generally bound by disk access time, increase writes rate often means to delay some of them, in the hope to collapse multiple writes into a single disk access. This means that any server crash / power outage can lead to greater data loss. (Note: this is the very reason because high-end disk controllers feature a persistent cache that protect against write loss).
So, what options are we going to examine in this article? In the first, read-related (and safe) group we have the following:
- innodb_buffer_pool_size: this is the size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables;
- query_cache_size: this is the amount of memory allocated for caching query results;
- query_cache_type: this value controls if the query cache is disabled (0, default value), always enabled (1) or on-demand enabled (2).
On the other hand, in a effort to increase write speed we are going to explore the following options:
- innodb_flush_method: this controls what kind of synchronization primitive, and file open options, mysql will use to flush data and log files;
- innodb_flush_log_at_trx_commit: it determines mysql disk write policy (basically, it the system should try to synch every commit or once per second).
A complete list and detailed explanation of mysql and InnoDB options can be found on mysql web site, here and here.