0

Recently we have noticed that our application has some errors related to the MySQL load. There are some basic queries on large database (approximately 32 GB) which takes longer time than expected.

For serving the MySQL server we use m6d.xlarge instance, average visitors volume is 3000 daily basis, though we see tendency of growth and sometime we have 5000 or more users, so I would like to solve the issue with the server before permanent growth. Thus I have in mind to add another instance something like t4g.2xlarge, with 8 CPU and with capability to gain extra credits.

Note that on the server we use only MySQL, so there are no any other obstacles on the server, all the load is on MySQL server which sometimes loads the server up to 300%. There are periods when some workers do website parsing which getting stored into db, therefore they create more load.

also I think, that 32Gb isn't huge size for the MySQL database, so I would like to hear some suggestions from experienced people whos faced similar cases.

Generally how would you suggest to scale it with EC2 instances which fits more for heavy MySQL work.

Please note that RDS doesn't fit for our needs, since it costs higher than we need actually.

Aren Hovsepyan
  • 101
  • 1
  • 2
  • The servers "with capacity to gain extra credits" also have the capacity to *run out of* credits, which if you're running sustained high load is probably not the right idea. Have you first tackled caching, indexing, optimizing queries? – ceejayoz Feb 18 '21 at 22:25
  • all frequently used relationships and columns are indexed, though ubuntu server has higher load (~300%) mostly all time, that's the reason we are considering to add another higher server for master and keep m6d.xlarge for replica – Aren Hovsepyan Feb 18 '21 at 22:49
  • 1
    Figure out *why* it's got a high load and then take it from there. Is it memory constrained? Disk I/O constrained? CPU constrained? Is Graviton the right CPU for it? Are the MySQL settings, caches, buffers, limits, configured correctly? Until you understand the problem you won't know what the most effective solution is. – MLu Feb 18 '21 at 23:44

1 Answers1

1

Your database is not that big but depends on how you use it. Rather than throwing more power at an under-performing database try to optimise the performance with your current setup.

  1. Enable MySQL Slow Query Log and figure out what queries take too long. Is there a pattern?

  2. Are your table indexes correct? Are they actually used in your queries? Use EXPLAIN to find out.

  3. Have you got a lot of similar reads? caching the results in something like Memcache or Redis will help? Or add a Read-replica node for reads and offload that burden from the master.

  4. Configure caches, buffers, various limits, etc. The defaults are usually quite conservative.

In fact go through the MySQL Optimisation guide step by step and you'll soon find out that you can squeeze a lot more performance from your current instance.

Hope that helps :)

MLu
  • 24,849
  • 5
  • 59
  • 86