Managing mariadb database performance with very large tables

Question

I have a joomla-3.9 and apache-2.4.34 system on fedora29 with mariadb-10.2.19 and the tables that contain the search content are very large. Several are larger than 1.2GB and one is larger than 5.5GB. Deleting articles takes a really long time.

The system it is running on is a Intel(R) Xeon(R) CPU E5-2623 v3 @ 3.00GHz with 64GB of RAM and a RAID5 SSD. There really isn't any other activity.

What options are there for improving performance? I've done some basic mariadb tuning, but there really isn't much I can do to the database itself.

Is this what Galera is for? Would putting the database in a ramdisk help?

What's involved in moving it to AWS?

Would changing to another filesystem other than ext4 make an appreciable difference?

I was thinking of putting just the search tables on a separate SSD without RAID5 since they can be rebuilt so easily, but I couldn't figure out how to decouple them from the rest of the database.

Here is my my.cnf config for this system. Are there any other tuning changes you would make to improve performance?

[client]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 16M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 8
relay_log_space_limit = 500M
relay_log_purge = 1
log-slave-updates = 1
max_heap_table_size = 256M
tmp_table_size = 256M
relay-log=bwimail01-relay-bin
log_bin                 = /var/log/mariadb/mysql-bin.log
expire_logs_days        = 5
max_binlog_size         = 100M
plugin_load=server_audit=server_audit.so
server_audit_events=connect,query
server_audit_file_path = /var/log/mariadb/server_audit.log
server_audit_file_rotate_size           = 1G
server_audit_file_rotations             = 1
slow-query-log = 1
slow-query-log-file = /var/log/mariadb/mariadb-slow.log
long_query_time = 1
log_error = /var/log/mariadb/mariadb-error.log
binlog_format=mixed
server-id       = 5
report-host=bwimail01.example.com
innodb_data_home_dir = /var/lib/mysql
innodb_defragment=1
innodb_file_per_table
innodb_flush_log_at_trx_commit = 2
innodb_data_file_path = ibdata1:10M:autoextend:max:500M
innodb_buffer_pool_size=850M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

edit: here is "show create table finder_links"

finder_links | CREATE TABLE `finder_links` (
`link_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`url` varchar(255) NOT NULL,
`route` varchar(255) NOT NULL,
`title` varchar(400) DEFAULT NULL,
`description` text DEFAULT NULL,
`indexdate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`md5sum` varchar(32) DEFAULT NULL,
`published` tinyint(1) NOT NULL DEFAULT 1,
`state` int(5) DEFAULT 1,
`access` int(5) DEFAULT 0,
`language` varchar(8) NOT NULL,
`publish_start_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`publish_end_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`start_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`end_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`list_price` double unsigned NOT NULL DEFAULT 0,
`sale_price` double unsigned NOT NULL DEFAULT 0,
`type_id` int(11) NOT NULL,
`object` mediumblob NOT NULL,
PRIMARY KEY (`link_id`),
KEY `idx_type` (`type_id`),
KEY `idx_title` (`title`(100)),
KEY `idx_md5` (`md5sum`),
KEY `idx_url` (`url`(75)),
KEY `idx_published_list` 
(`published`,`state`,`access`,`publish_start_date`,`publish_end_date`,`list_price`),
KEY `idx_published_sale`
(`published`,`state`,`access`,`publish_start_date`,`publish_end_date`,`sal e_price`)
) ENGINE=InnoDB AUTO_INCREMENT=62705 DEFAULT CHARSET=utf8mb4 

Part of the problem is that, because this is part of joomla, I really have no control over how the tables are organized, what changes I can make to improve the schema, or how I organize the writes to it.

It appears there are a bunch of slow queries similar to these:

INSERT  IGNORE INTO  `xu5gc_finder_terms`
    (`term`, `stem`, `common`, `phrase`, `weight`, `soundex`, `language`) 
  SELECT  ta.term, ta.stem, ta.common, ta.phrase, ta.term_weight,
        SOUNDEX(ta.term), ta.language
    FROM  `xu5gc_finder_tokens_aggregate` AS ta
    WHERE  ta.term_id = 0
    GROUP BY  ta.term, ta.stem, ta.common, ta.phrase, ta.term_weight,
        SOUNDEX(ta.term), ta.language;

INSERT INTO  `xu5gc_finder_tokens_aggregate`
               (`term_id`, `map_suffix`,
                `term`, `stem`, `common`, `phrase`, `term_weight`, `context`,
                `context_weight`, `total_weight`, `language`) 
  SELECT  COALESCE(t.term_id, 0), '', t1.term, t1.stem, t1.common,
          t1.phrase, t1.weight, t1.context,
          ROUND( t1.weight * COUNT( t2.term ) * 0.700000, 8 ) AS context_weight,
          0, t1.language
    FROM (
        SELECT  DISTINCT t1.term, t1.stem, t1.common, t1.phrase, t1.weight,
                t1.context, t1.language
            FROM  `xu5gc_finder_tokens` AS t1
            WHERE  t1.context = 2 
         ) AS t1
    JOIN  `xu5gc_finder_tokens` AS t2  ON t2.term = t1.term
    LEFT JOIN  `xu5gc_finder_terms` AS t  ON t.term = t1.term
    WHERE  t2.context = 2
    GROUP BY  t1.term, t.term_id, t1.term, t1.stem, t1.common,
        t1.phrase, t1.weight, t1.context, t1.language
    ORDER BY  t1.term DESC;

and

UPDATE  `xu5gc_finder_terms` AS t
    INNER JOIN  `xu5gc_finder_tokens_aggregate` AS ta
                    ON ta.term_id = t.term_id 
    SET t.`links` = t.links + 1;

SET timestamp=1546570831;
SELECT DISTINCT t.term_id AS id, t.term AS term
    FROM xu5gc_finder_terms AS t
    WHERE t.soundex = SOUNDEX('2018 2594-1')
      AND t.phrase = 1;

Edit: Adding more requested information. It has been running for about 24 hours now, but it's a development system so there really isn't much activity right now.

SHOW GLOBAL VARIABLES
https://pastebin.com/W0BPKtU5

SHOW GLOBALK STATUS
https://pastebin.com/gB0AQut6

Finder tables (SHOW INDEX FROM and EXPLAIN)
xu5gc_finder_terms is the 5.4GB table
https://pastebin.com/rJxts9Sp

MySQLTuner-perl-master
https://pastebin.com/AW6F0uMj

Mariadb Error Log (/var/log/mariadb/mariadb-error.log)
https://pastebin.com/EE8Q0k1D

I thought it might be helpful to have a few images. Please let me know if other stats would be helpful.

Edit: Added ulimit output (as root):

# ulimit -a
core file size          (blocks, -c) unlimited
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 128545
max locked memory       (kbytes, -l) 16384
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 128545
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

Answer 1

Analysis of VARIABLES and GLOBAL STATUS:

Observations:

• Version: 10.2.18-MariaDB-log
• 64 GB of RAM
• Uptime = 20:39:00; some GLOBAL STATUS values may not be meaningful yet.
• You are not running on Windows.
• Running 64-bit version
• You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

• innodb_io_capacity can probably be increased to 1000, maybe more.

• In this server, Galera is turned off. Do you have it ON for production? If you are using normal replication, consider sync_binlog = ON.

• 6 Table scans / second. Half of queries involve a table scan. Let's see some more of your queries. (Only 79 sort-merge passes per hour.)

• Are you using NDB Cluster? (I see Handler_discover.)

• (Thanks: Good settings for slowlog.)

• innodb_use_atomic_writes -- Is the RAID controller hardware? What brand? Does it support atomic 16KB writes?

• engine_condition_pushdown = off -- Why?

Details and other observations:

( innodb_buffer_pool_size / _ram ) = 15360M / 65536M = 23.4% -- % of RAM used for InnoDB buffer_pool (But you don't have enough data to make increasing it worthwhile)

( ( Binlog_commits - Binlog_group_commits ) / Binlog_group_commits ) = ( 74938 - 74938 ) / 74938 = 0 -- Pct of COMMITs that could have been performed in parallel -- Increase binlog_commit_wait_usec and/or binlog_commit_wait_count on the Master.

( innodb_page_cleaners / innodb_buffer_pool_instances ) = 4 / 15 = 0.267 -- page_cleaners -- Recommend setting innodb_page_cleaners to innodb_buffer_pool_instances

( innodb_lru_scan_depth ) = 1,024 -- "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 ) = 705,616 * 16384 / 15360M = 71.8% -- buffer pool free -- buffer_pool_size is bigger than working set; could decrease it

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 705,616 / 983040 = 71.8% -- Pct of buffer_pool currently not in use -- innodb_buffer_pool_size is bigger than necessary?

( Innodb_buffer_pool_bytes_data / innodb_buffer_pool_size ) = 4,475,404,288 / 15360M = 27.8% -- Percent of buffer pool taken up by data -- A small percent may indicate that the buffer_pool is unnecessarily big.

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 74,340 / 60 * 1024M / 465657856 = 2,856 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf. -- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size. (Cannot change in AWS.)

( 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.

( 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.

( join_buffer_size / _ram ) = 1M / 65536M = 0.00% -- 0-N per thread. May speed up JOINs (better to fix queries/indexes) (all engines) Used for index scan, range index scan, full table scan, each full JOIN, etc. -- If large, decrease join_buffer_size to avoid memory pressure. Suggest less than 1% of RAM. If small, increase to 0.01% of RAM to improve some queries.

( net_buffer_length / max_allowed_packet ) = 16,384 / 16M = 0.10%

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

( bulk_insert_buffer_size / _ram ) = 8M / 65536M = 0.01% -- Buffer for multi-row INSERTs and LOAD DATA -- Too small could hinder such operations.

( tmp_table_size ) = 256M -- Limit on size of MEMORY temp tables used to support a SELECT -- Decrease tmp_table_size to avoid running out of RAM. Perhaps no more than 64M.

( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (20260 + 69883 + 2901 + 0) / 62678 = 1.48 -- Statements per Commit (assuming all InnoDB) -- Low: Might help to group queries together in transactions.

( Select_scan ) = 448,754 / 74340 = 6 /sec -- full table scans -- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 448,754 / 871456 = 51.5% -- % of selects doing full table scan. (May be fooled by Stored Routines.) -- Add indexes / optimize queries

( relay_log_space_limit ) = 500M -- The max total size for relay logs on a Slave. (0=unlimited) -- Let's discuss the rationale for having a limit.

( binlog_format ) = binlog_format = MIXED -- STATEMENT/ROW/MIXED. ROW is preferred; it may become the default.

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

( back_log / max_connections ) = 80 / 151 = 53.0%

Abnormally small:

Handler_read_next / Handler_read_key = 0.647
Innodb_secondary_index_triggered_cluster_reads = 7.8MB
Rows_tmp_read = 2.3MB
eq_range_index_dive_limit = 0
innodb_log_block_size = 0
innodb_max_bitmap_file_size = 0
innodb_max_changed_pages = 0
innodb_mirrored_log_groups = 0
innodb_sched_priority_cleaner = 0
innodb_show_locks_held = 0
lock_wait_timeout = 86400
slave_net_timeout = 60

Abnormally large:

Com_show_binlogs = 25 /HR
Com_show_engine_status = 26 /HR
Com_show_plugins = 25 /HR
Com_show_slave_hosts = 0.15 /HR
Com_show_slave_status = 0.04 /sec
Handler_discover = 27 /HR
Innodb_buffer_pool_pages_flushed / max(Questions, Queries) = 1
Innodb_buffer_pool_pages_free = 705,616
Innodb_system_rows_deleted = 0.81 /sec
Innodb_system_rows_inserted = 0.81 /sec
Innodb_system_rows_read = 60,203
Opened_plugin_libraries = 0.097 /HR
Performance_schema_file_classes_lost = 1
Slave_received_heartbeats = 656
Slaves_running = 1
group_concat_max_len = 1MB
innodb_adaptive_hash_index_partitions = 8
max_relay_log_size = 100MB

Abnormal strings:

Slave_running = ON
binlog_annotate_row_events = ON
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_corrupt_table_action = deprecated
innodb_data_home_dir = /var/lib/mysql
innodb_defragment = ON
innodb_fast_shutdown = 1
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_large_prefix = ON
innodb_locking_fake_changes = OFF
innodb_undo_directory = ./
innodb_use_atomic_writes = ON
innodb_use_global_flush_log_at_trx_commit = OFF
innodb_use_trim = ON
log_slow_admin_statements = ON
log_slow_slave_statements = ON
myisam_stats_method = NULLS_UNEQUAL
opt_s__engine_condition_pushdown = off
replicate_annotate_row_events = ON

Answer 2

My Mantra: "You can only throw hardware at a performance problem once; it's better to look for a software solution."

Deleting lots of rows is costly because it saves the old rows in case of a power failure (or other crash or need for ROLLBACK).

• If it is a small number of rows, perhaps you do not have an appropriate index? Please provide SHOW CREATE TABLE and the DELETE statement.

• If it is a large number of rows, then it is best to do it in chunks.

• If this is a repetitive process, such as removing records older than a month, then PARTITION BY RANGE can be very beneficial.

Both of the last two cases are covered here .

Generally you "cannot tune your way out of a performance problem"; however, I see a thing or two that is not properly tuned:

64GB of RAM with a 5.5GB table and innodb_buffer_pool_size=850M

That setting can be raised to 70% of available RAM. But even 10G should be beneficial.

Re, your musings:

• Do not use a ramdisk.

• AWS might or might not help. They would have set the buffer_pool to a reasonable size, but there might be other issues.

• Galera's main use is for HA (High Availability) -- surviving the crash of a server with virtually no hiccups.

• A different filesystem would not "help appreciably".

• RAID5 with SSDs is arguably the best way to go; don't move away from it. I hope that includes a hardware RAID controller with Battery Backed Write Cache? It is better to let RAID handle the accesses than to manually play with the placement of tables (files).

Queries

Desirable indexes:

xu5gc_finder_tokens_aggregate:  INDEX(term_id)   -- for insert..select #1 & Update

xu5gc_finder_tokens:  INDEX(context, term)  -- in that order.  
xu5gc_finder_terms:   INDEX(term)

xu5gc_finder_terms:   INDEX(term_id)

xu5gc_finder_terms:  INDEX(phase, soundex,  -- first, in either order
                           term, term_id)   -- in either order (for 'covering')

(It might be helpful to see SHOW CREATE TABLE.)

Answer 3

Jan 13, 2019 02:20 Alex, you are correct, Open Files of 1024 are too low for your level of activity.

Suggestion for your ulimit -a to consider please from Linux command prompt,

ulimit -n 16384 # from 1024 to enable more OS file handles to be active.

The above is dynamic with Linux OS. Stop/Start services would have access to the handles.

To make this persistent across OS shutdown/restart, review this url for similar OS instructions

These instructions set 500000 for the file-max, please set your capacity at 16384 for now.

ulimit please set to 16384, which will allow MySQL to use 1206 requested

and have spares for other apps

https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/

Looking forward to your contact by Skype. My Skype ID is [email protected] Thanks for your most recent comment.

Answer 4

Rate Per Second=RPS Suggestions to consider for your my.cnf [mysqld] section

innodb_io_capacity=1900  # from 200 to enable SSD additional IOPS
read_rnd_buffer_size=512K  # ~4M to reduce handler_read_rnd_next RPS of 7,601
innodb_lru_scan_depth=100  # from 1024 to reduce CPU busy cycles 90% for this function
innodb_log_buffer_size=16M  # from 8M to support ~ 30 minutes in buffer before WD
table_definition_cache=500  # from 500 to reduce opened_table_definitions count

Looking forward to your posting of ulimit -a results. It is likely your OS is limiting the number of Open File handles available to MySQL for table management.

For additional suggestions, please view my profile, Network profile has contact information.

Answer 5

Firstly RAID5 is very poor for writes, and delete does a lot of them.

When you do a big delete it all gets written to log first so recovery can happen if there's a crash. If your logs are on your RAID 5 array, move them somewhere else - preferably a Raid 1 array (mirrored), otherwise you have massive contention.

It may be worth dropping some indexes and rebuilding them after, as it reads the data to be deleted, writes it all to log, then goes thru the log doing the data deletes row by row including any indexes, so if you've got more indexes it will take longer.

Answer 6

HEADS UP: This limits you to a tiny 0.5GB of InnoDB data and indexes:

"innodb_data_file_path": "ibdata1:10M:autoextend:max:500M"

---

Tips on the slow queries. This is a wimpy list of suggestions, CREATE TABLE and the table sizes might help.

• Is the INSERT...SELECT being done repeatedly? Could it be done incrementally? As in, copy today's data today, but not again tomorrow?

• xu5gc_finder_tokens_aggregate -- Consider pre-computing SOUNDEX(term) into another column, rather than processing it in the SELECT.

• The SELECT ... FROM xu5gc_finder_tokens_aggregate Needs to read the entire table, build a temp, then GROUP BY. Rethink whether you need that much output. Wait! Why is there a GROUP BY? There are no aggregates??

• WHERE t.soundex = SOUNDEX('2018 2594-1') AND t.phrase = 1 needs INDEX(phrase, soundex) (in either order).

• JOIN t2 ON t2.term = t1.term ... WHERE t2.context = 2 may work better as JOIN t2 ON t2.term = t1.term AND t2.context = t1.context since the context is the same. Then, INDEX(term, context) (in either order) would probably be beneficial.

• Are term and term_id 1:1? The Grouping and Joining seem to be not consistent on which to use when. It may be worthwhile to get rid of term_id and promote term to be the PRIMARY KEY?