Mysql – the best configuration for a MySQL instance with a lot of databases and lot of tables

innodbmemoryMySQLmysql-5.7windows

I have a MySQL database instance with more than 3000 databases in it. Each database contains more than 200 tables. Total data of all theses database comes around 100 GB. I am using Windows Server 2012R2 operating system with 4 GB of RAM. The RAM memory utilization of the server system was always showing very high. So I tried to restart the system and restart is not working. It is showing restarting for long time and not restarting. When I checked the logs I understood that there was a memory issue. What is the best configuration for the MySQL with above architecture? What do I need to do to make this work with out failure in the future?

[Warning] InnoDB: Difficult to find free blocks in the buffer pool (1486 search iterations)! 1486 failed attempts to flush a page! Consider increasing the buffer pool size. It is also possible that in your Unix version fsync is very slow, or completely frozen inside the OS kernel. Then upgrading to a newer version of your operating system may help. Look at the number of fsyncs in diagnostic info below. Pending flushes (fsync) log: 0; buffer pool: 0. 26099 OS file reads, 1 OS file writes, 1 OS fsyncs. Starting InnoDB Monitor to print further diagnostics to the standard output.

Best Answer

There is a reason you will need more RAM ... the INFORMATION_SCHEMA database.

What has to get populated in it ??? Let pick just two tables as an example:

Imagine (all the columns in the tables + the number of tables) multiplied by 3000 ???

The metadata would be need lots of RAM to map them out. This is not to mention mapping indexes, constraints, views plus dozens of other database objects.

A client my employer had once had a Galera Cluster with 11000 databases with close to 2 million tables.

  • It took at least 24 hours to start mysqld and the Galera components.
  • Crash recovery made this nightmare scenario worse

See my old posts where I discussed memory consumption by the INFORFMATION_SCHEMA

WHAT YOU COULD DO

Please do the following:

  • Create a new MySQL instance on another server.
  • While the MySQL instance is empty, record how much RAM the server has free.
  • Create all the databases and tables in that instance but do not load any data.
  • After creating the databases and tables, record how much RAM the server has free.
  • Shutdown mysql
  • Wait like 1-2 minutes
  • Startup mysql
  • Record how much RAM the server has free and how long it took to start back up.

This will given a general idea (or a ballpark figure) how much RAM you will need to add.

Whatever number you choose to increase by for RAM, DOUBLE THAT PLEASE !!!

Increasing RAM will not reduce crash recovery time. Also, do not forget that DB Connections need memory love too (How costly is opening and closing of a DB connection?)

Related Question