2

I have a table in a MariaDB database with ~700,000,000 rows. I have indexed some columns of it.

My question is whether my DB behaves "normaly" or not - because I have not had previous experience with such an amount of data.

Take a look at these queries and please let me know if you think this is normal response time:

SELECT count( DISTINCT(QualityLevel) ) FROM patient_records;
+---------------------------------+
| count( DISTINCT(QualityLevel) ) |
+---------------------------------+
|                          265595 |
+---------------------------------+
1 row in set (1.248 sec)

This I think is great response time. However, look below:

select count(QualityLevel) from patient_records where QualityLevel>10.14;
+---------------------+
| count(QualityLevel) |
+---------------------+
|           700756562 |
+---------------------+
1 row in set (3 min 10.324 sec)

Not so great (I assume?). And, more:

select count(recordID) from patient_records where QualityLevel>10.14 and snpID='.';
+-----------------+
| count(recordID) |
+-----------------+
|        56627747 |
+-----------------+
1 row in set (23 min 53.028 sec)

Quite slow this one, although both columns (QualityLevel and snpID) are indexed.

I will need to build a web interface for these queries, but it can't happen that it takes 25 minutes to execute. What am I missing here? Maybe DB partiotining would help (don't know anything about it, so any suggestion would be highly appreciated).

My my.cnf file:

[mysqld]
datadir=/home/ssd/mysql_datadir
tmpdir=/home/ssd/mysql_tmdir
socket=/home/ssd/mysql_datadir/mysql.sock
innodb_buffer_pool_size=4GB
query_cache_type=1
query_cache_size = 10M
query_cache_limit=256k

[client]
port=3306
socket=/home/ssd/mysql_datadir/mysql.sock

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

!includedir /etc/my.cnf.d

My SHOW CREATE TABLE is:

patient_records | CREATE TABLE `patient_records` (
  `recordID` int(11) NOT NULL AUTO_INCREMENT,
  `MRD_sample_FORMAT_id` int(11) NOT NULL,
  `DataType` char(1) NOT NULL,
  `SequencingOrigin` char(1) NOT NULL,
  `ChrNo` varchar(2) NOT NULL,
  `ChrPos` int(10) unsigned NOT NULL,
  `snpID` varchar(20) NOT NULL,
  `NuclREF` varchar(500) NOT NULL,
  `NuclALT` varchar(3000) NOT NULL,
  `QualityLevel` float NOT NULL,
  `FilterString` char(1) NOT NULL,
  `InfoString` varchar(1000) NOT NULL,
  `GT` varchar(20) DEFAULT NULL,
  `AD` varchar(20) DEFAULT NULL,
  `DP` varchar(20) DEFAULT NULL,
  `GQ` varchar(20) DEFAULT NULL,
  `PL` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`recordID`),
  KEY `fk_patients_idx` (`MRD_sample_FORMAT_id`),
  KEY `ChrNo` (`ChrNo`) USING BTREE,
  KEY `snpID` (`snpID`),
  KEY `idx_patient_records_ChrNo` (`ChrNo`),
  KEY `position_chrom` (`ChrPos`),
  KEY `quality` (`QualityLevel`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=700756618 DEFAULT CHARSET=latin1 |
  • 1
    You have 2 indexes on ChrNo; you could drop one of them. – Rick James Aug 26 '19 at 16:56
3

It would be helpful to have SHOW CREATE TABLE, but I will make some guesses...

You have INDEX(QualityLevel), and that index is smaller than the buffer_pool?
You have been doing a bunch of queries; these were not the first? (Hence some things are cached.)
You did not use SQL_NO_CACHE to avoid the Query cache?

How much RAM do you have? innodb_buffer_pool_size=4GB is rather small unless you have only 6GB.

The COUNT(DISTINCT ..) seems too fast -- probably caching helped.

When doing timing tests:

SELECT SQL_NO_CACHE ...   -- to avoid the Query cache.

and run the query twice -- to compensate for I/O caching.

Responding to comments

You should switch to InnoDB. Then, with 250GB of RAM, set innodb_buffer_pool_size = 200G. This will help InnoDB performance. (Note: MyISAM does not use that at all; instead, it uses key_buffer_size, which should be set to about 50G for a MyISAM-only setup.)

In general, adding a useful index is worth doing -- in spite of the negative cost considerations (disk space an insert cost).

When building a composite (multi-column) index, the cardinality of the individual columns is irrelevant. The index becomes a unit, the components of which are not noticed.

For this query:

select count(recordID) from patient_records
      where QualityLevel>10.14 and snpID='.';

the following is optimal in this order:

INDEX(snpID, ,      -- because of `=`
      QualityLevel, -- a range, so comes after `=`
      recordID)     -- tacked on to make it "covering"

A note about COUNT(recordID) -- that checks which rows have a non-NULL value for recordID. This test is unnecessary since it is declared NOT NULL. I suggest getting in the habit of simply saying COUNT(*).

With COUNT(*), INDEX(snpID, QualityLevel) would be as good.

To design the optimal indexes, you need to know the SELECTs, DELETEs, and UPDATEs. You cannot pre-guess. See my Cookbook .

Another note: The order of WHERE clauses does not matter; the Optimizer tries all reorderings of the AND clauses. It will try all reorderings of multiple tables JOINed together. (LEFT may or may not limit what the Optimizer will try.)

need to build a web interface for these queries

It is passe to say "Exactly 56627747 widgets" in a web interface. Even "About 56 million" is rarely used these days. And that could be efficiently achieved by a nightly job that recomputes that, and other, statistics about your data.

If you do need both precision and speed, then build a Summary Table and query it. That table could, for example, have daily counts for the number of widgets in various quality brackets.

Summary Tables are the key to billion-row datasets.

  • 1
    @bioplanet - I responded to these Comments by adding to my Answer. Note that I replaced my previous composite index since I discovered a mistake. – Rick James Aug 26 '19 at 16:47
  • 1
    @BahtiyarSametÇoban - You state a couple of common misconceptions about indexes. I address them in my update to my Answer. – Rick James Aug 26 '19 at 16:48
  • 1
    InnoDB is preferred in virtually all cases (today). Any advice to the contrary is probably years old. Many benchmarks show that InnoDB is as fast or faster than MyISAM. (Of course, many of the benchmarks come from Oracle, which will be removing MyISAM in some future version.) – Rick James Aug 26 '19 at 18:46
  • 1
    @bioplanet - and, yes, 23 minutes is "slow". But that is a lot of data to work with, and you don't have the optimal index, and you are using MyISAM, etc. – Rick James Aug 26 '19 at 18:49
  • 1
    @bioplanet - "Partitioning is not a performance panacea". I don't (yet) see any benefit for PARTITIONing for you. Only a few use cases benefit. – Rick James Aug 26 '19 at 18:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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