Re: [HACKERS] PostgreSQL Benchmarks
Christopher Kings-Lynne wrote: Hrm. I just saw that the PHP ADODB guy just published a bunch of database benchmarks. It's fairly evident to me that benchmarking PostgreSQL on Win32 isn't really fair: http://php.weblogs.com/oracle_mysql_performance *sigh* Not fair, perhaps. But if you look, you'll see that *Cygwin* PostgreSQL beat most everything on the Win32 platform except MySQL and Oracle with PL/SQL. Read further and you'll see that Cygwin PostgreSQL came *really* close (within 10% or something) to MS-SQL. Considering that they weren't even running a native version of PostgreSQL, I think the results were surprisingly *good*. But yes, we really do want to be the fastest. :-) -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] PostgreSQL Benchmarks
Hrm. I just saw that the PHP ADODB guy just published a bunch of database benchmarks. It's fairly evident to me that benchmarking PostgreSQL on Win32 isn't really fair: http://php.weblogs.com/oracle_mysql_performance *sigh* Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] PostgreSQL Benchmarks
Hrm. I just saw that the PHP ADODB guy just published a bunch of database benchmarks. It's fairly evident to me that benchmarking PostgreSQL on Win32 isn't really fair: http://php.weblogs.com/oracle_mysql_performance And why is the highly advocated transaction capable MySQL 4 not tested? That's the problem, for every performance test they choose ISAM tables, and when transactions are mentioned it's said MySQL has transactions. But why no benchmarks? Regards, Mario Weilguni ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] PostgreSQL Benchmarks
On Tue, 2003-02-11 at 08:26, Christopher Kings-Lynne wrote: Hrm. I just saw that the PHP ADODB guy just published a bunch of database benchmarks. It's fairly evident to me that benchmarking PostgreSQL on Win32 isn't really fair: http://php.weblogs.com/oracle_mysql_performance *sigh* How much of the performance difference is from the RDBMS, from the middleware, and from the quality of implementation in the middleware. While I'm not surprised that the the cygwin version of PostgreSQL is slow, those results don't tell me anything about the quality of the middleware interface between PHP and PostgreSQL. Does anyone know if we can rule out some of the performance loss by pinning it to bad middleware implementation for PostgreSQL? Regards, -- Greg Copeland [EMAIL PROTECTED] Copeland Computer Consulting ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] PostgreSQL Benchmarks
On Tue, 2003-02-11 at 08:31, Mario Weilguni wrote: Hrm. I just saw that the PHP ADODB guy just published a bunch of database benchmarks. It's fairly evident to me that benchmarking PostgreSQL on Win32 isn't really fair: http://php.weblogs.com/oracle_mysql_performance And why is the highly advocated transaction capable MySQL 4 not tested? That's the problem, for every performance test they choose ISAM tables, and when transactions are mentioned it's said MySQL has transactions. But why no benchmarks? Insert Statement Not using bind variables (MySQL and Oracle): $DB-BeginTrans(); Using bind variables: $DB-BeginTrans(); PL/SQL Insert Benchmark Appears to not initiate a transaction. I'm assuming this is because it's implicitly within a transaction? Oddly enough, I am seeing explicit commits here. It appears that the benchmarks are attempting to use transactions, however, I have no idea if MySQL's HEAP supports them. For all I know, transactions are being silently ignored. Regards, -- Greg Copeland [EMAIL PROTECTED] Copeland Computer Consulting ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [pgsql-advocacy] [HACKERS] PostgreSQL Benchmarks
I've tested all the win32 versions of postgres I can get my hands on (cygwin and not), and my general feeling is that they have problems with insert performance with fsync() turned on, probably the fault of the os. Select performance is not so much affected. This is easily solved with transactions and other such things. Also Postgres benefits from pl just like oracle. May I make a suggestion that maybe it is time to start thinking about tuning the default config file, IMHO its just a little bit too conservative, and its hurting you in benchmarks being run by idiots, but its still bad publicity. Any real database admin would know his test are synthetic and not meaningful without having to look at the #s. This is irritating me so much that I am going to put together a benchmark of my own, a real world one, on (publicly available) real world data. Mysql is a real dog in a lot of situations. The FCC publishes a database of wireless transmitters that has tables with 10 million records in it. I'll pump that into pg, run some benchmarks, real world queries, and we'll see who the faster database *really* is. This is just a publicity issue, that's all. Its still annoying though. I'll even run an open challenge to database admin to beat query performance of postgres in such datasets, complex multi table joins, etc. I'll even throw out the whole table locking issue and analyze single user performance. Merlin _ How much of the performance difference is from the RDBMS, from the middleware, and from the quality of implementation in the middleware. While I'm not surprised that the the cygwin version of PostgreSQL is slow, those results don't tell me anything about the quality of the middleware interface between PHP and PostgreSQL. Does anyone know if we can rule out some of the performance loss by pinning it to bad middleware implementation for PostgreSQL? Regards, -- Greg Copeland [EMAIL PROTECTED] Copeland Computer Consulting ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Changing the default configuration (was Re: [pgsql-advocacy] [HACKERS] PostgreSQL Benchmarks)
Merlin Moncure [EMAIL PROTECTED] writes: May I make a suggestion that maybe it is time to start thinking about tuning the default config file, IMHO its just a little bit too conservative, It's a lot too conservative. I've been thinking for awhile that we should adjust the defaults. The original motivation for setting shared_buffers = 64 was so that Postgres would start out-of-the-box on machines where SHMMAX is 1 meg (64 buffers = 1/2 meg, leaving 1/2 meg for our other shared data structures). At one time SHMMAX=1M was a pretty common stock kernel setting. But our other data structures blew past the 1/2 meg mark some time ago; at default settings the shmem request is now close to 1.5 meg. So people with SHMMAX=1M have already got to twiddle their postgresql.conf settings, or preferably learn how to increase SHMMAX. That means there is *no* defensible reason anymore for defaulting to 64 buffers. We could retarget to try to stay under SHMMAX=4M, which I think is the next boundary that's significant in terms of real-world platforms (isn't that the default SHMMAX on some BSDen?). That would allow us 350 or so shared_buffers, which is better, but still not really a serious choice for production work. What I would really like to do is set the default shared_buffers to 1000. That would be 8 meg worth of shared buffer space. Coupled with more-realistic settings for FSM size, we'd probably be talking a shared memory request approaching 16 meg. This is not enough RAM to bother any modern machine from a performance standpoint, but there are probably quite a few platforms out there that would need an increase in their stock SHMMAX kernel setting before they'd take it. So what this comes down to is making it harder for people to get Postgres running for the first time, versus making it more likely that they'll see decent performance when they do get it running. It's worth noting that increasing SHMMAX is not nearly as painful as it was back when these decisions were taken. Most people have moved to platforms where it doesn't even take a kernel rebuild, and we've acquired documentation that tells how to do it on all(?) our supported platforms. So I think it might be okay to expect people to do it. The alternative approach is to leave the settings where they are, and to try to put more emphasis in the documentation on the fact that the factory-default settings produce a toy configuration that you *must* adjust upward for decent performance. But we've not had a lot of success spreading that word, I think. With SHMMMAX too small, you do at least get a pretty specific error message telling you so. Comments? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: Changing the default configuration (was Re: [pgsql-advocacy] [HACKERS] PostgreSQL Benchmarks)
On Tue, Feb 11, 2003 at 11:20:14AM -0500, Tom Lane wrote: ... We could retarget to try to stay under SHMMAX=4M, which I think is the next boundary that's significant in terms of real-world platforms (isn't that the default SHMMAX on some BSDen?). ... Assuming 1 page = 4k, and number of pages is correct in GENERIC kernel configs, SHMMAX=4M for NetBSD (8M for i386, x86_64) Cheers, Patrick ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: Changing the default configuration (was Re: [pgsql-advocacy] [HACKERS] PostgreSQL Benchmarks)
A quick-'n'-dirty first step would be more comments in postgresql.conf. Most of the lines are commented out which would imply use the default but the default is not shown. (I realize this has the difficulty of defaults that change depending upon how PostgreSQL was configured/compiled but perhaps postgresql.conf could be built by the make process based on the configuration options.) If postgresql.conf were commented with recommendations it would probably be all I need though perhaps a recommendation to edit that file should be displayed at the conclusion of make install. Cheers, Steve On Tuesday 11 February 2003 8:20 am, Tom Lane wrote: Merlin Moncure [EMAIL PROTECTED] writes: May I make a suggestion that maybe it is time to start thinking about tuning the default config file, IMHO its just a little bit too conservative, It's a lot too conservative. I've been thinking for awhile that we should adjust the defaults. The original motivation for setting shared_buffers = 64 was so that Postgres would start out-of-the-box on machines where SHMMAX is 1 meg (64 buffers = 1/2 meg, leaving 1/2 meg for our other shared data structures). At one time SHMMAX=1M was a pretty common stock kernel setting. But our other data structures blew past the 1/2 meg mark some time ago; at default settings the shmem request is now close to 1.5 meg. So people with SHMMAX=1M have already got to twiddle their postgresql.conf settings, or preferably learn how to increase SHMMAX. That means there is *no* defensible reason anymore for defaulting to 64 buffers. We could retarget to try to stay under SHMMAX=4M, which I think is the next boundary that's significant in terms of real-world platforms (isn't that the default SHMMAX on some BSDen?). That would allow us 350 or so shared_buffers, which is better, but still not really a serious choice for production work. What I would really like to do is set the default shared_buffers to 1000. That would be 8 meg worth of shared buffer space. Coupled with more-realistic settings for FSM size, we'd probably be talking a shared memory request approaching 16 meg. This is not enough RAM to bother any modern machine from a performance standpoint, but there are probably quite a few platforms out there that would need an increase in their stock SHMMAX kernel setting before they'd take it. So what this comes down to is making it harder for people to get Postgres running for the first time, versus making it more likely that they'll see decent performance when they do get it running. It's worth noting that increasing SHMMAX is not nearly as painful as it was back when these decisions were taken. Most people have moved to platforms where it doesn't even take a kernel rebuild, and we've acquired documentation that tells how to do it on all(?) our supported platforms. So I think it might be okay to expect people to do it. The alternative approach is to leave the settings where they are, and to try to put more emphasis in the documentation on the fact that the factory-default settings produce a toy configuration that you *must* adjust upward for decent performance. But we've not had a lot of success spreading that word, I think. With SHMMMAX too small, you do at least get a pretty specific error message telling you so. Comments? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: Changing the default configuration (was Re: [pgsql-advocacy] [HACKERS] PostgreSQL Benchmarks)
Justin Clift [EMAIL PROTECTED] writes: Personally I'd be a bunch happier if we set the buffers so high that we definitely have decent performance, and the people that want to run PostgreSQL are forced to make the choice of either: 1) Adjust their system settings to allow PostgreSQL to run properly, or 2) Manually adjust the PostgreSQL settings to run memory-constrained This way, PostgreSQL either runs decently, or they are _aware_ that they're limiting it. Yeah, that is the subtext here. If you can't increase SHMMAX then you can always trim the postgresql.conf parameters --- but theoretically, at least, you should then have a clue that you're running a badly-configured setup ... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: Changing the default configuration (was Re: [pgsql-advocacy] [HACKERS] PostgreSQL Benchmarks)
FYI, my stock linux 2.4.19 gentoo kernel has: kernel.shmall = 2097152 kernel.shmmax = 33554432 sysctl -a So it appears that linux at least is way above your 8 meg point, unless I am missing something. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: Changing the default configuration (was Re: [pgsql-advocacy] [HACKERS] PostgreSQL Benchmarks)
Jon Griffin [EMAIL PROTECTED] writes: So it appears that linux at least is way above your 8 meg point, unless I am missing something. Yeah, AFAIK all recent Linuxen are well above the range of parameters that I was suggesting (and even if they weren't, Linux is particularly easy to change the SHMMAX setting on). It's other Unixoid platforms that are likely to have a problem. Particularly the ones where you have to rebuild the kernel to change SHMMAX; people may be afraid to do that. Does anyone know whether cygwin has a setting comparable to SHMMAX, and if so what is its default value? How about the upcoming native Windows port --- any issues there? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [pgsql-advocacy] [HACKERS] PostgreSQL Benchmarks
Why don't we include a postgresql.conf.recommended along with our postgresql.conf.sample. That shouldn't be too hard. We can just jack up the shared buffers and wal buffers and everything - it doesn't matter if it's not perfect, but it will at least give people an idea of what needs to be increased, etc to get good results. I'm currently benchmarking our new DB server before we put it into production. I plan to publish the results from that shortly. Regards, Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Merlin Moncure Sent: Tuesday, 11 February 2003 11:44 PM To: Greg Copeland Cc: PostgresSQL Hackers Mailing List; [EMAIL PROTECTED] Subject: Re: [pgsql-advocacy] [HACKERS] PostgreSQL Benchmarks I've tested all the win32 versions of postgres I can get my hands on (cygwin and not), and my general feeling is that they have problems with insert performance with fsync() turned on, probably the fault of the os. Select performance is not so much affected. This is easily solved with transactions and other such things. Also Postgres benefits from pl just like oracle. May I make a suggestion that maybe it is time to start thinking about tuning the default config file, IMHO its just a little bit too conservative, and its hurting you in benchmarks being run by idiots, but its still bad publicity. Any real database admin would know his test are synthetic and not meaningful without having to look at the #s. This is irritating me so much that I am going to put together a benchmark of my own, a real world one, on (publicly available) real world data. Mysql is a real dog in a lot of situations. The FCC publishes a database of wireless transmitters that has tables with 10 million records in it. I'll pump that into pg, run some benchmarks, real world queries, and we'll see who the faster database *really* is. This is just a publicity issue, that's all. Its still annoying though. I'll even run an open challenge to database admin to beat query performance of postgres in such datasets, complex multi table joins, etc. I'll even throw out the whole table locking issue and analyze single user performance. Merlin _ How much of the performance difference is from the RDBMS, from the middleware, and from the quality of implementation in the middleware. While I'm not surprised that the the cygwin version of PostgreSQL is slow, those results don't tell me anything about the quality of the middleware interface between PHP and PostgreSQL. Does anyone know if we can rule out some of the performance loss by pinning it to bad middleware implementation for PostgreSQL? Regards, -- Greg Copeland [EMAIL PROTECTED] Copeland Computer Consulting ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [pgsql-advocacy] [HACKERS] PostgreSQL Benchmarks
On Tuesday 11 Feb 2003 8:01 pm, Mario Weilguni wrote: Hrm. I just saw that the PHP ADODB guy just published a bunch of database benchmarks. It's fairly evident to me that benchmarking PostgreSQL on Win32 isn't really fair: http://php.weblogs.com/oracle_mysql_performance And why is the highly advocated transaction capable MySQL 4 not tested? That's the problem, for every performance test they choose ISAM tables, and when transactions are mentioned it's said MySQL has transactions. But why no benchmarks? I did benchmark mysql/postgresql/oracle sometime back. Mysql with transaction is 90% as fast as postgresql. But it dies down with increased number of users no matter how much resources you throw at it. Oracle is 130% of postgresql. This was postgresql 7.2.x series so things have changed for sure, but you got the idea, right? Shridhar ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [pgsql-advocacy] [HACKERS] PostgreSQL Benchmarks
There's The Open Source Database Benchmark, http://osdb.sourceforge.net/. Anyone tried to use it? __ Do you Yahoo!? Yahoo! Shopping - Send Flowers for Valentine's Day http://shopping.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [pgsql-advocacy] [HACKERS] PostgreSQL Benchmarks
-Original Message- From: ow [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 11, 2003 10:32 PM To: Shridhar Daithankar[EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: [pgsql-advocacy] [HACKERS] PostgreSQL Benchmarks There's The Open Source Database Benchmark, http://osdb.sourceforge.net/. Anyone tried to use it? Requires a real Linux or UNIX installation. Won't run under Cygwin. At least I could not get it to work. Got it compiled, but I am missing something to make it work correctly. Here are some other benchmark things: http://www.eweek.com/article2/0,3959,293,00.asp Which has this link: ftp://ftp.eweek.com/pub/eweek/pdf/printpub/benchmark/dbbenchmark_v1.zip This article: http://www.dcc.unicamp.br/~celio/mc527/interbase/PCweek_test.html Has this code: ftp://ftp.zdnet.com/pcweek/labs/0207codefile.zip But it requires the very expensive Benchmark Factory product. It might be useful to people who already have benchmark factory. Another open source database test: http://sourceforge.net/projects/osdldbt Java database benchmark routines: http://www.firstsql.com/firstsqlj/ This site: http://www.mipt.sw.ru/ (which does not appear to be available right now) Has three excellent benchmark tools, ATS, LTS, OTS. They are Windows machine centric, though, and would probably be very hard to port to Unix environments. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [pgsql-advocacy] [HACKERS] PostgreSQL Benchmarks
A financial database benchmark: http://www.cs.nyu.edu/cs/faculty/shasha/fintime.html ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [pgsql-advocacy] [HACKERS] PostgreSQL Benchmarks
This is how to get the FIPS benchmark. It measures CONFORMANCE rather than performance: http://www.itl.nist.gov/div897/ctg/sql_form.htm ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [pgsql-advocacy] [HACKERS] PostgreSQL Benchmarks
The benchmark handbook: http://www.benchmarkresources.com/handbook/contents.asp ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: Changing the default configuration (was Re: [pgsql-advocacy] [HACKERS] PostgreSQL Benchmarks)
Matthew T. O'Connor [EMAIL PROTECTED] writes: ... So we ship postgresql.conf with 32M of shared memory and auto_shared_mem_reduction = true. With a comment that the administrator might want to turn this off for production. This really doesn't address Justin's point about clueless benchmarkers, however. In fact I fear it would make that problem worse: if Joe Blow says he got horrible performance, who knows whether he was running with a reasonable number of buffers or not? Especially when you ask him did you have lots of shared buffers and he responds yes, of course, it says 32M right here. We've recently been moving away from the notion that it's okay to silently lose functionality in order to run on a given system. For example, if you want to install without readline, you now have to explicitly tell configure that, because we heard why don't I have history in psql way too often from people who just ran configure and paid no attention to what it told them. I think that what this discussion is really leading up to is that we are going to decide to apply the same principle to performance. The out-of-the-box settings ought to give reasonable performance, and if your system can't handle it, you should have to take explicit action to acknowledge the fact that you aren't going to get reasonable performance. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
