What is the best configuration for a MySQL instance with a lot of databases and lot of tables?

Question

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.

Answer 1

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:

• INFORMATION_SCHEMA.TABLES
• INFORMATION_SCHEMA.COLUMNS

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

• How is INFORMATION_SCHEMA implemented in MySQL?
• Adding new tables -- memory usage increases
• Do dormant databases impact server performance?
• #1041 - Out of memory issue in mysql

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?)

Answer 2

So you have 100GB of data held in 3000 databases?

Each of those database is trivially small (I'd guess about 30MB each).

I would seriously suggest that you need to reconsider your desire to segment / segregate your data in this way. It's almost certainly not the best way to do things.

You're trying to run a Windows Server with only 4GB of RAM?
I'm surprised it even starts up!

OK, (just checked) Our Friends in Redmond recommend "at least 2GB" of RAM, but all that machine will be capable of doing is running the operating system itself and keeping the office a bit warmer, with its fan.

If you want to run any other software on it, then you need more memory and, with a DBMS, the more the merrier, generally speaking.