0

I am new to InnoDB and database performance/tweaking.

I am running five websites and estimate about 5k to 10k concurrent users (within a 3 min timeframe).

Hardware of my new server:

32 CPUs AMD
128 GB DDR4 ECC
480 GB NVMe SSD

With MySQL 8 and default installation (I used Serverpilot), the speed was not faster than the 4 vCPUs with 16 GB RAM I had before.

But then I found the recommended server settings for InnoDB (manually, not using the variable innodb_dedicated_server) which I used like this:

max_connections = 500
innodb_buffer_pool_size = 64G
innodb_log_file_size = 2G
innodb_log_files_in_group = 48

I am not sure about the max_connections, though.

The other values are based on the 128 GB RAM that the server has.

Can some experienced developer confirm that all settings are more or less correct. It will also help other newbies in this field, especially with regard to the new MySQL 8.

Avatar
  • 127
  • 1
  • 10
  • If you don't have multiple concurrent connections, extra CPUs don't help. If the dataset was "small", the extra RAM does not help. Etc. Tell us what prompted you to upgrade the system. Give us some numbers; show us some "slow" queries. – Rick James Apr 04 '21 at 16:22
  • With 4 vCPUs and 16 MB RAM we had ~50 % of resources used. When there were ~700 users online at the same time. Over the years we optimized most of our MySQL queries. We also implemented caching (static HTML) for visitors that are not loggedin. – Our goal is to be able to server up to 10k users (or more) at the same time, without any slow down of page loads. And without running into a lack of resources. That's why we have chosen this very powerful server. – Avatar Apr 04 '21 at 17:53
  • Sidenote: Our users will also have the option to upload image and video files (max. 16 MB/file). Also for this we considered additional resources. – Avatar Apr 04 '21 at 18:04
  • 1
    Keep an eye on network bandwidth usage; it may be your next bottleneck. – Rick James Apr 04 '21 at 18:51
  • For log_files, have only 2, and increase the size. – Rick James Apr 04 '21 at 18:52
  • 1
    For further analysis, see http://mysql.rjweb.org/doc.php/mysql_analysis – Rick James Apr 04 '21 at 18:53
  • Thank you. Regarding `innodb_log_files_in_group` the [docs](https://dev.mysql.com/doc/refman/8.0/en/innodb-dedicated-server.html) say: `ROUND(buffer pool size * 0.75)` (min-value 2, max-value 100, up to 512 GB). So I used 64 GB * 0.75 = 48 as value. – Avatar Apr 05 '21 at 04:32
  • In old versions of MySQL, having files_in_group >2 seemed to slow things down. I don't know whether that "flaw" has been fixed. – Rick James Apr 05 '21 at 05:26

0 Answers0