I have a database I am trying to configure but I am struggling to get the settings right. I keep changing the settings but I always get the same slow performance.
the mysql database has about 50 tables 2 of the tables have about 10.000.000 rows each. total size of the database is 6GB. Of those 6GB about 3GB are indexes (because of those 2 tables mainly where most operations occur) This db is expected to get about 1 million rows more per month added. (there is an import procedure of new records every morning) Other than that is mostly reading and manipulating data to present to the users. As this is for a web app, there are only 4 clients using it.
on our new server i have a VM with 64GB of RAM and 16 cores. on our previous server we had 16GB RAM and 8 cores.
on the previous server i had those settings only :
max_binlog_size = 100M binlog_do_db = equidata relay-log = /data/mysql/mysql-relay-bin innodb-buffer-pool-size = 8G innodb_log_buffer_size = 512M innodb_buffer_pool_instances = 8 join_buffer_size = 2G thread_cache_size = 50 query_cache_size = 4G query_cache_limit = 4G
Performance is ok, but until the tables are cached is a bit slow, I believe it could be lot faster. on the new server with the same settings I get about the same performance. using mysqltuner or the percona online advisor and following the advices, it does not get any better whatever i change.
there are many queries that are not using indexes properly, and have optimized most of them. when the queries that run to present data are appearing to be slow, I cannot see any spikes on the new server, it is like it is lagging, no activity on cpu and ram..
whenever i use suggestions from mysqltuner I end up with the warning that mysql maximum memory usage is dangerously high etc (because it suggest me to increase the join buffers so for example : Total buffers: 10.6G global + 2.0G per thread (151 max threads)
I am stuck with this, and I ve been trying to fix it for sometime but I always leave it for later as I have huge workload programming the application, but now I am at the point where it affects the application and we got this new big server.
I am open to suggestions for settings. I understand that the queries with no indexes is a major thing, but I am looking at the best optimal configuration for my case on this instance.