Re: [sqlite] SQLite Transaction Rate and speed...
Calling dbh->do("BEGIN")/ dbh->do("COMMIT") should get rid of the
automatic transactions. The txn's are still attomic.
I'm just guessing but I'd suspect the dbi interface..
Can you rewrite it in C and call the sqlite API directly,
You'll get better performance by creating a statement handles and preparing
those handles once. Then use the reset/bind to rebind data to the handles.
Hope that helps.
--- On Thu, 3/12/09, VF wrote:
> From: VF
> Subject: Re: [sqlite] SQLite Transaction Rate and speed...
> To: sqlite-users@sqlite.org
> Date: Thursday, March 12, 2009, 12:57 AM
> Great suggestion!
>
> I wonder however how should I implement it though... I am
> upserting tens of
> millions of rows in chunks of several hundreds thousands
> (naturally I can't
> do all this in memory). SQLite tutorial says if each
> update/insert is not
> resulting in transaction, it can make up to 50k updates a
> second.
>
> I tried calling:
>
> my $dbh = DBI->connect_cached( # connect to
> your database, create
> if needed
> "dbi:SQLite:dbname=$dbpath", # DSN: dbi,
> driver,
> database file
> "", # no user
> "", # no password
> { RaiseError => 1, AutoCommit => 0 },
> #
> complain if something goes wrong
> ) or die $DBI::errstr;
>
> # set pragmas
> $dbh->do('pragma synchronous=off;');
> $dbh->do('PRAGMA default_cache_size = 1;');
> $dbh->do('pragma page_size=819;2');
> $dbh->do('pragma temp_store=memory;');
>
> before the actual upserts, however it didn't seem to
> help.
>
> Now, if in my bulk_upser() function I do this:
> $dbh->do("BEGIN");
> foreach {
> $dbh-do("insert or ignore")
> }
> $dbh->do("COMMIT");
>
> ...would that work? How do I get rid of these atomic
> transactions?
>
> In general though - I am a little surprised. This is a
> seemingly simple task
> - hundreds of thousands of upserts in a non-shared DB -
> requiring such
> "gymnastics" and taking hours to complete - seem
> to be neither "SQL" nor
> "lite". Or is it a problem of Perl DBI interface?
>
> Thanks a lot in advance, Bobby
>
> > Is it possible for you to do the following? The
> INSERT should fail
> > silently if you violate a uniqueness constraint; we
> also set the initial
> > counter to 0 as it will be immediately bumped to 1 by
> the UPDATE. You
> > should be able to wrap the whole thing in a
> transaction.
> >
> > ***
> >
> > INSERT OR IGNORE INTO MAPPINGS_$idx
> > (key, mapping, rank, counter, timeCreated,
> timeModified)
> >values (?, ?, 1, 0, CURRENT_TIMESTAMP,
> CURRENT_TIMESTAMP);
> >
> > UPDATE MAPPINGS_$idx SET counter = counter + 1,
> > timeModified = CURRENT_TIMESTAMP WHERE key = ? AND
> mapping = ?;
> >
>
> ___
> 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
Re: [sqlite] SQLite Transaction Rate and speed...
Great suggestion!
I wonder however how should I implement it though... I am upserting tens of
millions of rows in chunks of several hundreds thousands (naturally I can't
do all this in memory). SQLite tutorial says if each update/insert is not
resulting in transaction, it can make up to 50k updates a second.
I tried calling:
my $dbh = DBI->connect_cached( # connect to your database, create
if needed
"dbi:SQLite:dbname=$dbpath", # DSN: dbi, driver,
database file
"", # no user
"", # no password
{ RaiseError => 1, AutoCommit => 0 }, #
complain if something goes wrong
) or die $DBI::errstr;
# set pragmas
$dbh->do('pragma synchronous=off;');
$dbh->do('PRAGMA default_cache_size = 1;');
$dbh->do('pragma page_size=819;2');
$dbh->do('pragma temp_store=memory;');
before the actual upserts, however it didn't seem to help.
Now, if in my bulk_upser() function I do this:
$dbh->do("BEGIN");
foreach {
$dbh-do("insert or ignore")
}
$dbh->do("COMMIT");
...would that work? How do I get rid of these atomic transactions?
In general though - I am a little surprised. This is a seemingly simple task
- hundreds of thousands of upserts in a non-shared DB - requiring such
"gymnastics" and taking hours to complete - seem to be neither "SQL" nor
"lite". Or is it a problem of Perl DBI interface?
Thanks a lot in advance, Bobby
> Is it possible for you to do the following? The INSERT should fail
> silently if you violate a uniqueness constraint; we also set the initial
> counter to 0 as it will be immediately bumped to 1 by the UPDATE. You
> should be able to wrap the whole thing in a transaction.
>
> ***
>
> INSERT OR IGNORE INTO MAPPINGS_$idx
> (key, mapping, rank, counter, timeCreated, timeModified)
>values (?, ?, 1, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
>
> UPDATE MAPPINGS_$idx SET counter = counter + 1,
> timeModified = CURRENT_TIMESTAMP WHERE key = ? AND mapping = ?;
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Transaction Rate and speed...
At 03:47am on 2009 March 08, VF did write: > CREATE UNIQUE INDEX MAP_IDX_$idx ON MAPPINGS_$idx (key, mapping); > CREATE INDEX KEY_IDX_$idx ON MAPPINGS_$idx(key); > > I am trying to do an upsert with the following logic: > > UPDATE MAPPINGS_$idx > SET counter = counter + 1 > , timeModified = CURRENT_TIMESTAMP > WHERE > key = ? AND > mapping = ?; > IF rowcount == 0 -- here tried rowid and other variations, too, it still > fails at he IF > BEGIN > INSERT INTO MAPPINGS_$idx > ( key , mapping , rank , counter , timeCreated , timeModified) >values ( ? , ? , 1 , 1 , CURRENT_TIMESTAMP , CURRENT_TIMESTAMP ) > END; > Unfortunately, it fails. I ended up having separate update, check the number > of rows modified, and if 0 - do insert in a separate statement. It works, > but painfully slow. Because of this I can't do BEGIN/COMMIT transaction from > the SQL. Is it possible for you to do the following? The INSERT should fail silently if you violate a uniqueness constraint; we also set the initial counter to 0 as it will be immediately bumped to 1 by the UPDATE. You should be able to wrap the whole thing in a transaction. *** INSERT OR IGNORE INTO MAPPINGS_$idx (key, mapping, rank, counter, timeCreated, timeModified) values (?, ?, 1, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP); UPDATE MAPPINGS_$idx SET counter = counter + 1, timeModified = CURRENT_TIMESTAMP WHERE key = ? AND mapping = ?; -- j...@3e8.org / 0x43340710 / 517B C658 D2CB 260D 3E1F 5ED1 6DB3 FBB9 4334 0710 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Transaction Rate and speed...
Not sure if it would work in your situation, but I got a good performance boost in a similar situation by essentially queuing all the updates in memory (not using SQLite), and eventually flushing thousands of queued updates as a single transaction. Worked great and was simple to implement, with the caveat that some items were 'processed' but in volatile memory longer than they might have been otherwise. Doug > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of VF > Sent: Sunday, March 08, 2009 3:48 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] SQLite Transaction Rate and speed... > > Hi SQLite Gurus, > > I am a pretty new SQLite user, and looks like (from the research I've > made > so far) I am facing a pretty typical problem with the product - > performing > millions upserts as fast as possible. The good news is - there's no > concurrent access involved - it's pretty much a single Perl script > which > processes text and inserts into SQLite DB. The bad news - upsert of > hundreds > of thousands of rows takes hours (!). > > Here's the schema of my table (it's split mod 10): > > create table MAPPINGS_$idx ( > key VARCHAR(32) NOT NULL > , mapping VARCHAR(32) NOT NULL > , rank CHAR(2) NOT NULL > , counter INTEGER NOT NULL > , timeCreated DATE NOT NULL > , timeModified DATE NOT NULL > ); > CREATE UNIQUE INDEX MAP_IDX_$idx ON MAPPINGS_$idx (key, mapping); > CREATE INDEX KEY_IDX_$idx ON MAPPINGS_$idx(key); > > I am trying to do an upsert with the following logic: > > UPDATE MAPPINGS_$idx > SET counter = counter + 1 > , timeModified = CURRENT_TIMESTAMP > WHERE > key = ? AND > mapping = ?; > IF rowcount == 0 -- here tried rowid and other variations, too, it > still > fails at he IF > BEGIN > INSERT INTO MAPPINGS_$idx ( > key > , mapping > , rank > , counter > , timeCreated > , timeModified > ) values ( > ? > , ? > , 1 > , 1 > , CURRENT_TIMESTAMP > , CURRENT_TIMESTAMP > ) > END; > > Unfortunately, it fails. I ended up having separate update, check the > number > of rows modified, and if 0 - do insert in a separate statement. It > works, > but painfully slow. Because of this I can't do BEGIN/COMMIT transaction > from > the SQL. I am using Perl DBI interface. I made sure Per's AucoCommit is > turned off, but I am not sure it still prevents SQLite engine to do > commit > after each upsert. And 'pragma synchronous=off' doesn't seem to work > either > (or else I am using it incorrectly). > > Any suggestion would be highly appreciated (ideally with some Perl > examples). > > Thanks in advance, Bobby > > ___ > 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
Re: [sqlite] SQLite Transaction Rate and speed...
Hi SQLite Gurus, I am a pretty new SQLite user, and looks like (from the research I've made so far) I am facing a pretty typical problem with the product - performing millions upserts as fast as possible. The good news is - there's no concurrent access involved - it's pretty much a single Perl script which processes text and inserts into SQLite DB. The bad news - upsert of hundreds of thousands of rows takes hours (!). Here's the schema of my table (it's split mod 10): create table MAPPINGS_$idx ( key VARCHAR(32) NOT NULL , mapping VARCHAR(32) NOT NULL , rank CHAR(2) NOT NULL , counter INTEGER NOT NULL , timeCreated DATE NOT NULL , timeModified DATE NOT NULL ); CREATE UNIQUE INDEX MAP_IDX_$idx ON MAPPINGS_$idx (key, mapping); CREATE INDEX KEY_IDX_$idx ON MAPPINGS_$idx(key); I am trying to do an upsert with the following logic: UPDATE MAPPINGS_$idx SET counter = counter + 1 , timeModified = CURRENT_TIMESTAMP WHERE key = ? AND mapping = ?; IF rowcount == 0 -- here tried rowid and other variations, too, it still fails at he IF BEGIN INSERT INTO MAPPINGS_$idx ( key , mapping , rank , counter , timeCreated , timeModified ) values ( ? , ? , 1 , 1 , CURRENT_TIMESTAMP , CURRENT_TIMESTAMP ) END; Unfortunately, it fails. I ended up having separate update, check the number of rows modified, and if 0 - do insert in a separate statement. It works, but painfully slow. Because of this I can't do BEGIN/COMMIT transaction from the SQL. I am using Perl DBI interface. I made sure Per's AucoCommit is turned off, but I am not sure it still prevents SQLite engine to do commit after each upsert. And 'pragma synchronous=off' doesn't seem to work either (or else I am using it incorrectly). Any suggestion would be highly appreciated (ideally with some Perl examples). Thanks in advance, Bobby ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Transaction Rate and speed...
On Fri, Mar 06, 2009 at 12:27:32PM -0800, Nuzzi wrote: > I have a project where I have to be determining if a row exists, if > so get the data, change it, and then write it back, if not, then > just writing the data. I have to be able to do millions of these > per minute. Is that pretty much impossible with SQLite or any other > DB? Do you really need an SQL database for this? I can think of various ways of doing this that aren't that complex and don't require an SQL database. Also, do you need on disk persistence? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Transaction Rate and speed...
Already there: http://www.sqlite.org/faq.html#q19 On Sun, Mar 8, 2009 at 8:15 AM, Alexey Pechnikov wrote: > Hello! > > On Saturday 07 March 2009 01:59:13 Roger Binns wrote: >> A transaction requires two syncs (ie requesting the drive write the data >> to the metal and not return until it does). On average each sync will >> take a disk rotation so a 7200rpm drive maxes out at 60 transactions a >> second. If you require each data update to be physically on the disk >> then this is your limit no matter what library or storage mechanism you >> use. > > That's great description! Can you add this to documentation? I did know the > limit experimentally but I didn't can to calculate this. > >> You can also look at alternate storage mechanisms. For example rotating >> drives can be paired with a battery backed controller, or solid state >> could be used if the wear leveling and write-erase performance meet your >> needs. > > Do you have experimental results of SQLite performance on SSD? > > Best regards. > ___ > 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
Re: [sqlite] SQLite Transaction Rate and speed...
Alexey Pechnikov wrote: > Do you have experimental results of SQLite performance on SSD? SSD doesn't have uniform performance like spinning disks. There are differences in the underlying block size, wear levelling, speed (SLC vs MLC), how smart the controller is, caching in SSD etc. This article gives an idea of the complexity: http://arstechnica.com/hardware/news/2009/02/intel-cant-yet-replicate-published-ssd-performance-drop-1.ars Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Transaction Rate and speed...
Hello! On Saturday 07 March 2009 01:59:13 Roger Binns wrote: > A transaction requires two syncs (ie requesting the drive write the data > to the metal and not return until it does). On average each sync will > take a disk rotation so a 7200rpm drive maxes out at 60 transactions a > second. If you require each data update to be physically on the disk > then this is your limit no matter what library or storage mechanism you > use. That's great description! Can you add this to documentation? I did know the limit experimentally but I didn't can to calculate this. > You can also look at alternate storage mechanisms. For example rotating > drives can be paired with a battery backed controller, or solid state > could be used if the wear leveling and write-erase performance meet your > needs. Do you have experimental results of SQLite performance on SSD? Best regards. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Transaction Rate and speed...
If your goal is to handle 1 million inserts/updates per second, and a hard transaction to disk takes 1/60th of a second, you need at least 1 insert/updates per transaction. Do your testing with a transaction size of 20,000 and see what kind of performance you get. I'd probably set it higher, like to 100,000 transactions if you have a continuous stream of data coming in at this rate. You get no benefit from one insert/update per commit, unless you are acking the transaction back to the source and it has retry/resend capabilities. In that case, you need a battery backed-up disk controller, and still will probably benefit from a large transaction size. I sort of doubt you have retry/resend abilities though, because for a data stream coming in at 192 MBit/sec (3 doubles = 192 bits), any kind of latency for error recovery might put you in a situation where you could never catch back up. Also, if you use "insert or replace", you can avoid the select, speeding up your app.r Jim On 3/6/09, Nuzzi wrote: > > > > ken-33 wrote: >> >> >> >> look at the sql syntax for insert or replace for sqlite. >> >> Also you goal to handle 1 million per minute is probably going to be >> dependant upon your hardware. >> >> For instance throughput greatly increases with disk striping. >> >> Also the faster the RPM of the drive the more transactions can be >> processed. Code it up and find out! >> >> > > I have actually coded it up. The way I am currently doing it is sending the > data to a function (the data is 3 doubles) and in that function doing a > SELECT to get the data currently in the DB, then updating the data, then > UPDATE or INSERT. The SQL calls are compiled statements with binds, etc. > It is woefully slow. I was kind of hoping that maybe I was missing a step > or just unfamiliar with the best techniques. I know that when adding a lot > of data with the BEGIN and END TRANSACTION things speed up considerably, but > I can't use it in my case (I don't believe) because the UPDATE depends upon > the data in the SELECT. > > John > > -- > View this message in context: > http://www.nabble.com/SQLite-Transaction-Rate-and-speed...-tp22379931p22380539.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Software first. Software lasts! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Transaction Rate and speed...
If you need high concurrency then you probably shouldn't spend too
much time looking at SQLite.
That said, how often do you actually need to read information from
this database? If you need to insert a lot but not necessarily read a
lot you might consider simply appending new information all the time
rather than trying to find/update rows and then resolving the
duplicates at query time. Call it a "time series" and declare it an
additional feature. :P
-T
On Fri, Mar 6, 2009 at 8:44 PM, Nuzzi wrote:
>
>
>
> Griggs, Donald-3 wrote:
>>
>> Regarding:
>> sorry.. no luck either... or I have a personal problem with your ftp
>> server -- he doesn't like my anonymous login... ;)
>>
>> ==
>>
>> I get an error of "421 Too many users logged in for this account."
>>
>> I guess we smoked your tool. ;-)
>>
>>
>> Pretty much echoing Roger Binn's recent comment, this posting by D. Hipp
>> may be of interest:
>> http://www.nabble.com/Behavior-of-SELECT-queries-during-transaction-to13
>> 249797.html#a13257203
>>
>> Just curious -- are you at liberty to say what this application is with
>> this firehose of data?
>>
>> Also, I know you required multiple-millions of actions per minute -- but
>> since you'll be updating some rows rather than inserting them all, about
>> how big do you anticpate the database being? That can make a big
>> difference because:
>> a) Building and consulting the table's index will get longer the
>> greater the number of rows, and
>> b) If it's small enough, an in-ram database (":MEMORY:") Roger
>> mentions might be feasible.
>>
>> FTP error below:
>>
>> N:\>ftp ftp.smokemytool.com
>> Connected to ftp.smokemytool.com.
>> 220-FileZilla Server version 0.9.24 beta
>> 220-written by Tim Kosse (tim.ko...@gmx.de)
>> 220 Please visit http://sourceforge.net/projects/filezilla/
>> User (ftp.smokemytool.com:(none)): anonymous
>> 331 Password required for anonymous
>> Password:
>> 421 Too many users logged in for this account. Try again later.
>> Login failed.
>> ftp>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>
> Sorry All, it is a very unreliable home web server. I upped the anonymous
> connections, so, theoretically, it should now work.
>
> Anyway, to answer one question, the DB can get pretty big. It is an
> application that has to keep track of soundings in cells that cover a
> certain area. The data comes fast and we have to record every bit of it, but
> it could get crazy big to keep all the individual soundings, so we are try
> keep the relevant data for each cell (min, max, sum, sum squares, hits,
> etc.). We used to do this in memory but were exploring ways to not do that
> anymore. So, this was my first try at some kind of file based option. I am
> not optimistic, though, but was just hoping it was more my ignorance rather
> than capabilities. Also, there will be many clients updating and reading
> from the "server." I think it will have to be a very complex combined file
> and memory approach.
>
> Thanks for suggestions, and I will look at the links, but I am pretty sure I
> will be starting to look at other options very soon.
>
> John
> --
> View this message in context:
> http://www.nabble.com/SQLite-Transaction-Rate-and-speed...-tp22379931p22383529.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> 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
Re: [sqlite] SQLite Transaction Rate and speed...
John,
looking at your example I have only some of the
standard remarks that might help to speed up:
1. add an index on tbl_data(cell) since you query/update
for cell = ?", each time.
2. Try to increase the cache for example by using:
db.execDML("PRAGMA page_size = 4096;");
db.execDML("PRAGMA default_cache_size = 1;");
do this right after you open the DB for the very first time.
3. If you do your AddData in a loop without having a
"BEGIN TRANSACTION" before, there is no doubt that it will
be slow. Try it... If you don't want to have a transaction
open all the time you may try at least couple your AddData
in a transaction for, say, 1000 updates/inserts.
4. Consider to do this on a in-memory data as mentioned allready here?
I think your sparkling, 64Bit, super-size, laptop should be
able to hold some memory... ;)
Hope this helps
Marcus
>
>
>
> Marcus Grimm wrote:
>>
>> you should in any case try to encapsulate your
>> inserting loop with a transaction. you should still
>> be able to do select statements inside, in particular
>> if all is done within one DB connection.
>> transactions do not speed up considerably, they do it dramatically... ;)
>>
>> also you may give a code example how you do this, since
>> there are enough sql gurus around here that may help to
>> improve further.
>>
>> hth
>> Marcus
>>
>>
>
> I have attached a .cpp and .h file that show what I am doing. A couple of
> notes: I am using a C++ wrapper to the sqlite API. The bottleneck is
> obviously in AddData. For testing I am just looping through 1 randoms
> entries. My last run took about 23 minutes for those 1 and I have a
> brand spanking new kick-ass Dell 2.93 Ghz laptop running Vista 64-bit.
> The
> helper functions in the AddData take no time at all, they were in use when
> I
> was doing everything in memory. Let me know what you think about SQLite
> end
> of it, I am trying to learn and hoping it can be a useful solution. I
> have
> already incorporated it for other file formats. Ones that don't need
> speed
> intensive, consistent use, of course. I do appreciate the comments (I was
> trying not to say that because the posting rules mention staying away from
> the little talk :-))
>
> John
> --
> View this message in context:
> http://www.nabble.com/SQLite-Transaction-Rate-and-speed...-tp22379931p22381160.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> 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
Re: [sqlite] SQLite Transaction Rate and speed...
Griggs, Donald-3 wrote:
>
> Regarding:
> sorry.. no luck either... or I have a personal problem with your ftp
> server -- he doesn't like my anonymous login... ;)
>
> ==
>
> I get an error of "421 Too many users logged in for this account."
>
> I guess we smoked your tool. ;-)
>
>
> Pretty much echoing Roger Binn's recent comment, this posting by D. Hipp
> may be of interest:
> http://www.nabble.com/Behavior-of-SELECT-queries-during-transaction-to13
> 249797.html#a13257203
>
> Just curious -- are you at liberty to say what this application is with
> this firehose of data?
>
> Also, I know you required multiple-millions of actions per minute -- but
> since you'll be updating some rows rather than inserting them all, about
> how big do you anticpate the database being? That can make a big
> difference because:
>a) Building and consulting the table's index will get longer the
> greater the number of rows, and
>b) If it's small enough, an in-ram database (":MEMORY:") Roger
> mentions might be feasible.
>
> FTP error below:
>
> N:\>ftp ftp.smokemytool.com
> Connected to ftp.smokemytool.com.
> 220-FileZilla Server version 0.9.24 beta
> 220-written by Tim Kosse (tim.ko...@gmx.de)
> 220 Please visit http://sourceforge.net/projects/filezilla/
> User (ftp.smokemytool.com:(none)): anonymous
> 331 Password required for anonymous
> Password:
> 421 Too many users logged in for this account. Try again later.
> Login failed.
> ftp>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
Sorry All, it is a very unreliable home web server. I upped the anonymous
connections, so, theoretically, it should now work.
Anyway, to answer one question, the DB can get pretty big. It is an
application that has to keep track of soundings in cells that cover a
certain area. The data comes fast and we have to record every bit of it, but
it could get crazy big to keep all the individual soundings, so we are try
keep the relevant data for each cell (min, max, sum, sum squares, hits,
etc.). We used to do this in memory but were exploring ways to not do that
anymore. So, this was my first try at some kind of file based option. I am
not optimistic, though, but was just hoping it was more my ignorance rather
than capabilities. Also, there will be many clients updating and reading
from the "server." I think it will have to be a very complex combined file
and memory approach.
Thanks for suggestions, and I will look at the links, but I am pretty sure I
will be starting to look at other options very soon.
John
--
View this message in context:
http://www.nabble.com/SQLite-Transaction-Rate-and-speed...-tp22379931p22383529.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Transaction Rate and speed...
if you need to always replace or create an new row you might just do a
"INSERT OR REPLACE" which will replace the record if it exists or
insert a new one if it does not.
On Fri, Mar 6, 2009 at 3:10 PM, Griggs, Donald
wrote:
> Regarding:
> sorry.. no luck either... or I have a personal problem with your ftp
> server -- he doesn't like my anonymous login... ;)
>
> ==
>
> I get an error of "421 Too many users logged in for this account."
>
> I guess we smoked your tool. ;-)
>
>
> Pretty much echoing Roger Binn's recent comment, this posting by D. Hipp
> may be of interest:
> http://www.nabble.com/Behavior-of-SELECT-queries-during-transaction-to13
> 249797.html#a13257203
>
> Just curious -- are you at liberty to say what this application is with
> this firehose of data?
>
> Also, I know you required multiple-millions of actions per minute -- but
> since you'll be updating some rows rather than inserting them all, about
> how big do you anticpate the database being? That can make a big
> difference because:
> a) Building and consulting the table's index will get longer the
> greater the number of rows, and
> b) If it's small enough, an in-ram database (":MEMORY:") Roger
> mentions might be feasible.
>
> FTP error below:
>
> N:\>ftp ftp.smokemytool.com
> Connected to ftp.smokemytool.com.
> 220-FileZilla Server version 0.9.24 beta
> 220-written by Tim Kosse (tim.ko...@gmx.de)
> 220 Please visit http://sourceforge.net/projects/filezilla/
> User (ftp.smokemytool.com:(none)): anonymous
> 331 Password required for anonymous
> Password:
> 421 Too many users logged in for this account. Try again later.
> Login failed.
> ftp>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Jim Dodgen
j...@dodgen.us
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Transaction Rate and speed...
Regarding:
sorry.. no luck either... or I have a personal problem with your ftp
server -- he doesn't like my anonymous login... ;)
==
I get an error of "421 Too many users logged in for this account."
I guess we smoked your tool. ;-)
Pretty much echoing Roger Binn's recent comment, this posting by D. Hipp
may be of interest:
http://www.nabble.com/Behavior-of-SELECT-queries-during-transaction-to13
249797.html#a13257203
Just curious -- are you at liberty to say what this application is with
this firehose of data?
Also, I know you required multiple-millions of actions per minute -- but
since you'll be updating some rows rather than inserting them all, about
how big do you anticpate the database being? That can make a big
difference because:
a) Building and consulting the table's index will get longer the
greater the number of rows, and
b) If it's small enough, an in-ram database (":MEMORY:") Roger
mentions might be feasible.
FTP error below:
N:\>ftp ftp.smokemytool.com
Connected to ftp.smokemytool.com.
220-FileZilla Server version 0.9.24 beta
220-written by Tim Kosse (tim.ko...@gmx.de)
220 Please visit http://sourceforge.net/projects/filezilla/
User (ftp.smokemytool.com:(none)): anonymous
331 Password required for anonymous
Password:
421 Too many users logged in for this account. Try again later.
Login failed.
ftp>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Transaction Rate and speed...
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Nuzzi wrote: > It is woefully slow. A transaction requires two syncs (ie requesting the drive write the data to the metal and not return until it does). On average each sync will take a disk rotation so a 7200rpm drive maxes out at 60 transactions a second. If you require each data update to be physically on the disk then this is your limit no matter what library or storage mechanism you use. You can increase performance by increasing the number of updates per transaction. You can also do things like have an attached memory database and then flush its contents to the disk database every 5 seconds or whatever your tolerance for data loss is. You can also look at alternate storage mechanisms. For example rotating drives can be paired with a battery backed controller, or solid state could be used if the wear leveling and write-erase performance meet your needs. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkmxqr0ACgkQmOOfHg372QR36ACgzApBSzqB3fW5lxgyAQaQjjn1 CaoAoNJ3nXrflO/QVZtNYlDFIfzgbLsO =n2sS -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Transaction Rate and speed...
sorry.. no luck either... or I have a personal problem with your ftp server -- he doesn't like my anonymous login... ;) > > > Marcus Grimm wrote: >> >> I'm afraid you can't post an attachment here... >> try it differently. >> >> > > Hmm... Bummer. Here are some links (hopefully): > > ftp://ftp.smokemytool.com/pub/matrix_file_db.cpp matrix_file_db.cpp > ftp://ftp.smokemytool.com/pub/matrix_file_db.h matrix_file_db.h > > John > > -- > View this message in context: > http://www.nabble.com/SQLite-Transaction-Rate-and-speed...-tp22379931p22381669.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > 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
Re: [sqlite] SQLite Transaction Rate and speed...
Marcus Grimm wrote: > > I'm afraid you can't post an attachment here... > try it differently. > > Hmm... Bummer. Here are some links (hopefully): ftp://ftp.smokemytool.com/pub/matrix_file_db.cpp matrix_file_db.cpp ftp://ftp.smokemytool.com/pub/matrix_file_db.h matrix_file_db.h John -- View this message in context: http://www.nabble.com/SQLite-Transaction-Rate-and-speed...-tp22379931p22381669.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Transaction Rate and speed...
I'm afraid you can't post an attachment here... try it differently. > > > > Marcus Grimm wrote: >> >> you should in any case try to encapsulate your >> inserting loop with a transaction. you should still >> be able to do select statements inside, in particular >> if all is done within one DB connection. >> transactions do not speed up considerably, they do it dramatically... ;) >> >> also you may give a code example how you do this, since >> there are enough sql gurus around here that may help to >> improve further. >> >> hth >> Marcus >> >> > > I have attached a .cpp and .h file that show what I am doing. A couple of > notes: I am using a C++ wrapper to the sqlite API. The bottleneck is > obviously in AddData. For testing I am just looping through 1 randoms > entries. My last run took about 23 minutes for those 1 and I have a > brand spanking new kick-ass Dell 2.93 Ghz laptop running Vista 64-bit. > The > helper functions in the AddData take no time at all, they were in use when > I > was doing everything in memory. Let me know what you think about SQLite > end > of it, I am trying to learn and hoping it can be a useful solution. I > have > already incorporated it for other file formats. Ones that don't need > speed > intensive, consistent use, of course. I do appreciate the comments (I was > trying not to say that because the posting rules mention staying away from > the little talk :-)) > > John > -- > View this message in context: > http://www.nabble.com/SQLite-Transaction-Rate-and-speed...-tp22379931p22381160.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > 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
Re: [sqlite] SQLite Transaction Rate and speed...
Marcus Grimm wrote: > > you should in any case try to encapsulate your > inserting loop with a transaction. you should still > be able to do select statements inside, in particular > if all is done within one DB connection. > transactions do not speed up considerably, they do it dramatically... ;) > > also you may give a code example how you do this, since > there are enough sql gurus around here that may help to > improve further. > > hth > Marcus > > I have attached a .cpp and .h file that show what I am doing. A couple of notes: I am using a C++ wrapper to the sqlite API. The bottleneck is obviously in AddData. For testing I am just looping through 1 randoms entries. My last run took about 23 minutes for those 1 and I have a brand spanking new kick-ass Dell 2.93 Ghz laptop running Vista 64-bit. The helper functions in the AddData take no time at all, they were in use when I was doing everything in memory. Let me know what you think about SQLite end of it, I am trying to learn and hoping it can be a useful solution. I have already incorporated it for other file formats. Ones that don't need speed intensive, consistent use, of course. I do appreciate the comments (I was trying not to say that because the posting rules mention staying away from the little talk :-)) John -- View this message in context: http://www.nabble.com/SQLite-Transaction-Rate-and-speed...-tp22379931p22381160.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Transaction Rate and speed...
you should in any case try to encapsulate your inserting loop with a transaction. you should still be able to do select statements inside, in particular if all is done within one DB connection. transactions do not speed up considerably, they do it dramatically... ;) also you may give a code example how you do this, since there are enough sql gurus around here that may help to improve further. hth Marcus > > > > ken-33 wrote: >> >> >> >> look at the sql syntax for insert or replace for sqlite. >> >> Also you goal to handle 1 million per minute is probably going to be >> dependant upon your hardware. >> >> For instance throughput greatly increases with disk striping. >> >> Also the faster the RPM of the drive the more transactions can be >> processed. Code it up and find out! >> >> > > I have actually coded it up. The way I am currently doing it is sending > the > data to a function (the data is 3 doubles) and in that function doing a > SELECT to get the data currently in the DB, then updating the data, then > UPDATE or INSERT. The SQL calls are compiled statements with binds, etc. > It is woefully slow. I was kind of hoping that maybe I was missing a step > or just unfamiliar with the best techniques. I know that when adding a lot > of data with the BEGIN and END TRANSACTION things speed up considerably, > but > I can't use it in my case (I don't believe) because the UPDATE depends > upon > the data in the SELECT. > > John > > -- > View this message in context: > http://www.nabble.com/SQLite-Transaction-Rate-and-speed...-tp22379931p22380540.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > 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
Re: [sqlite] SQLite Transaction Rate and speed...
ken-33 wrote: > > > > look at the sql syntax for insert or replace for sqlite. > > Also you goal to handle 1 million per minute is probably going to be > dependant upon your hardware. > > For instance throughput greatly increases with disk striping. > > Also the faster the RPM of the drive the more transactions can be > processed. Code it up and find out! > > I have actually coded it up. The way I am currently doing it is sending the data to a function (the data is 3 doubles) and in that function doing a SELECT to get the data currently in the DB, then updating the data, then UPDATE or INSERT. The SQL calls are compiled statements with binds, etc. It is woefully slow. I was kind of hoping that maybe I was missing a step or just unfamiliar with the best techniques. I know that when adding a lot of data with the BEGIN and END TRANSACTION things speed up considerably, but I can't use it in my case (I don't believe) because the UPDATE depends upon the data in the SELECT. John -- View this message in context: http://www.nabble.com/SQLite-Transaction-Rate-and-speed...-tp22379931p22380540.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Transaction Rate and speed...
ken-33 wrote: > > > > look at the sql syntax for insert or replace for sqlite. > > Also you goal to handle 1 million per minute is probably going to be > dependant upon your hardware. > > For instance throughput greatly increases with disk striping. > > Also the faster the RPM of the drive the more transactions can be > processed. Code it up and find out! > > I have actually coded it up. The way I am currently doing it is sending the data to a function (the data is 3 doubles) and in that function doing a SELECT to get the data currently in the DB, then updating the data, then UPDATE or INSERT. The SQL calls are compiled statements with binds, etc. It is woefully slow. I was kind of hoping that maybe I was missing a step or just unfamiliar with the best techniques. I know that when adding a lot of data with the BEGIN and END TRANSACTION things speed up considerably, but I can't use it in my case (I don't believe) because the UPDATE depends upon the data in the SELECT. John -- View this message in context: http://www.nabble.com/SQLite-Transaction-Rate-and-speed...-tp22379931p22380539.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Transaction Rate and speed...
look at the sql syntax for insert or replace for sqlite. Also you goal to handle 1 million per minute is probably going to be dependant upon your hardware. For instance throughput greatly increases with disk striping. Also the faster the RPM of the drive the more transactions can be processed. Code it up and find out! --- On Fri, 3/6/09, Nuzzi wrote: > From: Nuzzi > Subject: [sqlite] SQLite Transaction Rate and speed... > To: sqlite-users@sqlite.org > Date: Friday, March 6, 2009, 2:27 PM > I have a project where I have to be determining if a row > exists, if so get > the data, change it, and then write it back, if not, then > just writing the > data. I have to be able to do millions of these per > minute. Is that pretty > much impossible with SQLite or any other DB? > > Thanks, > > John > -- > View this message in context: > http://www.nabble.com/SQLite-Transaction-Rate-and-speed...-tp22379931p22379931.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > 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
[sqlite] SQLite Transaction Rate and speed...
I have a project where I have to be determining if a row exists, if so get the data, change it, and then write it back, if not, then just writing the data. I have to be able to do millions of these per minute. Is that pretty much impossible with SQLite or any other DB? Thanks, John -- View this message in context: http://www.nabble.com/SQLite-Transaction-Rate-and-speed...-tp22379931p22379931.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
