Re: [sqlite] SQLite behaviour on FreeBSD and KVM
Yes. It was sent from an unsubscribed address, queued in the moderator who looks like they just cleared the queue. Regards... Matthew On 10/1/09, Pavel Ivanov wrote: >> We solved this yesterday, right ? Is anyone else seeing this a second >> time ? > > Yes. Though date of the second letter correctly shows that it was sent > 2 days ago I've received it just minutes ago. > > Pavel > > On Thu, Oct 1, 2009 at 1:25 PM, Simon Slavin > wrote: >> >> On 29 Sep 2009, at 8:37pm, Tippett, Matthew wrote: >> >>> I would like to highlight the following SQLite benchmark results >>> posted >>> by Phoronix via Phoronix Test Suite (http://www.phoronix-test-suite.com/ >>> ) >> >> We solved this yesterday, right ? Is anyone else seeing this a second >> time ? >> >> Simon. >> ___ >> 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 > -- Sent from my mobile device ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite behaviour on FreeBSD and KVM
On Oct 1, 2009, at 1:35 PM, Pavel Ivanov wrote: >> We solved this yesterday, right ? Is anyone else seeing this a >> second >> time ? > > Yes. Though date of the second letter correctly shows that it was sent > 2 days ago I've received it just minutes ago. When a non-subscriber sends an email to this mailing list, the message is queued for approval. (Otherwise, you would all be seeing about 50 offers for "list of doctors in your area" and "test and keep DELL laptops" per day.) I check the pending queue about once per day. Those messages that appear to be legitimate are approved and then go to the list. But as I only check the queue once per day, there is about a one-day delay. This week, I am at a conference, and so I didn't have a chance to check the queue yesterday, hence, there was as much as a two-day delay. Sometimes a user will send the same message two or more times from varying email addresses. One of those addresses will be a subscriber address and will appear on the list immediately. Other messages will get queued for approval and will appear on the list after a delay of a day or two. That seems to be what happened here. If you see a redundant message, simply ignore it. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite behaviour on FreeBSD and KVM
> We solved this yesterday, right ? Is anyone else seeing this a second > time ? Yes. Though date of the second letter correctly shows that it was sent 2 days ago I've received it just minutes ago. Pavel On Thu, Oct 1, 2009 at 1:25 PM, Simon Slavin wrote: > > On 29 Sep 2009, at 8:37pm, Tippett, Matthew wrote: > >> I would like to highlight the following SQLite benchmark results >> posted >> by Phoronix via Phoronix Test Suite (http://www.phoronix-test-suite.com/ >> ) > > We solved this yesterday, right ? Is anyone else seeing this a second > time ? > > Simon. > ___ > 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 behaviour on FreeBSD and KVM
On 29 Sep 2009, at 8:37pm, Tippett, Matthew wrote: > I would like to highlight the following SQLite benchmark results > posted > by Phoronix via Phoronix Test Suite (http://www.phoronix-test-suite.com/ > ) We solved this yesterday, right ? Is anyone else seeing this a second time ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite behaviour on FreeBSD and KVM
Hi, I would like to highlight the following SQLite benchmark results posted by Phoronix via Phoronix Test Suite (http://www.phoronix-test-suite.com/) In particular http://www.phoronix.com/scan.php?page=article&item=linux_2631_kvm&num=3 http://www.phoronix.com/scan.php?page=article&item=freebsd8_ubuntu910&num=7 In both of these cases, there are configurations where there is an order of magnitude difference between the performance of the systems. I would like to confirm my expectation that SQLite will by default always do synchronous file operations. It seems that in the Ubuntu guest on KVM Ubuntu host and the FreeBSD-8.0-RC1 benchmarks, it would appears that the kernel and filesystem are not honoring the request for synchronous fileIO. Is my assertion sane? Regards, Matthew ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite behaviour on FreeBSD and KVM
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Matthew Tippett wrote: > I would like to highlight the following SQLite benchmark results posted > by Phoronix via Phoronix Test Suite (http://www.phoronix-test-suite.com/) An earlier test showed massive differences for Linux filesystems but those results also didn't hold up. Someone entered a ticket about it (which is not the right place to discuss these things!) and the last comment shows actual reproducible numbers: http://sqlite.org/cvstrac/tktview?tn=3934 My general advice is to produce a text file of queries and run those using the SQLite shell. That kind of test is easy for anyone else to download and reproduce. You then need to ensure the test suite itself gets the same numbers as the shell. Transactions and disk syncing have been covered by other comments. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkrCuCUACgkQmOOfHg372QRNBQCdEBh/jT0Np4d9z1/UNhHSD0k3 rGcAoJsvP8jHS7E46YVoqwkcqjiy/PmA =IOFK -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite behaviour on FreeBSD and KVM
On 29 Sep 2009, at 10:29pm, Matthew Tippett wrote: > If there is anyone who is interested in assisting in improving the > quality/value/functional interest of the benchmarks, then please > advise. In SQLite, when you know you are making many changes and don't need to consult the data until you're finished, you surround the changes with BEGIN TRANSACTION and END TRANSACTION. This makes them into one big update rather than lots of little ones, and it means that disk gets updated just once (handwave here) rather than after each command. Naturally, this is hugely faster. No way should 2500 inserts in SQLite take 14 minutes. So much faster that, as Pavel noted upthread, it's suspiciously like what you're seeing in the result for KVM. This suggests that KVM is not really writing results to disk immediately. Putting those INSERTs into one transaction could make Ubuntu 9.10 (not KVM) as fast or faster than the result you're getting for KVM. Sorry, I have no Linux to test it on. You could modify the sqlite test to reflect this. You could perhaps turn sqlite-2500-insertions.txt into 50 transactions, with BEGIN TRANSACTION and END TRANSACTION around each 50 INSERT commands. Or you could have two tests: make two copies of sqlite-2500- insertions.txt, leave one as it is and put BEGIN TRANSACTION and END TRANSACTION at the beginning and end of the other. This would test both 2500 individual INSERT commands and one transaction of 2500 INSERTs, testing SQLite as both kinds of application would need to use it. I know nothing about KVM but I assume that it's operating correctly here: since the entire machine is virtualised it doesn't matter that it's not really writing to real disk. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite behaviour on FreeBSD and KVM
If there is anyone who is interested in assisting in improving the
quality/value/functional interest of the benchmarks, then please
advise.
PTS can handle direct numerical, average or gemetric means at
individual test case or aggregate test suites.
Regards... Matthew
On 9/29/09, Simon Slavin wrote:
>
> On 29 Sep 2009, at 8:57pm, Matthew Tippett wrote:
>
>> In particular
>>
>> http://www.phoronix.com/scan.php?page=article&item=linux_2631_kvm&num=3
>> http://www.phoronix.com/scan.php?page=article&item=freebsd8_ubuntu910&num=7
>>
>> In both of these cases, there are configurations where there is an
>> order
>> of magnitude difference between the performance of the systems.
>>
>> I would like to confirm my expectation that SQLite will by default
>> always do synchronous file operations. It seems that in the Ubuntu
>> guest on KVM Ubuntu host and the FreeBSD-8.0-RC1 benchmarks, it would
>> appears that the kernel and filesystem are not honoring the request
>> for
>> synchronous fileIO.
>>
>> Is my assertion sane? If some SQLite domain experts could look at the
>> results and temper to what performance numbers "sound right" for
>> SQLite
>> it would be appreciated.
>
> To save everyone else tracking it down, the SQL test in this case
> consists of
>
> CREATE TABLE pts1 ('I' SMALLINT NOT NULL, 'DT' TIMESTAMP NOT NULL
> DEFAULT CURRENT_TIMESTAMP, 'F1' VARCHAR(4) NOT NULL, 'F2' VARCHAR(16)
> NOT NULL);\
>
> and then running 2500 lines like these:
>
> INSERT INTO 'pts1' ('I', 'DT', 'F1', 'F2') VALUES ('1',
> CURRENT_TIMESTAMP, '6758', '9844343722998287');
> INSERT INTO 'pts1' ('I', 'DT', 'F1', 'F2') VALUES ('2',
> CURRENT_TIMESTAMP, '3733', '8925952369645997');
> INSERT INTO 'pts1' ('I', 'DT', 'F1', 'F2') VALUES ('3',
> CURRENT_TIMESTAMP, '5636', '8366445547934654');
> INSERT INTO 'pts1' ('I', 'DT', 'F1', 'F2') VALUES ('4',
> CURRENT_TIMESTAMP, '5626', '1439954363252147');
>
> I cannot find any sign of BEGIN;COMMIT; .
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Sent from my mobile device
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite behaviour on FreeBSD and KVM
> I cannot find any sign of BEGIN;COMMIT; .
And as a result of that, Matthew, you're right, on OS (or OS + file
system) where fsync() doesn't do physical write test getting huge
boost in performance.
Just take the second test: 12,500 transactions in 771.13 seconds
giving about 16 transactions per second which is reasonable and
explainable. Any faster performance is questionable, especially the
order of magnitude faster...
Pavel
On Tue, Sep 29, 2009 at 5:17 PM, Simon Slavin
wrote:
>
> On 29 Sep 2009, at 8:57pm, Matthew Tippett wrote:
>
>> In particular
>> http://www.phoronix.com/scan.php?page=article&item=linux_2631_kvm&num=3
>> http://www.phoronix.com/scan.php?page=article&item=freebsd8_ubuntu910&num=7
>>
>> In both of these cases, there are configurations where there is an
>> order
>> of magnitude difference between the performance of the systems.
>>
>> I would like to confirm my expectation that SQLite will by default
>> always do synchronous file operations. It seems that in the Ubuntu
>> guest on KVM Ubuntu host and the FreeBSD-8.0-RC1 benchmarks, it would
>> appears that the kernel and filesystem are not honoring the request
>> for
>> synchronous fileIO.
>>
>> Is my assertion sane? If some SQLite domain experts could look at the
>> results and temper to what performance numbers "sound right" for
>> SQLite
>> it would be appreciated.
>
> To save everyone else tracking it down, the SQL test in this case
> consists of
>
> CREATE TABLE pts1 ('I' SMALLINT NOT NULL, 'DT' TIMESTAMP NOT NULL
> DEFAULT CURRENT_TIMESTAMP, 'F1' VARCHAR(4) NOT NULL, 'F2' VARCHAR(16)
> NOT NULL);\
>
> and then running 2500 lines like these:
>
> INSERT INTO 'pts1' ('I', 'DT', 'F1', 'F2') VALUES ('1',
> CURRENT_TIMESTAMP, '6758', '9844343722998287');
> INSERT INTO 'pts1' ('I', 'DT', 'F1', 'F2') VALUES ('2',
> CURRENT_TIMESTAMP, '3733', '8925952369645997');
> INSERT INTO 'pts1' ('I', 'DT', 'F1', 'F2') VALUES ('3',
> CURRENT_TIMESTAMP, '5636', '8366445547934654');
> INSERT INTO 'pts1' ('I', 'DT', 'F1', 'F2') VALUES ('4',
> CURRENT_TIMESTAMP, '5626', '1439954363252147');
>
> I cannot find any sign of BEGIN;COMMIT; .
>
> Simon.
> ___
> 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 behaviour on FreeBSD and KVM
On 29 Sep 2009, at 8:57pm, Matthew Tippett wrote:
> In particular
> http://www.phoronix.com/scan.php?page=article&item=linux_2631_kvm&num=3
> http://www.phoronix.com/scan.php?page=article&item=freebsd8_ubuntu910&num=7
>
> In both of these cases, there are configurations where there is an
> order
> of magnitude difference between the performance of the systems.
>
> I would like to confirm my expectation that SQLite will by default
> always do synchronous file operations. It seems that in the Ubuntu
> guest on KVM Ubuntu host and the FreeBSD-8.0-RC1 benchmarks, it would
> appears that the kernel and filesystem are not honoring the request
> for
> synchronous fileIO.
>
> Is my assertion sane? If some SQLite domain experts could look at the
> results and temper to what performance numbers "sound right" for
> SQLite
> it would be appreciated.
To save everyone else tracking it down, the SQL test in this case
consists of
CREATE TABLE pts1 ('I' SMALLINT NOT NULL, 'DT' TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP, 'F1' VARCHAR(4) NOT NULL, 'F2' VARCHAR(16)
NOT NULL);\
and then running 2500 lines like these:
INSERT INTO 'pts1' ('I', 'DT', 'F1', 'F2') VALUES ('1',
CURRENT_TIMESTAMP, '6758', '9844343722998287');
INSERT INTO 'pts1' ('I', 'DT', 'F1', 'F2') VALUES ('2',
CURRENT_TIMESTAMP, '3733', '8925952369645997');
INSERT INTO 'pts1' ('I', 'DT', 'F1', 'F2') VALUES ('3',
CURRENT_TIMESTAMP, '5636', '8366445547934654');
INSERT INTO 'pts1' ('I', 'DT', 'F1', 'F2') VALUES ('4',
CURRENT_TIMESTAMP, '5626', '1439954363252147');
I cannot find any sign of BEGIN;COMMIT; .
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite behaviour on FreeBSD and KVM
Hi, I would like to highlight the following SQLite benchmark results posted by Phoronix via Phoronix Test Suite (http://www.phoronix-test-suite.com/) In particular http://www.phoronix.com/scan.php?page=article&item=linux_2631_kvm&num=3 http://www.phoronix.com/scan.php?page=article&item=freebsd8_ubuntu910&num=7 In both of these cases, there are configurations where there is an order of magnitude difference between the performance of the systems. I would like to confirm my expectation that SQLite will by default always do synchronous file operations. It seems that in the Ubuntu guest on KVM Ubuntu host and the FreeBSD-8.0-RC1 benchmarks, it would appears that the kernel and filesystem are not honoring the request for synchronous fileIO. Is my assertion sane? If some SQLite domain experts could look at the results and temper to what performance numbers "sound right" for SQLite it would be appreciated. Regards, Matthew ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
