2 million tables in 15 thousand databases in MySQL, how to live with it?

Question

MySQL

Subject. How to tune and what, the hardware is fast, there is no load, but requests slow down.

Answer 1

Pin at least the people, where did you get 15k bases from? And what is their average size? Can unite all bases, enlarge, only divide users? As in some commercial CMS, you can create several sites on one copy of the CMS, but they will have one base.

Answer 2

the simplest:
1. first, always look at the number of objects in the selection, before getting the selection lines themselves.
2. limit the maximum number of entries to 1 if searching by ID or key.
3. increase the cache size
4. use one transaction instead of several in a row.
5. don't use linked tables in complex selections: make 2-3 separate selections in one transaction through variables...
Oh, and also MANDATORY: redesign the base in 3NF!

Answer 3

As I understand it, this is hosting, all databases live in one computer. Most likely you rested on the performance of the disk system or kernel resources, such as the number of open sockets, files per user, etc. If so, then look where the plugs are, then improve. Well, transfer especially loaded databases to another server.

Answer 4

In general, this is such an experiment. There was a server with 5 thousand bases, with 700 thousand tables. For some kind of iron, he calmly pulled his load without straining, everything flew. It was decided to take a cool server 4 times more powerful and push 3 times more on it, in theory it should have worked, but no.

Answer 5

A large %sys value can indicate a lot of system calls - opening/closing files, creating/ending threads. It is worth trying to increase table_open_cache and thread_pool_size.

Answer 6

to scatter on 15 servers on one thousand bases for a start.