I have an application where each client gets their own database. This is for security, backup ease, and portability. It provides great flexibility and performance has been fine. I am up to 300+ databases and almost 20k tables. I use a 4gb ram server with this configuration. I use a database as a service so I don't actually have physical access to server; but can make changes and have root mysql access.
I am looking to move up the 8gb of memory as my innodb buffer pool is smaller than how much data I have in innodb; and I only have about 600-700mb free memory.
My host allows me to provision up to 32 GB of ram. At some point I will start adding 1 or more new database instances and change my application logic to have a lookup table to find which database to go to.
Right now I am trying to keep costs down and keep it simple so I don't have to re-enginer a bunch of stuff. (Not hard; would take about 2-3 days).
When is the right time to start scaling out instead of up? What issues might I run into if I keep scaling up?
Best Answer
As long as the bottleneck is the amount of RAM you can keep scaling up. MyISAM has some bug related to large key buffer size but InnoDB handles massive buffer pool just fine (even in the TB range). I see you already have 4 buffer pool instances. By increasing the buffer pool it's good to increase this too. The only "issue" you may experience is if you also have larger innodb log files and more dirty pages in the buffer pool then stopping the database may take longer and in case of a crash innodb recovery will take longer.
For scaling out the most efficient simple measurement is the concurrently running threads:
show global status like 'Threads_running';You can take a benchmark and see where is the tipping point for TPS on your system. (Usually with increasing concurrency you will see transactions / second increase for a while until it hits different contention issues and then drop usually quite quickly, you can see a sample benchmark and what the results look like here: https://www.percona.com/blog/2014/01/23/percona-server-improve-scalability-percona-thread-pool/) You want to keep it always under that level. Measuring this will give you a good indication of when will you need new server and how many.