Noah, really nice job with this tool. I find it quire useful just to get a
relative performance comparison between my hardware systems.
On my AMD system the tests seem to be CPU bound. But this is a DB, My concern
is that this is really more of a CPU stress test than an I/O DB test.
That begs the question, What do you really want to test?
CPU prepare/parse with calculations ? Or select insert/update/delete that Is
more I/O bound (what about indexing ?)
Maybe think of creating another test suite that stresses i/o subsystem and does
not run complex calculations.
HTH
Ken
--- On Fri, 8/15/08, Noah Hart <[EMAIL PROTECTED]> wrote:
From: Noah Hart <[EMAIL PROTECTED]>
Subject: Re: [sqlite] Generic speed testing
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Date: Friday, August 15, 2008, 4:34 PM
Good Point Ken,
Here is version 1.2 with the missing CREATE TABLE statement and some new
PRAGMA settings.
Any suggestions for the CACHE_SIZE setting?
Also -- I can see how to modify an existing Wiki page, but does anyone
know how to create a new Wiki page, so I can put this script there
rather than repeating it in email?
Noah
SQLite 3.6.1 running under "Vista 32 bit, QuadCore 2.4GHz, 3G ram,
5000rpmDisk"
0|performance.txt,v 1.2|1.0|0|0.0K Rows/Second
1|Trivial Inserts|16.31|10077696|618.0K Rows/Second
2|Trivial Selects|0.85|10077696|11898.0K Rows/Second
3|Trivial Updates|82.41|10077696|122.0K Rows/Second
4|Trivial Deletes|23.32|10077696|432.0K Rows/Second
5|Insert with calculations|29.02|10077696|347.0K Rows/Second
6|Updates with calculations and longer rows|54.52|10077696|185.0K
Rows/Second
--
-- The author disclaims copyright to this source code. In place of
-- a legal notice, here is a blessing:
--
-- May you do good and not evil.
-- May you find forgiveness for yourself and forgive others.
-- May you share freely, never taking more than you give.
--
--**********************************************************************
*
-- This file contains code used to implement the performance scripts
--
-- $Id: performance.txt,v 1.2 2008/08/15 14:15:00 nbh Exp $
------------------------------------------
-- LEVEL THE PLAYING FIELD WITH PRAGMAs
------------------------------------------
PRAGMA auto_vacuum = NONE;
PRAGMA cache_size = 20000;
PRAGMA count_changes = 1;
PRAGMA encoding = "UTF-8";
PRAGMA fullfsync = 0;
PRAGMA journal_mode = DELETE;
PRAGMA locking_mode = EXCLUSIVE;
PRAGMA page_size = 1024;
PRAGMA synchronous = OFF;
PRAGMA temp_store = MEMORY;
------------------------------------------
-- A LITTLE SETUP BEFORE WE BEGIN
------------------------------------------
CREATE TABLE TIMER(TestNumber INTEGER, Description TEXT, StartTime
REAL, EndTime REAL DEFAULT NULL, Rows INTEGER DEFAULT NULL);
INSERT INTO TIMER VALUES(0, 'performance.txt,v 1.2', 0, 1, 0);
CREATE TABLE TEST1 (I INTEGER, T TEXT);
CREATE TABLE N_1(i INTEGER, t TEXT);
INSERT INTO N_1 VALUES(1, 't1_');
INSERT INTO N_1 VALUES(2, 't_22_');
INSERT INTO N_1 VALUES(3, 'tx_3_3_3_');
INSERT INTO N_1 VALUES(4, 'txt_4_4_4_4_');
INSERT INTO N_1 VALUES(5, 'text_55555555555_');
INSERT INTO N_1 VALUES(6, ' ');
CREATE TABLE N_2(i INTEGER, t TEXT);
INSERT INTO N_2 SELECT N1.I+N2.I*7, N1.T||N2.T FROM N_1 N1 CROSS
JOIN N_1 N2 CROSS JOIN N_1 N3;
--------------------------------------------------------
-- TEST 1
-- TRIVIAL INSERTS -- KEEP THE NUMBER AND TEXT SMALL
--------------------------------------------------------
BEGIN;
INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT
1+MAX(TESTNUMBER), 'Trivial Inserts', (julianday('now') -
2440587.5)*86400 FROM TIMER;
INSERT INTO TEST1 SELECT 1,'T' FROM N_2 N1 CROSS JOIN N_2 N2 CROSS
JOIN N_2 N3;
COMMIT;
UPDATE TIMER SET EndTime = (julianday('now') -
2440587.5)*86400.0, Rows = changes()
WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
--------------------------------------------------------
-- TEST 2
-- TRIVIAL SELECTS
--------------------------------------------------------
INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT
1+MAX(TESTNUMBER), 'Trivial Selects', (julianday('now') -
2440587.5)*86400 FROM TIMER;
UPDATE TIMER SET Rows = (SELECT COUNT(*) FROM TEST1 where rowid
> 0)
WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
UPDATE TIMER SET EndTime = (julianday('now') -
2440587.5)*86400.0
WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
--------------------------------------------------------
-- TEST 3
-- TRIVIAL UPDATES -- THE NUMBERS AND ROW SIZE ARE SMALL
--------------------------------------------------------
BEGIN;
INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT
1+MAX(TESTNUMBER), 'Trivial Updates', (julianday('now') -
2440587.5)*86400 FROM TIMER;
UPDATE TEST1 SET I=I;
COMMIT;
UPDATE TIMER SET EndTime = (julianday('now') -
2440587.5)*86400.0, Rows = changes()
WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
--------------------------------------------------------
-- TEST 4
-- TRIVIAL DELETES
--------------------------------------------------------
BEGIN;
INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT
1+MAX(TESTNUMBER), 'Trivial Deletes', (julianday('now') -
2440587.5)*86400 FROM TIMER;
DELETE FROM TEST1 WHERE I >0;
COMMIT;
UPDATE TIMER SET EndTime = (julianday('now') -
2440587.5)*86400.0, Rows = changes()
WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
------------------------------------------
-- A LITTLE CLEANUP BEFORE WE CONTINUE
------------------------------------------
DROP TABLE TEST1;
CREATE TABLE TEST1 (I INTEGER, T TEXT);
PRAGMA page_count;
VACUUM;
PRAGMA page_count;
--------------------------------------------------------
-- TEST 5
-- INSERTS WITH CALCULATIONS -- SHOULD BE SLOWER THAN 1
--------------------------------------------------------
BEGIN;
INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT
1+MAX(TESTNUMBER), 'Insert with calculations',
(julianday('now') -
2440587.5)*86400 FROM TIMER;
INSERT INTO TEST1 SELECT N1.I*N2.I+N3.I, N1.T||N2.T||N3.T FROM N_2
N1 CROSS JOIN N_2 N2 CROSS JOIN N_2 N3;
COMMIT;
UPDATE TIMER SET EndTime = (julianday('now') -
2440587.5)*86400.0, Rows = changes()
WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
--------------------------------------------------------
-- TEST 6
-- UPDATES WITH CALCULATIONS -- SHOULD BE SLOWER THAN 2
--------------------------------------------------------
BEGIN;
INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT
1+MAX(TESTNUMBER), 'Updates with calculations and longer rows',
(julianday('now') - 2440587.5)*86400 FROM TIMER;
UPDATE TEST1 SET I=I*1+2-3;
COMMIT;
UPDATE TIMER SET EndTime = (julianday('now') -
2440587.5)*86400.0, Rows = changes()
WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
-----------------------------------------------
-- REPORT THE RESULTS
--------------------------------------------------------
Select TestNumber, Description, ROUND(EndTime- StartTime,2),
Rows, Round(Rows/(EndTime-StartTime)/1000)||'K Rows/Second' from TIMER;
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Kees Nuyt
Sent: Friday, August 15, 2008 2:03 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Generic speed testing
On Thu, 14 Aug 2008 13:25:56 -0700, you wrote:
>I'm not sure if this will even be a valid comparison, so your feedback
>and initial numbers are appreciated.
>
>Please reply with your data as follows
(v1.1 without the DROP TABLE TEST1 statement):
sqlite 3.6.0 running on Acer Aspire 9423 under "MS Windows
Vista 32bit on Intel Core 2 Duo T5500 1.66GHz, 667 MHz FSB,
2GB DDR2 RAM, Hitachi HTS541616J9SA00 SATA 5400RPM with 8MB
buffer, write caching enabled"
0|performance.txt,v 1.1|1.0|0|0.0K Rows/Second
1|Trivial Inserts|31.41|10077696|321.0K Rows/Second
2|Trivial Selects|2.06|10077696|4887.0K Rows/Second
3|Trivial Updates|142.87|10077696|71.0K Rows/Second
4|Trivial Deletes|41.39|10077696|243.0K Rows/Second
5|Insert with calculations|61.81|10077696|163.0K Rows/Second
6|Updates with calculations and longer
rows|383.63|10077696|26.0K Rows/Second
I might be able to squeeze more performance out of this box.
Hint:
For a genuinely level playing field you'd have to use more
PRAGMAs, like page size and cache size. Defaults might be
different in different environments.
--
( Kees Nuyt
)
c[_]
CONFIDENTIALITY NOTICE:
This message may contain confidential and/or privileged information. If you are
not the addressee or authorized to receive this for the addressee, you must not
use, copy, disclose, or take any action based on this message or any information
herein. If you have received this message in error, please advise the sender
immediately by reply e-mail and delete this message. Thank you for your
cooperation.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
