Geek Answers Handbook

How many databases can MySQL handle?

My MySql server currently has 235 databases. Should I worry? They all have the same structure as MyISAM tables.

Hardware is a virtual machine with 2 GB of RAM, running on a quad-core AMD Opteron 2.2 GHz processor.

CPanel recently sent me an email stating that MySql was unsuccessful and a restart was made.

It is expected that new databases will be created, and I wonder if I will add more memory or just add another virtual machine.

+7
database mysql hardware load
source share
4 answers

"Databases" in mysql really are directories, do not affect its limits, regardless of whether you put all the tables in one or each of them.

The main problem is the table cache. Without configuring it, you will have a table cache by default (= 64 usually), which means that you close the table every time you open it. This is incredibly bad.

With the exception of MyISAM, this is even worse, because closing the table yields its key blocks from the key cache, which means that subsequent searches or crawls will read the actual blocks from disk, which is terrible and slow and should actually be avoided.

My advice:

  • If possible, immediately increase the table cache to> the total number of tables
  • Monitor the global OpenedTable state variable in your monitoring; if it grows fast, it’s bad.
  • Test performance and reliability on the same equipment in a non-production environment (if you are not already doing this).
+6
source share

(repeating my comment for better visibility) Thank you all for your comments. The system is similar to Google Analytics. Visits to user websites are recorded in the "master" table. A native application controls the main table and processes registered visits and writes them to the user database. Each user has his own database. This was decided for the shards. Various reports and statistics are run for each user. And this is faster if it works only on a specific database (less data). I know this is not the best setting. But we have to deal with this for a while.

+1
source share

I do not believe that there is a hard limit, the only thing that really limits you is your equipment and the traffic that these databases will receive.

You seem to have very little memory, which probably means you don't have a lot of connections ...

You should start by using profiling for each database (or set of databases, depending on how they are used, of course).

My suggestion is that MySQL (or any database server, for that matter) could use more memory. You will never have enough.

0
source share

You are doing it wrong.

Comment on some of the features of your databases, and we may possibly fill you with where your design went wrong.

0
source share

More articles:


All Articles