Thank you for replying so quickly! On Sun, 29 Oct 2023 16:11:28 +0100 Antony Stone via discuss <discuss@lists.mariadb.org> wrote:
The first question I would ask about this is "what spec hardware are you running it on?"
1. How much RAM?
32 GB.
2. HDD or SSD?
SSD. To be more specific, ext4 on top of LVM on top of LUKS on top of an NVMe SSD. I can provide some measurements if needed.
3. CPU speed / cores?
It's an 8-core Ryzen 3900X. It dynamically speeds up to 4.5GHz, but the mysqld process only uses one core while the query is running, and the other ones idle at ~2GHz.
Also, you said "2 to 28 million rows per molecule, and 4 molecules", but what's the actual quantity of data (Gbytes used on disk)?
Here are the sizes of large (>100M) files under /var/lib/mysql: 1,4G ibdata1 469M mol_trans#P#AlO.ibd 2,7G mol_trans#P#CaO.ibd # <-- species_id = 6115 221M mol_trans#P#CN2.ibd 473M mol_trans#P#CN3.ibd There are other tables in the database, but none as large as mol_trans. -- Best regards, Ivan