0

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.

  • What did you see in the slow query log? – Michael Hampton Jan 26 '18 at 22:29
  • Adding to post. – Marc Jan 26 '18 at 23:05
  • 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 8:32
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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