2

I have a MySQL 5.7 running on a Centos 6. I enabled Binary Logging and didn't provide any custom value for the maximum size of Binary Log in my configuration file. By default, the parameter max_binlog_size = 1073741824 which is equivalent to 1GB. This means, once a binary log reaches the size of 1GB, a new file should be created.

I observed in my server that all the binary log files are of different sizes with lot of contrast like 200GB, 165GB, 4GB etc...

Why does this happen when the maximum size is 1GB for a binary log file? Is it necessary to mention even the default maximum size explicitly in the configuration file?

  • Let's see ls -l of those huge files. – Rick James Oct 31 '18 at 5:41
3

The reason your binary logs are so gigantic is simple: YOU ARE COMMITTING TOO MUCH DATA PER SINGLE TRANSACTION !!! Why is this the case ? According to the MySQL Documentation on max_binlog_size:

A transaction is written in one chunk to the binary log, so it is never split between several binary logs. Therefore, if you have big transactions, you might see binary log files larger than max_binlog_size.

SUGGESTION

Please try to commit data in smaller chunks.

|improve this answer|||||
  • store binary logs on fast HDDs (not on SSDs) for faster write performance ?! Are you serious about that?! Even the worst SSD will outperform any HDD (10k or 15k) on the market in both RANDOM WRITE or READ and IOPS. For instance, my SSD on the server can achieve 100k IOPS for READ, while fast 10k SAS drive can only about 1k. HDD's are simply limited by physics for random I/O. – stamster Jan 23 '18 at 11:49
  • @stamster For bare metal machines, I am drop dead serious. SSDs will age faster than HDD’s in the long run. Cloud providers like Amazon and Google will not provide decent HDD‘s – RolandoMySQLDBA Jan 23 '18 at 11:52
  • @stamster binary logs are always written sequentially. They should go on a different disk at least, apart from the data. Data should go on SSD drives (where random writes can flourish for data) while binary logs should go to another drive whether it is HDD or SSD – RolandoMySQLDBA Jan 23 '18 at 12:01
  • Who ever mentioned drives aging / longevity? That's out of consideration when we're discussing DB performance here. You stated that HDD's are better for RDBMS binary logs workload - and now you have explained your statement with binary logs being sequential. Well, that's whole another story and perspective and I can agree with you that logs shall at least go onto dedicated drive(s). And yes - being sequential data it's OK to go onto mechanical disks. And some folks even advise to disable completely MySQL/MariaDB binary logs. Even I did it once to minimize I/O on the primary SAS 10K drives. – stamster Jan 24 '18 at 17:14
  • I suspect that "use HDD for logs" is an old wives tale that no longer applies. (But I don't have any proof either way.) – Rick James Oct 31 '18 at 5:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.