How to tune for better performance?

Hi list,

I've looked at maxdb, and it looks pretty sexy ;) I am however not
getting the performance I expected.

I'm researching different database-engines for a project involving a
database with several large tables. Some of the tables will contain
billions of records. As a start, I've exported a table containing 135
million records from a MS SQL server. I used the script below to import
the data to maxdb with loadercli:

---- start script

create table Testtable (
        Id FIXED(18) NOT NULL,
        CountryId INT DEFAULT 47 NOT NULL,
        SiteId INT NOT NULL,
        Dato timestamp NOT NULL,
        Value INT NOT NULL,
        Kvalitet VARCHAR(10) ASCII NULL
)
//
FASTLOAD TABLE Testtable
        Id 1
        SiteId 2
        Dato 3
        Value 4
        Kvalitet 5
INSTREAM '/home/jgaa/data/Testtable' COMPRESSED
TIMESTAMP ISO
SEPARATOR '\t'
START 2 1000000000
//
CREATE INDEX ix_1 on Testtable (CountryId, SiteId, Dato)
//
CREATE INDEX ix_2 on Testtable (Dato, CountryId, SiteId)
//
CREATE INDEX ix_3 on Testtable (CountryId, SiteId,Value)

---- end script

I timed the loadercli command, and got this result:
        real    1392m55.301s
        user    27m10.356s
        sys     0m43.983s

With 135 million records, that's just above 1600 records/sec. I noticed
that the actual load was very fast, while the indexes took forever to
build. 

The test-machine is a P4 3GHz machine with 1G RAM and a 320GB ATA disk
running Debian GNU/Linux "testing" and Linux kernel 2.6.8 with support
for hyperthreading. The database has 4 x 500 GB raw devices + 1 60 GB
raw device for the log (maxdb refused to accept one 200 GB raw device).
The Maxdb kernel is version 7.5.0 (Debian build in "testing").

A few other strange things I've noticed; 

  1) "SELECT COUNT(*) from Testtable" seems to cause a tablescan (which
is _slow_), and when it runs, the sql studio application hangs until the
connection times out.

  2) I tried to add a key-column with a "DEFAULT SERIAL" to get a unique
ID in a visible column (the original data contains duplicate "Id"
values), but FASTLOAD complained about null value.

  3) I started and aborted the import a few times. It seems like DROP
TABLE does not release the used space in the volumes.

My main concern at the moment is however I can tune the server for
better performance. I don't plan to use a desktop PC for the real
database, but I'm not comfortable with just 1600 rows/sec on a P4
machine. 

Jarle
-- 
Jarle Aase                      email: jgaa@xxxxxxxx
Author of freeware.             http://www.jgaa.com
                                news:alt.comp.jgaa

War FTP Daemon:     http://www.warftp.org
War FTP Daemon FAQ: http://www.warftp.org/faq/warfaq.htm
Jgaa's PGP key:     http://war.jgaa.com/pgp
NB: If you reply to this message, please include all relevant
information from the conversation in your reply. Thanks.
<<< no need to argue - just kill'em all! >>>


-- 
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/maxdb?unsub=gcdm-maxdb@xxxxxxxxxxx