Re: [sqlite] Generic speed testing
On Fri, Aug 15, 2008 at 11:19 PM, Noah Hart <[EMAIL PROTECTED]> wrote: > Greg, I intended that sqlite3 be launched without a filename, so this > will give a memory based database and disk I/O would not need to be > considered. If I remember correctly the default is an "anonymous" disk based database (without the fsync overhead), but can be a pure memory db if you pass the ":memory:" filename (and can be configured with the right option passed to the "configure" script or with the right "#define"). Also, IIRC, the memory db is a bit slower than the disk based one because it's not optimized for memory use (like using B+ Trees in memory instead of something like RB Trees, as it was on 2.8.x). This is all from memory, so someone correct me if I'm wrong. Regards, ~Nuno Lucas > > Regards -- Noah ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Generic speed testing
Greg, I intended that sqlite3 be launched without a filename, so this will give a memory based database and disk I/O would not need to be considered. Regards -- Noah -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Griggs, Donald Sent: Friday, August 15, 2008 2:52 PM To: [EMAIL PROTECTED]; General Discussion of SQLite Database Subject: Re: [sqlite] Generic speed testing Regarding: "On my AMD system the tests seem to be CPU bound." On that note, I believe the test creates a 625 megabyte database before deleting most of it and vacuuming down to a tiny size. So I guess included in the test is not just one's disk speed, but how fast one's operating system can allocate the space and how fragmented the result is. This email and any attachments have been scanned for known viruses using multiple scanners. We believe that this email and any attachments are virus free, however the recipient must take full responsibility for virus checking. This email message is intended for the named recipient only. It may be privileged and/or confidential. If you are not the named recipient of this email please notify us immediately and do not copy it or use it for any purpose, nor disclose its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 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
Re: [sqlite] Generic speed testing
Ken,
I'm not really sure what I want to test, or rather what would be a meaningful
test, so I wanted to start a public discussion about relative performances.
All systems will have limits in some way based on CPU, memory and disk. But an
interesting question to me is sqlite whether sqlite is more efficient (whatever
that means) on one platform vs. another.
This first script was intended to focus on CPU which (with enough data) should
give a nice baseline for further testing on various platforms.
I should also note that it is intended that sqlite3 be launched without a
filename, since this will give a memory based database, ignoring all I/O
Regards -- Noah
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ken
Sent: Friday, August 15, 2008 2:46 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Generic speed testing
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 = 2;
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_555_');
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);
-
Re: [sqlite] Generic speed testing
Regarding: "On my AMD system the tests seem to be CPU bound." On that note, I believe the test creates a 625 megabyte database before deleting most of it and vacuuming down to a tiny size. So I guess included in the test is not just one's disk speed, but how fast one's operating system can allocate the space and how fragmented the result is. This email and any attachments have been scanned for known viruses using multiple scanners. We believe that this email and any attachments are virus free, however the recipient must take full responsibility for virus checking. This email message is intended for the named recipient only. It may be privileged and/or confidential. If you are not the named recipient of this email please notify us immediately and do not copy it or use it for any purpose, nor disclose its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Generic speed testing
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 = 2;
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_555_');
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
Re: [sqlite] Generic speed testing
E 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
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[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Generic speed testing
On Thu, 14 Aug 2008 13:25:56 -0700, you wrote: >-- >-- A LITTLE CLEANUP BEFORE WE CONTINUE >-- > > DROP TABLE TEST1; I don't think you really want to drop TEST1. We'll need it later. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Generic speed testing
Windows XP SP2, SQLite 3.6.1, Intel T2400 (1.83GHZ) Dual Core, 2Gb RAM, 5000RPM Drive SQL error near line 112: no such table: TEST1 SQL error near line 127: no such table: TEST1 0|performance.txt,v 1.0|1.0|0|0.0K Rows/Second 1|Trivial Inserts|21.17|10077696|476.0K Rows/Second 2|Trivial Selects|1.61|10077696|6259.0K Rows/Second 3|Trivial Updates|105.89|10077696|95.0K Rows/Second 4|Trivial Deletes|28.31|10077696|356.0K Rows/Second 5|Insert with calculations|0.0|1| 6|Updates with calculations and longer rows|0.0|1| Not sure why the last two results didn't print with "Rows/Second" column. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
