0

I am about to upgrade my MySQL server because I have currently used it mainly with a couple of people in the development stage. Before we launch, however, we would like more capacity to avoid the server crashing. I currently run on a Ubuntu system with 2 GB of RAM, 2 vCPU's, 60 GB disk and 3 TB of monthly transfer.

My variables are set to:

innodb_buffer_pool_size = 256M
innodb_io_capacity      = 1900
innodb_flush_method     = O_DIRECT
key_buffer_size         = 10M
max_allowed_packet      = 32M
thread_stack            = 192K
thread_cache_size       = 100
myisam-recover-options  = BACKUP
query_cache_limit       = 10M
query_cache_size        = 0
slow_query_log          = 1
slow_query_log_file     = /var/log/mysql/mysql-slow.log
long_query_time         = 2
wait_timeout            = 300
interactive_timeout     = 300

I am about to ugprade my server to 8 GB RAM, 4 vCPU's, 160 GB of disk space and 5 TB of monthly transfer. The size of my table is currently 1.3 MB, but this will of course increase as soon as more users become active. How much this will increase and how fast, I can not really say for sure.

So I am now trying to figure out how I should change my variables to get the best performance. I know I should allocate my memory correctly, and these settings work fine for the setup I currently have, but if I need to change anything for the upgraded system, I would really appreciate some insights.

Cheers!

  • SSD? Or not?... – Rick James Jul 16 at 18:55
  • Yes, ssd disk. I’m hosting on DigitalOcean as we speak. – PennyWise Jul 16 at 19:00
0

If your dataset size is only 1,3MB, then buffer_pool_size = 256M gives you a lot of growing room.

If HDD, change innodb_io_capacity back to the default of 200.

The slowlog (which you have already turned on) will provide info on what to fix if you have performance problems.

Has the server crashed? If so, let's see details on that.

Do you have other apps crammed into the tiny 2GB of RAM? Changing to 8GB is likely to help with that.

  • Cheers for the feedback! It’s definitely SSD disk and the server is dedicated for the MySQL database. Other processes are just the regular Ubuntu stuff on there. I have a separate droplet/server for my node.js application (which has lower specs, but I figured http requests won’t cost me as much memory or cpu power) and I have a dedicated object spaces solution from DigitalOcean for my image/video files. Would 1900 create a problem on io capacity? – PennyWise Jul 16 at 19:03
  • Also, server hadn’t crashed with these configurations. Used to run on 1 GB ram, 1 vCPU and default MySQL my.cnf settings, crashed once and had the configuration adapted since then. – PennyWise Jul 16 at 19:14
  • Now when we are launching, I expect a couple hundred to thousand new users, so I’m kind of afraid the server might crash in that scenario, hence why I want to upgrade so we are in the safe. – PennyWise Jul 16 at 19:15
  • @PennyWise - 1900 is probably fine for SSD. How many Queries per second when you get the thousand users? – Rick James Jul 16 at 19:35
  • 1
    @PennyWise - Sure, 10 users doing very little; that is fine. Based on the limited information available, growing to 1000 users with similar activity will probably still be fine. 17qps is only about the 30th percentile. As I said, the slowlog will be your friend if you do hit performance problems. – Rick James Jul 16 at 20:32

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.