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
"innodb_data_file_path": "ibdata1:10M:autoextend:max:500M"