Alright, so I've noticed through checking via SSH, mysqld sometimes would go EXTREMELY high on cpu usage. So I addressed this issue with my providers and they changed the settings to: tmp_table_size=64M max_heap_table_size=64M query_cache_type=1 query_cache_limit=1M query_cache_size =64M thread_cache_size = 128 table_cache=160 key_buffer=128M innodb_buffer_pool_size = 128M Now, the CPU usage is never above 30% or so, which is great. And the CPU load average wont go drastically high anymore. However, now mysqld will instead, use EXTREME amounts of memory, hitting my limit. How would I address this problem? Buy more RAM (I have 2GB atm)? Or virtual processor..?
You need to add more RAM for MySQL to use. Your provider is giving MySQL more RAM to use which decreased the load since it can process the work faster.
Here are few steps you could perform to get more information on the issue: 1. to use mysqltuner.pl 2. to enable slow query log 3. to investigate slow queries
Hello there, What is your max_connections variable set to? If you have this inflated, it will use up more RAM for connections that never get used. I would also suggest using a couple of tuning scripts, as well as repairing and optimizing all databases on your server. Regards, Chris
Usually that is how things work in this regard. When you limit CPU usage, your RAM usage generally rises. When you limit RAM usage, your CPU will go higher. The only way to prevent this cycle is to purchase more of one or the other. The most simple solution, in that case, would be to buy more RAM.
NO, before doing any fine-tuning check mysql slow queries log (enable or change the slowness threshold if you don't see it) there might be one sql command in your script that is badly written/not optimized. Multiple users executing that query (with different parameters so it won't cache) would kill the server without a problem