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