Question Long running query

[Home Alone]

I'm testing this on our staging site. There are 300,000 users, 68M posts and 4M threads. This is the query:

# Time: 210227 15:51:32
# User@Host: root[root] @ localhost []
# Thread_id: 167311  Schema: newlsa  QC_hit: No
# Query_time: 4411.449023  Lock_time: 0.000070  Rows_sent: 25  Rows_examined: 309467094
# Rows_affected: 0  Bytes_sent: 60361
SET timestamp=1614459092;
SELECT `xf_dbtech_credits_transaction`.*, `xf_dbtech_credits_event_Event_1`.*, `xf_user_TargetUser_2`.*, `xf_user_SourceUser_3`.*, `xf_user_Owner_4`.*
                        FROM `xf_dbtech_credits_transaction` FORCE INDEX (`transaction_date`)
                        INNER JOIN `xf_dbtech_credits_event` AS `xf_dbtech_credits_event_Event_1` ON (`xf_dbtech_credits_event_Event_1`.`event_id` = `xf_dbtech_credits_transaction`.`event_id`)
LEFT JOIN `xf_user` AS `xf_user_TargetUser_2` ON (`xf_user_TargetUser_2`.`user_id` = `xf_dbtech_credits_transaction`.`user_id`)
LEFT JOIN `xf_user` AS `xf_user_SourceUser_3` ON (`xf_user_SourceUser_3`.`user_id` = `xf_dbtech_credits_transaction`.`source_user_id`)
LEFT JOIN `xf_user` AS `xf_user_Owner_4` ON (`xf_user_Owner_4`.`user_id` = `xf_dbtech_credits_transaction`.`owner_id`)
                        WHERE ((`xf_dbtech_credits_transaction`.`transaction_state` IN ('visible', 'moderated'))) AND (`xf_dbtech_credits_event_Event_1`.`active` = 1 AND `xf_dbtech_credits_event_Event_1`.`display` = 1) AND (`xf_dbtech_credits_transaction`.`currency_id` IN (1))
                        ORDER BY `xf_dbtech_credits_transaction`.`dateline` DESC, `xf_dbtech_credits_transaction`.`transaction_id` DESC

LIMIT 25;

Any idea why this took so long?

 

[Fillip H.]

Can you please run the following query:

EXPLAIN SELECT `xf_dbtech_credits_transaction`.*, `xf_dbtech_credits_event_Event_1`.*, `xf_user_TargetUser_2`.*, `xf_user_SourceUser_3`.*, `xf_user_Owner_4`.*
                        FROM `xf_dbtech_credits_transaction` FORCE INDEX (`transaction_date`)
                        INNER JOIN `xf_dbtech_credits_event` AS `xf_dbtech_credits_event_Event_1` ON (`xf_dbtech_credits_event_Event_1`.`event_id` = `xf_dbtech_credits_transaction`.`event_id`)
LEFT JOIN `xf_user` AS `xf_user_TargetUser_2` ON (`xf_user_TargetUser_2`.`user_id` = `xf_dbtech_credits_transaction`.`user_id`)
LEFT JOIN `xf_user` AS `xf_user_SourceUser_3` ON (`xf_user_SourceUser_3`.`user_id` = `xf_dbtech_credits_transaction`.`source_user_id`)
LEFT JOIN `xf_user` AS `xf_user_Owner_4` ON (`xf_user_Owner_4`.`user_id` = `xf_dbtech_credits_transaction`.`owner_id`)
                        WHERE ((`xf_dbtech_credits_transaction`.`transaction_state` IN ('visible', 'moderated'))) AND (`xf_dbtech_credits_event_Event_1`.`active` = 1 AND `xf_dbtech_credits_event_Event_1`.`display` = 1) AND (`xf_dbtech_credits_transaction`.`currency_id` IN (1))
                        ORDER BY `xf_dbtech_credits_transaction`.`dateline` DESC, `xf_dbtech_credits_transaction`.`transaction_id` DESC

LIMIT 25;

And tell me the output?

 

[Home Alone]

---------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------------------------------+--------+-----------------------------+---------+---------+-----------------------------------------------------+----------+-------------------------------------------------+
| 1 | SIMPLE | xf_dbtech_credits_transaction | ALL | NULL | NULL | NULL | NULL | 60983487 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | xf_user_TargetUser_2 | eq_ref | PRIMARY | PRIMARY | 4 | newlsa.xf_dbtech_credits_transaction.user_id | 1 | |
| 1 | SIMPLE | xf_user_SourceUser_3 | eq_ref | PRIMARY | PRIMARY | 4 | newlsa.xf_dbtech_credits_transaction.source_user_id | 1 | |
| 1 | SIMPLE | xf_user_Owner_4 | eq_ref | PRIMARY | PRIMARY | 4 | newlsa.xf_dbtech_credits_transaction.owner_id | 1 | |
| 1 | SIMPLE | xf_dbtech_credits_event_Event_1 | ALL | PRIMARY,transaction_display | NULL | NULL | NULL | 3 | Using where; Using join buffer (flat, BNL join) |
+------+-------------+---------------------------------+--------+-----------------------------+---------+---------+-----------------------------------------------------+----------+-------------------------------------------------+
5 rows in set (0.003 sec)

 

[Fillip H.]

Alright, so the problem is that your server is ignoring the FORCE INDEX command. Can you double-check that the transaction_date index exists on the xf_dbtech_credits_transaction table?

If it does, then you'll need to contact your server administrator for assistance, as your site is the only one I've seen that ignores that index, so it's likely a server configuration issue.

 

[Home Alone]

Does this addon work with MariaDB 10.4?

 

[Fillip H.]

I've tested this on MariaDB 10.5 locally and the FORCE INDEX works, and it also works on MariaDB 10.3.

If the index exists in your table, then it's possible MariaDB 10.4 is the problem. That version has caused a slew of issues, there were quite a number of threads about it over @ XenForo.com.

 

[Home Alone]

The transaction_date index exists on the xf_dbtech_credits_purchase_transaction

 

[Fillip H.]

In that case, please contact your server administrator to find out why it's not applying for you in that query, as this is likely a server side issue.

 

[Home Alone]

Per MariaDB:

FORCE_INDEX works by only considering the given indexes (like with USE_INDEX) but in addition it tells the optimizer to regard a table scan as something very expensive. However if none of the 'forced' indexes can be used, then a table scan will be used anyway.

So apparently none of the forced indexes could be used. This table has 60 million entries. Is that a problem?

 

[Fillip H.]

Yes, scanning a table with 60 million entries because it decides to not use the index is a problem.

Like I said, you will need the assistance of your server administrator because I have no way to assist you further.

If they are able to reconstruct the query in such a way that it uses the index or in some other way avoids a full table scan, I can see if those changes can be added to the core.

Really, though, that person will be much better equipped to handle this than I am, because I am not a server administrator so my knowledge of ultra-large databases is limited.

 

[Home Alone]

But this has nothing to do with the server. MariaDB 10.4 has been installed and in no way altered. So if Force Index isn't being used it's because MariaDB doesn't think any of the forced indexes CAN be used.

Is this software big board friendly? The xf_dbtech_credits_transaction has 60 million rows and we haven't even used the software yet. That's just from installing. It's going to grow exponentially when 200,000 active users begin using the software. Is this a table that can be truncated periodically?

 

[Fillip H.]

I'm in the process of creating fake records in the transaction table in order to test the scalability of this. Here's the current progress:



Unless you're telling me my MacBook Pro running MariaDB 10.5 in a Docker container is more powerful than your database server, or unless it's going to suddenly scale terribly when I add 10x more rows, I refer back to my previous posts.

 

[Home Alone]

I truncated that table and now the query isn't slow anymore. It appears that the bigger that table gets the slower the query.

 

[Home Alone]

I also noticed that someone reported this query as a bug, but it was closed with no reponse. Did that person ever resolve their issue? Are there any forums as large as ours running this software?

 

[Fillip H.]

I have to confess, it's getting a little bit frustrating to constantly talk past each other like this. Is there a reason why you are completely ignoring my requests to involve your server administrator?

I also noticed that someone reported this query as a bug, but it was closed with no reponse. Did that person ever resolve their issue? Are there any forums as large as ours running this software?

It was closed with no response because it was a duplicate and they re-posted their issue in the Private sub-forum.



They reacted with a "Like" and then never responded to the ticket, so it auto-closed 14 days later. I presume their server admin resolved their issue since they have not been back to report any further issues.

 

[Home Alone]

I am the server administrator. Been administering servers for 20 years (vervehosting.com). That's why I know MariaDB is not the issue.

 

[Fillip H.]

I see, well in that case we're at an impasse, because I've added another 1m rows and query time has not changed at all, the forced index is also still in play.

Is it necessary for me to generate another 53m rows in order for you to be convinced?

 

[Home Alone]

I'm searching for others experiencing this problem in 10.4 and there are others. One explanation:

The optimizer may decide that a table scan is faster than bothering with the INDEX. This usually happens when it appears that more than about 20% of the table needs to be touched. (Using the index implies bouncing back and forth between the index's BTree and the data's BTree.) If it shuns the index, don't worry; it is probably a wise decision.

There is also a thread at https://community.centminmod.com/threads/anyone-using-mariadb-10-4-8-on-live-and-busy-site.18327/ explaining issues with MariaDB 10.4 and Xenforo.

I'm asking these questions because I used this mod on VB 3 and it became so cumbersome as the site grew that I didn't install it when I migrated to Xenforo. I'm preparing an upgrade to Xenforo 2.2 and I don't want to install this mod if it's going to cause the same problems it did on VB3.

 

[Home Alone]

And there's really no need for you to get so defensive. I'm a customer asking questions.

 

[Fillip H.]

And there's really no need for you to get so defensive. I'm a customer asking questions.

I'm sure you can understand my frustration when you repeatedly dismissed the notion that MariaDB 10.4 could be the cause of the problem.

I'll continue to generate rows until I hit 60m, then I'll post back whether query times have changed.