2

I have created a django app that is running on virtual machine. All db tables are rather small (up to 1M rows) but recently I was asked to add a big table (460M rows).

CREATE TABLE `genescorrelation` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `correlation` double NOT NULL,
 `gene1_id` int(10) unsigned NOT NULL,
 `gene2_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `c_gene1_id_6b1d81605661118_fk_genes_gene_entrez`   (`gene1_id`),
KEY `gene2_gene1_idx1` (`gene2_id`,`gene1_id`)) 
ENGINE=InnoDB AUTO_INCREMENT=92345005 DEFAULT CHARSET=latin1

The query that user will run (up to 10 genes):

 SELECT gene1_id, AVG(correlation) AS avg FROM genescorrelation 
 WHERE gene2_id IN (176829, 176519, 176230) 
 GROUP BY gene1_id ORDER BY NULL 

Currently I have imported around 89M rows and the query is slow. It would take around 30-40 seconds for 3 or 4 genes.

I think my VM is too weak because it was a default set up (for smaller project/database). Currently it has just 2GB RAM and 36GB space.

I need to ask to set up prod machine.

What are the memory/space suggestion for this kind of set up?
Is there anything that could be done in MySQL config?
Is there anything else I can do to speed up a query?

Any hints appreciated, thanks.

FYI, I asked my initial question on so: https://stackoverflow.com/questions/31969964/mysql-slow-avg-query-for-411m-rows. After that I was trying to add the suggested index (gene2, gene1) but didn't have enough space, so dropped the table and reimported 90M rows.

2 Answers 2

1

Looking at the query

SELECT gene1_id, AVG(correlation) AS avg FROM genescorrelation 
WHERE gene2_id IN (176829, 176519, 176230) 
GROUP BY gene1_id ORDER BY NULL 

I have four(4) suggestions

SUGGESTION #1

I see three(3) columns. I recommend making a covering index

ALTER TABLE genescorrelation
ADD INDEX gene2_gene1_correlation_idx1 (gene2_id,gene_id,correlation);

The StackOverflow accepted answer already mentioned making this index. That way, the aggregation never touches the table. All the needed data will come from the index.

Since you already made gene2_gene1_idx1 here is the fix

ALTER TABLE genescorrelation
DROP INDEX gene2_gene1_idx1,
ADD INDEX gene2_gene1_correlation_idx1 (gene2_id,gene_id,correlation);

SUGGESTION #2

You probably need more read threads

Add this to my.cnf

[mysqld]
innodb_read_io_threads = 8

Restart of mysql is required

SUGGESTION #3

If the server is just a DB Server, you should reserve up to 75% of RAM for InnoDB Buffer Pool or the sum total of all InnoDB Data and Indexes (SELECT SUM(data_length+index_length) InnoDBDataAndIndex FROM information_schema.tables WHERE engine='InnoDB';), whichever is smaller.

SUGGESTION #4 (OPTIONAL)

Change the Query to see break down by gene2_id

SELECT
    IFNULL(gene2_id,'All Gene2 IDs') gene2,
    IFNULL(gene1_id,'All Gene1 IDs') gene1,
    AVG(correlation) AS avg FROM genescorrelation 
WHERE gene2_id IN (176829, 176519, 176230) 
GROUP BY gene2_id,gene1_id WITH ROLLUP;
2
  • Thanks, Rolando - the covering index made a huge difference! I am going to look into other suggestions as well.
    – matali
    Commented Aug 14, 2015 at 23:42
  • My DB is on Unix, and according to docs: increasing the number (innodb_read_io_threads) has no effect; InnoDB always uses the default value.
    – matali
    Commented Aug 17, 2015 at 14:21
0
CREATE TABLE `genescorrelation` (
   `correlation` FLOAT NOT NULL,
   `gene1_id` SMALLINT unsigned NOT NULL,
   `gene2_id` SMALLINT unsigned NOT NULL,
  PRIMARY KEY (gene1_id, gene2_id),
  KEY         (gene2_id, gene1_id, correlation)
) ENGINE=InnoDB;

and reload.

I am assuming that FLOAT, with about 7 significant digits is more than enough for a correlation. It will save 2GB overall.

Rolando's suggestion took care of one direction; the PK change takes care of the other.

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.