You are here

configuration

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!

Max_used_connections per user/account

How many connections can be opened concurrently against my MySQL or MariaDB database can be configured and checked with the following command:

SHOW GLOBAL VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 505   |
+-----------------+-------+

If this limit was ever reached in the past can be checked with:

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 definition cache too small

The number of table definitions (SHOW CREATE TABLE\G) that can be stored in the table definition cache (table_definition_cache). If you have a large number of tables (> 400) in your database instance, you should consider a larger table definition cache to increase your database throughput and decrease your query latency.

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.

MySQL database configurator

Will come soon...

Please let us know your intention to for choosing this page!

Undefined

Configuration of MySQL for Shared Hosting

If you ask around about shared hosting setups with MySQL everybody is frightened. In fact it looks like shared hosting is one of the most difficult setups you can get.

The number of users is big, the number of tables huge and the load pattern is completely unpredictable and the queries often very, let us say: non-optimal.

Here one of the DBA wisdoms come into play: Controlling developers is like herding cats.

If you talk to the Shared MySQL Hoster they confirm that this setups are very demanding!

MySQL Cluster Local Checkpoint (LCP) and Global Checkpoint (GCP)

MySQL Cluster is mainly an in-memory database. Nevertheless it requires a good I/O system for writing various different information to disk.

The information MySQL Cluster writes to disk are the:

  • Global Checkpoints (GCP) which are the transactions.
  • Local Checkpoints (LCP) which is a dirty image of the data.
  • Backup.

In the following schema (a 2-node Cluster) you can see what is related to each other:

lcp_gcp.png

MySQL and MariaDB Configuration File template (my.cnf/my.ini)

The MySQL and MariaDB default configuration is not very great for production use. Some of the default values in my.cnf should be changed when you need it for business critical applications.

The following MySQL configuration file is in our opinion a good average configuration file for MySQL, Galera Cluster, MariaDB and Percona Server. For MySQL servers more performance tuning is not need in most cases.

Undefined
Subscribe to RSS - configuration