You are here

variables

MariaDB configuration analysis

Taxonomy upgrade extras: 

If we do customers database configuration analysis we check on one side if the most important MariaDB server system variables (innodb_buffer_pool_size, ...) are set appropriately but also if some MariaDB server system variables are configured completely wrong.

Fortunately MariaDB introduced in MariaDB 10.1 the INFORMATION_SCHEMA.SYSTEM_VARIABLES view where you can find all the relevant information. But one!

MySQL and MariaDB variables inflation

Taxonomy upgrade extras: 

MySQL is well known and widely spread because of its philosophy of Keep it Simple (KISS).

We recently had the discussion that with newer releases also MySQL and MariaDB relational databases becomes more and more complicated.

InnoDB Buffer Pool Instances is too small

If you are using MariaDB/MySQL 5.5 and newer you should use several InnoDB Buffer Pool Instances for performance reasons.
Some rules to size InnoDB Buffer Pool instances are:

  • One InnoDB Buffer Pool Instance should be at least 1 Gibyte in size (innodb_buffer_pool_size / innodb_buffer_pool_instances >= 1 Gib).
  • InnoDB Buffer Pool Instances you can set equal to the number of cores of your machine.

Binlog format MIXED with filtering

Binlog format MIXED changes the binary log format (ROW or STATEMENT) depending on the queries (deterministic or not). This makes it impossible to define 100% correctly working binary log filter rules.

Flush time is set

If the variable flush_time is set to a non-zero value, all tables are closed (and flushed) every flush_time seconds to disk. This can cause unnecessary and high I/O spikes.

For further information consult MariaDB or MySQL documentation.

Table open cache too small

The Table Open Cache (table_open_cache or old name table_cache) is a cache to store file handles for all threads. The actual value of cache entries can be seen with the global status of open tables (Open_tables).
Increasing table_open_cache increases the number of file descriptors (open_files_limit) that MySQL requires.

InnoDB Flush Method has changed

The InnoDB Flush Method has changed. This can have an impact on InnoDB write Performance.

Subscribe to RSS - variables