Re: [sqlite] Slow Transaction Speed?

We are happy with the results using JFS on AIX and SUSE Linux. Also no concerns with EXT3 on various Linuxes. We have learned to avoid Windows. Christian Smith wrote: > On Wed, May 27, 2009 at 08:05:00AM -0400, pyt...@bdurham.com wrote: > >> >> In your experience, which Linux file system(s)

Re: [sqlite] Slow Transaction Speed?

On Wed, May 27, 2009 at 08:05:00AM -0400, pyt...@bdurham.com wrote: > Dr. Hipp, > > > Your OS and filesystem configuration have a big impact too. I've notice, > > for example, that transactions are really slow on RieserFS on Linux > > compared to Ext3. > > In your experience, which Linux file

Re: [sqlite] Slow Transaction Speed?

On Mon, Jun 01, 2009 at 03:09:46AM +0100, Simon Slavin wrote: > On 31 May 2009, at 11:56pm, John Stanton wrote: > > You will then understand the reason for absolute transactional > > integrity and why Sqlite must use fsync or similar and expects fsync > > to be a complete implementation which

Re: [sqlite] Slow Transaction Speed?

Microsoft has an interesting article on hard drive caches re: SQL Server: http://support.microsoft.com/kb/234656 "Many disk drives (SATA, ATA, SCSI and IDE based) contain onboard caches of 512 KB, 1 MB, and larger. Drive caches usually rely on a capacitor and not a battery-backed solution. These

Re: [sqlite] Slow Transaction Speed?

Simon Slavin wrote: > On 31 May 2009, at 11:56pm, John Stanton wrote: > > >> Try studying basic database theory and technology to get a better >> understanding of the problem. >> > > I have a pretty good understanding, I think. Plus 25 years experience. > Is it 23 years experience or 1

Re: [sqlite] Slow Transaction Speed?

On 1 Jun 2009, at 3:38am, Olaf Schmidt wrote: > Is fsync() somehow "messed up" on linux currently? Correct. Read the man page. It doesn't do what most people think it does. Nor does sync as the man page for sync explains. The only way, under most setups, to get all changes to the disk

Re: [sqlite] Slow Transaction Speed?

"Marcus Grimm" schrieb im Newsbeitrag news:4a1e6034.3030...@medcom-online.de... > just for anybody who is interested: > > I translated Jim's function into window code and added > a page of 1024 that will be written, instead of a single byte. > On my Win-XP system I got

Re: [sqlite] Slow Transaction Speed?

On 31 May 2009, at 11:56pm, John Stanton wrote: > Try studying basic database theory and technology to get a better > understanding of the problem. I have a pretty good understanding, I think. Plus 25 years experience. > You will then understand the reason for > absolute transactional

Re: [sqlite] Slow Transaction Speed?

Try studying basic database theory and technology to get a better understanding of the problem. You will then understand the reason for absolute transactional integrity and why Sqlite must use fsync or similar and expects fsync to be a complete implementation which ensures that each

Re: [sqlite] Slow Transaction Speed?

> > On 31 May 2009, at 9:24pm, Jay A. Kreibich wrote: > >> The whole point of a transaction is that once it >> returns "success" on a commit, it is committed to permanent storage, >> no matter what -- including crashes, power failures, and other major >> problems. > > My impression is that

Re: [sqlite] Slow Transaction Speed?

On 31 May 2009, at 9:24pm, Jay A. Kreibich wrote: > The whole point of a transaction is that once it > returns "success" on a commit, it is committed to permanent storage, > no matter what -- including crashes, power failures, and other major > problems. My impression is that COMMIT/ROLLBACK

Re: [sqlite] Slow Transaction Speed?

On Sun, May 31, 2009 at 06:38:45PM +0100, Simon Slavin scratched on the wall: > > On 31 May 2009, at 5:53pm, Jim Wilcoxson wrote: > > > the real point here is that Python and SQLite aren't doing real > > transactions > > But they /are/ real transactions. They are not ACID transactions. They

Re: [sqlite] Slow Transaction Speed?

On 31 May 2009, at 5:53pm, Jim Wilcoxson wrote: > the real point here is that Python and SQLite aren't doing real > transactions But they /are/ real transactions. You write rows, and read them back, and the values are there. Use SQL commands in the way they're meant to be used and the

Re: [sqlite] Slow Transaction Speed?

Simon, the real point here is that Python and SQLite aren't doing real transactions. What we should be seeing here is AT MOST 30 TPS with synchronous=normal, assuming SQLite does 3 hard disk syncs per commit as was described in an earlier post. On my old AMD box, the one where fsync() is broken,

Re: [sqlite] Slow Transaction Speed?

On 31 May 2009, at 4:59pm, Simon Slavin wrote: > Standard MacBookPro3,1 Core 2 Duo 2.4 GHz, OS 10.5.7, whatever hard > disk Apple puts in them. > Using SQLite version 3.4.0 (installed as part of the OS installation). > > SimonsMBP:Documents simon$ python --version > Python 2.5.1 > >

Re: [sqlite] Slow Transaction Speed?

On 31 May 2009, at 2:27pm, Jim Wilcoxson wrote: > g5:~ mac$ Python-2.6.1/python.exe dbsync.py > /Users/mac/Python-2.6.1/Lib/sqlite3/dbapi2.py:27: RuntimeWarning: > Python C API version mismatch for module _sqlite3: This Python has API > version 1013, module _sqlite3 has version 1012. > from

Re: [sqlite] Slow Transaction Speed?

Here's an update for the commit rate tester for Mac OSX. With the original version, using fsync(), OSX doesn't do real syncs because the fsync call isn't properly implemented (my opinion). Apple took a shortcut with fsync and only flushes OS buffers to the drive, but the drive is then free to

Re: [sqlite] Slow Transaction Speed?

I agree that adding this to the library, and making it accessible via a pragma command would be very useful. For example, pragma commitrate 1000 would test the commit rate of 1000 commits and return the results in transactions per second as a row. If I install my app on a client's machine, I

Re: [sqlite] Slow Transaction Speed?

> On Thu, May 28, 2009 at 03:12:55PM -0700, Allen Fowler scratched on the > wall: >> >> > just for anybody who is interested: >> >> > >> > I translated Jim's function into window code and added >> > a page of 1024 that will be written, instead of a single byte. >> > On my Win-XP system I got 55

Re: [sqlite] Slow Transaction Speed?

On Thu, May 28, 2009 at 03:12:55PM -0700, Allen Fowler scratched on the wall: > > > just for anybody who is interested: > > > > > I translated Jim's function into window code and added > > a page of 1024 that will be written, instead of a single byte. > > On my Win-XP system I got 55 TPS, much

Re: [sqlite] Slow Transaction Speed?

> just for anybody who is interested: > > I translated Jim's function into window code and added > a page of 1024 that will be written, instead of a single byte. > On my Win-XP system I got 55 TPS, much faster than sqlite > seems to write a page but that might be related to the > additional

Re: [sqlite] Slow Transaction Speed?

just for anybody who is interested: I translated Jim's function into window code and added a page of 1024 that will be written, instead of a single byte. On my Win-XP system I got 55 TPS, much faster than sqlite seems to write a page but that might be related to the additional overhead sqlite

Re: [sqlite] Slow Transaction Speed?

I should have mentioned that when running my C test program on Linux, with the hard drive cache enabled, it ran instantly. But it should have taken quite a while to do 3000 fsyncs. So this problem with defeating the hard drive cache is not an SQLite issue; it's a Linux issue. I also ran the

Re: [sqlite] Slow Transaction Speed?

I'm running on Linux with ext3 and just wrote a Python test program to insert rows into a table with a single column, no indexing, and doing a commit after each insert. When I first ran it, I got around 440 inserts per second, which is clearly impossible. This is a 7200rpm drive, so even if I

Re: [sqlite] Slow Transaction Speed?

Thanks Nick, good point. ahh yes, I've read about this somewhere... My extension is currently ".db", a quick check indicates that using ".abc" gives a slight speed improvement, maybe 10%. But that is allready very close to the variation I get between different test runs, so I'm not really sure

Re: [sqlite] Slow Transaction Speed?

Dr. Hipp, > Your OS and filesystem configuration have a big impact too. I've notice, for > example, that transactions are really slow on RieserFS on Linux compared to > Ext3. In your experience, which Linux file system(s) provide the high performance platform for SQLite? Which Linux file

Re: [sqlite] Slow Transaction Speed?

> my developemnt system is a Win XP, with of course NTFS, I > don't know which drive it has, I guess a standard 7200 rpm. > What file extension (i.e. the letters after the dot in the filename) do you give the database? I faintly recall there is a windows peculiarity with system restore or

Re: [sqlite] Slow Transaction Speed?

thank you for the hint, my current approach to copy the database file, is even more restrictive -- it is encapsulated by an EXCLUSIVE transaction. ;) In my case it is unlikely that it will block anybody from using the server since it is a typical "working-hours" application and the backup will

Re: [sqlite] Slow Transaction Speed?

On Wed, May 27, 2009 at 12:51 PM, Marcus Grimm wrote: > Yes, I understood that this is unsafe and I'll not use it right now. > But my feeling is that it will be an option for the future. My databases > are not so big (say in megabyte range) and currently my server

Re: [sqlite] Slow Transaction Speed?

my developemnt system is a Win XP, with of course NTFS, I don't know which drive it has, I guess a standard 7200 rpm. I tried also on a Win 2000 and Vista, as expected Vista appears to be the slowest even this machine has the best hardware, theoretically. All measurements vary +- 20%. Just for

Re: [sqlite] Slow Transaction Speed?

On May 27, 2009, at 7:51 AM, Marcus Grimm wrote: > > > Nuno Lucas wrote: >> On Wed, May 27, 2009 at 5:58 AM, Marcus Grimm > > wrote: On Tue, May 26, 2009 at 9:47 PM, Marcus Grimm http://www.sqlite.org/faq.html#q19 >>> the faq

Re: [sqlite] Slow Transaction Speed?

Nuno Lucas wrote: > On Wed, May 27, 2009 at 5:58 AM, Marcus Grimm wrote: >>> On Tue, May 26, 2009 at 9:47 PM, Marcus Grimm >>> http://www.sqlite.org/faq.html#q19 >> the faq as well as the speed comparison speaks about a few dozen >> of

Re: [sqlite] Slow Transaction Speed?

On Wed, May 27, 2009 at 5:58 AM, Marcus Grimm wrote: >> On Tue, May 26, 2009 at 9:47 PM, Marcus Grimm >> http://www.sqlite.org/faq.html#q19 > > the faq as well as the speed comparison speaks about a few dozen > of transaction per second... that's

Re: [sqlite] Slow Transaction Speed?

> On Tue, May 26, 2009 at 9:47 PM, Marcus Grimm > wrote: > [..] >> So the question is: >> Is it somehow normal to have only 7 transactions per second? > > Yes ehm... why? > > [..] >> Any comment on this ? > > http://www.sqlite.org/faq.html#q19 the faq as well as the

Re: [sqlite] Slow Transaction Speed?

On Tue, May 26, 2009 at 9:47 PM, Marcus Grimm wrote: [..] > So the question is: > Is it somehow normal to have only 7 transactions per second? Yes [..] > Any comment on this ? http://www.sqlite.org/faq.html#q19 Regards, ~Nuno Lucas