Invision Community 4: SEO, prepare for v5 and dormant account notifications By Matt Monday at 02:04 PM
Ghust Posted July 28 Posted July 28 (edited) Hey, last weeks I've been having complaints from our users that our (self hosted) IP is slower than before. First I tried tweaking php-fpm settings, but I never really saw enough concurrent connections to really justify upping the max_clients to something absurd. Then I saw that the database seemed to be having difficulties. I enabled slow query logging and found the following query that was really slow (> 5seconds) for about every user. MariaDB [mysql]> select * from slow_log limit 10; +----------------------------+--------------------------------------+-----------------+-----------------+-----------+---------------+-------+----------------+-----------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------------+ | start_time | user_host | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text | thread_id | rows_affected | +----------------------------+--------------------------------------+-----------------+-----------------+-----------+---------------+-------+----------------+-----------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------------+ | 2024-07-28 08:38:04.975686 | forumrsca[forumrsca] @ [172.19.0.8] | 00:00:07.687649 | 00:00:00.000022 | 1 | 2595147 | forum | 0 | 0 | 1 | /*forum::forumrsca::IPS\Content\_Item::_comments:2109*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `forums_posts` LEFT JOIN `core_members` AS `author` ON author.member_id = forums_posts.author_id LEFT JOIN `core_pfields_content` AS `author_pfields` ON author_pfields.member_id=author.member_id WHERE forums_posts.topic_id=8215 AND forums_posts.queued!=-2 AND forums_posts.queued!=-3 ORDER BY post_date ASC LIMIT 0,1 | 3542766 | When running an EXPLAIN i see the following: MariaDB [forum]> EXPLAIN SELECT forums_posts.*, author.*, author_pfields.* FROM forums_posts LEFT JOIN core_members AS author ON author.member_id = forums_posts.author_id LEFT JOIN core_pfields_content AS author_pfields ON author_pfields.member_id = author.member_id WHERE forums_posts.topic_id = 8215 AND forums_posts.queued != -2 AND forums_posts.queued != -3 ORDER BY post_date ASC LIMIT 0, 1; +------+-------------+----------------+--------+-------------------------------------------------------+-----------+---------+------------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------------+--------+-------------------------------------------------------+-----------+---------+------------------------------+------+-------------+ | 1 | SIMPLE | forums_posts | index | queued,topic_id,first_post,idx_topic_queued_post_date | post_date | 5 | NULL | 835 | Using where | | 1 | SIMPLE | author | eq_ref | PRIMARY,mgroup | PRIMARY | 8 | forum.forums_posts.author_id | 1 | Using where | | 1 | SIMPLE | author_pfields | eq_ref | PRIMARY | PRIMARY | 3 | forum.author.member_id | 1 | Using where | +------+-------------+----------------+--------+-------------------------------------------------------+-----------+---------+------------------------------+------+-------------+ 3 rows in set (0.51 sec) It's using post_date here, which isn't really ideal as there's no indexing on topic_id I then tried creating this index: CREATE INDEX idx_topic_id_queued_post_date ON forums_posts(topic_id, queued, post_date); But it's not being used in this query. And admin CP doesn't agree with my fix 😄 How could I fix this issue? Is this a known one? I guess I could drop the post_date index and recreate it with the topic_id and queued instead... But hey, I'd rather double check 🙂 Added info: Invision Community v4.7.17 Edited July 28 by Ghust
Ghust Posted July 28 Author Posted July 28 (edited) Addendum: I dropped the post_date index for now, it seems to be running better. But I'm now a bit scared that future updates will break, so if I have to recreate post_date instead, let me know! Edited July 28 by Ghust
Marc Posted July 29 Posted July 29 We would not advise on removing indexes, no. They will be simply readded on the next update. I will however pass this along to our developers
Ghust Posted July 29 Author Posted July 29 Hey Marc, Assuming I drop my new index and recreate it as post_date, I should be fine right? Unless something has to be changed on a specific index (such as implementing the topic_id 😄 ) I doubt an upgrade will drop and recreate all indexes, would take ages
Solution Marc Posted July 29 Solution Posted July 29 Yes, that would be correct. You may find its not an issue on the next release though. Always test first
Recommended Posts