select max() taking 19 seconds!

1 post / 0 new
gsaxena
gsaxena's picture
Offline
Last seen: 1 year 7 months ago
Joined: Oct 20 2010
Junior Boarder

Posts: 13

Gautam Saxena
select max(<some column>) taking 19 seconds!

Hi,

I have a 2 billion row table that's very narrow. I ran the simplest of simple aggregate statements and it took 19 seconds and used most of the machine's CPU and 30% of RAM. This was the sql statement.

SELECT MAX(td_mz_key) FROM td_mz;

It also took the same time (19 seconds or so), to do something even more common/simple:

SELECT count(1) FROM td_mz;

On a "normal" RDBMS with a primary key (eg InnoDB in MySQL), I suspect that we would get responses almost immediately for the above 2 queries. What am I missing, since Infindib's main strength should be on these aggregate queries?

I even did a caltrace, as follows:

SELECT calsettrace(1);
SELECT MAX(td_mz_key) FROM td_mz;
SELECT calgettrace();

DESC MODE TABLE TableOID ReferencedColumns PIO LIO PBE Elapsed ROWS
BPS PM td_mz 4946 (td_mz_key) 0 1005688 0 18.719 251422
TAS UM - - - - - - 18.692 1

Other logisitcal details:

Centos 6.3
Calpont Infinidb 2.2.9 (default installation -- nothing really changed, **I think**; or at least, nothing major. I may have made some minot tweaks to the my.cnf file, but that should only affect the MyISAM performance aspects)