Mysql – mariadb server optimization for a db of 6GB size

MySQL

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.

Best Answer

This is really bad:

query_cache_size = 4G

It should not be more than about 50M

How much RAM do you have; this should not be more than about 1%:

join_buffer_size = 2G

This is not bad, but most people do just fine with 8M; let's compromise at 16M...

innodb_log_buffer_size         = 512M

The main setting that you should tune is innodb_buffer_pool_size; it should be up to about 70% of RAM (so, about 40G), but does not need to be much bigger than the 6GB that you have in data+index (so 8G is fine for now).

If this VM is dedicated to MySQL, you are paying for more RAM than you need.

If you are chewing up the CPU(s), then look to the queries, indexes, and schema. Let's see the slowest query and SHOW CREATE TABLE.

Related Question