MySQL 8 / MariaDB same query/database, MariaDB 380x faster

Question

We changed MariaDB to MySQL because we switched to Google Cloud SQL. The performance was very similar until now.

But after a new test, we noticed that a query took 380 times longer on MySQL than on MariaDB :

• On MySQL (8.0.27) :

3469 rows in set (21.28 sec)

Explain :

+----+--------------------+-----------------------+------------+------+--------------------+--------------------+---------+------------------------------------+------+----------+-------------------------+
| id | select_type        | table                 | partitions | type | possible_keys      | key                | key_len | ref                                | rows | filtered | Extra                   |
+----+--------------------+-----------------------+------------+------+--------------------+--------------------+---------+------------------------------------+------+----------+-------------------------+
|  1 | PRIMARY            | tst_batch             | NULL       | ALL  | NULL               | NULL               | NULL    | NULL                               |   32 |    10.00 | Using where             |
|  1 | PRIMARY            | tst_batch_test_case_1 | NULL       | ref  | batch_id           | batch_id           | 4       | reference.tst_batch.id             |   21 |   100.00 | NULL                    |
|  1 | PRIMARY            | tst_batch_step_1      | NULL       | ref  | batch_test_case_id | batch_test_case_id | 4       | reference.tst_batch_test_case_1.id |   31 |   100.00 | NULL                    |
|  8 | DEPENDENT SUBQUERY | tst_batch_step        | NULL       | ref  | batch_test_case_id | batch_test_case_id | 4       | reference.tst_batch_test_case_1.id |   31 |   100.00 | Using index             |
|  6 | DEPENDENT SUBQUERY | tst_batch_test_case   | NULL       | ref  | batch_id           | batch_id           | 4       | reference.tst_batch.id             |   21 |   100.00 | Using index             |
|  6 | DEPENDENT SUBQUERY | tst_batch_step        | NULL       | ref  | batch_test_case_id | batch_test_case_id | 4       | reference.tst_batch_test_case.id   |   31 |   100.00 | Using where; Not exists |
|  5 | DEPENDENT SUBQUERY | tst_batch_test_case   | NULL       | ref  | PRIMARY,batch_id   | batch_id           | 4       | reference.tst_batch.id             |   21 |   100.00 | Using index             |
|  5 | DEPENDENT SUBQUERY | tst_batch_step        | NULL       | ref  | batch_test_case_id | batch_test_case_id | 4       | reference.tst_batch_test_case.id   |   31 |   100.00 | Using index             |
|  4 | DEPENDENT SUBQUERY | tst_batch_test_case   | NULL       | ref  | batch_id           | batch_id           | 4       | reference.tst_batch.id             |   21 |   100.00 | Using index             |
|  3 | DEPENDENT SUBQUERY | tst_batch_test_case   | NULL       | ref  | PRIMARY,batch_id   | batch_id           | 4       | reference.tst_batch.id             |   21 |   100.00 | Using index             |
|  3 | DEPENDENT SUBQUERY | tst_batch_step        | NULL       | ref  | batch_test_case_id | batch_test_case_id | 4       | reference.tst_batch_test_case.id   |   31 |    10.00 | Using where             |
|  2 | DEPENDENT SUBQUERY | tst_batch_test_case   | NULL       | ref  | PRIMARY,batch_id   | batch_id           | 4       | reference.tst_batch.id             |   21 |   100.00 | Using index             |
|  2 | DEPENDENT SUBQUERY | tst_batch_step        | NULL       | ref  | batch_test_case_id | batch_test_case_id | 4       | reference.tst_batch_test_case.id   |   31 |    10.00 | Using where             |
+----+--------------------+-----------------------+------------+------+--------------------+--------------------+---------+------------------------------------+------+----------+-------------------------+
13 rows in set, 8 warnings (0.00 sec)

JSON explain : https://pastebin.com/RX5ZrKhb

SHOW VARIABLES : https://pastebin.com/C4rWEn8j

SHOW GLOBAL STATUS : https://pastebin.com/jrwfqP8R

• On MariaDB (10.5.12) :

3469 rows in set (0.055 sec)

Explain :

+------+--------------------+-----------------------+------+--------------------+--------------------+---------+-----------------------------------+------+------------------------------------+
| id   | select_type        | table                 | type | possible_keys      | key                | key_len | ref                               | rows | Extra                              |
+------+--------------------+-----------------------+------+--------------------+--------------------+---------+-----------------------------------+------+------------------------------------+
|    1 | PRIMARY            | tst_batch             | ALL  | NULL               | NULL               | NULL    | NULL                              | 32   | Using where                        |
|    1 | PRIMARY            | tst_batch_test_case_1 | ref  | batch_id           | batch_id           | 4       | portail2.tst_batch.id             | 10   |                                    |
|    1 | PRIMARY            | tst_batch_step_1      | ref  | batch_test_case_id | batch_test_case_id | 4       | portail2.tst_batch_test_case_1.id | 15   | Using where                        |
|    8 | DEPENDENT SUBQUERY | tst_batch_step        | ref  | batch_test_case_id | batch_test_case_id | 4       | portail2.tst_batch_test_case_1.id | 15   | Using index                        |
|    6 | DEPENDENT SUBQUERY | tst_batch_test_case   | ref  | batch_id           | batch_id           | 4       | portail2.tst_batch.id             | 10   | Using where; Using index           |
|    7 | DEPENDENT SUBQUERY | tst_batch_step        | ref  | batch_test_case_id | batch_test_case_id | 4       | portail2.tst_batch_test_case.id   | 15   | Using index condition; Using where |
|    5 | DEPENDENT SUBQUERY | tst_batch_test_case   | ref  | PRIMARY,batch_id   | batch_id           | 4       | portail2.tst_batch.id             | 10   | Using index                        |
|    5 | DEPENDENT SUBQUERY | tst_batch_step        | ref  | batch_test_case_id | batch_test_case_id | 4       | portail2.tst_batch_test_case.id   | 15   | Using index                        |
|    4 | DEPENDENT SUBQUERY | tst_batch_test_case   | ref  | batch_id           | batch_id           | 4       | portail2.tst_batch.id             | 10   | Using index                        |
|    3 | DEPENDENT SUBQUERY | tst_batch_test_case   | ref  | PRIMARY,batch_id   | batch_id           | 4       | portail2.tst_batch.id             | 10   | Using index                        |
|    3 | DEPENDENT SUBQUERY | tst_batch_step        | ref  | batch_test_case_id | batch_test_case_id | 4       | portail2.tst_batch_test_case.id   | 15   | Using where                        |
|    2 | DEPENDENT SUBQUERY | tst_batch_test_case   | ref  | PRIMARY,batch_id   | batch_id           | 4       | portail2.tst_batch.id             | 10   | Using index                        |
|    2 | DEPENDENT SUBQUERY | tst_batch_step        | ref  | batch_test_case_id | batch_test_case_id | 4       | portail2.tst_batch_test_case.id   | 15   | Using where                        |
+------+--------------------+-----------------------+------+--------------------+--------------------+---------+-----------------------------------+------+------------------------------------+
13 rows in set (0.001 sec)

JSON explain : https://pastebin.com/L6yrxM9T

SHOW VARIABLES : https://pastebin.com/4eDZD774

SHOW GLOBAL STATUS : https://pastebin.com/BNNFKW6Z

I have looked at the difference between the MySQL / MariaDB explain, however I am not competent enough to understand the difference.

The query/datas/environnemen exactly the same and built by SQLAlchemy. The query is not very complex, it includes a few FROM, a coalesce, and some LEFT OUTER JOIN. Here is the query (without AS to simplify as much as possible) :

SELECT tst_batch.created_at, tst_batch.updated_at, tst_batch.id, tst_batch.organisation_id, tst_batch.name, tst_batch.created_date, tst_batch.expected_start_date, tst_batch.expected_finish_date, tst_batch.end_date, tst_batch.version_number, tst_batch.type, (SELECT count(tst_batch_step.id) > 0
FROM tst_batch_step, tst_batch_test_case
WHERE tst_batch_test_case.batch_id = tst_batch.id AND tst_batch_step.batch_test_case_id = tst_batch_test_case.id AND tst_batch_step.s_status = 'KO'), (SELECT count(tst_batch_step.id) > 0
FROM tst_batch_step, tst_batch_test_case
WHERE tst_batch_test_case.batch_id = tst_batch.id AND tst_batch_step.batch_test_case_id = tst_batch_test_case.id AND tst_batch_step.s_status = 'Not Valid'), (SELECT count(tst_batch_test_case.id)
FROM tst_batch_test_case
WHERE tst_batch_test_case.batch_id = tst_batch.id), (SELECT count(tst_batch_step.id)
FROM tst_batch_step, tst_batch_test_case
WHERE tst_batch_test_case.batch_id = tst_batch.id AND tst_batch_step.batch_test_case_id = tst_batch_test_case.id), (SELECT count(tst_batch_test_case.id)
FROM tst_batch_test_case
WHERE tst_batch_test_case.batch_id = tst_batch.id AND (tst_batch_test_case.id NOT IN (SELECT distinct(tst_batch_step.batch_test_case_id)
FROM tst_batch_step
WHERE tst_batch_test_case.id = tst_batch_step.batch_test_case_id AND tst_batch_step.s_status IS NULL))), tst_batch_step_1.created_at, tst_batch_step_1.updated_at, tst_batch_step_1.id, tst_batch_step_1.batch_test_case_id , tst_batch_step_1.s_step_number, tst_batch_step_1.s_menu1, tst_batch_step_1.s_menu2, tst_batch_step_1.s_menu3, tst_batch_step_1.s_menu4, tst_batch_step_1.s_menu5, tst_batch_step_1.s_do, tst_batch_step_1.s_what_where1, tst_batch_step_1.s_what_where2, tst_batch_step_1.s_what_where3, tst_batch_step_1.s_what_where4, tst_batch_step_1.s_what_where5, tst_batch_step_1.s_what_where6, tst_batch_step_1.s_what_where7, tst_batch_step_1.s_what_where8, tst_batch_step_1.s_action1, tst_batch_step_1.s_action2, tst_batch_step_1.s_file_to_upload, tst_batch_step_1.s_close_window, tst_batch_step_1.s_open_window, tst_batch_step_1.s_look1, tst_batch_step_1.s_look2, tst_batch_step_1.s_link_img, tst_batch_step_1.s_validate_input, tst_batch_step_1.s_available_actions, tst_batch_step_1.s_available_values, tst_batch_step_1.s_status, tst_batch_step_1.s_comment, tst_batch_step_1.s_screenshot_link, tst_batch_step_1.s_completion_date, tst_batch_test_case_1.created_at, tst_batch_test_case_1.updated_at, tst_batch_test_case_1.id, tst_batch_test_case_1.batch_id, tst_batch_test_case_1.assign_id, tst_batch_test_case_1.tc_code, tst_batch_test_case_1.tc_name, tst_batch_test_case_1.tc_created_for_version, tst_batch_test_case_1.tc_family_name, tst_batch_test_case_1.tc_sub_family_name, tst_batch_test_case_1.tc_sub_sub_family_name, tst_batch_test_case_1.tc_criticality, (SELECT coalesce(max(tst_batch_step.s_step_number), 0)
FROM tst_batch_step
WHERE tst_batch_step.batch_test_case_id = tst_batch_test_case_1.id)
FROM tst_batch LEFT OUTER JOIN tst_batch_test_case AS tst_batch_test_case_1 ON tst_batch.id = tst_batch_test_case_1.batch_id LEFT OUTER JOIN tst_batch_step AS tst_batch_step_1 ON tst_batch_test_case_1.id = tst_batch_step_1.batch_test_case_id
WHERE tst_batch.end_date IS NULL;

What can be done to increase performance on the MySQL side? Such a difference seems to me really huge. Do I have to set up something on the server side? (basic configuration at the moment)

Answer 1

I think a strong clue answer might come from the output of this on MariaDB

SHOW GLOBAL STATUS LIKE 'Subquery%';

For deeper analysis of the settings, please provide the VARIABLES and GLOBAL STATUS (for both servers) as discussed in http://mysql.rjweb.org/doc.php/mysql_analysis#tuning

More

Based on the numbers about 14M hits / 1M misses in the Subquery cache, MariaDB avoided reevaluating 94% of your subqueries -- MySQL has no similar optimization. If you want to speed up MySQL, it would require some reformulation, possibly with WITH and CTEs (available in 8.0).

(MariaDB is also better of jettisoning unnecessary Joins.)

Answer 2

Analysis of GLOBAL STATUS and VARIABLES on MySQL server:

Observations:

• Version: 8.0.18-google
• 2 GB of RAM
• Uptime = 21:46:44; some GLOBAL STATUS values may not be meaningful yet.
• Are you sure this was a SHOW GLOBAL STATUS ?
• 2.79 Queries/sec : 1.58 Questions/sec

The More Important Issues:

If the disk is SSD, there some minor optimizations.

Be aware of CHARACTER SET differences between your two servers.

There seem to be a lot of ROLLBACKs.

Galera does not seem to be on, yet innodb_autoinc_lock_mode seems to be set to the value perferred on Galera. (I doubt if this has any significant impact.)

Recommended settings change (many save RAM):

max_connections = 100;       -- 4030 wastes memory that won't be used.
thread_cache_size = 15
innodb_page_cleaners = 256;
innodb_ft_result_cache_limit = 500M  --    if you have only 2GB, lower this

Details and other observations:

( table_open_cache ) = 4,000 -- Number of table descriptors to cache -- Several hundred is usually good.

( binlog_cache_size * max_connections ) = (32768 * 4030) / 2048M = 6.1% -- RAM used for caching transactions on the way to the binlog. -- Decrease binlog_cache_size (now 32768) and/or max_connections (now 4030)

( innodb_lru_scan_depth * innodb_page_cleaners ) = 2,048 * 1 = 2,048 -- Amount of work for page cleaners every second. -- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixable by lowering lru_scan_depth: Consider 1000 / innodb_page_cleaners (now 1). Also check for swapping.

( innodb_lru_scan_depth ) = 2,048 -- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth

( Innodb_buffer_pool_pages_free * 16384 / innodb_buffer_pool_size ) = 85,838 * 16384 / 1408M = 95.3% -- buffer pool free -- buffer_pool_size is bigger than working set; could decrease it

( innodb_io_capacity_max ) = 10,000 -- When urgently flushing, use this many IOPs. -- Reads could be slugghish or spiky.

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 85,838 / 90112 = 95.3% -- Pct of buffer_pool currently not in use -- innodb_buffer_pool_size (now 1476395008) is bigger than necessary?

( Innodb_pages_written / Innodb_buffer_pool_write_requests ) = 184,767 / 906041 = 20.4% -- Write requests that had to hit disk -- Check innodb_buffer_pool_size (now 1476395008)

( Innodb_buffer_pool_bytes_data / innodb_buffer_pool_size ) = 69,369,856 / 1408M = 4.7% -- Percent of buffer pool taken up by data -- A small percent may indicate that the buffer_pool is unnecessarily big.

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 108,566,016 / (78404 / 3600) / 2 / 512M = 0.00464 -- Ratio -- (see minutes)

( Com_rollback ) = 24,057 / 78404 = 0.31 /sec -- ROLLBACKs in InnoDB. -- An excessive frequency of rollbacks may indicate inefficient app logic.

( innodb_flush_neighbors ) = 2 -- A minor optimization when writing blocks to disk. -- Use 0 for SSD drives; 1 for HDD.

( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON -- Whether to use the adapative hash (AHI). -- ON for mostly readonly; OFF for DDL-heavy ( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks. -- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( max_connections ) = 4,030 -- Maximum number of connections (threads). Impacts various allocations. -- If max_connections (now 4030) is too high and various memory settings are high, you could run out of RAM.

( 176000 * max_connections ) = (176000 * 4030) / 2048M = 33.0% -- Estimate of ram usage due to the size of max_connections. -- max_connections (now 4030) is somewhat high

( 176000 * max_connections ) = (176000 * 4030) / 2048M = 33.0% -- Estimate of ram usage due to the size of max_connections. -- max_connections (now 4030) is dangerously high; lower it significantly. Possible falue for comparision: max_used_connections .

( innodb_ft_result_cache_limit ) = 2,000,000,000 / 2048M = 93.1% -- Byte limit on FULLTEXT resultset. (It grows as needed.) -- Lower the setting.

( innodb_autoextend_increment * 1048576 ) = (64 * 1048576) / 2048M = 3.1% -- How much to increase ibdata1 by (when needed). -- Decrease setting to avoid premature swapping.

( character_set_client ) = character_set_client = latin1 --

( character_set_connection ) = character_set_connection = latin1 --

( character_set_results ) = character_set_results = latin1 --

( local_infile ) = local_infile = ON -- local_infile (now ON) = ON is a potential security issue

( Com_rollback / (Com_commit + Com_rollback) ) = 24,057 / (16 + 24057) = 99.9% -- Rollback : Commit ratio -- Rollbacks are costly; change app logic

( Com_admin_commands / Queries ) = 93,650 / 219139 = 42.7% -- Percent of queries that are "admin" commands. -- What's going on?

( Com__biggest ) = Com__biggest = Com_admin_commands -- Which of the "Com_" metrics is biggest. -- Normally it is Com_select (now 40940). If something else, then it may be a sloppy platform, or may be something else.

( Com_show__star / Questions ) = 27,116 / 123880 = 21.9% -- Pct of queries that are SHOW commands. -- Why so many SHOW commands?

( slow_query_log ) = slow_query_log = OFF -- Whether to log slow queries. (5.1.12)

( long_query_time ) = 10 -- Cutoff (Seconds) for defining a "slow" query. -- Suggest 2

( log_slow_slave_statements ) = log_slow_slave_statements = OFF -- (5.6.11, 5.7.1) By default, replicated statements won't show up in the slowlog; this causes them to show. -- It can be helpful in the slowlog to see writes that could be interfering with Replica reads.

( Max_used_connections / max_connections ) = 14 / 4030 = 0.35% -- Peak % of connections -- Since several memory factors can expand based on max_connections (now 4030), it is good not to have that setting too high.

( thread_cache_size / Max_used_connections ) = 48 / 14 = 342.9% -- There is no advantage in having the thread cache bigger than your likely number of connections. Wasting space is the disadvantage.

( thread_stack * max_connections ) = (286720 * 4030) / 2048M = 53.8% -- Bare minimum memory allocation for max_connections. -- Lower max_connections (now 4030)

Abnormally small:

Com_update = 0
Handler_read_rnd = 77 /HR
Innodb_buffer_pool_reads * innodb_page_size / innodb_buffer_pool_size = 3.0%
Open_files = 2
Select_range = 0
Select_range / Com_select = 0
Sort_rows = 1.0e+2 /HR
innodb_flushing_avg_loops = 5
performance_schema_max_cond_classes = 0
performance_schema_max_file_classes = 0
performance_schema_max_file_handles = 0
performance_schema_max_mutex_classes = 0
performance_schema_max_rwlock_classes = 0
performance_schema_max_socket_classes = 0
performance_schema_max_stage_classes = 0
performance_schema_max_statement_classes = 0
performance_schema_max_thread_classes = 0
slave_net_timeout = 30

Abnormally large:

Acl_cache_items_count = 4
Com_create_procedure = 24 /HR
Com_drop_db = 0.046 /HR
Com_drop_procedure = 73 /HR
Com_purge_before_date = 0.046 /HR
Com_show_engine_status = 0.033 /sec
Com_show_slave_status = 0.067 /sec
Handler_read_rnd_next / Handler_read_rnd = 10,442
Innodb_buffer_pool_pages_flushed / max(Questions, Queries) = 0.843
Innodb_data_pending_fsyncs = 1.84e+19
Max_execution_time_set = 6
Max_execution_time_set / Com_select = 0.01%
Ssl_default_timeout = 7,200
Ssl_verify_depth = 1.84e+19
Ssl_verify_mode = 1
max_error_count = 1,024
max_length_for_sort_data = 4,096
optimizer_trace_offset = --1
server_id = 3.23e+9

Abnormal strings:

Current_tls_ca = /mysql/datadir/client_ca_cert.pem
Current_tls_cert = /mysql/datadir/server_cert.pem
Current_tls_cipher = ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES128-SHA:ECDHE-RSA-AES128-SHA:ECDHE-ECDSA-AES256-SHA:ECDHE-RSA-AES256-SHA:AES128-GCM-SHA256:AES128-SHA:AES256-SHA:DES-CBC3-SHA
Current_tls_key = /mysql/datadir/server_pkey.pem
admin_address = 127.0.0.1
binlog_transaction_dependency_tracking = WRITESET
create_admin_listener_thread = ON
enforce_gtid_consistency = ON
enforce_storage_engine = Innodb
event_scheduler = ON
gtid_mode = ON
have_query_cache = NO
innodb_fast_shutdown = 1
innodb_monitor_enable = all
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
partial_revokes = ON
protocol_compression_algorithms = zlib,zstd,uncompressed
relay_log_recovery = ON
slave_rows_search_algorithms = INDEX_SCAN,HASH_SCAN
sql_slave_skip_counter = 0

Answer 3

Analysis of GLOBAL STATUS and VARIABLES on MariaDB server:
 

Observations:

• Version: 10.5.12-MariaDB
• 2 GB of RAM -- Is this correct?
• Uptime = 127d 22:12:03
• Are you sure this was a SHOW GLOBAL STATUS ?
• 0.218 QPS

The More Important Issues:

One query every 5 seconds? That's very idle!

If the disk is SSD, there some minor optimizations.

What OS are you using? innodb_flush_method may not be optimal.

Recommended settings change (many save RAM):

key_buffer_size = 20M        -- to free up space for more important uses
innodb_buffer_pool_size = 1G -- if you have only 2GB of RAM; else 70% of RAM
max_connections = 100
thread_cache_size = 15
innodb_page_cleaners = 256
myisam_sort_buffer_size = 10M

Details and other observations:

( (key_buffer_size - 1.2 * Key_blocks_used * 1024) ) = ((128M - 1.2 * 0 * 1024)) / 2048M = 6.2% -- Percent of RAM wasted in key_buffer. -- Decrease key_buffer_size (now 134217728).

( Key_blocks_used * 1024 / key_buffer_size ) = 0 * 1024 / 128M = 0 -- Percent of key_buffer used. High-water-mark. -- Lower key_buffer_size (now 134217728) to avoid unnecessary memory usage.

( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) ) = ((128M / 0.20 + 128M / 0.70)) / 2048M = 40.2% -- Most of available ram should be made available for caching. -- http://mysql.rjweb.org/doc.php/memory

( Binlog_cache_disk_use / Binlog_cache_use ) = 396 / 3401 = 11.6% -- Spill to disk -- Increase binlog_cache_size (now 32768)

( innodb_buffer_pool_size ) = 128M -- InnoDB Data + Index cache -- 128M (an old default) is woefully small.

( innodb_buffer_pool_size ) = 128 / 2048M = 6.2% -- % of RAM used for InnoDB buffer_pool -- Set to about 70% of available RAM. (To low is less efficient; too high risks swapping.)

( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) ) = ((128M / 0.20 + 128M / 0.70)) / 2048M = 40.2% -- (metric for judging RAM usage)

( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,536 * 1 = 1,536 -- Amount of work for page cleaners every second. -- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixable by lowering lru_scan_depth: Consider 1000 / innodb_page_cleaners (now 1). Also check for swapping.

( innodb_lru_scan_depth ) = 1,536 -- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth

( innodb_io_capacity ) = 200 -- When flushing, use this many IOPs. -- Reads could be slugghish or spiky.

( innodb_io_capacity_max / innodb_io_capacity ) = 2,000 / 200 = 10 -- Capacity: max/plain -- Recommend 2. Max should be about equal to the IOPs your I/O subsystem can handle. (If the drive type is unknown 2000/200 may be a reasonable pair.)

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 321,595,904 / (11052723 / 3600) / 1 / 96M = 0.00104 -- Ratio -- (see minutes)

( innodb_flush_method ) = innodb_flush_method = fsync -- How InnoDB should ask the OS to write blocks. Suggest O_DIRECT or O_ALL_DIRECT (Percona) to avoid double buffering. (At least for Unix.) See chrischandler for caveat about O_ALL_DIRECT

( default_tmp_storage_engine ) = default_tmp_storage_engine =

( innodb_flush_neighbors ) = 1 -- A minor optimization when writing blocks to disk. -- Use 0 for SSD drives; 1 for HDD.

( innodb_io_capacity ) = 200 -- I/O ops per second capable on disk . 100 for slow drives; 200 for spinning drives; 1000-2000 for SSDs; multiply by RAID factor.

( sync_binlog ) = 0 -- Use 1 for added security, at some cost of I/O =1 may lead to lots of "query end"; =0 may lead to "binlog at impossible position" and lose transactions in a crash, but is faster. 0 is OK for Galera.

( Handler_rollback/Questions ) = 423,005/2414126 = 17.5% -- Rollbacks/query -- Why so many ROLLBACKs?

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks. -- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( myisam_sort_buffer_size ) = 134,216,704 / 2048M = 6.2% -- Used for ALTER, CREATE INDEX, OPTIMIZE, LOAD DATA; set when you need it. Also for MyISAM's REPAIR TABLE. -- Decrease myisam_sort_buffer_size (now 134216704) to keep from blowing out RAM.

( innodb_ft_result_cache_limit ) = 2,000,000,000 / 2048M = 93.1% -- Byte limit on FULLTEXT resultset. (Possibly not preallocated, but grows?) -- Lower the setting.

( innodb_autoextend_increment * 1048576 ) = (64 * 1048576) / 2048M = 3.1% -- How much to increase ibdata1 by (when needed). -- Decrease setting to avoid premature swapping.

( local_infile ) = local_infile = ON -- local_infile (now ON) = ON is a potential security issue

( Com_rollback / (Com_commit + Com_rollback) ) = 1,033,039 / (186964 + 1033039) = 84.7% -- Rollback : Commit ratio -- Rollbacks are costly; change app logic

( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (128354 + 22970 + 71418 + 0) / 186964 = 1.19 -- Statements per Commit (assuming all InnoDB) -- Low: Might help to group queries together in transactions; High: long transactions strain various things.

( Com__biggest ) = Com__biggest = Com_rollback -- Which of the "Com_" metrics is biggest. -- Normally it is Com_select (now 956577). If something else, then it may be a sloppy platform, or may be something else.

( log_slave_updates ) = log_slave_updates = OFF -- Galera needs log_slave_updates (now OFF) ON

( wsrep_log_conflicts ) = wsrep_log_conflicts = OFF -- If you get deadlock conflicts during COMMIT, this flag can be helpful.

( slow_query_log ) = slow_query_log = OFF -- Whether to log slow queries. (5.1.12)

( long_query_time ) = 10 -- Cutoff (Seconds) for defining a "slow" query. -- Suggest 2

( back_log ) = 80 -- (Autosized as of 5.6.6; based on max_connections) -- Raising to min(150, max_connections (now 151)) may help when doing lots of connections.

( Max_used_connections / max_connections ) = 4 / 151 = 2.6% -- Peak % of connections -- Since several memory factors can expand based on max_connections (now 151), it is good not to have that setting too high.

( thread_cache_size / Max_used_connections ) = 151 / 4 = 3775.0% -- There is no advantage in having the thread cache bigger than your likely number of connections. Wasting space is the disadvantage.

( thread_pool_max_threads ) = 65,536 -- One of many settings for MariaDB's thread pooling -- Lower the value.

VSClasses.inc.254 Error with eval('((0 - 0) / 0) / 4096') expr=[[((query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache) / query_cache_min_res_unit]] VSClasses.inc.254 Error with eval('(0 - 0) / 0 / 16384') expr=[[(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size]]

Abnormally small:

( Innodb_pages_read + Innodb_pages_written ) / Uptime = 0.0005
3600 * (wsrep_received_bytes + wsrep_replicated_bytes) / Uptime = 0.1MB
Bytes_received = 111 /sec
Bytes_sent = 481 /sec
Com_select = 0.087 /sec
Com_set_option = 9.9 /HR
Com_show_status = 0.0013 /HR
Created_tmp_files = 0.032 /HR
Delete_scan = 0.0013 /HR
Empty_queries = 0.055 /sec
Handler_read_first = 0.33 /HR
Handler_read_key = 0.95 /sec
Handler_read_next = 9.5 /sec
Handler_tmp_update = 0
Handler_tmp_write = 0.24 /sec
Handler_update = 7.5 /HR
Handler_write = 0.048 /sec
Innodb_buffer_pool_bytes_data = 12 /sec
Innodb_buffer_pool_pages_made_young = 0.12 /HR
Innodb_buffer_pool_pages_misc = 0
Innodb_buffer_pool_pages_misc * 16384 / innodb_buffer_pool_size = 0
Innodb_buffer_pool_pages_total = 8,065
Innodb_buffer_pool_read_requests = 13 /sec
Innodb_buffer_pool_write_requests = 0.34 /sec
Innodb_data_read = 1.6 /sec
Innodb_data_reads = 0.38 /HR
Innodb_data_writes = 27 /HR
Innodb_data_writes - Innodb_log_writes - Innodb_dblwr_writes = 2.2 /HR
Innodb_data_written = 6.7 /sec
Innodb_log_write_requests = 6.1 /HR
Innodb_mem_dictionary = 1.13e+6
Innodb_pages_read = 0.34 /HR
Innodb_pages_read + Innodb_pages_written = 1.8 /HR
Innodb_pages_written = 1.5 /HR
Innodb_rows_read = 11 /sec
Memory_used_initial = 31.9MB
Rows_read = 11 /sec
Rows_sent = 1.2 /sec
Rows_tmp_read = 0.42 /sec
Select_range = 0.01 /HR
Sort_priority_queue_sorts = 0.00065 /HR
Table_locks_immediate = 4.6 /HR
Table_open_cache_hits = 0.17 /sec
innodb_adaptive_max_sleep_delay = 0
wsrep_apply_oooe = 0
wsrep_apply_window = 1
wsrep_cert_interval = 0
wsrep_cluster_conf_id = 0.00065 /HR
wsrep_commit_window = 1
wsrep_last_committed = 1.37e+6
wsrep_local_cached_downto = 1.36e+6
wsrep_local_commits = 3,373
wsrep_local_index = 0
wsrep_received = 7 /HR
wsrep_received_bytes = 2.9 /sec
wsrep_received_bytes + wsrep_replicated_bytes = 29 /sec
wsrep_replicated = 9,661

Abnormally large:

Innodb_buffer_pool_pages_lru_flushed = 97
Innodb_buffer_pool_write_requests / Innodb_buffer_pool_pages_flushed = 833
Innodb_instant_alter_column = 156
Innodb_log_writes / Innodb_log_write_requests = 403.1%
Innodb_master_thread_idle_loops = 1.1e+7
Open_streams = 4
Tc_log_page_size = 4,096
innodb_lru_scan_depth / innodb_io_capacity = 7.68
performance_schema_max_statement_classes = 222
wsrep_cert_index_size = 3,384

Abnormal strings:

Slave_heartbeat_period = 0
Slave_received_heartbeats = 0
aria_recover_options = BACKUP,QUICK
disconnect_on_expired_password = OFF
innodb_fast_shutdown = 1
myisam_stats_method = NULLS_UNEQUAL
old_alter_table = DEFAULT
optimizer_trace = enabled=off
slave_parallel_mode = optimistic
sql_slave_skip_counter = 0
wsrep_cluster_address = gcomm://
wsrep_cluster_status = Primary
wsrep_connected = ON
wsrep_data_home_dir = /home/mysql/
wsrep_node_address = 10.15.15.1
wsrep_provider = /usr/lib64/galera-4/libgalera_smm.so
wsrep_provider_capabilities = :MULTI_MASTER:CERTIFICATION:PARALLEL_APPLYING:TRX_REPLAY:ISOLATION:PAUSE:CAUSAL_READS:INCREMENTAL_WRITESET:UNORDERED:PREORDERED:STREAMING:NBO:
wsrep_provider_name = Galera
wsrep_provider_vendor = Codership Oy 
wsrep_provider_version = 26.4.9(r819f29c)
wsrep_ready = ON
wsrep_replicate_myisam = ON

Answer 4

I don't have your schema, but based on your query, check if you have these indexes:

ALTER TABLE `tst_batch` ADD INDEX `tst_batch_idx_end_date` (`end_date`);
ALTER TABLE `tst_batch_step` ADD INDEX `tst_batch_step_idx_batch_id` (`batch_test_case_id`);
ALTER TABLE `tst_batch_step` ADD INDEX `tst_batch_step_idx_s_status_batch_id` (`s_status`,`batch_test_case_id`);
ALTER TABLE `tst_batch_test_case` ADD INDEX `tst_batch_test_cas_idx_batch_id_id` (`batch_id`,`id`);

and you should try to avoid correlated Subqueries like this one:

...tst_batch_test_case.id NOT IN (
                    SELECT
                        DISTINCT...    

I used EverSQL to identify the missing indexes.