Tuning my.cnf and 64GB Ram and 16 WordPress websites

[wattie]

One more thing to note:

1. If you have many reads and less writes, Query Cache is better;
2. If you have many writes, Memcache/Redis is better;
3. DO NOT USE BOTH.

 

[Sab]

I see your point, these days CPU core and RAM are very low cost. Getting a 24core CPU with 128GB ram under $100 should make us think differently

 

[Sab]

This is typical read/write spread over a few servers

1
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Com_delete    | 31619   |
| Com_insert    | 91347   |
| Com_select    | 5067857 |
| Com_update    | 109736  |
+---------------+---------+

4
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Com_delete    | 97949   |
| Com_insert    | 155146  |
| Com_select    | 7532805 |
| Com_update    | 309532  |
+---------------+---------+
4 rows in set (0.00 sec)

3
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| Com_delete    | 324880   |
| Com_insert    | 3128694  |
| Com_select    | 53219857 |
| Com_update    | 1071453  |
+---------------+----------+
4 rows in set (0.00 sec)

2
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| Com_delete    | 276335   |
| Com_insert    | 2232959  |
| Com_select    | 73927604 |
| Com_update    | 1253005  |
+---------------+----------+
4 rows in set (0.00 sec)

What would you recommend here?

 

[wattie]

First find the bottleneck. There are many services that run on the system. Yes, SQL is usually the suspect for high load but... not always the case. Let me show you and example of what happens on my setup (with SSD disk). If I look at the processes, MariaDB is always at the highest usage of CPU and RAM:

USERNAME       THR PRI NICE   SIZE    RES STATE   C   TIME    WCPU COMMAND
mysql           65  20    0  9607M  5906M select  3 897.6H 253.79% mysqld

But that's literally NOTHING and optimizing it will make no difference to nothing because:

root@srv2:~ # top
load averages:  2.91,  2.65,  2.80
371 processes: 1 running, 367 sleeping, 3 zombie
CPU: 10.0% user,  0.0% nice,  0.5% system,  0.0% interrupt, 89.5% idle
Mem: 6051M Active, 20G Inact, 1888M Laundry, 3176M Wired, 1572M Buf, 411M Free
Swap: 20G Total, 2822M Used, 17G Free, 13% Inuse

What you see is much free RAM, low CPU usage but still a decent load (not high but it's there). This load in my case is coming from the fact that there are hundreds of small sites on the server and there are constantly hits to new files:

root@srv2:~ # systat -iostat
                    /0   /1   /2   /3   /4   /5   /6   /7   /8   /9   /10
     Load Average   ||||||||||||||||

          /0%  /10  /20  /30  /40  /50  /60  /70  /80  /90  /100
cpu  user|XXXXXXX
     nice|
   system|
interrupt|
     idle|XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

          /0%  /10  /20  /30  /40  /50  /60  /70  /80  /90  /100
md99  MB/s
      tps|
ada0  MB/sX
      tps|XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX108.27
ada1  MB/sXX
      tps|XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX121.99

So disks are loaded pretty high and this makes the load. I made a big offload by creating tmpfs partition in the ram and I moved /tmp there - it made huge difference, but still IO is my bottleneck. Caching PHP scripts can definitely fix this issue (but I am lazy to do that because the load is not high anyway).

Now on topic: what I would do first is to:

1. Disable all caching. Monitor the load on the server for some time;
2. Start with relatively small query cache. Monitor the server load for some time;
3. Increase the query cache to a higher size. Minitor the server load for some time;
4. Compare the differences and choose the best variant of the three. It's very rare that the third wins

Regarding memcache/redis... I am not big fan of them in single server setup. The power of the NoSQL DBs is on multi-server systems. The built-in query cache is better on a single server - it's consistent and works in the server (yes, you may save the authentication step... but that's not too much if you have many cache misses).

 

[Sab]

I'm curious how do you calculate, measure 'cache misses'. If you have a query or command please do share

 

[wattie]

Do as da_admin user:

- SHOW STATUS LIKE 'Qcache%';
- SHOW VARIABLES LIKE 'query%';

Than calculate:

1. Cache fill (how much RAM you use and how much you reserved for the cache - you want that close to 100%):

((query_cache_size – Qcache_free_memory) / query_cache_size )*100

2. Cache hits (how much cache hits you have compared to all queries - you also want that close to 100%):

(Qcache_hits / (Qcache_hits+Qcache_inserts+Qcache_not_cached) )*100

3. Efficiency of the cached data (how many times a single cached query is used before it is dropped - the higher, the better):

Qcache_hits / Qcache_inserts

 

[Zhenyapan]

yes I tried Apache and it is not as fast as openlitespeed

Try to install Opcache, my php7.2 opcache configured:
opcache.memory_consumption=1200
opcache.interned_strings_buffer=32
opcache.max_accelerated_files=32000
opcache.revalidate_freq=30
opcache.enable_cli=1
opcache.validate_permission=1
opcache.use_cwd=1
opcache.revalidate_path=1
opcache.enable_file_override=1
---
you may increase first 3 strings, in my case it's enough for 55 WP sites with 99.9% hit rate (80milions hits/day total).
Xeon(R) CPU E3-1270 V2 / 16G RAM / all system on 1 ssd.
----
my.cnf
[mysqld]
local-infile = 0

table_open_cache=4000
max_heap_table_size=512M
tmp_table_size=512M
query_cache_type=1
query_cache_size=128M
query_cache_limit=2M
performance_schema = ON

innodb_file_per_table
innodb_buffer_pool_size=2G
innodb_buffer_pool_instances=2
innodb_log_file_size=256M
innodb_io_capacity=2000
---
my.cnf must be filled according to myslqtuner result, not just tuning recommendations at the end, but look for general statistics like:
-Storage Engine Statistics
-Performance Metrics
-MyISAM Metrics
-InnoDB Metrics
If You can - show us this fields.
---
ofcourse use WP caches (try any)