Re: [sqlite] More benchmarks
On Tue, Feb 07, 2006 at 07:54:19PM +0100, Nemanja Corlija wrote: > On 2/7/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote: > > You might want to put a legend on your results tables so it's clear > > what the numbers represent. I assume these are times but I didn't see > > anything so far that said. > Its time in seconds. > "sync" in case of SQLite is PRAGMA synchronous=FULL; while in case of > MySQL it signifies usage of InnoDB engine. > "nosync" is the opposite, of course synchronous=OFF; and MyISAM engine. BTW, if you want to test both sync and async options you can turn fsync off in PostgreSQL by setting fsync to false. If you do that I'd also turn full_page_writes off as well, since there's no point in writing the extra info. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] More benchmarks
On Tue, Feb 07, 2006 at 08:07:03PM +0100, Nemanja Corlija wrote: > On 2/7/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > > AFAIK MySQL ships with a few different config files, so presumably > > choosing the appropriate one would be equivalent to what I provided for > > PostgreSQL. > Yes, and I installed it as a developer machine. That should be the > least performant one of the 3 available. Which is probably targeted at a machine similar to yours. > > BTW, has anyone looked at adding SQLite support to any of the DBT > > benchmarks? http://sourceforge.net/projects/osdldbt > I just glanced over it, but I think they concentrate on heavy load > testing while my focus is on single user environment for now. Well, they're replacements for the various TPC benchmarks. dbt3 is equivalent to TPC:H, which is a warehousing environment. The advantage to these tests is that they're built on real-world applications, while much of this benchmark is stuff you'd never want to see in a real application, at least not if you cared about performance. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] More benchmarks
On Tue, Feb 07, 2006 at 11:51:22AM -0500, [EMAIL PROTECTED] wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> wrote: > > On Tue, Feb 07, 2006 at 09:54:22AM -0600, Jim C. Nasby wrote: > > > Hrm, that's rather odd. What does top show when it's running through > > > psql? Are the test scripts available for download? I'll try this on my > > > machine as well... > > > > I see theh tcl now... is TCL piping into psql, or are there a set of raw > > files you could post or send me? If you're piping from TCL, I'd be > > curious to see what the difference is if you run this manually. For > > these large data sets I also think it's not very reflective of the > > database to send the result set all the way back through the client, > > since that's not very representative of the real world. In the case of > > PostgreSQL, a good alternative would be > > > > SELECT count(*) FROM ( > > SELECT t1.a FROM ... > > ) a > > ; > > > > But I'm not sure if all the other databases support that. > > SQLite supports the syntax above, FWIW. > > Your theory is that SQLite does well because it doesn't need to > send data back and forth between the client and server? You're > probably right. On the other hand, what good is the data if > the client never sees it? Well, my point was that the test in question is probably generating close to 100k rows if not more. Trying to pull that much data from the database at once is either poor design (something I've seen far too often) or a pretty unusual set of requirements. In any case, it certainly wouldn't surprise me if psql gets in the way here. > You'll notice that SQLite seems to do particularly well on the > tests that involve a lot of SQL. For example, test 2 with > 25000 separate INSERT statements. SQLite ran in 0.7 seconds > versus 16.5 seconds for PostgreSQL. Probably a big fraction of > the 16.5 seconds PostgreSQL used were in transmitting all of > that SQL over a socket to the server. I'm wondering if the > use of prepared statements might reduce the performance gap > somewhat? Notice that when doing an equally large insert in > Test 12, but an insert that involves much less SQL and parsing, > that PostgreSQL is actually a little faster than SQLite. > > Any volunteers to run the experiment? Jim? The original poster is sending me the generated files. I'll run an experiment with prepared statements and see what that gains us. But yes, trying to prepare that many statements over and over is a sure-fire way to slow things down. > Another explanation for the poor performance by PostgreSQL in > test 2 might be the PostgreSQL parser is less efficient. Or > perhaps the PostgreSQL spends a lot more time trying to > optimize - which can pay off on a big query but is a drag for > lots of silly little inserts. A test using prepared statements > would help clearify the issue. Off the top of my head, in the INNER JOIN case I believe there's about 8 different ways to execute that query, and PostgreSQL will consider all of them for every statement. So it certainly wouldn't surprise me if that was a major issue. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] More benchmarks
On Tue, Feb 07, 2006 at 08:11:02PM +0100, Nemanja Corlija wrote: > On 2/7/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > > I see theh tcl now... is TCL piping into psql, or are there a set of raw > > files you could post or send me? > TCL generates files and then I'm piping those files to all databases > except Firebird, which doesn't accept commands from stdin so I'm > pointing isql to read that same file from disk. > I'll email you row files if you want. Its less 750KB bziped. Please do. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] More benchmarks
On 2/7/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > I see theh tcl now... is TCL piping into psql, or are there a set of raw > files you could post or send me? TCL generates files and then I'm piping those files to all databases except Firebird, which doesn't accept commands from stdin so I'm pointing isql to read that same file from disk. I'll email you row files if you want. Its less 750KB bziped. -- Nemanja Corlija <[EMAIL PROTECTED]>
Re: [sqlite] More benchmarks
On 2/7/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > AFAIK MySQL ships with a few different config files, so presumably > choosing the appropriate one would be equivalent to what I provided for > PostgreSQL. Yes, and I installed it as a developer machine. That should be the least performant one of the 3 available. > BTW, has anyone looked at adding SQLite support to any of the DBT > benchmarks? http://sourceforge.net/projects/osdldbt I just glanced over it, but I think they concentrate on heavy load testing while my focus is on single user environment for now. -- Nemanja Corlija <[EMAIL PROTECTED]>
Re: [sqlite] More benchmarks
> > You might want to put a legend on your results tables so it's clear > > what the numbers represent. I assume these are times but I didn't see > > anything so far that said. > Its time in seconds. > "sync" in case of SQLite is PRAGMA synchronous=FULL; while in case of > MySQL it signifies usage of InnoDB engine. > "nosync" is the opposite, of course synchronous=OFF; and MyISAM engine. Thanks, :) I thought it was important to update the page so visitors stumbling upon it were clear what it said.
Re: [sqlite] More benchmarks
Jim C. Nasby said: > Finally, and luckily this applies to SQLite as well so this doesn't get > too off topic :), PLEASE seek help/advice BEFORE spending a bunch of > money on a big server! All too often I see people who spend a load of $$ > on equipment they didn't need or won't be able to utilize because they > didn't do enough research before hand. Granted, I'm biased since I make > money on consulting, but the amount of money I've seen people spend on > needless hardware would often buy a pretty good chunk of my time. Fear not, I'm pretty feircely conservative (i.e. cheap) when it comes to acquiring hardware and software. This comes of having a small budget. Clay -- Simple Content Management http://www.ceamus.com
Re: [sqlite] More benchmarks
On 2/7/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote: > You might want to put a legend on your results tables so it's clear > what the numbers represent. I assume these are times but I didn't see > anything so far that said. Its time in seconds. "sync" in case of SQLite is PRAGMA synchronous=FULL; while in case of MySQL it signifies usage of InnoDB engine. "nosync" is the opposite, of course synchronous=OFF; and MyISAM engine. -- Nemanja Corlija <[EMAIL PROTECTED]>
Re: [sqlite] More benchmarks
On 2/7/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Looks like I need to work on Test 6 some, huh? Your suggestion > that the servers are creating a temporary index to do the join > was my first throught too. I wonder if I should look into teaching > that trick to SQLite. Do you think you might add another test > (6b?) that repeated the same join after indexing one of the join > columns? You do this at Test 13, but at that point the tables contain > different data, I think. I guess I'll just copy test 13 to where test 8 is right now. Though those test numbers will likely create a lot of confusion that way. > Other people have posted that the PostgreSQL tests are meaningless > because the database is not tuned. I am someone sympathetic to > their complaints. If you have time, I think it would be useful > to show both a tuned and and untuned version for PostgreSQL. It > is also useful to know that PostgreSQL needs tuning in order to > run well. At first, I wanted to get by cheaply by not tuning anything. But yeah, tuning each database would be a sensible thing to do after all. > It is also interesting to note that PostgreSQL get significantly > slower in Test 13 (join with an index) versus Test 6 (the same > join without an index). What is that about? Firebird shows the > same effect, but less dramatically. Could it be a difference in > the data that the tables hold at that point. Test 6B proposed > above really would be instructive here, I think. I suspect that postgres and firebird just lost track of what's in the database at that point and they could really use some ANALYZE at that point. Just an assumption though. > I also wonder if MySQL and Firebird would benefit from tuning. > The MySQL people are rather laid back and probably will say > something like "whatever" if asked. The Firebird crowd, on the > other hand, tend to be edgy and I suspect we will be hearing > some pointed commentary from them in the near future. I'd like to gather some input on this and then rerun test after that. So if you have some tips for optimizing any database involved, please speak up. > Is there any chance of seeing additional information such as > the amount of disk space used by the various databases or the > amount of RAM consumed? These values would be more difficult > to arrive at, but will be helpful to many people, I think, if > available. I don't have much of those information ATM, but I will tell you that SQLite3 used 3MB, while SQLite2 used 3.8MB for the duration of test 6 and they both used up all CPU. Will try to gather information about database sizes next time. But I don't know how can I reliably measure memory usage on windows. -- Nemanja Corlija <[EMAIL PROTECTED]>
Re: [sqlite] More benchmarks
On Tue, Feb 07, 2006 at 10:04:43AM -0500, Clay Dowling wrote: > > Jim C. Nasby said: > > > Well, that certainly won't help things... at a minimum, on your machine, > > you should change the following: > > shared_buffers=1 > > effective_cache_size=10 > > > > The following should also help: > > work_mem=1 > > vacuum_cost_delay=50 > > autovacuum=on > > autovacuum_vacuum_scale_factor=0.2 > > Jim, > > I just want to say thanks for providing these tuning parameters. I not > currently using your product, but I hope to in the near future for a > larger scale version of my own product. Performance tuning is something > of an arcane art from my perspective, so getting any help on it is highly > desirable. You're welcome. Just to clarify, PostgreSQL isn't really a Pervasive product; we just have a bundled installer and offer support and services, but it's all the same as the community code. When it comes to tuning, http://www.powerpostgresql.com/PerfList and http://www.revsys.com/writings/postgresql-performance.html are a couple places to start looking. Finally, and luckily this applies to SQLite as well so this doesn't get too off topic :), PLEASE seek help/advice BEFORE spending a bunch of money on a big server! All too often I see people who spend a load of $$ on equipment they didn't need or won't be able to utilize because they didn't do enough research before hand. Granted, I'm biased since I make money on consulting, but the amount of money I've seen people spend on needless hardware would often buy a pretty good chunk of my time. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] More benchmarks
On Tue, Feb 07, 2006 at 09:54:22AM -0600, Jim C. Nasby wrote: > Hrm, that's rather odd. What does top show when it's running through > psql? Are the test scripts available for download? I'll try this on my > machine as well... I see theh tcl now... is TCL piping into psql, or are there a set of raw files you could post or send me? If you're piping from TCL, I'd be curious to see what the difference is if you run this manually. For these large data sets I also think it's not very reflective of the database to send the result set all the way back through the client, since that's not very representative of the real world. In the case of PostgreSQL, a good alternative would be SELECT count(*) FROM ( SELECT t1.a FROM ... ) a ; But I'm not sure if all the other databases support that. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] More benchmarks
On Tue, Feb 07, 2006 at 08:07:53AM -0500, [EMAIL PROTECTED] wrote: > It is also interesting to note that PostgreSQL get significantly > slower in Test 13 (join with an index) versus Test 6 (the same > join without an index). What is that about? Firebird shows the > same effect, but less dramatically. Could it be a difference in > the data that the tables hold at that point. Test 6B proposed > above really would be instructive here, I think. Well, I'm a bit skeptical as to the usefulness of that test, since if I'm reading things correctly it's essentially a cartesian product. In any case, it's very likely that the lack of analysis and default parameters resulted in a bad query plan. The output of EXPLAIN ANALYZE would be most instructive. > I also wonder if MySQL and Firebird would benefit from tuning. > The MySQL people are rather laid back and probably will say > something like "whatever" if asked. The Firebird crowd, on the > other hand, tend to be edgy and I suspect we will be hearing > some pointed commentary from them in the near future. FWIW, I wouldn't really consider the changes I suggested 'tuning', as they're rather off-the-cuff based strictly on my experience and limited knowledge as to the workload. Personally, I'd prefer if PostgreSQL would at least provide multiple sample configs, but c'est la vie. AFAIK MySQL ships with a few different config files, so presumably choosing the appropriate one would be equivalent to what I provided for PostgreSQL. BTW, has anyone looked at adding SQLite support to any of the DBT benchmarks? http://sourceforge.net/projects/osdldbt -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] More benchmarks
On Tue, Feb 07, 2006 at 10:08:23AM +0100, Nemanja Corlija wrote: > On 2/7/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > > On Tue, Feb 07, 2006 at 07:31:50AM +0100, Nemanja Corlija wrote: > > > > Well, that certainly won't help things... at a minimum, on your machine, > > > > you should change the following: > > > > shared_buffers=1 > > > > effective_cache_size=10 > > > > > > > > The following should also help: > > > > work_mem=1 > > > > vacuum_cost_delay=50 > > > > autovacuum=on > > > > autovacuum_vacuum_scale_factor=0.2 > > > Sure, I could do that. But then I'd also need to tune all other > > > databases to make things fair and that's not really what I intended to > > > do here. I want to keep things as "out of the box" as possible. > > > > Then you should just drop PostgreSQL from the tests, because they're not > > doing anyone any good. It's pretty well known that the default > > postgresql.conf is meant to allow for bringing the database up on a > > machine with very minimal hardware. It's the equivalent to using MySQL's > > minimum configuration file. > > OK, I've changed above settings but now I get even worse performance. > 265.223 seconds. > File I've edited is C:\Program Files\PostgreSQL\8.1\data\postgresql.conf > AFAICT that's the one. Then I've restarted postgres. I guess that > should load new settings? > Then I ran VACUUM ANALYZE t2; > > Hmmm, now I ran that same script from pgAdmin and it completed in 5 seconds. > I guess its reasonable to assume that psql is actually the bottleneck > here. I tried redirecting to file but that was a minute ago and it's > still running. Any ideas? Hrm, that's rather odd. What does top show when it's running through psql? Are the test scripts available for download? I'll try this on my machine as well... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] More benchmarks
On 2/6/06, Nemanja Corlija <[EMAIL PROTECTED]> wrote: > I've posted some benchmarks between SQLite, PostgreSQL, MySQL and FirebirdSQL. > > Details at http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison You might want to put a legend on your results tables so it's clear what the numbers represent. I assume these are times but I didn't see anything so far that said.
Re: [sqlite] More benchmarks
Jim C. Nasby said: > Well, that certainly won't help things... at a minimum, on your machine, > you should change the following: > shared_buffers=1 > effective_cache_size=10 > > The following should also help: > work_mem=1 > vacuum_cost_delay=50 > autovacuum=on > autovacuum_vacuum_scale_factor=0.2 Jim, I just want to say thanks for providing these tuning parameters. I not currently using your product, but I hope to in the near future for a larger scale version of my own product. Performance tuning is something of an arcane art from my perspective, so getting any help on it is highly desirable. Clay Dowling -- Simple Content Management http://www.ceamus.com
Re: [sqlite] More benchmarks
Nemanja Corlija <[EMAIL PROTECTED]> wrote: > I've posted some benchmarks between SQLite, PostgreSQL, MySQL and FirebirdS= > QL. > > Details at http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison > Thanks for your hard work, Nemanja! This is useful information. Looks like I need to work on Test 6 some, huh? Your suggestion that the servers are creating a temporary index to do the join was my first throught too. I wonder if I should look into teaching that trick to SQLite. Do you think you might add another test (6b?) that repeated the same join after indexing one of the join columns? You do this at Test 13, but at that point the tables contain different data, I think. Other people have posted that the PostgreSQL tests are meaningless because the database is not tuned. I am someone sympathetic to their complaints. If you have time, I think it would be useful to show both a tuned and and untuned version for PostgreSQL. It is also useful to know that PostgreSQL needs tuning in order to run well. It is also interesting to note that PostgreSQL get significantly slower in Test 13 (join with an index) versus Test 6 (the same join without an index). What is that about? Firebird shows the same effect, but less dramatically. Could it be a difference in the data that the tables hold at that point. Test 6B proposed above really would be instructive here, I think. I also wonder if MySQL and Firebird would benefit from tuning. The MySQL people are rather laid back and probably will say something like "whatever" if asked. The Firebird crowd, on the other hand, tend to be edgy and I suspect we will be hearing some pointed commentary from them in the near future. Is there any chance of seeing additional information such as the amount of disk space used by the various databases or the amount of RAM consumed? These values would be more difficult to arrive at, but will be helpful to many people, I think, if available. Thanks again for your hard work in preparing these benchmarks! Even if you do nothing else with them, your work so far has been a big help. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] More benchmarks
On 2/7/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > On Tue, Feb 07, 2006 at 07:31:50AM +0100, Nemanja Corlija wrote: > > > Well, that certainly won't help things... at a minimum, on your machine, > > > you should change the following: > > > shared_buffers=1 > > > effective_cache_size=10 > > > > > > The following should also help: > > > work_mem=1 > > > vacuum_cost_delay=50 > > > autovacuum=on > > > autovacuum_vacuum_scale_factor=0.2 > > Sure, I could do that. But then I'd also need to tune all other > > databases to make things fair and that's not really what I intended to > > do here. I want to keep things as "out of the box" as possible. > > Then you should just drop PostgreSQL from the tests, because they're not > doing anyone any good. It's pretty well known that the default > postgresql.conf is meant to allow for bringing the database up on a > machine with very minimal hardware. It's the equivalent to using MySQL's > minimum configuration file. OK, I've changed above settings but now I get even worse performance. 265.223 seconds. File I've edited is C:\Program Files\PostgreSQL\8.1\data\postgresql.conf AFAICT that's the one. Then I've restarted postgres. I guess that should load new settings? Then I ran VACUUM ANALYZE t2; Hmmm, now I ran that same script from pgAdmin and it completed in 5 seconds. I guess its reasonable to assume that psql is actually the bottleneck here. I tried redirecting to file but that was a minute ago and it's still running. Any ideas? -- Nemanja Corlija <[EMAIL PROTECTED]>
Re: [sqlite] More benchmarks
On Tue, Feb 07, 2006 at 07:06:26AM +0100, Nemanja Corlija wrote: > On 2/7/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > > Did you happen to do an analyze? > Nope. All databases are run as default as possible. And, they all get > same scripts to execute. Then your results for PostgreSQL are utterly meaningless. (And in this case, the poor performance reflects poorly on you, the DBA, not on PostgreSQL.) > > What changes have you made to the default postgresql.conf? > None. Then your test results are bogus. Last I heard, the default value in postgresql.conf were intended to simply work AT ALL on the widest possible range of hardware, operating systems, etc., and are NOT recommended values for any actual production use. Yes, I that sounds very foolish of the PostgreSQL folks to me too, but there you have it. Using PostgreSQL properly REQUIRES that you modify those settings. > Sure, I could do that. But then I'd also need to tune all other > databases to make things fair and that's not really what I intended to > do here. I want to keep things as "out of the box" as possible. The above is not exactly "tuning", it is basic "Running PostgreSQL 101" type stuff. Look at it this way: Different databases have different installation requirements. Editing postgresql.conf and collecting statistics with vacuum analyze are simply part of the required install procedure for PostgreSQL. If you don't do the basic stuff like that, your database is simply misconfigured, and any performance results you generate are worthless - because in the real world, NO ONE with any clue at all would ever run their database that way. Minimally, you need to install and configure each of the databases you're benchmarking in the manner expected of a competent but non-expert user of that tool. Naturally this various for different databases. If you find the process of properly installing and configuring the database software overly complicated or poorly documented, then that's a perfectly legitimate complaint, but it has nothing to do with performance benchmarking. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/
Re: [sqlite] More benchmarks
On Tue, Feb 07, 2006 at 07:31:50AM +0100, Nemanja Corlija wrote: > > Did you happen to do an analyze? > Nope. All databases are run as default as possible. And, they all get > same scripts to execute. Which means PostgreSQL can only take a wild stab at what's in the database. > > > > What changes have you made to the default postgresql.conf? > > > None. > > > > Well, that certainly won't help things... at a minimum, on your machine, > > you should change the following: > > shared_buffers=1 > > effective_cache_size=10 > > > > The following should also help: > > work_mem=1 > > vacuum_cost_delay=50 > > autovacuum=on > > autovacuum_vacuum_scale_factor=0.2 > Sure, I could do that. But then I'd also need to tune all other > databases to make things fair and that's not really what I intended to > do here. I want to keep things as "out of the box" as possible. Then you should just drop PostgreSQL from the tests, because they're not doing anyone any good. It's pretty well known that the default postgresql.conf is meant to allow for bringing the database up on a machine with very minimal hardware. It's the equivalent to using MySQL's minimum configuration file. It certainly doesn't seem unreasonable to tweak a handful of parameters for each database. I wouldn't even consider this to be tuning; everything I recommended is a fairly standard set of adjustments. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] More benchmarks
On 2/7/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > On Tue, Feb 07, 2006 at 07:06:26AM +0100, Nemanja Corlija wrote: > > On 2/7/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > > > For test 8 on PostgreSQL, what's EXPLAIN ANALYZE for one of those show? > > test=# EXPLAIN ANALYZE SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<100; > > QUERY PLAN > > > > - > > -- > > Aggregate (cost=6.02..6.03 rows=1 width=4) (actual time=0.183..0.185 > > rows=1 loops=1) > >-> Index Scan using i2b on t2 (cost=0.00..6.01 rows=1 width=4) > > (actual time=0.030..0.108 rows=12 loo > > ps=1) > > Index Cond: ((b >= 0) AND (b < 100)) > > Total runtime: 0.510 ms > > (4 rows) > > Well, it expected 1 row and got 12. In this example it probably doesn't > matter, but for other parameters it probably will. > > Did you happen to do an analyze? Nope. All databases are run as default as possible. And, they all get same scripts to execute. > > > What changes have you made to the default postgresql.conf? > > None. > > Well, that certainly won't help things... at a minimum, on your machine, > you should change the following: > shared_buffers=1 > effective_cache_size=10 > > The following should also help: > work_mem=1 > vacuum_cost_delay=50 > autovacuum=on > autovacuum_vacuum_scale_factor=0.2 Sure, I could do that. But then I'd also need to tune all other databases to make things fair and that's not really what I intended to do here. I want to keep things as "out of the box" as possible. -- Nemanja Corlija <[EMAIL PROTECTED]>
Re: [sqlite] More benchmarks
On Tue, Feb 07, 2006 at 07:06:26AM +0100, Nemanja Corlija wrote: > On 2/7/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > > For test 8 on PostgreSQL, what's EXPLAIN ANALYZE for one of those show? > test=# EXPLAIN ANALYZE SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<100; > QUERY PLAN > > - > -- > Aggregate (cost=6.02..6.03 rows=1 width=4) (actual time=0.183..0.185 > rows=1 loops=1) >-> Index Scan using i2b on t2 (cost=0.00..6.01 rows=1 width=4) > (actual time=0.030..0.108 rows=12 loo > ps=1) > Index Cond: ((b >= 0) AND (b < 100)) > Total runtime: 0.510 ms > (4 rows) Well, it expected 1 row and got 12. In this example it probably doesn't matter, but for other parameters it probably will. Did you happen to do an analyze? > > What changes have you made to the default postgresql.conf? > None. Well, that certainly won't help things... at a minimum, on your machine, you should change the following: shared_buffers=1 effective_cache_size=10 The following should also help: work_mem=1 vacuum_cost_delay=50 autovacuum=on autovacuum_vacuum_scale_factor=0.2 -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] More benchmarks
On 2/7/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > For test 8 on PostgreSQL, what's EXPLAIN ANALYZE for one of those show? test=# EXPLAIN ANALYZE SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<100; QUERY PLAN - -- Aggregate (cost=6.02..6.03 rows=1 width=4) (actual time=0.183..0.185 rows=1 loops=1) -> Index Scan using i2b on t2 (cost=0.00..6.01 rows=1 width=4) (actual time=0.030..0.108 rows=12 loo ps=1) Index Cond: ((b >= 0) AND (b < 100)) Total runtime: 0.510 ms (4 rows) > What changes have you made to the default postgresql.conf? None. -- Nemanja Corlija <[EMAIL PROTECTED]>
Re: [sqlite] More benchmarks
On Tue, Feb 07, 2006 at 05:57:24AM +0100, Nemanja Corlija wrote: > I've posted some benchmarks between SQLite, PostgreSQL, MySQL and FirebirdSQL. > > Details at http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison For test 8 on PostgreSQL, what's EXPLAIN ANALYZE for one of those show? What changes have you made to the default postgresql.conf? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
[sqlite] More benchmarks
I've posted some benchmarks between SQLite, PostgreSQL, MySQL and FirebirdSQL. Details at http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison -- Nemanja Corlija <[EMAIL PROTECTED]>
