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.

  • First thing: Comment out that line setting join_buffer_size to 2G and never touch it again until you absolutely understand what it does. You're going to burn your processors down with that. Also very unlikely that you're benefiting from a 4G query cache. – user118129 Mar 6 '17 at 11:19

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.


Server tuning it always iteration process, and affects of tune queries more proper and long term

I can suggest You start from:

max_binlog_size   = 100M
innodb_file_per_table         = 1 (if not default)
relay-log = /data/mysql/mysql-relay-bin
innodb-buffer-pool-size        = 32G

32Gb - You do not need at this time, just for not forget change when data will grow

innodb_log_file_size           = 512M

and leave all other default,

than enable slow query logs, and collect query statistics and from query logs and using other tools:

  • which queries longest?
  • which queries take most server time
  • which queries most often run
  • which queries locks table

and than continue

have a huge buffers and cache - it more bad than good

just add: do not trust mysql tuner, it is not know Your real loading and often give wrong advices.

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.