1

we're running MariaDB on our DB server. We have to install multiple databases on each DB instance (in fact each DB server runs multiple MariaDB instances) because each database must be separated.

We're nearly like an hoster (excepted that we do not provide any file hosting). When you subscribe an hosting plan you often have 1 to X databases and that's what we want to do.

In our company we have 2 DBA: 1 says that we can't have more than 500 DB by instance (due to memory limits) and the 2nd one says that's there's no limitation (excepted FS limitations).

Could you help me to understand or give me some tips (because I'm a project manager and dev. solution need to be chosen and I not a DB expert).

Thanks

Colin 't Hart
  • 9,455
  • 15
  • 36
  • 44
Unkl Benz
  • 11
  • 1
  • 2

2 Answers2

2

Both DBAs are right, in a way. There are no limits on the number of schemas, except those imposed by the filesystem, as in MySQL/MariaDB, a schema is basically a directory, producing absolutely no difference for both MyISAM and InnoDB if a table is part of one schema or another in terms of performance or features (you can join 2 tables in different schemas as fast or as slow as if they were in the same schema). I've managed instances with hundreds of thousands of schemas, and the multiple directories were not the performance bottleneck. It is a pure identifier thing. While you can run into some issues, those are indirect to having more or less schemas: for example, in 5.6, a slave can apply changes in parallel only for rows from different schemas.

Why do I say that both are right, in a way? Because having too many objects in the database is a very common source of issues. When you have lots of databases, you usually have 10x or more the number of tables, and that may mean thousands of file descriptors open (that requires memory), thousands of data dictionary cache entries, millions of open table cache entries, etc., which can lead truly to a CPU and above all, memory, bottleneck.

jynus
  • 15,057
  • 2
  • 37
  • 46
1

Initially, I looked into database benchmarks for this (I'm interested in realistic benchmarks), but found that neither the HammerDB nor Sysbench tools appear to handle multiple databases (i.e. schemas). I couldn't find any Google link despite searching for many combinations of "MySQL benchmark multiple schemas/databases".

With respect to multi-tenancy, there are three views - multiple schemas or tables partitioned by, for example, customer_id or again, having different tables within the same schema for each customer. The OP in this thread ran into a limit of 32000 schemas per instance - though if you had that many clients, quite why you couldn't afford another machine is beyond me! :-)

I then found this post which seems to suggest that 4096 schemas per MySQL instance is still workable, although obviously not all of those schemas will be working flat-out, otherwise your server would be brought to its knees. CPU, RAM, I/O and bandwidth are fundamentals that won't change no matter what way your database is organised. IMHO, you should test and when your customer base grows, you will have to invest in more machines.

"You cannae change the law o' physiks, Jim!"

Community
  • 1
Vérace
  • 30,923
  • 9
  • 73
  • 85