1

Our scientific application needs to store and query fundamental parameters for a number of molecules. There are 2 to 28 million rows per molecule, but the number of molecules is expected to stay small (currently 4). Here's the table we're using:

CREATE TABLE `mol_trans` (
  `species_id` int(11) DEFAULT NULL,
  `wl_vac` double DEFAULT NULL,
  `upper_id` int(11) DEFAULT NULL,
  `lower_id` int(11) DEFAULT NULL,
  `prob` double DEFAULT NULL,
  `flag` tinyint(4) DEFAULT NULL,
  KEY `spid_flag_wl` (`species_id`,`flag`,`wl_vac`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
 PARTITION BY LIST (`species_id`)
(PARTITION `CaO` VALUES IN (6115) ENGINE = InnoDB,
 PARTITION `CN3` VALUES IN (6121) ENGINE = InnoDB,
 PARTITION `CN2` VALUES IN (6119) ENGINE = InnoDB,
 PARTITION `AlO` VALUES IN (6109) ENGINE = InnoDB)

(The partitions are here to make it easier to drop a whole molecule if needed, which would otherwise be a painful large DELETE. The performance problems were there before the partitions had been added.)

I'll be using 10.3.39-MariaDB-0+deb10u1 (connecting via UNIX domain socket using the command line client) for tests, but we've been seeing the same problems on MySQL 5.6 and MariaDB 10.11 on Windows 10.

The following query takes approximately 45 seconds on my machine, measured using time echo "$QUERY" | mysql $DATABASE >/dev/null:

select
  mtr.prob,
  mtr.lower_id,
  mtr.upper_id
from
  mol_trans mtr
where (
  mtr.species_id=6115
  and mtr.wl_vac > 766.0
  and mtr.wl_vac < 883.0
  and mtr.flag = 1
)
order by mtr.wl_vac;

The query produces 3024559 rows and seems to use an index:

+------+-------------+-------+------+---------------+--------------+---------+-------------+----------+-------------+
| id   | select_type | table | type | possible_keys | key          | key_len | ref         | rows     | Extra       |
+------+-------------+-------+------+---------------+--------------+---------+-------------+----------+-------------+
|    1 | SIMPLE      | mtr   | ref  | spid_flag_wl  | spid_flag_wl | 7       | const,const | 14158123 | Using where |
+------+-------------+-------+------+---------------+--------------+---------+-------------+----------+-------------+

I have tried converting the database to PostgreSQL, and while I don't fully trust the conversion results, the same query returns more than 3 million rows in less than 6 seconds on the same machine. But MySQL/MariaDB C connector API is what our application is already written with, and we'd like to keep the convenience of updating the database in a centralised manner.

The question: How do I speed up MySQL so that the query takes less time to complete, at least on the local server, closer to PostgreSQL's 6 seconds? I have tried enabling 255-byte histograms and running ANALYZE TABLE mol_trans PERSISTENT FOR ALL, but that made it worse (up to 2 minutes to run the same query). Surprisingly, OPTIMIZE TABLE mol_trans got the query time back to ~40 seconds (by recreating the table). Additionally, if I do set profiling=on and run ANALYZE with the query, most of the time is shown as being spent in sending data:

+------------------------+-----------+
| Status                 | Duration  |
+------------------------+-----------+
| Starting               |  0.000078 |
| Checking permissions   |  0.000005 |
| Opening tables         |  0.000021 |
| After opening tables   |  0.000004 |
| System lock            |  0.000004 |
| Table lock             |  0.000004 |
| Init                   |  0.000028 |
| Optimizing             |  0.000027 |
| Statistics             |  0.000088 |
| Preparing              |  0.000021 |
| Sorting result         |  0.000008 |
| Executing              |  0.000003 |
| Sending data           | 40.324591 |
| End of update loop     |  0.000032 |
| Query end              |  0.000002 |
| Commit                 |  0.000003 |
| Closing tables         |  0.000003 |
| Unlocking tables       |  0.000001 |
| Closing tables         |  0.000008 |
| Starting cleanup       |  0.000002 |
| Freeing items          |  0.000006 |
| Updating status        |  0.000011 |
| Reset for next command |  0.000002 |
+------------------------+-----------+

When talking to a remote server, I can see the query results appearing in Wireshark as text quite soon after the query is submitted (the terminal stays silent until the whole result has been received). Is there a way to speed up the text formatting process? MariaDB documentation suggests that prepared statements may result in the use of binary protocol, which is presumably faster to serialize. Or is it? I compiled a test program that downloads the query results using mysql_store_result and mysql_stmt_fetch, and it looks like the two methods work approximately as fast.

Do I have any other options?

6
  • 1
    Hi, and welcome to dba.se! SQLite models its syntax on PostgreSQL - this is a long shot, but if you can easily migrate to SQLite, you could see what happens on a PostgreSQL server?
    – Vérace
    Oct 27, 2023 at 3:48
  • There's no way to beat the latency of an embedded database with a remote one. Based on your profiling, if the majority of the time is spent writing data to the UNIX domain socket, you're essentially benchmarking the overhead of the UNIX domain sockets compared to a memory copy in userspace. If you need a remote database and can't use an embedded one, you can't compare it to SQLite and expect the results to be comparable. The MariaDB server does have an embedded version of it that supports SQLite-like behavior but this limits it to a single application.
    – markusjm
    Oct 27, 2023 at 5:19
  • @Vérace, thanks for the idea! I had previously made use of SQLite's forgiveness about column types, but converting the output of sqlite3 $DB .dump into something that psql would accept was reasonably simple. time echo "$query" | psql >/dev/null says 0m5,252s. The row count is off by one, but it's still 3024558 rows.
    – aitap
    Oct 27, 2023 at 8:02
  • @markusjm: I hope the comparison with PostgreSQL is more fair. My conversion wasn't flawless, but experiments with local psql show a significant improvement in transfer speed over the UNIX domain socket. Presented as text, the query output is something like 60 megabytes. At local network speeds, I would like to be able to transfer it in 6 seconds or less.
    – aitap
    Oct 27, 2023 at 8:21
  • Doesn't PostgreSQL take 5 seconds and 252 ms? Or have I misread what you've written in your reply to me? Why don't you take the output as a file on the server rather than transmit it across the network?
    – Vérace
    Oct 27, 2023 at 9:35

2 Answers 2

1

I believe you found the answer yourself to the slowness:

What did improve performance was creating the id column with the type INT UNSIGNED AUTO_INCREMENT and setting it to be the primary key. With the index spid_flag_wl(species_id, flag, wl_vac) recreated, the EXPLAIN output now looks a bit differently:

id: 1 select_type: SIMPLE table: mtr type: range possible_keys: spid_flag_wl key: spid_flag_wl key_len: 16 ref: NULL rows: 5487882 Extra: Using index condition

...and I get my 3024559 rows in slightly more than 6 seconds.

In the comments you mentioned:

PostgreSQL CLI over UNIX domain socket does indeed take less than 6 seconds

The 6 seconds you measured against PostgreSQL is still likely to be the overhead of the kernel transporting the data over a UNIX domain socket from one process to another. An embedded database like SQLite is inherently faster than a daemon process.

2
  • 1
    I care less about the 4 second UNIX domain socket overhead between PostgreSQL and SQLite and more about the 1.5 minute slowdown because MariaDB decides to use a prefix of an index instead of the full index. Having said that, I do agree that the protocol overhead exists and that it's not reasonable to expect full embedded database performance from a client-server envine.
    – aitap
    Oct 31, 2023 at 13:20
  • 1
    Yes, the bad query plans is indeed a very interesing one. I think that if you do file a bug report for the optimizer, it would be good to include it in your answer so that when it gets fixed, there's a reference to it.
    – markusjm
    Oct 31, 2023 at 14:23
1

"Sending data" is a red herring

It can be visible in sql/sql_select.cc that JOIN::exec_inner() sets stage_sending_data before calling do_select() that performs a lot of additional work besides having the result set serialised and sent to the user. So even if the query spends a lot of time "sending data", the problem might still be due to the way the query is planned and executed, not protocol overhead.

There's more than one way to use an index

The following two queries differ only in the FORCE INDEX statement:

Without FORCE INDEX With FORCE INDEX
analyze select
mtr.prob, mtr.lower_id, mtr.upper_id
from mol_trans mtr
where (
mtr.species_id=6115
and mtr.wl_vac > 766.0
and mtr.wl_vac < 883.0
and mtr.flag = 1
)
order by mtr.wl_vac
analyze select
mtr.prob, mtr.lower_id, mtr.upper_id
from mol_trans mtr
force index(spid_flag_wl)
where (
mtr.species_id=6115
and mtr.wl_vac > 766.0
and mtr.wl_vac < 883.0
and mtr.flag = 1
)
order by mtr.wl_vac
           id: 1
select_type: SIMPLE
table: mtr
type: ref
possible_keys: spid_flag_wl
key: spid_flag_wl
key_len: 7
ref: const,const
rows: 14025100
r_rows: 28417908.00
filtered: 100.00
r_filtered: 10.64
Extra: Using where
           id: 1
select_type: SIMPLE
table: mtr
type: range
possible_keys: spid_flag_wl
key: spid_flag_wl
key_len: 16
ref: NULL
rows: 6260712
r_rows: 3024559.00
filtered: 100.00
r_filtered: 100.00
Extra: Using where
1 min 48,719 sec 11,086 sec

By itself, the query optimiser seems to prefer to use the prefix of the spid_flag_wl index (see: type=ref and key_len=7) and then filter the rows by WHERE. With FORCE INDEX, the whole index is used (key_len=16, which seems to correspond to two ints followed by a double; also, type=range). Using only the prefix of the index, the query planner expects to find 14 million rows, but finds twice as many and has to extract only ~10% (r_filtered) of them. With the full index, not only the query planner finds less rows than estimated, but all of them are applicable.

(See EXPLAIN and ANALYZE on how to interpret the output of ANALYZE SELECT.)

Use the FORCE, Luke

Unfortunately, no amount of ANALYZE TABLE (in my experience) helped MariaDB to choose to use the full index automatically, based on the distribution of the key values. But since the index was specifically designed for this query, there is no harm in using FORCE INDEX to guide the query optimiser. This solution improves the query performance on everything I've tried, ranging from MySQL 5.6 on Windows 10 to MariaDB-10.3.39-0+deb10u1 and 11.1.2 on GNU/Linux.

This has been reported as bug MDEV-32646.

Increasing the pH suggested (Who needs ACID?)

MyISAM is a storage engine optimized for environments with heavy read operations, which is exactly what this scientific application does. Writes happen rarely, as a maintenance procedure, and the table is relatively easy to create from scratch if it does get damaged. Following the excellent advice by Vassilis Virvilis, I tried recreating the table with ENGINE=MyISAM. I got the query results in 3,528s, which beats all other results I've got with client-server database engines.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.