ry 14,
> 2011 5:04 PM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected
>
> On 14 Feb 2011, at 8:50pm, Black, Michael (IS) wrote:
> > And if you want to talk about data reliability...BACK UP YOUR DATA.
>
> An
On Mon, Feb 14, 2011 at 6:04 PM, Simon Slavin wrote:
>
> If the data is backed up when SQLite has the files closed, things are fine.
> The really hard part of this comes when you're working with systems that must
> be live at all times. Which is why you'll never see a bank use SQLite to
> mai
> I'll give you another failure point that most people never see or think of.
>
> I used to manage numerous Linux systems with RAID-5. One time I had a drive
> fail, the spare kicked in, and then during the rebuild a 2nd drive
> failed...hosing the RAID (i.e. two failed disks).
>
> The problem w
v...@bigfraud.org]
Sent: Monday, February 14, 2011 5:04 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected
On 14 Feb 2011, at 8:50pm, Black, Michael (IS) wrote:
> And if you want to talk about data reliability...BACK UP YOUR DATA.
On 14 Feb 2011, at 8:50pm, Black, Michael (IS) wrote:
> And if you want to talk about data reliability...BACK UP YOUR DATA.
And keep the backups off-site. And once a year try to actually restore one of
them. I've earned /lots/ of money from companies with backup systems that
claimed to work
On Mon, Feb 14, 2011 at 3:02 PM, Max Vlasov wrote:
> On Mon, Feb 14, 2011 at 8:42 PM, Jim Wilcoxson wrote:
>
>> On Mon, Feb 14, 2011 at 10:56 AM, Pavel Ivanov wrote:
>> >> So my question is, does it maintain the other 3 parts of ACID, so that
>> >> the database will never be in a corrupted state
On Mon, Feb 14, 2011 at 11:50 PM, Black, Michael (IS) <
michael.bla...@ngc.com> wrote:
> And if you want to talk about data reliability...BACK UP YOUR DATA.
> The likely failure points I can think of are:
> #1 Power supply (redundant supplies available)
> #2 Hard drive smoked (and your data is toa
te-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Max Vlasov [max.vla...@gmail.com]
Sent: Monday, February 14, 2011 2:02 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected
On Mon, Feb 14, 2011 at 8:42 PM, Jim
On Mon, Feb 14, 2011 at 8:42 PM, Jim Wilcoxson wrote:
> On Mon, Feb 14, 2011 at 10:56 AM, Pavel Ivanov wrote:
> >> So my question is, does it maintain the other 3 parts of ACID, so that
> >> the database will never be in a corrupted state after a power loss,
> >> even though it may be missing so
On Mon, Feb 14, 2011 at 10:56 AM, Pavel Ivanov wrote:
>> So my question is, does it maintain the other 3 parts of ACID, so that
>> the database will never be in a corrupted state after a power loss,
>> even though it may be missing some writes that were confirmed?
>
> Jim, I think the answer to yo
On Mon, Feb 14, 2011 at 7:48 PM, Jim Wilcoxson wrote:
> On Mon, Feb 14, 2011 at 10:56 AM, Pavel Ivanov wrote:
> >> So my question is, does it maintain the other 3 parts of ACID, so that
> >> the database will never be in a corrupted state after a power loss,
> >> even though it may be missing so
On 14 Feb 2011, at 4:48pm, Jim Wilcoxson wrote:
> I believe (though am not sure) that the drive can reorder individual
> sector/record writes between syncs however it likes, as long as it
> doesn't allow writes to cross a sync and also executes the sync groups
> (for lack of a better term - the g
On Mon, Feb 14, 2011 at 10:56 AM, Pavel Ivanov wrote:
>> So my question is, does it maintain the other 3 parts of ACID, so that
>> the database will never be in a corrupted state after a power loss,
>> even though it may be missing some writes that were confirmed?
>
> Jim, I think the answer to yo
On 14 Feb 2011, at 3:56pm, Pavel Ivanov wrote:
>> So my question is, does it maintain the other 3 parts of ACID, so that
>> the database will never be in a corrupted state after a power loss,
>> even though it may be missing some writes that were confirmed?
>
> Jim, I think the answer to your qu
> So my question is, does it maintain the other 3 parts of ACID, so that
> the database will never be in a corrupted state after a power loss,
> even though it may be missing some writes that were confirmed?
Jim, I think the answer to your question is already in Max's tests:
the USB drive is compl
On Mon, Feb 14, 2011 at 2:33 AM, Max Vlasov wrote:
>
> On Sun, Feb 13, 2011 at 8:53 PM, Jim Wilcoxson wrote:
>
> > > But I thought about how it would be possible to test this explanation .
> > I'm
> > > going to do some tests that works like this. The same id updating, but in
> > > the middle of
Also...here's another part of the benchmark which shows triggers aren't as evil
as I thought. Trigger for this example was 2X the manual update.
F:\>d:batch 50 1000 12
using wal mode
using update trigger
Sqlite Version: 3.7.5
Inserting 50 rows using a bulk of 1000
commits per second: 714
SOLVED!!
Marcus Grimm and I went back forth a number of times trying to figure out why
my benchmarks were so much faster than his.
Found it...
My SATA RAID setup had "Enable Advanced Performance" on by default (I had never
turned it on).
My secondary tests on an IDE drive showed similar perfor
On 14 Feb 2011, at 10:30am, Max Vlasov wrote:
> On Mon, Feb 14, 2011 at 12:05 PM, Simon Slavin wrote:
>
>> Can you expand upon your conclusion ? I like your test, and am interested
>> to know precisely where you think the lag is happening.
>
> Simon, the conclusion came from the assumption th
On Mon, Feb 14, 2011 at 12:05 PM, Simon Slavin wrote:
> > The second variation was just unplugging the cord but keeping the power
> > intact, so if it's drive that caches, it would end its operations
> > completely. This time the results were perfect, for example 4822 -> 4822,
> > and even 5371 -
On 14 Feb 2011, at 7:33am, Max Vlasov wrote:
> The first variation was similar to the one with bus-powered, this time I
> used hard button on the box that is equivalent to unplugging both connection
> and power. The difference for a single test was really impressive 5355 ->
> 4445 = -910. And whe
On Sun, Feb 13, 2011 at 8:53 PM, Jim Wilcoxson wrote:
> > But I thought about how it would be possible to test this explanation .
> I'm
> > going to do some tests that works like this. The same id updating, but in
> > the middle of 10,000 operation I will unplug the cord, the sqlite will
> say
>
On 13 Feb 2011, at 5:53pm, Jim Wilcoxson wrote:
> I also found this page, used to force a Linux system crash:
>
> http://evuraan.blogspot.com/2005/06/how-to-force-paniccrash-in-linux.html
>
> I seem to remember a post that SQLite commit/sync is tested with the kill
> command, but it seems like
On Sun, Feb 13, 2011 at 11:55 AM, Max Vlasov wrote:
> On Sun, Feb 13, 2011 at 5:31 PM, Jim Wilcoxson wrote:
>
> > On Sun, Feb 13, 2011 at 1:15 AM, Max Vlasov
> wrote:
> > > So the final results:
> > > - the db was always ok and contains the correct value (id=10001 for
> > initial
> > > 1).
> >
>But what I postulate is that you can't physically write *the same* record
>over and over more than 90 times per second on a 5400 rpm drive,
>unless the
>drive, OS, or filesystem implements something like wear-leveling,
>where the
>physical location of sectors is constantly changing.
It's still
On Sun, Feb 13, 2011 at 5:31 PM, Jim Wilcoxson wrote:
> On Sun, Feb 13, 2011 at 1:15 AM, Max Vlasov wrote:
> > So the final results:
> > - the db was always ok and contains the correct value (id=10001 for
> initial
> > 1).
> > - the speed was about 227 commits per second so very close to my syst
On Sun, Feb 13, 2011 at 7:07 AM, Jean-Christophe Deschamps wrote:
>
> >So if the drive is 5400 rpm, 227 is much more than 5400/60=90 and even if
> >it's 7200 (manufacturers sometimes upgrade drives inside portable hd
> >without
> >prior notice), it's still twice as much as 7200/60=120.
>
> 5400/6
On Sun, Feb 13, 2011 at 1:15 AM, Max Vlasov wrote:
> On Sat, Feb 12, 2011 at 7:11 PM, Jim Wilcoxson wrote:
>
> >
> >
> > Unless I'm missing something, SQLite has to update the first page of the
> > database on every commit, to update the change counter. Assuming you are
> > using rotating media
>So if the drive is 5400 rpm, 227 is much more than 5400/60=90 and even if
>it's 7200 (manufacturers sometimes upgrade drives inside portable hd
>without
>prior notice), it's still twice as much as 7200/60=120.
5400/60, 7200/60 ... those values rely on the assumption that
successive LBAs are ma
On Sat, Feb 12, 2011 at 7:11 PM, Jim Wilcoxson wrote:
>
>
> Unless I'm missing something, SQLite has to update the first page of the
> database on every commit, to update the change counter. Assuming you are
> using rotating media, that record can only be updated 120 times per second,
> maximum,
On Sun, Feb 13, 2011 at 12:27 AM, Marcus Grimm wrote:
> So my brain isn't that lasy, we count 3 syncs :-)
>
> When you turn journaling to off you will most likely
> see less syncs, probably 2 in your case. So that is all
> in line..
>
> Marcus
>
>
Marcus, you're right about general journalin
On February 12, 2011, Black, Michael (IS) wrote:
> Are you wrapping your data dump into a "BEGIN" "COMMIT"?
Yup.
> Also...you mention using a select...I assume you are testing first to see
> if you need to update or insert?
The select is not used when dumping data. The in memory data structure k
>>
>> Interesting, I did a test on a 7200 file and the best I could do was 50
>> commits per second (a simple base/table with only id, journalling off
>> and
>> no
>> extra code since the tool I use has "a repeated query" option with
>> accurate
>> timing). You mentioned 3 syncs per commit, but I t
> On Sat, Feb 12, 2011 at 9:48 PM, Marcus Grimm
> wrote:
>
>> > I should've realized it wasn't running this fast but the small 5000
>> record
>> > size got me.
>> > Test it yourself.
>> > I do have a 7200RPM drive. My 261.4 numer is still 2+X your
>> theoretical.
>>
>> I don't want to be a smart-a
> OK...I added your trigger example as option 8. And I had pasted the wrong
> version in my last email. My timings were correct. Your example also did
> sql_exec instead of using prepare so it will run slower.
Yes, but that should be marginal.
When I send my code the trigger version wasn't fin
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 02/12/2011 11:54 AM, Simon Slavin wrote:
> Compensating for this behaviour is a big part of what SQLite does in
> journaling.
SQLite and other journalling mechanisms depend on an operating system call
fsync that flushes their relevant data to th
On Sat, Feb 12, 2011 at 9:48 PM, Marcus Grimm wrote:
> > I should've realized it wasn't running this fast but the small 5000
> record
> > size got me.
> > Test it yourself.
> > I do have a 7200RPM drive. My 261.4 numer is still 2+X your theoretical.
>
> I don't want to be a smart-arse, but I stil
OK...I added your trigger example as option 8. And I had pasted the wrong
version in my last email. My timings were correct. Your example also did
sql_exec instead of using prepare so it will run slower.
I also made this compilable on Unix too.
On Unix my timing matches the run time and there
On 12 Feb 2011, at 4:11pm, Jim Wilcoxson wrote:
> I don't understand how you can do 360K commits per second if your system is
> actually doing "to the platter" writes on every commit. Can someone clue me
> in?
My field of expertise, I'm afraid. The answer is "Hard disks lie.".
Almost all hard
begin",NULL,NULL,NULL);
> }
> sqlite3_bind_int(stmt,1,n);
> rc = sqlite3_step(stmt);
> if (rc != SQLITE_DONE) {
>puts(sqlite3_errmsg(db));
> }
> sqlite3_reset(stmt);
> }
> sqlite3_exec(db,"commit",NULL,NULL,NULL);
> //printf("%.1f i
f("%.1f inserts per sec\n",nrec/((elapsed()-t1)/CLOCKS_PER_SEC));
printf("%.1f\n",nrec/((elapsed()-t1)/CLOCKS_PER_SEC));
sqlite3_close(db);
}
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
____________________
From: sqlite-users-boun...@sqlite.org [s
> On Sat, Feb 12, 2011 at 9:10 AM, Black, Michael (IS)
> > D:\SQLite>batch 5000 1
>> 360766.6 inserts per sec
>>
>
> Unless I'm missing something, SQLite has to update the first page of the
> database on every commit, to update the change counter. Assuming you are
> using rotating media, that reco
On Sat, Feb 12, 2011 at 9:10 AM, Black, Michael (IS) wrote:
> Here's a little benchmark program I wrote to test a super-simple
> one-integer insert to test sql speed and commit interval behavior.
>
> Running it on my machine (Windows XP64 8-core 3Ghz gcc (tdm64-1) 4.5.1) I
> go from 320M inserts
n Systems
Advanced Analytics Directorate
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Thomas Fjellstrom [tfjellst...@strangesoft.net]
Sent: Friday, February 11, 2011 9:50 PM
To: Teg
Cc: General Discussion of SQLite
On Fri, Feb 11, 2011 at 10:50 PM, Thomas Fjellstrom <
tfjellst...@strangesoft.net> wrote:
> I've seen numbers on the internet stating that sqlite is super fast, should
> be
> able to handle tens of thousands of inserts or updates in a second or two,
> even in sync mode. So I'm a bit stumped as to
On February 11, 2011, Teg wrote:
> Hello Thomas,
>
> I download off the net at between 400Mbps to 1 Gbps and have to pack
> the data away into a Sqlite DB. I use an intermediate buffer on disk
> and a background thread to write the data to the DB. Obviously, I
> couldn't keep up if the data was co
Hello Thomas,
I download off the net at between 400Mbps to 1 Gbps and have to pack
the data away into a Sqlite DB. I use an intermediate buffer on disk
and a background thread to write the data to the DB. Obviously, I
couldn't keep up if the data was continuous but, the lulls in between
the peak d
47 matches
Mail list logo
