Mariadb tuning - slow writes

Question

Background: I have a a website I am migrating from a stand alone MariaDB server to a MariaDB galera cluster. Part of this had me convert the tables from a large number of MyISAM to InnoDB. For what it is worth, the website is a large joomla installation.

The front end end side of the website is performing fine. No issues there. The admin section is painfully slow to save anything - 25-30 seconds from the time you hit save until it finishes.

Setup 3 node galera setup - 12 core Intel(R) Xeon(R) CPU E5-1650 v4 @ 3.60GHz 64GB w SSD on Raid

HA Proxy for load balancing

/etc/my.cnf

[mysqld]
innodb_buffer_pool_siz=32Gb
innodb_log_file_size = 2Gb
innodb_flush_method=O_DIRECT
max_connections=750
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=2Mb
query_cache_size=0
skip_name_resolve
sync-binlog=0

Right now I have only a single development site hooked up so I can measure what's going on reliably. Here is what I noticed with waits when a single save was performed:

Prior to save:

MariaDB [(none)]> show global status like "%waits%";
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_log_waits              | 0     |
| Innodb_mutex_os_waits         | 4     |
| Innodb_mutex_spin_waits       | 4     |
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_waits         | 0     |
| Innodb_s_lock_os_waits        | 9     |
| Innodb_s_lock_spin_waits      | 11    |
| Innodb_x_lock_os_waits        | 1     |
| Innodb_x_lock_spin_waits      | 0     |
| Tc_log_page_waits             | 0     |
+-------------------------------+-------+
10 rows in set (0.01 sec)

After Save:

MariaDB [(none)]> show global status like "%waits%";
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_log_waits              | 0     |
| Innodb_mutex_os_waits         | 177   |
| Innodb_mutex_spin_waits       | 1580  |
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_waits         | 0     |
| Innodb_s_lock_os_waits        | 164   |
| Innodb_s_lock_spin_waits      | 687   |
| Innodb_x_lock_os_waits        | 656   |
| Innodb_x_lock_spin_waits      | 905   |
| Tc_log_page_waits             | 0     |
+-------------------------------+-------+

I know there is more overhead with innodb than myisam but I thought disabling sync-binlog and setting innodb_flush_log_at_trx_commit to 2 would help but what I am actually seeing is about a 1000% degradation in write performance.

Just looking for some guidance on what to check, what might be wrong. I am not sure if that os_waits count is high on a single save in an application, it seems like it is to me, but I don't really follow what would cause it or how to fix it.

After setting the slow query log to 2s, this is the only thing it caught while trying to save:

# Time: 180126 18:03:38
# User@Host: dev[dev] @  [192.168.10.200]
# Thread_id: 136  Schema: dev  QC_hit: No
# Query_time: 6.306918  Lock_time: 0.000000  Rows_sent: 0  Rows_examined: 109438
# Rows_affected: 82677
use dev;
SET timestamp=1517007818;
UPDATE jos_assets
SET lft = lft + 2
WHERE lft > 337711;
# Time: 180126 18:03:48
# User@Host: dev[dev] @  [192.168.10.200]
# Thread_id: 136  Schema: dev  QC_hit: No
# Query_time: 9.985669  Lock_time: 0.000000  Rows_sent: 0  Rows_examined: 109438
# Rows_affected: 82683
SET timestamp=1517007828;
UPDATE jos_assets
SET rgt = rgt + 2
WHERE rgt >= 337711;

Thx in advance.

Try running EXPLAIN on the slow query shown there. It's certainly doing a lot of work in a relatively short time. — Simon Greenwood, Jan 27 '18 at 08:32

score 0 · Answer 1 · answered Feb 05 '18 at 14:36

This problem turned out to be very Joomla specific and has to do with inefficiencies with the _assets table. I guess I should have checked the slow query log earlier, but as you can see in the original post there were 2 queries that were updating the table in one "save" when posting a new article causing 82677 rows to be updated. Even in the speediest deployment this will take time.

Why Joomla needs to do this every time a post is saved I am not entirely sure, but some research suggested it was safe to remove all article related entries in that table which knocked it down to a total of about 4000 rows. Much faster now.

For reference if you have a Joomla site that is slow after converting to Innodb, this is the page I found some info on the asset table:

https://www.itoctopus.com/creating-new-articles-on-your-joomla-website-is-taking-a-long-time-clean-your-assets-table

Mariadb tuning - slow writes

1 Answers1