Pending transaction resolver selects are taking way too long
Closed, ResolvedPublic
Actions

Assigned To

None

Authored By

	Ejegg
	Oct 3 2025, 3:56 PM

Description

Now that we've got the is_resolved column and are keeping rows around longer for use in audit recon, queries that used to resolve in a few ms are taking upwards of 5 sec to come back. This will make it impractical to resolve all the pending transactions

Example query

select * from pending where gateway = 'paypal_ec' and is_resolved = 0 and payment_method in ('cc','google','paypal') order by date asc limit 1

It appears to be using idx_pending_date

create index idx_pending_date
    on smashpig.pending (date);

and not idx_pending_date_gateway_resolved

create index idx_pending_date_gateway_resolved
    on smashpig.pending (date, gateway, is_resolved);

Perhaps we should add payment_method to the larger index?

Details

Related Changes in Gerrit:

	Subject	Repo	Branch	Lines +/-
	Update index for pending table	wikimedia/fundraising/SmashPig	master	+8 -5

Customize query in gerrit

Related Objects

Mentioned In: T415697: Fundraising Sprint A Priorities
T412831: Fundraising Sprint X Summary
T406292: Fundraising Sprint W 2025- Priorities

Event Timeline

Ejegg created this task.Oct 3 2025, 3:56 PM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptOct 3 2025, 3:56 PM

Change #1193480 had a related patch set uploaded (by Ejegg; author: Ejegg):

[wikimedia/fundraising/SmashPig@master] Update index for pending table

https://gerrit.wikimedia.org/r/1193480

gerritbot added a project: Patch-For-Review.Oct 3 2025, 7:06 PM

Tested adding this index on dev_smashpig db:

MariaDB [dev_smashpig]> select count(*) from pending;
+----------+
| count(*) |
+----------+
|   204516 |
+----------+
1 row in set (0.034 sec)

MariaDB [dev_smashpig]> create index idx_pending_date_gateway_resolved on dev_smashpig.pending (date, gateway, payment_method, is_resolved);
Query OK, 0 rows affected (0.307 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [dev_smashpig]> show index from pending;
+---------+------------+------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table   | Non_unique | Key_name                           | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+---------+------------+------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| pending |          0 | PRIMARY                            |            1 | id             | A         |      190602 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_date                   |            1 | date           | A         |      190602 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_date_gateway           |            1 | date           | A         |      190602 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_date_gateway           |            2 | gateway        | A         |      190602 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_order_id_gateway       |            1 | order_id       | A         |      190602 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_order_id_gateway       |            2 | gateway        | A         |      190602 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_gateway_txn_id_gateway |            1 | gateway_txn_id | A         |       13614 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_gateway_txn_id_gateway |            2 | gateway        | A         |       12706 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_date_gateway_resolved  |            1 | date           | A         |      190602 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_date_gateway_resolved  |            2 | gateway        | A         |      190602 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_date_gateway_resolved  |            3 | payment_method | A         |      190602 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_date_gateway_resolved  |            4 | is_resolved    | A         |      190602 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+---------+------------+------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
12 rows in set (0.001 sec)

Ejegg tested and it still wasn't using the desired index. Speculating that it may be due to the lower volume of data, we installed the index on frdb1006's replica of the smashpig db.

MariaDB [dev_smashpig]> select count(*) from smashpig.pending;
+----------+
| count(*) |
+----------+
|  3142217 |
+----------+
1 row in set (0.426 sec)

MariaDB [dev_smashpig]> drop index idx_pending_date_gateway_resolved on smashpig.pending;
Query OK, 0 rows affected (0.021 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [dev_smashpig]> create index idx_pending_date_gateway_resolved on smashpig.pending (date, gateway, payment_method, is_resolved);
Query OK, 0 rows affected (4.851 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [dev_smashpig]> show index from smashpig.pending;
+---------+------------+------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table   | Non_unique | Key_name                           | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+---------+------------+------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| pending |          0 | PRIMARY                            |            1 | id             | A         |     2904438 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_date                   |            1 | date           | A         |     2904438 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_order_id_gateway       |            1 | order_id       | A         |     2904438 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_order_id_gateway       |            2 | gateway        | A         |     2904438 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_gateway_txn_id_gateway |            1 | gateway_txn_id | A         |     2904438 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_gateway_txn_id_gateway |            2 | gateway        | A         |     2904438 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_date_gateway_resolved  |            1 | date           | A         |     2904438 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_date_gateway_resolved  |            2 | gateway        | A         |     2904438 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_date_gateway_resolved  |            3 | payment_method | A         |     2904438 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_date_gateway_resolved  |            4 | is_resolved    | A         |     2904438 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+---------+------------+------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
10 rows in set (0.001 sec)

Queries were still slow so we decided to rearrange the order of the columns in the index definition:

MariaDB [dev_smashpig]> drop index idx_pending_date_gateway_resolved on smashpig.pending; 
Query OK, 0 rows affected (0.009 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [dev_smashpig]> create index idx_pending_date_gateway_resolved on smashpig.pending (`gateway`, `payment_method`, `is_resolved`, `date`);
Query OK, 0 rows affected (8.963 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [dev_smashpig]> show index from smashpig.pending;
+---------+------------+------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table   | Non_unique | Key_name                           | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+---------+------------+------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| pending |          0 | PRIMARY                            |            1 | id             | A         |     2965568 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_date                   |            1 | date           | A         |     2965568 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_order_id_gateway       |            1 | order_id       | A         |     2965568 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_order_id_gateway       |            2 | gateway        | A         |     2965568 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_gateway_txn_id_gateway |            1 | gateway_txn_id | A         |     2965568 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_gateway_txn_id_gateway |            2 | gateway        | A         |     2965568 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_date_gateway_resolved  |            1 | gateway        | A         |           6 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_date_gateway_resolved  |            2 | payment_method | A         |          34 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_date_gateway_resolved  |            3 | is_resolved    | A         |          62 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_date_gateway_resolved  |            4 | date           | A         |     2965568 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+---------+------------+------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
10 rows in set (0.001 sec)

Cardinality has definitely changed with this iteration.

Adjusted the index one more time in testing to see if it would make a difference:

MariaDB [dev_smashpig]> drop index idx_pending_date_gateway_resolved on smashpig.pending;
Query OK, 0 rows affected (0.008 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [dev_smashpig]> create index idx_pending_date_gateway_resolved on smashpig.pending (`is_resolved`, `gateway`, `payment_method`, `date`);
Query OK, 0 rows affected (8.887 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [dev_smashpig]> show index from smashpig.pending;
+---------+------------+------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table   | Non_unique | Key_name                           | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+---------+------------+------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| pending |          0 | PRIMARY                            |            1 | id             | A         |     2954689 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_date                   |            1 | date           | A         |     2954689 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_order_id_gateway       |            1 | order_id       | A         |     2954689 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_order_id_gateway       |            2 | gateway        | A         |     2954689 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_gateway_txn_id_gateway |            1 | gateway_txn_id | A         |     2954689 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_gateway_txn_id_gateway |            2 | gateway        | A         |     2954689 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_date_gateway_resolved  |            1 | is_resolved    | A         |           2 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_date_gateway_resolved  |            2 | gateway        | A         |          14 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_date_gateway_resolved  |            3 | payment_method | A         |          62 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| pending |          1 | idx_pending_date_gateway_resolved  |            4 | date           | A         |     2954689 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+---------+------------+------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
10 rows in set (0.001 sec)

It adjusted the cardinality but the query engine was still selecting the idx_pending_date index and performing slower that preferred (~5 sec). By forcing the query to use idx_pending_date_gateway_resolved the query completes in ~.015 sec.

MariaDB [dev_smashpig]> select * from smashpig.pending where gateway = 'paypal_ec' and is_resolved = 0 and payment_method in ('cc','google','paypal') order by date asc limit 1;
+-----------+---------------------+-----------+-----------------+-------------+----------------+---------+----------------+-------------+
| id        | date                | gateway   | gateway_account | order_id    | gateway_txn_id | message | payment_method | is_resolved |
[ ... results removed ... ]
1 row in set (5.110 sec)

MariaDB [dev_smashpig]> select * from smashpig.pending force index (idx_pending_date_gateway_resolved) where gateway = 'paypal_ec' and is_resolved = 0 and payment_method in ('cc','google','paypal
') order by date asc limit 1;
    -> ;
+-----------+---------------------+-----------+-----------------+-------------+----------------+---------+----------------+-------------+
| id        | date                | gateway   | gateway_account | order_id    | gateway_txn_id | message | payment_method | is_resolved |
[ ... results removed ... ]
1 row in set (0.015 sec)

Dwisehaupt moved this task from Triage to In Progress on the fundraising-tech-ops board.Oct 3 2025, 10:43 PM

Ejegg moved this task from Backlog to Ready for Review on the Fundraising Tech - Chaos Crew board.Oct 6 2025, 7:29 PM

XenoRyet moved this task from Triage to Chaos Crew Backlog on the Fundraising-Backlog board.Oct 6 2025, 8:16 PM

jgleeson moved this task from Ready for Review to Being Reviewed on the Fundraising Tech - Chaos Crew board.Oct 7 2025, 12:11 PM

Change #1193480 merged by jenkins-bot:

[wikimedia/fundraising/SmashPig@master] Update index for pending table

https://gerrit.wikimedia.org/r/1193480

Maintenance_bot removed a project: Patch-For-Review.Oct 7 2025, 5:31 PM

XenoRyet moved this task from Being Reviewed to Done on the Fundraising Tech - Chaos Crew board.Dec 8 2025, 9:02 PM

XenoRyet closed this task as Resolved.Dec 15 2025, 8:45 PM

XenoRyet set Final Story Points to 2.

Damilare mentioned this in T406292: Fundraising Sprint W 2025- Priorities.Dec 16 2025, 2:54 PM