1

I'm currently trying to optimize my MySQL database (running innodb engine) for my requests that are mainly INSERT and UPDATE statements.

I'm using a very basic server with 2GB memory/2 CPU's and a 40GB disk. I've gone through much of the MySQL docs, including optimization (https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-diskio.html) and memory use (https://dev.mysql.com/doc/refman/5.7/en/memory-use.html).

I've set my my.conf file to as follows:

[mysqld] default-storage-engine = innodb innodb_buffer_pool_size = 2147483648 innodb_buffer_pool_instances = 4 innodb_buffer_pool_chunk_size = 1073741824 innodb_change_buffer_max_size=30

Despite increasing the pool size, chunk size and number of instances, I'm still seeing significant query bottlenecks and the both CPU's are running at 100%.

Is there annoying more I can do on the innodb engine to reduce these bottlenecks? Or is it simply a case of using greater spec hardware?

Improve this question
5
  • 2Gbytes of memory is not very much (doesn't my phone have about that much RAM?). However, if you want to optimize a query load, it is important to understand the query load. This may or may not be a hardware/software problem. Commented Oct 30, 2016 at 14:06
  • One thing you can check is adding indexes. Commented Oct 30, 2016 at 14:08
  • @GordonLinoff thanks - I'm just trying to see how much I can eek out of a small server, could you expand on 'query load'? Commented Oct 30, 2016 at 14:08
  • Is that a virtual machine? I don't know where to buy a 2GB hardware. But no hardware or settings will help you much, if you run poorly optimized queries. Commented Oct 30, 2016 at 14:09
  • 1
    Voting to move this question to dba.stackexchange.com. It's a fine question, but probably a better fit for that site since this question does not mention any programming problem. Commented Oct 30, 2016 at 19:34

2 Answers 2

Reset to default
3

innodb_buffer_pool_size = 2147483648 will NOT work on a 2GB server. Change to 300M.

You have a tiny machine; if you increased any other settings, revert the changes!

Let's see your my.cnf; there may be some settings to shrink so that you can fit in 2GB (or 2GiB) of RAM.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

The poor innodb settings is for sure. Your innodb_buffer_pool_size is larger than 2GB, it is 2GiB.. It should not even close as everyone suggested and also documented.

may be also insufficient hardware, and even bad programmed SQL.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.