D B A S E C E N T E R

Loading

mysql-dba-consulting-dbasecenter

Your time span is limited so I will keep it uncomplicated & precise. In the first series of sexy tips and tricks for MySQL, I will present you with some valuable content which you now and then would need for yourself or forward a co-worker etc.

In MySQL there are only five really important variables that need to be changed. However, there are other InnoDB and global MySQL variables that might need to be tuned for a specific workload and hardware.

1. innodb_buffer_pool_size

Start with 50% 70% of total RAM. Does not need to be larger than the database size.

To figure out the total size of your database run this query:

select engine,
round(sum(data_length+index_length)/1024/1024/1024, 2) as total_gb,
round(sum(data_length)/1024/1024/1024,2) as data_gb,
round(sum(index_length)/1024/1024/1024,2) as index_gb
from information_schema.tables
where table_schema not in ('information_schema', 'mysql', 'performance_schema')
and TABLE_TYPE <> 'VIEW' and engine="Innodb"
group by 1
order by 2 des2c;

2. innodb_log_file_size

This is the size of the redo logs. The redo logs are used to make sure writes are fast and durable and also during crash recovery.
Now since every environment is different its a bit hard to evaluate the right size.

Here is a good trick to figure out how much you really need. Run this query on the highest or busiest workload on your server:

mysql> pager grep sequence
show engine innodb statusG select sleep(60); show engine innodb statusG
select (OUTPUT 1 - OUTPUT 2) / 1024 / 1024 as MB_per_min;

That’s it!

3. innodb_flush_log_at_trx_commit = 0

This variable controls InnoDB flushing behavior; with this value, InnoDB writes the transaction log after every commit and flushes it every second. A setting of 1 is the most durable setting, and it’s necessary for full ACID-compliance, but it is also the slowest, as it does a write and flush after every commit. A setting of zero is the fastest but least durable setting; it does a write and flush once per second, which means that a MySQL crash could cause the loss of one second’s worth of data. A setting of 2 is often a good compromise; a second’s worth of data loss is only possible if the entire server crashes (as opposed to just losing mysqld). Typically, the proper setting of this variable is more dependent upon business needs than any specific technical concern.

4. innodb_flush_method = O_DIRECT

This avoids operating system double-buffering on InnoDB log files and is the recommended setting here for the XFS
filesystem with log files that are smaller than 8GB.

5. sync_binlog = 0

As with innodb_flush_log_at_trx_commit this can also incur additional IO overhead when set to 1.

Conclusion or TL;DR version
Even if we have the latest MySQL version installed we still need to configure InnoDB variables to take advantage of the amount of RAM on the box. After installation, follow these steps above and you should be fine.

If you need any further assistance in your MySQL environment or have a performance question feel free to contact me.

Leave a Comment

Recent Posts

mysql-dba-consulting-dbasecenter