Lukas Vileikis is an ethical hacker and a frequent conference speaker.
– Atomicity means that statements in a transaction must operate as an indivisible unit meaning that their results must be seen collectively or not seen at all.
– Consistency is handled by MySQL’s logging mechanisms.
– Isolation – row-level locking provided by InnoDB.
– Durability means that InnoDB maintains a log file that tracks all changes.
The explanation above should give you a pretty good idea of how powerful InnoDB really is, but in order to keep the engine running as smooth as butter, you need to understand how is it monitored.
Before we actually tell you how you should monitor the performance of your InnoDB engine, we should probably answer the question “why do you need to monitor it in the first place?”. In general, monitoring the performance of all of your engines (not only InnoDB) can come in handy if you want to make sure your database always is in tip-top shape and also to avoid issues that might arise in the future due to mismanagement of data or other things.
What do You Need to Monitor?
When it comes to InnoDB, there’s a couple of things that you need to keep in mind (we won’t list everything here, but this list should give you an idea of just how powerful InnoDB really is):
– The data residing in InnoDB engines is situated around the ibdata1 file which stores all data relevant to InnoDB including the data and indexes of InnoDB tables, table metadata, MVCC data, the doublewrite buffer and the insert buffer.
– One of the most important parameters in InnoDB is the innodb_buffer_pool_size parameter – this parameter acts as a memory buffer that InnoDB uses to cache the data and indexes of its tables. For the best performance, the value of this parameter should consist of 50- 80% of the available RAM on your system (keep in mind that you need space for the processes running in the OS aswell)
– The innodb_log_file_size parameter defines the size of InnoDB log files.
– The innodb_log_buffer_size is used to write to the log files on disk.
As you can probably tell, you should try to monitor at least some of those things because they are absolutely crucial for InnoDB to function properly – now we will look into how you can monitor one of the most important parameters of InnoDB – the innodb_log_buffer_size – in dbWatch.
How do You Monitor the InnoDB Engine in dbWatch?
In general, to monitor the InnoDB engine in dbWatch, run the InnoDB buffer pool check job:
This job can be configured with a hit ratio alarm threshold and a hit ratio warning threshold. The hit ratio alarm threshold will give an alarm if the hit ratio falls behind a certain specified percentage that you specify and the hit ratio warning threshold will give a warning if the hit ratio falls below a certain
This job is very useful when monitoring InnoDB because by using it, you can verify that tables running InnoDB in your MySQL instance are actually doing an effective job and InnoDB is used properly. If the hit ratio falls behind a certain low percentage, it might be time to check your my.cnf configuration.