MySQL Tips and Tricks for Better performance
NoSQL databases maybe the cool kids on the block, but the days of Relation Data Bases are far from over. With proper tweaking, the performance of these traditional databases can be very well optimised. Please note that the tips discussed here are applicable for MySQL databases and it’s many variants, although the broader concepts may apply for other systems as well.
Optimising for Writes
In the recent past, we had to adjust a customer’s IoT Server deployment for high number of writes. Given the large device count and high communication frequency, performance against a non-optimised MySQL DB, proved to be abysmal. To overcome this I ended up adjusting the following few parameters.
innodb_buffer_pool_size
InnoDB caches tables and indexes, whenever data is accessed in an area of memory called the Buffer Pool. When a change is done to a row, what’s changed first is the page pertaining to this data in the buffer pool. This page will later be flushed and written back to the disk. Note that in MySQL, for an update to be marked as successful, the page need not necessarily be written to the disk, rather just the transaction log (redo-log according to Oracle terminology) will be updated. This is adequate for the transaction to stay safe. In the event of a crash or failure all changes present in the re-do log will be applied upon recovery.
To be fair, both reads and writes benefit from having an increased innodb_buffer_pool_size, also both master and slave nodes (if present). Increase in the buffer pool will increase the likelihood of a given page being present in RAM, when a query is run, leading to better performance. It is usually safe to set this parameter to a value that is about 70% — 80% of the total memory available in the MySQL server instance, equating to about 1/4 the size of your database.
It is to be kept in mind that innodb_buffer_pool_size should be a multiple of innodb_buffer_pool_chunk_size (If not this will be adjusted automatically by the InnoDB engine, during runtime). This is because the buffer pool consists of individual chunks, of the specified size (by default this is set to 128MB, and I do not recommend you meddle with the default value. If you need to change this refer this article), and chunks will be added or removed depending on the expansion or constriction of the pool.
Information on how to change buffer pool size available here.
innodb_flush_log_at_trx_commit
This property controls the balance between ACID compliance and performance. As we discussed in the earlier topic, for InnoDB engine to know a transaction actually took place, there needs to be a transaction log entry present. MySQL does something called write-ahead-logging to maintain data consistency. This will result in transaction log being updated prior to commit, for every transaction. While this is necessary for transaction completion, it can also be an overhead. Also to be considered is the fact that, the log entry needs to be written to durable storage or the transaction will be lost entirely, in the event of a system crash or volatile storage failure. Therefore, InnoDB engine provides the topic under discussion as a programable parameter to optimise performance.
The value for innodb_flush_log_at_trx_commit can be either 0, 1 or 2. By default this is at 1, meaning transactions logs are written to disk at each transaction commit. This ensure full ACID compliance, and no data loss in the event of a crash. Setting this property as 0 will make sure transaction logs are written and flushed to durable storage every second. In a high write scenario, where a reasonable consideration needs to be given for consistency, I believe setting this value to 2 is ideal. Doing so, will make sure that transaction logs are written at each transaction commit. However, flushing to durable storage will only happen once every second.
This value can be set in run time as follows.
SET GLOBAL innodb_flush_log_at_trx_commit=2
More information on the property available here.
Optimising for Reads
When it comes to optimising for reads, once we cross off the usual culprits like:
- Indexing columns
- Restructuring database schemas
- Optimising querries (Minimising use of ‘like’s, full-text searches, wildcards etc.)
- Implementing Query Caching
There comes a point where some focus need to be given to adjusting the transaction isolation level, to eke out the maximum performance possible, from the DB engine, without compromising too much on reliability, consistency and repeatability of the results when multiple transactions are making changes at the same time.
READ COMMITTED
By default the InnoDB engine will be using REPEATABLE READ level for transaction isolation. This will ensure a consistent result set across multiple reads within the same transaction. This is made possible by a snapshot which is established upon the first read. If you can afford to relax this consistency a little bit, we can use READ COMMITTED isolation, which will eliminate the dependency on this original snapshot resulting in much higher read throughputs.
This level of isolation, avoids the phenomenon of a dirty read as only changes that are committed will be taken in to account. However, given that each SELECT uses its own snapshot, the same query executing multiple times within the same transaction will result in a different sets of results (given that other transactions are performing changes to the same set of data, simultaneously). This phenomenon is called non-repeatable read.
For more information on this please refer this link.
On a final note, there are many points of optimisation in InnoDB, what’s discussed here is just a handful. However, all these performance tweaks are highly subjective and scenario dependent. There is always a compromise somewhere, so do your research and ample amounts of testing before settling on a set of properties that works for you.
Further reading :
