Not the answer you need?
Register and ask your own question!

High InnoDB Log File Usage

leeusleeus ContributorInactive User Role Beginner
I installed an InnoDB database yesterday on a new database server I am working on. Using the latest Percona 5.7 release from the apt repo.

The database contains 2 tables of highly transient data. One table has a high update rate and the other has a high insert & delete rate.

I installed PMM and was shocked at the attached graph!

Thing is I can actually sacrifice some durability of the data as the data is almost immediately invalidated by upstream sensors that feed in the data and I don't need to keep the changes made. Obviously what I don't want to sacrifice is that in the event of a crash being unable to open the tables, I don't mind losing some data though!

My config file is as follows...
#
# The Percona Server 5.7 configuration file.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
user   = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket   = /var/run/mysqld/mysqld.sock
port   = 3306
basedir    = /usr
datadir    = /var/lib/mysql
tmpdir   = /tmp
lc-messages-dir  = /usr/share/mysql
explicit_defaults_for_timestamp

local-infile = 0

log-error    = /var/log/mysql/error.log

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0


# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
innodb-log-files-in-group      = 2
innodb-log-file-size           = 256M
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 8G
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.