wavelog: Advanced logbook is very slow if it contains a lot of qsos
Hi,
first thanks for your awesome log software.
We are using wavelog in our clubstation. Recently we notices that the advanced logbook got really slow (5s with 25k qsos). Some
profiling revealed the culprit. It is the following query from application/models/Logbookadvanced_model.php
SELECT * FROM TABLE_HRD_CONTACTS_V01 qsos
INNER JOIN station_profile ON qsos.station_id=station_profile.station_id
LEFT OUTER JOIN satellite ON qsos.COL_SAT_NAME = satellite.name
LEFT OUTER JOIN dxcc_entities ON qsos.col_dxcc=dxcc_entities.adif
LEFT OUTER JOIN lotw_users ON qsos.col_call=lotw_users.callsign
LEFT OUTER JOIN (select count(*) as qslcount, qsoid from qsl_images group by qsoid) x on qsos.COL_PRIMARY_KEY = x.qsoid
WHERE station_profile.user_id = 3 AND qsos.station_id = '2'
ORDER BY qsos.COL_TIME_ON desc, qsos.COL_PRIMARY_KEY desc
LIMIT 250;
Profiling the query with mariadb revealed the following.
+------------------------+----------+
| Status | Duration |
+------------------------+----------+
| Starting | 0.000229 |
| checking permissions | 0.000010 |
| Opening tables | 0.000062 |
| After opening tables | 0.000015 |
| System lock | 0.000007 |
| table lock | 0.000010 |
| Opening tables | 0.000017 |
| After opening tables | 0.000003 |
| System lock | 0.000004 |
| table lock | 0.000575 |
| Unlocking tables | 0.000006 |
| closing tables | 0.000017 |
| init | 0.000280 |
| Optimizing | 0.000059 |
| Optimizing | 0.000007 |
| Statistics | 0.000041 |
| Preparing | 0.000020 |
| Creating tmp table | 0.000033 |
| Sorting result | 0.000028 |
| Statistics | 0.000330 |
| Preparing | 0.000050 |
| Creating tmp table | 0.000404 |
| Sorting result | 0.000073 |
| Executing | 0.000004 |
| Sending data | 0.066014 |
| Executing | 0.000005 |
| Sending data | 0.000038 |
| Creating sort index | 0.000051 |
| Removing tmp table | 0.000006 |
| Creating sort index | 4.602838 |
| Creating sort index | 0.073452 |
| Removing tmp table | 0.015294 |
| Creating sort index | 0.000026 |
| End of update loop | 0.000020 |
| Query end | 0.000003 |
| Commit | 0.000006 |
| closing tables | 0.000002 |
| Removing tmp table | 0.000006 |
| closing tables | 0.000002 |
| Unlocking tables | 0.000001 |
| closing tables | 0.000016 |
| Starting cleanup | 0.000003 |
| Freeing items | 0.000027 |
| Updating status | 0.000031 |
| Reset for next command | 0.000006 |
+------------------------+----------+
The normal logbook and the callsign search are still working normally.
Version Information We need some information about your versions. You can get all Information in the Admin > Debug Menu
- OS: Debian 12
- PHP: 8.2.18
- MySQL/MariaDB: 10.11.6-MariaDB-0+deb12u1
- Wavelog: 1.5.2
About this issue
- Original URL
- State: closed
- Created 2 years ago
- Comments: 15 (9 by maintainers)
@derFogel the reason I did not see the problem here, was that on my dev, I already had an index from when testing stuff earlier. Sorry about that, and thanks for reporting!
Yes you can
@derFogel seems like we found the issue. it’s a missing index. i fixed it at #401 - u can try it via two ways:
@derFogel can you provide a db-dump to us?! Furthermore I can offer you 1:1 support tomorrow. No guarantee to solve it instantly but to a start a deeper investigation?!
Hi,
Happy you like it, and thanks for the issue report. We did notice a slowdown, and a fix for that is in the dev branch. We will shortly make another release with these fixes. You can either try the dev branch, or wait till the release.
Andreas.