Serious performance issue

  • net

    (@krstarica)


    2 years, 9 months ago

    New Relic shows that the slowest plugin on several WordPress instances is Simple History.

    For example, the following SQL query takes almost 5 seconds on high performance server:

    
# Query_time: 4.892989  Lock_time: 0.000226  Rows_sent: 20  Rows_examined: 1392818
# Rows_affected: 0  Bytes_sent: 4616
SET timestamp=1656344904;
/*NO_SELECT_FOUND_ROWS*/
SELECT
        SQL_CALC_FOUND_ROWS
        h.id,
        h.logger,
        h.level,
        h.date,
        h.message,
        h.initiator,
        h.occasionsID,
        count(t.repeated) AS subsequentOccasions,
        t.rep,
        t.repeated,
        t.occasionsIDType,
        c1.value AS context_message_key

FROM wp_simple_history AS h

LEFT OUTER JOIN wp_simple_history_contexts AS c1 ON (c1.history_id = h.id AND c1.key = "_message_key")

INNER JOIN (
        SELECT
                id,
                IF(@a=occasionsID,@counter:=@counter+1,@counter:=1) AS rep,
                IF(@counter=1,@groupby:=@groupby+1,@groupby) AS repeated,
                @a:=occasionsID occasionsIDType
        FROM wp_simple_history AS h2

        # First/inner where
        WHERE
                1 = 1 AND logger IN ("SimpleCommentsLogger", "SimpleCoreUpdatesLogger", "SimpleExportLogger", "SimpleLogger", "SimpleMediaLogger", "SimpleMenuLogger", "SimpleOptionsLogger", "SimplePluginLogger", "SimplePostLogger", "SimpleThemeLogger", "SimpleUserLogger", "SimpleCategoriesLogger", "AvailableUpdatesLogger", "FileEditsLogger", "SH_Privacy_Logger", "SH_Translations_Logger", "SH_Jetpack_Logger", "PluginUserSwitchingLogger", "PluginWPCrontrolLogger", "PluginEnableMediaReplaceLogger", "Plugin_UltimateMembers_Logger", "Plugin_LimitLoginAttempts", "Plugin_Redirection", "Plugin_DuplicatePost", "Plugin_ACF", "Plugin_BeaverBuilder")

# lastdays
AND date >= DATE(NOW()) - INTERVAL 1 DAY

        ORDER BY id DESC, date DESC
) AS t ON t.id = h.id

WHERE
        # Outer/Second where
        1 = 1

GROUP BY repeated
ORDER BY id DESC, date DESC
LIMIT 0, 20;

    Is it possible to speed it up? Many thanks.

Viewing 6 replies - 1 through 6 (of 6 total)
  • Plugin Author P?r Thernstr?m

    (@eskapism)

    Thanks for the report. I have plans to optimize the query a bit further. Could you give me some more info about your setup? For example PHP version, MySQL version, WordPress version, and the number of events in the database?

    Some people have reported similar issues but since I never have had the same problem myself so it’s difficult to debug/find the cause.

    Thread Starter net

    (@krstarica)

    Thanks for getting back to me.

    Here is the information you’ve requested:

    PHP 7.4
    MariaDB 10.6.8
    WordPress 6.0.2
    wp_simple_history records: 20920
    wp_simple_history_contexts records: 260552

    Thread Starter net

    (@krstarica)

    This issue should not be marked as resolved. The query is still extremely slow.

    Plugin Author P?r Thernstr?m

    (@eskapism)

    So far I’m still unable to reproduce this. I have a local test site with WP 6.1.1 and MariaDB 10.5.13 running via Docker. wp_simple_history has 104902 rows and wp_simple_history_context has 1446052 rows and the query you posted takes around 20 ms to run.

    I will try to get a version with MariaDB 10.6.8 too and see if that changes anything.

    Any chance you could run the query with explain so we can see if it uses any keys or if anything is off there. Also, any chance that the site is moved or has been restored from a backup or similar and maybe some database indexes got lost during that transition?

    Thread Starter net

    (@krstarica)

    +------+-------------+------------+-------+-----------------+------------+---------+----------------+-------+---------------------------------------------------------------------+
| id   | select_type | table      | type  | possible_keys   | key        | key_len | ref            | rows  | Extra                                                               |
+------+-------------+------------+-------+-----------------+------------+---------+----------------+-------+---------------------------------------------------------------------+
|    1 | PRIMARY     | h          | ALL   | PRIMARY         | NULL       | NULL    | NULL           | 23232 | Using temporary; Using filesort                                     |
|    1 | PRIMARY     | c1         | ref   | history_id,key  | history_id | 8       | h.id           | 6     | Using where                                                         |
|    1 | PRIMARY     | <derived2> | ref   | key0            | key0       | 8       | h.id           | 10    |                                                                     |
|    2 | DERIVED     | h2         | range | date,loggerdate | date       | 5       | NULL           | 1711  | Using index condition; Using where; Using temporary; Using filesort |
+------+-------------+------------+-------+-----------------+------------+---------+----------------+-------+---------------------------------------------------------------------+

    wp_simple_history has 25692 records
    wp_simple_history_contexts has 327438 records

    Thread Starter net

    (@krstarica)

    @eskapism can you address this issue, also? Thanks.

Viewing 6 replies - 1 through 6 (of 6 total)
  • The topic ‘Serious performance issue’ is closed to new replies.