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.