A
A
Alexey2014-08-29 21:05:34
MySQL
Alexey, 2014-08-29 21:05:34

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

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

Answer the question

In order to leave comments, you need to log in

6 answer(s)
O
Optimus, 2014-08-29
Pyan @marrk2

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.

X
xmoonlight, 2014-08-29
@xmoonlight

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!

A
Alexey Cheremisin, 2014-08-29
@leahch

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.

A
Alexey, 2014-08-29
@alexxxst

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.

A
Alexander Melekhovets, 2014-08-30
@Blast

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.

P
Puma Thailand, 2014-09-03
@opium

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

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question