1

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?

2

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.

  • Threads running is 4; so I assume that is fine. – Chris Muench May 27 '16 at 16:55
0

2306867200 is dangerously high for innodb_buffer_pool_size on a 4GB VM. With an 8GB VM, 5000M should be OK. More RAM for a bigger buffer_pool is usually the best hardware solution to a performance problem. (Fixing slow queries is usually even better, but you may not have that option.)

The buffer_pool does not need to be much bigger than all the data and indexes in the system. And, often, it can be much smaller with only minimal performance impact. After all, the buffer_pool is a 'cache'. You may not need to add RAM yet.

Each "instance" should be no bigger than 1GB, so the current setting of innodb_buffer_pool_instances is arguably high.

Please provide SHOW GLOBAL STATUS so we can also see how well those settings are doing. For example, it will tell if table_open_cache, innodb_log_file_size, and max_connections are big enough.

innodb_file_per_table = ON is both good and bad. Since it seems like you have lots of 'tiny' tables, I consider that setting not so good. I prefer to use ON when creating 'large' table, but OFF for the rest.

Since slow_query_log is off, you will have trouble pinning performance issues on a naughty user.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.