0

I'm trying to tune a MySQL 8.0.33 on Ubuntu 22.04 (32G RAM, 8 cores). (The same machine is also a web server, with 4 apache/php-fpm virtual hosts running).

I'm not sure if this is the right thing to do, but I used mysqlslap to compare the performance before and after the config alterations.

As advised by mysqltuner, I raised innodb_buffer_pool_size and innodb_redo_log_capacity values, but it seems that the performance haven't changed (average time to run mysqlslap queries 0.386 seconds, and 0.387 seconds after configuration changes).

Shouldn't the queries be faster with a innodb_buffer_pool_size almost 40x bigger? What's the correct way to measure the before/after performance?

What am I doing wrong? Any advices?

This was my original /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
user            = mysql
bind-address            = *
mysqlx-bind-address     = 127.0.0.1
key_buffer_size         = 8M
max_allowed_packet      = 128M
myisam-recover-options  = BACKUP
log_error = /var/log/mysql/error.log
slow_query_log                = 1
slow_query_log_file   = /var/log/mysql/mysql-slow.log
long_query_time = 2
max_binlog_size   = 100M

log_timestamps=SYSTEM
skip_name_resolve=ON

I tested using mysqlslap:

mysqlslap --concurrency=100 --iterations=20 --number-int-cols=30 --number-char-cols=30 --auto-generate-sql --auto-generate-sql-execute-number=10

That resulted:

Benchmark
        Average number of seconds to run all queries: 0.386 seconds
        Minimum number of seconds to run all queries: 0.345 seconds
        Maximum number of seconds to run all queries: 0.531 seconds
        Number of clients running queries: 100
        Average number of queries per client: 10

Then I runned ./mysqltuner.pl, with the results:

 >>  MySQLTuner 2.2.7
         * Jean-Marie Renouard <[email protected]>
         * Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[--] Using mysql to check login
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 4.4G (Tables: 942)
[OK] Total fragmented tables: 0

[OK] Currently running supported MySQL version 8.0.33-0ubuntu0.22.04.4

-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/log/mysql/error.log exists
[--] Log file: /var/log/mysql/error.log (986B)
[OK] Log file /var/log/mysql/error.log is not empty
[OK] Log file /var/log/mysql/error.log is smaller than 32 MB
[!!] Log file /var/log/mysql/error.log isn't readable.

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- Views Metrics -----------------------------------------------------------------------------

-------- Triggers Metrics --------------------------------------------------------------------------

-------- Routines Metrics --------------------------------------------------------------------------

-------- Security Recommendations ------------------------------------------------------------------
[--] Skipped due to unsupported feature for MySQL 8.0+

-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 30m 4s (74K q [41.427 qps], 2K conn, TX: 14G, RX: 62M)
[--] Reads / Writes: 78% / 22%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory     : 31.3G
[--] Max MySQL memory    : 704.7M
[--] Other process memory: 0B
[--] Total buffers: 168.0M global + 1.9M per thread (151 max threads)
[--] Performance_schema Max memory usage: 248M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 613.2M (1.91% of installed RAM)
[OK] Maximum possible memory usage: 704.7M (2.20% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/74K)
[OK] Highest usage of available connections: 68% (103/151)
[OK] Aborted connections: 0.00% (0/2704)
[--] Query cache has been removed since MySQL 8.0
[OK] Sorts requiring temporary tables: 4% (122 temp sorts / 2K sorts)
[!!] Joins performed without indexes: 159
[OK] Temporary tables created on disk: 0% (0 on disk / 1K total)
[!!] Thread cache hit rate: 31% (1K created / 2K connections)
[OK] Table cache hit rate: 94% (69K hits / 73K requests)
[OK] table_definition_cache (2000) is greater than number of tables (1271)
[OK] Open file limit used: 0% (3/10K)
[OK] Table locks acquired immediately: 100% (182 immediate / 182 locks)
[OK] Binlog cache memory access: 99.99% (15178 Memory / 15180 Total)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance_schema is activated.
[--] Memory used by Performance_schema: 248.8M
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] General MyIsam metrics:
[--]  +-- Total MyISAM Tables  : 0
[--]  +-- Total MyISAM indexes : 0B
[--]  +-- KB Size :8.0M
[--]  +-- KB Used Size :1.5M
[--]  +-- KB used :18.2%
[--]  +-- Read KB hit rate: 0% (0 cached / 0 reads)
[--]  +-- Write KB hit rate: 0% (0 cached / 0 writes)
[--] No MyISAM table(s) detected ....

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDb Buffer Pool size ( 128.0M ) under limit for 64 bits architecture: (17179869184.0G )
[!!] InnoDB buffer pool / data size: 128.0M / 4.4G
[!!] Ratio InnoDB redo log capacity / InnoDB Buffer pool size (75%): 100.0M / 128.0M should be equal to 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk: 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.95% (10397117 hits / 10402401 total)
[!!] InnoDB Write Log efficiency: 77.34% (100765 hits / 130280 total)
[OK] InnoDB log waits: 0.00% (0 waits / 29515 writes)

-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine not available.

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    MySQL was started within the last 24 hours: recommendations may be inaccurate
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
             (specially the conclusions at the bottom of the page).
    Be careful, increasing innodb_redo_log_capacity means higher crash recovery mean time
Variables to adjust:
    join_buffer_size (> 256.0K, or always use indexes with JOINs)
    thread_cache_size (> 9)
    innodb_buffer_pool_size (>= 4.4G) if possible.
    innodb_redo_log_capacity should be (=32M) if possible, so InnoDB Redo log Capacity equals 25% of buffer pool size.

On running ./tuning-primer.sh the results were:

 -- MYSQL PERFORMANCE TUNING PRIMER --
      - By: Matthew Montgomery -

MySQL Version 8.0.33-0ubuntu0.22.04.4 x86_64

Uptime = 0 days 0 hrs 33 min 13 sec
Avg. qps = 40.26
Total Questions = 80240
Threads Connected = 1

Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html

Visit https://github.com/BMDan/tuning-primer.sh for the latest version of
this script, or to suggest improvements.

SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 2.000000 sec.
Since startup, 0 out of 80261 queries have taken longer than <long_query_time-when-they-were-executed> to complete.
Your long_query_time seems reasonable.

BINARY UPDATE LOG
The binary update log is enabled
The expire_logs_days is not set.
The mysqld will retain the entire binary log until RESET MASTER or PURGE MASTER LOGS commands are run manually
Setting expire_logs_days will allow you to remove old binary logs automatically
See http://dev.mysql.com/doc/refman/8.0/en/purge-master-logs.html

WORKER THREADS
Current thread_cache_size = 9
Current threads_cached = 8
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 151
Current threads_connected = 1
Historic max_used_connections = 103
The number of used connections is 68% of the configured maximum.
Your max_connections variable seems to be fine.

INNODB STATUS
Current InnoDB index space = 749 M
Current InnoDB data space = 3.64 G
Current InnoDB buffer pool free = 25 %
Current innodb_buffer_pool_size = 128 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

MEMORY USAGE
Max Memory Ever Allocated : 348 M
Configured Max Per-thread Buffers : 287 M
Configured Max Global Buffers : 152 M
Configured Max Memory Limit : 439 M
Physical Memory : 31.30 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
No key reads.  If you aren't using MyISAM, this is normal.  If you are
using MyISAM, this is very, very bad.
Current MyISAM index space = 0 bytes
Current key_buffer_size = 8 M
Key cache miss rate is 1 : 0
Key buffer free ratio = 81 %
Your key_buffer_size seems to be fine

QUERY CACHE
Your server does not support the query cache.  That's probably a good thing.

SORT OPERATIONS
Current sort_buffer_size = 256 K
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 260.00 K
You have had 187 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 10000 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_open_cache = 4000 tables
Current table_definition_cache = 2000 tables
You have a total of 1090 tables
You have 2109 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 16 M
Of 1244 temp tables, 0% were created on disk
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 84 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 0 : 80525
Your table locking seems to be fine

Based on mysqltuner and tuning-primer results, I added these lines in the end of /etc/mysql/mysql.conf.d/mysqld.cnf:

innodb_buffer_pool_size=5G
innodb_redo_log_capacity=1250M

Restarted with sudo systemctl restart mysql.service, and verified the values:

mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 5368709120 |
+-------------------------+------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'innodb_redo_log_capacity';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_redo_log_capacity | 1310720000 |
+--------------------------+------------+
1 row in set (0.01 sec)

And runned mysqlslap again:

Benchmark
        Average number of seconds to run all queries: 0.387 seconds
        Minimum number of seconds to run all queries: 0.349 seconds
        Maximum number of seconds to run all queries: 0.552 seconds
        Number of clients running queries: 100
        Average number of queries per client: 10
1

1 Answer 1

0

Shouldn't the queries be faster with a innodb_buffer_pool_size almost 40x bigger?

The buffer_pool is a "cache" for "blocks" being read from disk. A bigger cache lets more blocks stay in RAM without being reread from disk.

  • If the disk space used by the database(s) you have is less than the buffer_pool_size, increasing it won't make any difference.

  • If you have a large dataset - much bigger than RAM - increasing it will likely speed up queries.

(There are various nuances relating to "working set size", extra stuff that lives in the buffer pool, etc, but the above two bullet items should be sufficient for this discussion.)

I am not a fan of benchmarks; most test one thing -- which may not be the least bit related to the 'real' application. In particular, mysqlslap probably used so little data that it did not stress the cache.

InnoDB buffer pool / data size: 128.0M / 4.4G ... 32G RAM ... PHP

  • After allowing space for Apache and PHP, you 'should' assign 70% of the remaining RAM to the buffer_pool
  • 128M is an old default; it should be increased.
  • Once you have increased it, you should rarely see any I/O; most things will be well cached.
  • Your change to 5G does not change anything I am saying here. I would pick if the other apps don't need the space.

increasing innodb_buffer_pool_size not working?

I hope I have both explained that that question it is a "test case error" and explained what you should do.

2
  • Thanks for the answer! I'll review the settings. I got the idea to try MySQL tuning because I ran a benchmark on a WordPress (with a WordPress plugin), and it scored very low in the Database category. For example, "Importing large amount of data" scored 0.54 on a scale of 0 to 10; "Simple queries on single table" scored 1. As a comparison, on another server, with fewer cores (4) and less memory (16GB), and using MySQL 5.7 (WAMP default configuration) the scores were 5.95 and 10 respectively. Even though they are very different platforms, I thought it was something related to MySQL 8.
    – Cintya
    Aug 11, 2023 at 21:27
  • @Cintya - WP has other issues -- some are helped by WP Index Improvements
    – Rick James
    Aug 11, 2023 at 23:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.