Re: [sqlite] Race condition -- fixed?
On 10/26/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > This is true of SQLite because isolation in SQLite > is "SERIALIZABLE". This is the highest level of isolate > provided by SQL. Most client/server database engines > by default implement "READ COMMITTED". The value of > "balance" might change between the SELECT and the > UPDATE in MySQL, for example. (I'm less clear about > what happens in PostgreSQL and Oracle. The point ism > that your mileage may vary so be cautious.) PostgreSQL supports both READ COMMITTED and SERIALIZABLE, but the out-of-box default is READ COMMITTED. It can be selected per-transaction, or changed as a default for all transactions. I believe Oracle is similar. MySQL is a little weird due to different storage engines. From what I can tell, MyISAM doesn't actually support transactions, so you essentially get READ UNCOMMITTED if you try; InnoDB supports all 4 levels and defaults to REPEATABLE READ. The differences between MVCC (Multi-Version Concurrency Control) and locking -based implementations can also show up in corner cases, so it's something you definitely want to look at whenever examing a database engine. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
Richard: Actually No, process A will not acquire the reserved lock. It failes returning a sqlite_busy, and must perform a rollback. Even if Process B commits. Process A will get a sqlite_busy, forcing you to rollback. In order for the application to be correct the entire transaction must be restarted. The select would be run again, returning process B's correct value of (10,000 + 1000) = 11,000 ; A simpler/cleaner implementation might be: begin immediate --- Busy waiting would occur here. ie sqlite busy is returned. select update commit; Ken Ken <[EMAIL PROTECTED]> wrote: Richard Klein wrote: Dan Kennedy wrote: > On Wed, 2007-10-24 at 21:38 -0700, Richard Klein wrote: >> As I was thinking about the locking mechanism in SQLite 3, >> it occurred to me that the following race condition could >> occur. >> >> Imagine a joint bank account with a balance of $10,000. >> The wife makes a withdrawal of $1,000 at ATM 'A' (serviced >> by process A in the bank's mainframe), while at the same >> time the husband makes a deposit of $1,000 at ATM 'B' >> (serviced by process B). The steps performed by each >> process are as follows: >> >> Process A >> - >> BEGIN TRANSACTION; >> SELECT balance FROM accounts WHERE accountId = '123-45-6789'; >> UPDATE accounts SET balance = >> WHERE accountId = '123-45-6789'; >> COMMIT; >> >> Process B >> - >> BEGIN TRANSACTION; >> SELECT balance FROM accounts WHERE accountId = '123-45-6789'; >> UPDATE accounts SET balance = >> WHERE accountId = '123-45-6789'; >> COMMIT; >> >> Both processes open the accounts database, obtain SHARED >> locks, and proceed at about the same pace. Process A >> updates her local cache with a new balance of $900, while >> process B updates his local cache with a new balance of >> $11,000. > > Evaluating the UPDATE requires a RESERVED lock on the database > file. Since only one process can hold the RESERVED lock, this > particular scenario cannot occur. One or other of the updates > will fail with SQLITE_BUSY. > > Dan. I went over the documentation again, and it appears that you are correct: A process wanting to do an UPDATE must first acquire a RESERVED lock. However, I believe there is still a race condition. Assume that process B is the first to reach the UPDATE statement, and so process B gets the RESERVED lock (which he later escalates to PENDING and EXCLUSIVE). Then process A is stuck in a busy wait until process B commits and releases his locks. At this process A acquires the RESERVED lock and does *her* UPDATE, but she does it using the old, now *stale*, value of the account balance ($10,000). That is, she computes the new balance as ($10,000 - $1,000) = $9,000, which is *wrong*. She needs to *re-execute* her SELECT statement to pick up the new balance of $11,000. Or, in general: She needs to start her transaction over from the beginning. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] - I submit that there is no race condition present. Merely a progamming error. BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT; This is a comman and naive assumption that the balance selected will remain consistent. Even in other DB's such as oracle the problem described would persist. Two my knowledge there are two solutions. 1: Don't programatically put the balance into a variable. BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = balance - WHERE accountId = '123-45-6789'; COMMIT; 2: Lock the row with the select statement, this requires additional syntax, which sqlite does not support. BEGIN TRANSACTION; SELECT balance FOR UPDATE FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT;
Re: [sqlite] Re[sqlite] garding software of SQlite2.1
[Default] On Thu, 25 Oct 2007 21:04:44 -0700 (PDT), Vijaya Lakshmi <[EMAIL PROTECTED]> wrote: >Hi all, > I need small help regarding software .Acutually my application was >developed in SQLite2.1 by some body now i need SQLite2.1 version.Actually I >found SQLite3 version but by using this software i am unable to run my >application.please let me know from which site i can get SQLite2.1 version >software. >thank for ur help in advance. > >vijaya http://www.sqlite.org/download.html Scroll to: Historical Binaries And Source Code You will find 2.8.17 there. 2.8.17 is outdated but stable. You should try 2.8.17, 2.1 is really obsolete. See http://www.sqlite.org/cvstrac/timeline for changes, at the bottom you can specify what you want to see. If 2.8.17 really is not an option for you, you'll have to try to get an earlier version from CVS, or try to download an earlier version by specifying the version you need by hand, using the same naming scheme as the versions listed on the download page. Often the older files are still there, just not listed anymore on the download page. Upgrading to 3.5.1 might be less difficult than you first think, and v2 databases can usually be converted easily to v3 with: sqlite2 yourv2.db .dump | sqlite3 yourv3.db HTH -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Very poor performance on an INSERT command
Given Johns suggestion of faster disk: a 15k disk should get 125 commits /second. a 10k disk should get 83 commits/ second a 7200 gets 60 commits/second I wonder what the impact of the varying filesystem configurations would have on sqlite commit performance ? Say reiser, ext3 (journaled/ordered), etc.. Maybe you have different filesystems. What about raid devices? Ken Fabio Durieux Lopes <[EMAIL PROTECTED]> wrote: Hmm, GW0 (RHE3, the fast one) does 190-300 inserts/second. GW2 does around 15 inserts/second. I'm gonna try to commit every 1000 records and I'll be back with results. Thanks! On 26 Oct 2007 at 17:02, [EMAIL PROTECTED] wrote: > "Fabio Durieux Lopes" wrote: > > > > I've seen people saying one should use transactions > > [B]ut my question is: If I'm using the same binary on > > both computers shouldn't I get similar performances on > > both computers? > > > > INSERT is very fast in SQLite. What is slow is not > the INSERT but the implied COMMIT that occurs after > each INSERT if you do not have an explicit transaction. > The COMMIT does not return until all data has been > safely written to the disk platter. This typically > takes two complete rotations of the disk platter, which > means you can do no more than about 60 COMMITs per > second on your average computer. In contrast, you > should be able to do 6 INSERTs/second. > > SQLite uses the fsync() system call to make sure > data has reached the disk before continuing. But > fsync() is busted on some implementations. On > some systems, fsync() is a no-op. This certainly > makes it run a lot faster, but the downside is that > the data does not necessarly reach the disk surface > when SQLite thinks it does, and so if you lose power, > your data might get corrupted. > > I have not heard of problems with fsync() on RHE3. > But perhaps fsync() is disabled on that OS. Or > perhaps fsync() is disabled by the particular disk > controller you are using. Who knows. > > This is certain: By the laws of physics you cannot > do more than about 60 transactions per second on a > 7200 RPM disk drive. If you are seeing more than > that, then something is wrong with your system and > you will likely corrupt your databases if you lose > power. > > -- > D. Richard Hipp > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > -- Fabio Durieux Lopes Desenvolvimento Suntech Telecom Solutions www.suntech.com.br Office: +55 48 3234 0107 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
That makes sense given SERIALIZABLE transactions. The entire trasaction will require rollback since it gets a sqlite_busy, Not just the update. This also has the effect of invalidating the data selected within the transaction that performs the rollback. Oracle defaults to Read Committed. But you may set a transaction to serailazable as well. Sorry for my confusion and thanks for the clarification. Ken Ken wrote: > > BEGIN TRANSACTION; > SELECT balance FROM accounts WHERE accountId = '123-45-6789'; > UPDATE accounts SET balance = > WHERE accountId = '123-45-6789'; > COMMIT; > > This is a comman and naive assumption that the balance selected > will remain consistent. > Actually, SQLite does provide this guarantee. Nothing in the database will change during a transaction, except for changes caused by INSERT, UPDATE, and DELETE statements that occur within the transaction itself. It is not possible for another process to modify the value of the "balance" in between the SELECT and the UPDATE in the SQL above. This is true of SQLite because isolation in SQLite is "SERIALIZABLE". This is the highest level of isolate provided by SQL. Most client/server database engines by default implement "READ COMMITTED". The value of "balance" might change between the SELECT and the UPDATE in MySQL, for example. (I'm less clear about what happens in PostgreSQL and Oracle. The point is that your mileage may vary so be cautious.) But SQLite gets this right. Transactions are fully serializable, which means they appear as if the entire transaction happens instanteously with no chance for outside processes to change values in the middle of a transaction. -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Very poor performance on an INSERT command
Sent a Commit every 1000 records and now I have 1600-2000 inserts per second. Still don't know what was wrong first, but it's working now. Thanks everybody! On 26 Oct 2007 at 17:02, [EMAIL PROTECTED] wrote: > "Fabio Durieux Lopes" <[EMAIL PROTECTED]> wrote: > > > > I've seen people saying one should use transactions > > [B]ut my question is: If I'm using the same binary on > > both computers shouldn't I get similar performances on > > both computers? > > > > INSERT is very fast in SQLite. What is slow is not > the INSERT but the implied COMMIT that occurs after > each INSERT if you do not have an explicit transaction. > The COMMIT does not return until all data has been > safely written to the disk platter. This typically > takes two complete rotations of the disk platter, which > means you can do no more than about 60 COMMITs per > second on your average computer. In contrast, you > should be able to do 6 INSERTs/second. > > SQLite uses the fsync() system call to make sure > data has reached the disk before continuing. But > fsync() is busted on some implementations. On > some systems, fsync() is a no-op. This certainly > makes it run a lot faster, but the downside is that > the data does not necessarly reach the disk surface > when SQLite thinks it does, and so if you lose power, > your data might get corrupted. > > I have not heard of problems with fsync() on RHE3. > But perhaps fsync() is disabled on that OS. Or > perhaps fsync() is disabled by the particular disk > controller you are using. Who knows. > > This is certain: By the laws of physics you cannot > do more than about 60 transactions per second on a > 7200 RPM disk drive. If you are seeing more than > that, then something is wrong with your system and > you will likely corrupt your databases if you lose > power. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > -- Fabio Durieux Lopes Desenvolvimento Suntech Telecom Solutions www.suntech.com.br Office: +55 48 3234 0107 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Very poor performance on an INSERT command
Not sure, but 10K or 15K.
On 26 Oct 2007 at 12:39, John Stanton wrote:
> What are the rotational speeds of the disks?
>
> Fabio Durieux Lopes wrote:
> > Hmmm, let me see, both machines are DELL machines and with very
> > similar specs. If any, I'd say that the new hardware should run
> > faster. And the performance problem is way slower. GW0 runs for 8
> > minutes, while GW2 was running it for 30 minutes when I decided to
> > interrupt it.
> >
> > I also made a littleprogram that would read my text file an then
> > copy it to another file. As I expected it did instantaneously (on
> > GW2). The text file is around 3MB. I also used linux "dd" to create a
> > 1 GB file and I didn't take too long. Any test I should do to check
> > if I have a HD problem? I used a software from DELL to search for
> > hardware problems and didn't find anything.
> >
> > Thanks!
> > On 26 Oct 2007 at 12:01, John Stanton wrote:
> >
> >
> >>Your experiment gives you the answer to your question. You are running
> >>the same software and therefore you are measuring the differences in the
> >>disk and disk controller function between the two platforms. Sqlite's
> >>ACID capability is very much dependent upon the disk hardware.
> >>
> >>Fabio Durieux Lopes wrote:
> >>
> >>> Ok, I have a performance problem with SQLITE. First of all I'd like
> >>>to say that Ihave searched the archives and other sites too but
> >>>didn't find a problem like mine.
> >>>
> >>> Recently I made a software that got records from a remote ORACLE
> >>>database, created a local SQLITE database and then inserted those
> >>>records in the local database. All worked fine while developing and
> >>>testing, but when I moved it to the computer where it should run I
> >>>found it was way too slow to create the database. Did some testing
> >>>and haven't found network, hard disk or memory problems.
> >>>
> >>> This is my scenario now:
> >>>GW0 - 2 32 bit INTEL HyperThreading Processors 1 GB RAM
> >>> RED HAT ENTERPRISE 3 kernel 2.4.21-4.ELsmp
> >>> Good performance
> >>>
> >>> GW2 - 2 32 bit INTEL Dual core Processors 4 GB RAM
> >>> RED HAT ENTERPRISE 4 kernel 2.6.9-5.ELsmp
> >>> Poor performance
> >>>
> >>> My software is slow on GW2 but fast on GW0. Same binary.
> >>>I have reinstalled sqlite3 on GW2, copied sqlite3 binaries and
> >>>libraries from GW0 to GW2 but didn't succeed.
> >>>
> >>> I also made a little test to read records from a text file and
> >>>insert in a DB. Same thing, slow on GW2, fast on GW0. Note that I
> >>>used the same binary on both computers.
> >>>
> >>> The table I'm using looks like this:
> >>> CREATE TABLE name (col1 TEXT, col2 TEXT, col3 TEXT);
> >>>
> >>> and I insert like this:
> >>> INSERT INTO name VALUES ('value1', 'value2', 'value3');
> >>>
> >>> I've seen people saying one should use transactions or to bind
> >>>variables and some syncronize stuff, but my question is: If I'm using
> >>>the same binary on both computers shouldn't I get similar
> >>>performances on both computers?
> >>>
> >>> Anyone has any idea of what might be wrong?
> >>>
> >>> Thanks in advance and sorry for the long mail.
> >>>
> >>>--
> >>>Fabio Durieux Lopes
> >>>Desenvolvimento
> >>>Suntech Telecom Solutions
> >>>www.suntech.com.br
> >>>Office: +55 48 3234 0107
> >>>
> >>>-
> >>>To unsubscribe, send email to [EMAIL PROTECTED]
> >>>-
> >>>
> >>
> >>
> >>-
> >>To unsubscribe, send email to [EMAIL PROTECTED]
> >>-
> >
> >
> >
> > --
> > Fabio Durieux Lopes
> > Desenvolvimento
> > Suntech Telecom Solutions
> > www.suntech.com.br
> > Office: +55 48 3234 0107
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
> >
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
--
Fabio Durieux Lopes
Desenvolvimento
Suntech Telecom Solutions
www.suntech.com.br
Office: +55 48 3234 0107
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Very poor performance on an INSERT command
What are the rotational speeds of the disks?
Fabio Durieux Lopes wrote:
Hmmm, let me see, both machines are DELL machines and with very
similar specs. If any, I'd say that the new hardware should run
faster. And the performance problem is way slower. GW0 runs for 8
minutes, while GW2 was running it for 30 minutes when I decided to
interrupt it.
I also made a littleprogram that would read my text file an then
copy it to another file. As I expected it did instantaneously (on
GW2). The text file is around 3MB. I also used linux "dd" to create a
1 GB file and I didn't take too long. Any test I should do to check
if I have a HD problem? I used a software from DELL to search for
hardware problems and didn't find anything.
Thanks!
On 26 Oct 2007 at 12:01, John Stanton wrote:
Your experiment gives you the answer to your question. You are running
the same software and therefore you are measuring the differences in the
disk and disk controller function between the two platforms. Sqlite's
ACID capability is very much dependent upon the disk hardware.
Fabio Durieux Lopes wrote:
Ok, I have a performance problem with SQLITE. First of all I'd like
to say that Ihave searched the archives and other sites too but
didn't find a problem like mine.
Recently I made a software that got records from a remote ORACLE
database, created a local SQLITE database and then inserted those
records in the local database. All worked fine while developing and
testing, but when I moved it to the computer where it should run I
found it was way too slow to create the database. Did some testing
and haven't found network, hard disk or memory problems.
This is my scenario now:
GW0 - 2 32 bit INTEL HyperThreading Processors 1 GB RAM
RED HAT ENTERPRISE 3 kernel 2.4.21-4.ELsmp
Good performance
GW2 - 2 32 bit INTEL Dual core Processors 4 GB RAM
RED HAT ENTERPRISE 4 kernel 2.6.9-5.ELsmp
Poor performance
My software is slow on GW2 but fast on GW0. Same binary.
I have reinstalled sqlite3 on GW2, copied sqlite3 binaries and
libraries from GW0 to GW2 but didn't succeed.
I also made a little test to read records from a text file and
insert in a DB. Same thing, slow on GW2, fast on GW0. Note that I
used the same binary on both computers.
The table I'm using looks like this:
CREATE TABLE name (col1 TEXT, col2 TEXT, col3 TEXT);
and I insert like this:
INSERT INTO name VALUES ('value1', 'value2', 'value3');
I've seen people saying one should use transactions or to bind
variables and some syncronize stuff, but my question is: If I'm using
the same binary on both computers shouldn't I get similar
performances on both computers?
Anyone has any idea of what might be wrong?
Thanks in advance and sorry for the long mail.
--
Fabio Durieux Lopes
Desenvolvimento
Suntech Telecom Solutions
www.suntech.com.br
Office: +55 48 3234 0107
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
--
Fabio Durieux Lopes
Desenvolvimento
Suntech Telecom Solutions
www.suntech.com.br
Office: +55 48 3234 0107
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Very poor performance on an INSERT command
An obvious improvement is to use 15,000 RPM disks.
Fabio Durieux Lopes wrote:
No, it is definitely local.
Does anyone know if theres any kind of hardware/os spec that may
influence sqlite performance?
On 26 Oct 2007 at 16:53, Renaud HUILLET wrote:
My 2 cents:
Could it be that on the new machine, your sqlite file is no longer local ?
If you access it through NFS, you get some overhead .
Renaud
From: [EMAIL PROTECTED]
To: sqlite-users@sqlite.org
Date: Fri, 26 Oct 2007 14:32:37 -0200
Subject: [sqlite] Very poor performance on an INSERT command
Ok, I have a performance problem with SQLITE. First of all I'd like
to say that Ihave searched the archives and other sites too but
didn't find a problem like mine.
Recently I made a software that got records from a remote ORACLE
database, created a local SQLITE database and then inserted those
records in the local database. All worked fine while developing and
testing, but when I moved it to the computer where it should run I
found it was way too slow to create the database. Did some testing
and haven't found network, hard disk or memory problems.
This is my scenario now:
GW0 - 2 32 bit INTEL HyperThreading Processors 1 GB RAM
RED HAT ENTERPRISE 3 kernel 2.4.21-4.ELsmp
Good performance
GW2 - 2 32 bit INTEL Dual core Processors 4 GB RAM
RED HAT ENTERPRISE 4 kernel 2.6.9-5.ELsmp
Poor performance
My software is slow on GW2 but fast on GW0. Same binary.
I have reinstalled sqlite3 on GW2, copied sqlite3 binaries and
libraries from GW0 to GW2 but didn't succeed.
I also made a little test to read records from a text file and
insert in a DB. Same thing, slow on GW2, fast on GW0. Note that I
used the same binary on both computers.
The table I'm using looks like this:
CREATE TABLE name (col1 TEXT, col2 TEXT, col3 TEXT);
and I insert like this:
INSERT INTO name VALUES ('value1', 'value2', 'value3');
I've seen people saying one should use transactions or to bind
variables and some syncronize stuff, but my question is: If I'm using
the same binary on both computers shouldn't I get similar
performances on both computers?
Anyone has any idea of what might be wrong?
Thanks in advance and sorry for the long mail.
--
Fabio Durieux Lopes
Desenvolvimento
Suntech Telecom Solutions
www.suntech.com.br
Office: +55 48 3234 0107
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
_
Discover the new Windows Vista
http://search.msn.com/results.aspx?q=windows+vista=en-US=QBRE
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
--
Fabio Durieux Lopes
Desenvolvimento
Suntech Telecom Solutions
www.suntech.com.br
Office: +55 48 3234 0107
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Very poor performance on an INSERT command
Hmm, GW0 (RHE3, the fast one) does 190-300 inserts/second. GW2 does around 15 inserts/second. I'm gonna try to commit every 1000 records and I'll be back with results. Thanks! On 26 Oct 2007 at 17:02, [EMAIL PROTECTED] wrote: > "Fabio Durieux Lopes" <[EMAIL PROTECTED]> wrote: > > > > I've seen people saying one should use transactions > > [B]ut my question is: If I'm using the same binary on > > both computers shouldn't I get similar performances on > > both computers? > > > > INSERT is very fast in SQLite. What is slow is not > the INSERT but the implied COMMIT that occurs after > each INSERT if you do not have an explicit transaction. > The COMMIT does not return until all data has been > safely written to the disk platter. This typically > takes two complete rotations of the disk platter, which > means you can do no more than about 60 COMMITs per > second on your average computer. In contrast, you > should be able to do 6 INSERTs/second. > > SQLite uses the fsync() system call to make sure > data has reached the disk before continuing. But > fsync() is busted on some implementations. On > some systems, fsync() is a no-op. This certainly > makes it run a lot faster, but the downside is that > the data does not necessarly reach the disk surface > when SQLite thinks it does, and so if you lose power, > your data might get corrupted. > > I have not heard of problems with fsync() on RHE3. > But perhaps fsync() is disabled on that OS. Or > perhaps fsync() is disabled by the particular disk > controller you are using. Who knows. > > This is certain: By the laws of physics you cannot > do more than about 60 transactions per second on a > 7200 RPM disk drive. If you are seeing more than > that, then something is wrong with your system and > you will likely corrupt your databases if you lose > power. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > -- Fabio Durieux Lopes Desenvolvimento Suntech Telecom Solutions www.suntech.com.br Office: +55 48 3234 0107 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Very poor performance on an INSERT command
Hmmm, let me see, both machines are DELL machines and with very
similar specs. If any, I'd say that the new hardware should run
faster. And the performance problem is way slower. GW0 runs for 8
minutes, while GW2 was running it for 30 minutes when I decided to
interrupt it.
I also made a littleprogram that would read my text file an then
copy it to another file. As I expected it did instantaneously (on
GW2). The text file is around 3MB. I also used linux "dd" to create a
1 GB file and I didn't take too long. Any test I should do to check
if I have a HD problem? I used a software from DELL to search for
hardware problems and didn't find anything.
Thanks!
On 26 Oct 2007 at 12:01, John Stanton wrote:
> Your experiment gives you the answer to your question. You are running
> the same software and therefore you are measuring the differences in the
> disk and disk controller function between the two platforms. Sqlite's
> ACID capability is very much dependent upon the disk hardware.
>
> Fabio Durieux Lopes wrote:
> > Ok, I have a performance problem with SQLITE. First of all I'd like
> > to say that Ihave searched the archives and other sites too but
> > didn't find a problem like mine.
> >
> > Recently I made a software that got records from a remote ORACLE
> > database, created a local SQLITE database and then inserted those
> > records in the local database. All worked fine while developing and
> > testing, but when I moved it to the computer where it should run I
> > found it was way too slow to create the database. Did some testing
> > and haven't found network, hard disk or memory problems.
> >
> >This is my scenario now:
> > GW0 - 2 32 bit INTEL HyperThreading Processors 1 GB RAM
> > RED HAT ENTERPRISE 3 kernel 2.4.21-4.ELsmp
> > Good performance
> >
> > GW2 - 2 32 bit INTEL Dual core Processors 4 GB RAM
> > RED HAT ENTERPRISE 4 kernel 2.6.9-5.ELsmp
> > Poor performance
> >
> > My software is slow on GW2 but fast on GW0. Same binary.
> > I have reinstalled sqlite3 on GW2, copied sqlite3 binaries and
> > libraries from GW0 to GW2 but didn't succeed.
> >
> > I also made a little test to read records from a text file and
> > insert in a DB. Same thing, slow on GW2, fast on GW0. Note that I
> > used the same binary on both computers.
> >
> > The table I'm using looks like this:
> > CREATE TABLE name (col1 TEXT, col2 TEXT, col3 TEXT);
> >
> > and I insert like this:
> > INSERT INTO name VALUES ('value1', 'value2', 'value3');
> >
> > I've seen people saying one should use transactions or to bind
> > variables and some syncronize stuff, but my question is: If I'm using
> > the same binary on both computers shouldn't I get similar
> > performances on both computers?
> >
> > Anyone has any idea of what might be wrong?
> >
> > Thanks in advance and sorry for the long mail.
> >
> > --
> > Fabio Durieux Lopes
> > Desenvolvimento
> > Suntech Telecom Solutions
> > www.suntech.com.br
> > Office: +55 48 3234 0107
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
> >
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
--
Fabio Durieux Lopes
Desenvolvimento
Suntech Telecom Solutions
www.suntech.com.br
Office: +55 48 3234 0107
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Very poor performance on an INSERT command
"Fabio Durieux Lopes" <[EMAIL PROTECTED]> wrote: > > I've seen people saying one should use transactions > [B]ut my question is: If I'm using the same binary on > both computers shouldn't I get similar performances on > both computers? > INSERT is very fast in SQLite. What is slow is not the INSERT but the implied COMMIT that occurs after each INSERT if you do not have an explicit transaction. The COMMIT does not return until all data has been safely written to the disk platter. This typically takes two complete rotations of the disk platter, which means you can do no more than about 60 COMMITs per second on your average computer. In contrast, you should be able to do 6 INSERTs/second. SQLite uses the fsync() system call to make sure data has reached the disk before continuing. But fsync() is busted on some implementations. On some systems, fsync() is a no-op. This certainly makes it run a lot faster, but the downside is that the data does not necessarly reach the disk surface when SQLite thinks it does, and so if you lose power, your data might get corrupted. I have not heard of problems with fsync() on RHE3. But perhaps fsync() is disabled on that OS. Or perhaps fsync() is disabled by the particular disk controller you are using. Who knows. This is certain: By the laws of physics you cannot do more than about 60 transactions per second on a 7200 RPM disk drive. If you are seeing more than that, then something is wrong with your system and you will likely corrupt your databases if you lose power. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Very poor performance on an INSERT command
Your experiment gives you the answer to your question. You are running
the same software and therefore you are measuring the differences in the
disk and disk controller function between the two platforms. Sqlite's
ACID capability is very much dependent upon the disk hardware.
Fabio Durieux Lopes wrote:
Ok, I have a performance problem with SQLITE. First of all I'd like
to say that Ihave searched the archives and other sites too but
didn't find a problem like mine.
Recently I made a software that got records from a remote ORACLE
database, created a local SQLITE database and then inserted those
records in the local database. All worked fine while developing and
testing, but when I moved it to the computer where it should run I
found it was way too slow to create the database. Did some testing
and haven't found network, hard disk or memory problems.
This is my scenario now:
GW0 - 2 32 bit INTEL HyperThreading Processors 1 GB RAM
RED HAT ENTERPRISE 3 kernel 2.4.21-4.ELsmp
Good performance
GW2 - 2 32 bit INTEL Dual core Processors 4 GB RAM
RED HAT ENTERPRISE 4 kernel 2.6.9-5.ELsmp
Poor performance
My software is slow on GW2 but fast on GW0. Same binary.
I have reinstalled sqlite3 on GW2, copied sqlite3 binaries and
libraries from GW0 to GW2 but didn't succeed.
I also made a little test to read records from a text file and
insert in a DB. Same thing, slow on GW2, fast on GW0. Note that I
used the same binary on both computers.
The table I'm using looks like this:
CREATE TABLE name (col1 TEXT, col2 TEXT, col3 TEXT);
and I insert like this:
INSERT INTO name VALUES ('value1', 'value2', 'value3');
I've seen people saying one should use transactions or to bind
variables and some syncronize stuff, but my question is: If I'm using
the same binary on both computers shouldn't I get similar
performances on both computers?
Anyone has any idea of what might be wrong?
Thanks in advance and sorry for the long mail.
--
Fabio Durieux Lopes
Desenvolvimento
Suntech Telecom Solutions
www.suntech.com.br
Office: +55 48 3234 0107
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Very poor performance on an INSERT command
No, it is definitely local.
Does anyone know if theres any kind of hardware/os spec that may
influence sqlite performance?
On 26 Oct 2007 at 16:53, Renaud HUILLET wrote:
>
> My 2 cents:
> Could it be that on the new machine, your sqlite file is no longer local ?
> If you access it through NFS, you get some overhead .
>
> Renaud
>
> > From: [EMAIL PROTECTED]
> > To: sqlite-users@sqlite.org
> > Date: Fri, 26 Oct 2007 14:32:37 -0200
> > Subject: [sqlite] Very poor performance on an INSERT command
> >
> > Ok, I have a performance problem with SQLITE. First of all I'd like
> > to say that Ihave searched the archives and other sites too but
> > didn't find a problem like mine.
> >
> > Recently I made a software that got records from a remote ORACLE
> > database, created a local SQLITE database and then inserted those
> > records in the local database. All worked fine while developing and
> > testing, but when I moved it to the computer where it should run I
> > found it was way too slow to create the database. Did some testing
> > and haven't found network, hard disk or memory problems.
> >
> > This is my scenario now:
> > GW0 - 2 32 bit INTEL HyperThreading Processors 1 GB RAM
> > RED HAT ENTERPRISE 3 kernel 2.4.21-4.ELsmp
> > Good performance
> >
> > GW2 - 2 32 bit INTEL Dual core Processors 4 GB RAM
> > RED HAT ENTERPRISE 4 kernel 2.6.9-5.ELsmp
> > Poor performance
> >
> > My software is slow on GW2 but fast on GW0. Same binary.
> > I have reinstalled sqlite3 on GW2, copied sqlite3 binaries and
> > libraries from GW0 to GW2 but didn't succeed.
> >
> > I also made a little test to read records from a text file and
> > insert in a DB. Same thing, slow on GW2, fast on GW0. Note that I
> > used the same binary on both computers.
> >
> > The table I'm using looks like this:
> > CREATE TABLE name (col1 TEXT, col2 TEXT, col3 TEXT);
> >
> > and I insert like this:
> > INSERT INTO name VALUES ('value1', 'value2', 'value3');
> >
> > I've seen people saying one should use transactions or to bind
> > variables and some syncronize stuff, but my question is: If I'm using
> > the same binary on both computers shouldn't I get similar
> > performances on both computers?
> >
> > Anyone has any idea of what might be wrong?
> >
> > Thanks in advance and sorry for the long mail.
> >
> > --
> > Fabio Durieux Lopes
> > Desenvolvimento
> > Suntech Telecom Solutions
> > www.suntech.com.br
> > Office: +55 48 3234 0107
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
> >
>
> _
> Discover the new Windows Vista
> http://search.msn.com/results.aspx?q=windows+vista=en-US=QBRE
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
--
Fabio Durieux Lopes
Desenvolvimento
Suntech Telecom Solutions
www.suntech.com.br
Office: +55 48 3234 0107
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Very poor performance on an INSERT command
My 2 cents:
Could it be that on the new machine, your sqlite file is no longer local ?
If you access it through NFS, you get some overhead .
Renaud
> From: [EMAIL PROTECTED]
> To: sqlite-users@sqlite.org
> Date: Fri, 26 Oct 2007 14:32:37 -0200
> Subject: [sqlite] Very poor performance on an INSERT command
>
> Ok, I have a performance problem with SQLITE. First of all I'd like
> to say that Ihave searched the archives and other sites too but
> didn't find a problem like mine.
>
> Recently I made a software that got records from a remote ORACLE
> database, created a local SQLITE database and then inserted those
> records in the local database. All worked fine while developing and
> testing, but when I moved it to the computer where it should run I
> found it was way too slow to create the database. Did some testing
> and haven't found network, hard disk or memory problems.
>
> This is my scenario now:
> GW0 - 2 32 bit INTEL HyperThreading Processors 1 GB RAM
> RED HAT ENTERPRISE 3 kernel 2.4.21-4.ELsmp
> Good performance
>
> GW2 - 2 32 bit INTEL Dual core Processors 4 GB RAM
> RED HAT ENTERPRISE 4 kernel 2.6.9-5.ELsmp
> Poor performance
>
> My software is slow on GW2 but fast on GW0. Same binary.
> I have reinstalled sqlite3 on GW2, copied sqlite3 binaries and
> libraries from GW0 to GW2 but didn't succeed.
>
> I also made a little test to read records from a text file and
> insert in a DB. Same thing, slow on GW2, fast on GW0. Note that I
> used the same binary on both computers.
>
> The table I'm using looks like this:
> CREATE TABLE name (col1 TEXT, col2 TEXT, col3 TEXT);
>
> and I insert like this:
> INSERT INTO name VALUES ('value1', 'value2', 'value3');
>
> I've seen people saying one should use transactions or to bind
> variables and some syncronize stuff, but my question is: If I'm using
> the same binary on both computers shouldn't I get similar
> performances on both computers?
>
> Anyone has any idea of what might be wrong?
>
> Thanks in advance and sorry for the long mail.
>
> --
> Fabio Durieux Lopes
> Desenvolvimento
> Suntech Telecom Solutions
> www.suntech.com.br
> Office: +55 48 3234 0107
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
_
Discover the new Windows Vista
http://search.msn.com/results.aspx?q=windows+vista=en-US=QBRE
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Very poor performance on an INSERT command
Ok, I have a performance problem with SQLITE. First of all I'd like
to say that Ihave searched the archives and other sites too but
didn't find a problem like mine.
Recently I made a software that got records from a remote ORACLE
database, created a local SQLITE database and then inserted those
records in the local database. All worked fine while developing and
testing, but when I moved it to the computer where it should run I
found it was way too slow to create the database. Did some testing
and haven't found network, hard disk or memory problems.
This is my scenario now:
GW0 - 2 32 bit INTEL HyperThreading Processors 1 GB RAM
RED HAT ENTERPRISE 3 kernel 2.4.21-4.ELsmp
Good performance
GW2 - 2 32 bit INTEL Dual core Processors 4 GB RAM
RED HAT ENTERPRISE 4 kernel 2.6.9-5.ELsmp
Poor performance
My software is slow on GW2 but fast on GW0. Same binary.
I have reinstalled sqlite3 on GW2, copied sqlite3 binaries and
libraries from GW0 to GW2 but didn't succeed.
I also made a little test to read records from a text file and
insert in a DB. Same thing, slow on GW2, fast on GW0. Note that I
used the same binary on both computers.
The table I'm using looks like this:
CREATE TABLE name (col1 TEXT, col2 TEXT, col3 TEXT);
and I insert like this:
INSERT INTO name VALUES ('value1', 'value2', 'value3');
I've seen people saying one should use transactions or to bind
variables and some syncronize stuff, but my question is: If I'm using
the same binary on both computers shouldn't I get similar
performances on both computers?
Anyone has any idea of what might be wrong?
Thanks in advance and sorry for the long mail.
--
Fabio Durieux Lopes
Desenvolvimento
Suntech Telecom Solutions
www.suntech.com.br
Office: +55 48 3234 0107
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Race condition -- fixed?
Ken <[EMAIL PROTECTED]> wrote: > > BEGIN TRANSACTION; > SELECT balance FROM accounts WHERE accountId = '123-45-6789'; > UPDATE accounts SET balance = > WHERE accountId = '123-45-6789'; > COMMIT; > > This is a comman and naive assumption that the balance selected > will remain consistent. > Actually, SQLite does provide this guarantee. Nothing in the database will change during a transaction, except for changes caused by INSERT, UPDATE, and DELETE statements that occur within the transaction itself. It is not possible for another process to modify the value of the "balance" in between the SELECT and the UPDATE in the SQL above. This is true of SQLite because isolation in SQLite is "SERIALIZABLE". This is the highest level of isolate provided by SQL. Most client/server database engines by default implement "READ COMMITTED". The value of "balance" might change between the SELECT and the UPDATE in MySQL, for example. (I'm less clear about what happens in PostgreSQL and Oracle. The point is that your mileage may vary so be cautious.) But SQLite gets this right. Transactions are fully serializable, which means they appear as if the entire transaction happens instanteously with no chance for outside processes to change values in the middle of a transaction. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
Richard Klein <[EMAIL PROTECTED]> wrote: Dan Kennedy wrote: > On Wed, 2007-10-24 at 21:38 -0700, Richard Klein wrote: >> As I was thinking about the locking mechanism in SQLite 3, >> it occurred to me that the following race condition could >> occur. >> >> Imagine a joint bank account with a balance of $10,000. >> The wife makes a withdrawal of $1,000 at ATM 'A' (serviced >> by process A in the bank's mainframe), while at the same >> time the husband makes a deposit of $1,000 at ATM 'B' >> (serviced by process B). The steps performed by each >> process are as follows: >> >> Process A >> - >> BEGIN TRANSACTION; >> SELECT balance FROM accounts WHERE accountId = '123-45-6789'; >> UPDATE accounts SET balance = >> WHERE accountId = '123-45-6789'; >> COMMIT; >> >> Process B >> - >> BEGIN TRANSACTION; >> SELECT balance FROM accounts WHERE accountId = '123-45-6789'; >> UPDATE accounts SET balance = >> WHERE accountId = '123-45-6789'; >> COMMIT; >> >> Both processes open the accounts database, obtain SHARED >> locks, and proceed at about the same pace. Process A >> updates her local cache with a new balance of $900, while >> process B updates his local cache with a new balance of >> $11,000. > > Evaluating the UPDATE requires a RESERVED lock on the database > file. Since only one process can hold the RESERVED lock, this > particular scenario cannot occur. One or other of the updates > will fail with SQLITE_BUSY. > > Dan. I went over the documentation again, and it appears that you are correct: A process wanting to do an UPDATE must first acquire a RESERVED lock. However, I believe there is still a race condition. Assume that process B is the first to reach the UPDATE statement, and so process B gets the RESERVED lock (which he later escalates to PENDING and EXCLUSIVE). Then process A is stuck in a busy wait until process B commits and releases his locks. At this process A acquires the RESERVED lock and does *her* UPDATE, but she does it using the old, now *stale*, value of the account balance ($10,000). That is, she computes the new balance as ($10,000 - $1,000) = $9,000, which is *wrong*. She needs to *re-execute* her SELECT statement to pick up the new balance of $11,000. Or, in general: She needs to start her transaction over from the beginning. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] - I submit that there is no race condition present. Merely a progamming error. BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT; This is a comman and naive assumption that the balance selected will remain consistent. Even in other DB's such as oracle the problem described would persist. Two my knowledge there are two solutions. 1: Don't programatically put the balance into a variable. BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = balance - WHERE accountId = '123-45-6789'; COMMIT; 2: Lock the row with the select statement, this requires additional syntax, which sqlite does not support. BEGIN TRANSACTION; SELECT balance FOR UPDATE FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT;
RE: [sqlite] Re: Some Questions Regarding Access To a SQLite Database By More Than One Process
PTHREAD_PROCESS_SHARED mutex type for system-wide scope is equivalent to the USYNC_PROCESS flag to mutex_init() in the Solaris API (see below). The object initialized with this attribute must be allocated in memory shared between processes, either in System V shared memory (see shmop(2)). or in memory mapped to a file (see mmap(2)). It is illegal to initialize the object this way and to not allocate it in such shared memory. You are indeed correct, I however, contend that if you have to write code to create shared process memory just to allocate a process wide mutex. Then a semaphor would be simpler. So in general a mutex is not process capable. Its only when its created in shared memory that its can be made process wide. Thanks for raising this point. I've learned something new. Regards, Ken James Dennett <[EMAIL PROTECTED]> wrote: > -Original Message- > From: Ken [mailto:[EMAIL PROTECTED] > Sent: Wednesday, October 24, 2007 1:22 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Re: Some Questions Regarding Access To a SQLite > Database By More Than One Process > > If you are using seperate processes then a mutex will not help since it is > local to a process. A semaphore could be used however. Many (maybe even most) platforms support mutexes which work across multiple processes. (Mutex is, of course, short for "mutual exclusion semaphore"; there's no a priori reason to assume that mutexes are process-local while other semaphores are not.) -- James - To unsubscribe, send email to [EMAIL PROTECTED] - James Dennett <[EMAIL PROTECTED]> wrote: > -Original Message- > From: Ken [mailto:[EMAIL PROTECTED] > Sent: Wednesday, October 24, 2007 1:22 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Re: Some Questions Regarding Access To a SQLite > Database By More Than One Process > > If you are using seperate processes then a mutex will not help since it is > local to a process. A semaphore could be used however. Many (maybe even most) platforms support mutexes which work across multiple processes. (Mutex is, of course, short for "mutual exclusion semaphore"; there's no a priori reason to assume that mutexes are process-local while other semaphores are not.) -- James - To unsubscribe, send email to [EMAIL PROTECTED] - James Dennett <[EMAIL PROTECTED]> wrote: > -Original Message- > From: Ken [mailto:[EMAIL PROTECTED] > Sent: Wednesday, October 24, 2007 1:22 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Re: Some Questions Regarding Access To a SQLite > Database By More Than One Process > > If you are using seperate processes then a mutex will not help since it is > local to a process. A semaphore could be used however. Many (maybe even most) platforms support mutexes which work across multiple processes. (Mutex is, of course, short for "mutual exclusion semaphore"; there's no a priori reason to assume that mutexes are process-local while other semaphores are not.) -- James - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Slightly off topic question - Launch another EXE within the same address space
Igor Tandetnik <[EMAIL PROTECTED]> wrote: Ingar Steinsland <[EMAIL PROTECTED]> wrote: I want to open a communication channel in program A. This program will, based upon input from the communication channel, launch either program B or C. Fine, no problem. But I want to let the communication stay open and also make it available for program B or C possibly through some sort of a callback function. Is this possible? No. You can however have B and C talk back to A, and have A relay data from the "communication channel" down to the spawned process. I guess that what I ask for is to be able to lauch an external program within the same address space as the launcher. I have never done this before. By definition, process == address space. You can't have two separate processes share address space: having their own address space is what makes them separate in the first place. Oops - wrong group. Please disregard Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Slightly off topic question - Launch another EXE within the same address space
Ingar Steinsland <[EMAIL PROTECTED]> wrote: I want to open a communication channel in program A. This program will, based upon input from the communication channel, launch either program B or C. Fine, no problem. But I want to let the communication stay open and also make it available for program B or C possibly through some sort of a callback function. Is this possible? No. You can however have B and C talk back to A, and have A relay data from the "communication channel" down to the spawned process. I guess that what I ask for is to be able to lauch an external program within the same address space as the launcher. I have never done this before. By definition, process == address space. You can't have two separate processes share address space: having their own address space is what makes them separate in the first place. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
