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
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
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]
-
[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]
-
