CODEWITHSUNDEEP
performancesqlite
vijayst
vijayst

Reputation: 21846

Select takes a long time in a 160GB database

I have an SQLite database of 160GB. There is an index on the id column and stage column of table hwmp.

CREATE INDEX idx_hwmp_id ON hwmp (id, stage);

When I do a count of rows the query returns in 0.09 seconds.

sqlite> select count (*) from hwmp where id = 2000 and stage = 4;
59397
Run Time: real 0.091 user 0.000074 sys 0.080494

However, for a select all the real time is 85 seconds. The user and system time combined is only 2.5 seconds. Why would the real time be so high?

select * from hwmp where id = 2000 and stage = 4;
Run Time: real 85.420 user 0.801639 sys 1.754250

How to fix it? Another query on a sqlite3 database (300MB) used to return in 20ms. Today, it was taking 652ms.

Run Time: real 0.652 user 0.018766 sys 0.010595

There is something wrong with the Linux environment today. I downloaded the same SQLite to my Mac and it ran quickly.

Run Time: real 0.028 user 0.005990 sys 0.010420

It is using the index:

sqlite> explain query plan select * from hwmp where id = 78 and stage = 4;
QUERY PLAN
`--SEARCH hwmp USING INDEX idx_hwmp_id (id=? AND stage=?)
Run Time: real 0.005 user 0.000857 sys 0.000451

Upvotes: 1

Views: 170

Answers (1)

vijayst
vijayst

Reputation: 21846

The relevant setting is pragma cache_size = 200000; 200000 pages of 4096 bytes. After setting that, for the first time query, it takes approximately 3s and second time query takes 0.28s. Phew.

The cache settings improved the performance for some time. We are working off an AWS linux VM with EBS SSD attached. There seems to be problem in the environment as well. The query times in my Mac is 6.3 times faster than the AWS linux / EBS environment.

Upvotes: 1

Related Questions