Thread: Postgres performance comments from a MySQL user
Some backs-story. I'm in the process of converting our internal file based data storage to an RDBMS. After looking a bit at PostgreSQL and MySQL, I chose Postgresql. My boss has heard of MySQL and has not heard of PostgreSQL and every now and then ahe make allusions that we shuold be using MySQL. One comment he got from the architect of another web site is as follows > If we were to start again > from scratch now, I'd still use InnoDB over postgres unless the > performance picked up with postgres recently. > > Keep in mind our application is very write-heavy so your numbers may > be different. Does postgres still keep the old row versions in the > primary-key B-Tree? If it does I doubt performance improved much for > write-heavy apps, that was a very poor design decision by them. InnoDB > takes the Oracle route of moving old row versions to a seperate > on-disk data structure. Does what he say make sense? If so, has the situation changed? BNasically, I need something intelligent to say to my boss to either counter or mitigate his perception. Thanks Take care, Jay
> > If we were to start again > > from scratch now, I'd still use InnoDB over postgres unless the > > performance picked up with postgres recently. > > > > Keep in mind our application is very write-heavy so your numbers may > > be different. Does postgres still keep the old row versions in the > > primary-key B-Tree? If it does I doubt performance improved much for > > write-heavy apps, that was a very poor design decision by them. InnoDB > > takes the Oracle route of moving old row versions to a seperate > > on-disk data structure. 1) A write-heavy app will almost certainly be faster with Postgres. 2) Postgres has had many speed improvements over the last few years 3) If your point was to move to a relational database, then you should choose Postgres. MySQL, although it's SQL, hardly qualifies as relational For example, MySQL does not have: * Views * Triggers * Stored Procedures * Subselects (although they may have added this one) And then when you want to do real transactions, MySQLs speed slows way down. If you are moving _to_ a database system, it seems pointless to stop halfway and go with MySQL, when you can go the full way with Postgres. Jon > > Does what he say make sense? If so, has the situation changed? BNasically, > I need something intelligent to say to my boss to either counter or > mitigate his perception. > > Thanks > > Take care, > Jay > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html >
Hi Jay, From his comments about PostgreSQL's performance, I'd extremely strongly suspect he has only tested it with untuned (default) memory settings. The default memory settings in all of the previous releases of PostgreSQL have been extremely conservative to maintain the widest compatibility. However, they _significantly_ reduce the throughput and transaction speed of PostgreSQL. The _very first_ thing to do with any PostgreSQL installation is bump up the memory settings (at least "sort_mem" and "shared_buffers") in the postgresql.conf and restart it. Tell him to test it with decent settings (try about 4000 for each as an initial start), and he'll find that a decently tuned PostgreSQL matches the speed of a MySQL installation with any table type. An in write-intensive applications, the MySQL server will always fall behind. _Especially_ as the number of simultaneous clients rises. MySQL falls behind, as does Oracle 8i a bit further on (not sure about 9i and 9iR2), and PostgreSQL keeps on performing at pretty much the same throughput for far higher numbers of client connections. And _that_, is for real. Regards and best wishes, Justin Clift Jay O'Connor wrote: > Some backs-story. I'm in the process of converting our internal file based > data storage to an RDBMS. After looking a bit at PostgreSQL and MySQL, I > chose Postgresql. My boss has heard of MySQL and has not heard of > PostgreSQL and every now and then ahe make allusions that we shuold be > using MySQL. > > One comment he got from the architect of another web site is as follows > > >>If we were to start again >>from scratch now, I'd still use InnoDB over postgres unless the >>performance picked up with postgres recently. >> >>Keep in mind our application is very write-heavy so your numbers may >>be different. Does postgres still keep the old row versions in the >>primary-key B-Tree? If it does I doubt performance improved much for >>write-heavy apps, that was a very poor design decision by them. InnoDB >>takes the Oracle route of moving old row versions to a seperate >>on-disk data structure. > > > Does what he say make sense? If so, has the situation changed? BNasically, > I need something intelligent to say to my boss to either counter or > mitigate his perception. > > Thanks > > Take care, > Jay > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html
On Wed, 11 Jun 2003, Jay O'Connor wrote: > Some backs-story. I'm in the process of converting our internal file based > data storage to an RDBMS. After looking a bit at PostgreSQL and MySQL, I > chose Postgresql. My boss has heard of MySQL and has not heard of > PostgreSQL and every now and then ahe make allusions that we shuold be > using MySQL. > > One comment he got from the architect of another web site is as follows > > > If we were to start again > > from scratch now, I'd still use InnoDB over postgres unless the > > performance picked up with postgres recently. > > > > Keep in mind our application is very write-heavy so your numbers may > > be different. Does postgres still keep the old row versions in the > > primary-key B-Tree? If it does I doubt performance improved much for > > write-heavy apps, that was a very poor design decision by them. InnoDB > > takes the Oracle route of moving old row versions to a seperate > > on-disk data structure. > > Does what he say make sense? If so, has the situation changed? BNasically, > I need something intelligent to say to my boss to either counter or > mitigate his perception. Well, one big thing for update (or delete/insert) heavy apps is to make sure to set the free space map to a reasonable size and vacuum frequently. If he's only tried older PostgreSQL servers, he may not realize that vacuums can be run concurrently with queries. There were still index related bloat problems, but IIRC those should be going away in 7.4.
"Jay O'Connor" <joconnor@cybermesa.com> writes:
> One comment he got from the architect of another web site is as follows
As best I can tell, this comment is based on ancient information.
Postgres has gotten very substantially faster over the years ... and
it also helps a lot to know something about how to tune it (the
out-of-the-box settings are excessively conservative). I would not
put a lot of stock in hearsay evaluations of performance, especially
not from someone who hasn't tested recent PG releases.
regards, tom lane
> The _very first_ thing to do with any PostgreSQL installation is bump up > the memory settings (at least "sort_mem" and "shared_buffers") in the > postgresql.conf and restart it. This makes me wonder why is the default configuration so conservative? If this is the very first thing with any postgre installation then wouldn't it make more sense to have higher default memory settings and these who then have problems could lower the memory settings.
> 3) If your point was to move to a relational database, then you should > choose Postgres. MySQL, although it's SQL, hardly qualifies as relational > > For example, MySQL does not have: > > * Views > * Triggers > * Stored Procedures > * Subselects (although they may have added this one) > > And then when you want to do real transactions, MySQLs speed slows way > down. > > If you are moving _to_ a database system, it seems pointless to stop > halfway and go with MySQL, when you can go the full way with Postgres. According to the MySQL website, subqueries are in 4.1, which is is still in alpha release. I can speak with some first-hand experience about both databases. A project I became involved with this spring was originally begun using MySQL, but my recommendation as the incoming DB architect/DBA was that we switch to pgsql, in large measure because of the features listed above. I also have Oracle 9i, MySQL and pgsql running on the same Redhat 8 platform with similar datasets, so I can address some performance issues, and when I get past a deadline this month I may be able to run some three-way performance tests on some moderately large tables (10 million rows). Based on some ad-hoc work I've been doing with that data, it appears to me that Oracle beats pgsql in most update situations, though perhaps that will change with 7.4. -- Mike Nolan
Kaarel <kaarel@future.ee> writes:
> This makes me wonder why is the default configuration so conservative?
There are still a lot of platforms where desirable settings will cause
the database to fail to start, because the default kernel limits on
shared memory are still set for 1980s-vintage hardware.
We have had a policy for a long time that we'd rather the out-of-the-box
settings be guaranteed to start than that they be optimal for
performance. No one is going to get as far as testing performance
if the system won't start for them.
Having said that, though, I do wonder whether we couldn't bump them up a
little. Are there still any live platforms where the default SHMMAX is
less than 4 MB? (There was discussion of this a month or two back on
pghackers, but no conclusion.)
regards, tom lane
Tom Lane wrote: > Kaarel <kaarel@future.ee> writes: > > This makes me wonder why is the default configuration so conservative? > > There are still a lot of platforms where desirable settings will cause > the database to fail to start, because the default kernel limits on > shared memory are still set for 1980s-vintage hardware. > > We have had a policy for a long time that we'd rather the out-of-the-box > settings be guaranteed to start than that they be optimal for > performance. No one is going to get as far as testing performance > if the system won't start for them. > > Having said that, though, I do wonder whether we couldn't bump them up a > little. Are there still any live platforms where the default SHMMAX is > less than 4 MB? (There was discussion of this a month or two back on > pghackers, but no conclusion.) Could we have per-platforms defaults? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
> -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Wednesday, June 11, 2003 12:28 PM > To: Kaarel > Cc: Justin Clift; Jay O'Connor; pgsql-general@postgresql.org > Subject: Re: [GENERAL] Postgres performance comments from a > MySQL user > > > Kaarel <kaarel@future.ee> writes: > > This makes me wonder why is the default configuration so > conservative? > > There are still a lot of platforms where desirable settings > will cause the database to fail to start, because the default > kernel limits on shared memory are still set for > 1980s-vintage hardware. > > We have had a policy for a long time that we'd rather the > out-of-the-box settings be guaranteed to start than that they > be optimal for performance. No one is going to get as far as > testing performance if the system won't start for them. > > Having said that, though, I do wonder whether we couldn't > bump them up a little. Are there still any live platforms > where the default SHMMAX is less than 4 MB? (There was > discussion of this a month or two back on pghackers, but no > conclusion.) I think this would be very, very nice: Config_tool.exe runs, and examines: Operating system, available memory, disk, cpu speed, etc. (whatever it can figure out). Then it makes good guesses for what PostgreSQL parameters to use and reconfigures PostgreSQL. If it was part of the installation, then even better.
On Wed, 2003-06-11 at 21:28, Tom Lane wrote: > There are still a lot of platforms where desirable settings will cause > the database to fail to start, because the default kernel limits on > shared memory are still set for 1980s-vintage hardware. > > We have had a policy for a long time that we'd rather the out-of-the-box > settings be guaranteed to start than that they be optimal for > performance. No one is going to get as far as testing performance > if the system won't start for them. How about providing several .conf files with an indication of what hardware profiles they correspond to? For example a low end, a mid range and a high end machine. Plus a SMP .conf file. Cheers Tony Grant -- www.tgds.net Library management software toolkit, redhat linux on Sony Vaio C1XD, Dreamweaver MX with Tomcat and PostgreSQL
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> Having said that, though, I do wonder whether we couldn't bump them up a
>> little. Are there still any live platforms where the default SHMMAX is
>> less than 4 MB? (There was discussion of this a month or two back on
>> pghackers, but no conclusion.)
> Could we have per-platforms defaults?
Hmm, that might be a nice solution. Could we arrange for the template
file to supply defaults for shared_buffers and so forth? They'd have
to be inserted into postgresql.conf during installation or initdb, but
we already do some editing of postgresql.conf during initdb ...
regards, tom lane
> We have had a policy for a long time that we'd rather the out-of-the-box > settings be guaranteed to start than that they be optimal for > performance. No one is going to get as far as testing performance > if the system won't start for them. I get the point here and I respect that. I'm just afraid that as long as the default settings reduce performance all benchamrks using the default install will show postgre in slow light.
> -----Original Message-----
> From: Dann Corbit
> Sent: Wednesday, June 11, 2003 12:35 PM
> To: Tom Lane; Kaarel
> Cc: Justin Clift; Jay O'Connor; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Postgres performance comments from a
> MySQL user
>
>
> > -----Original Message-----
> > From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> > Sent: Wednesday, June 11, 2003 12:28 PM
> > To: Kaarel
> > Cc: Justin Clift; Jay O'Connor; pgsql-general@postgresql.org
> > Subject: Re: [GENERAL] Postgres performance comments from a
> > MySQL user
> >
> >
> > Kaarel <kaarel@future.ee> writes:
> > > This makes me wonder why is the default configuration so
> > conservative?
> >
> > There are still a lot of platforms where desirable settings
> > will cause the database to fail to start, because the default
> > kernel limits on shared memory are still set for
> > 1980s-vintage hardware.
> >
> > We have had a policy for a long time that we'd rather the
> > out-of-the-box settings be guaranteed to start than that they
> > be optimal for performance. No one is going to get as far as
> > testing performance if the system won't start for them.
> >
> > Having said that, though, I do wonder whether we couldn't
> > bump them up a little. Are there still any live platforms
> > where the default SHMMAX is less than 4 MB? (There was
> > discussion of this a month or two back on pghackers, but no
> > conclusion.)
>
> I think this would be very, very nice:
> Config_tool.exe runs, and examines:
> Operating system, available memory, disk, cpu speed, etc.
> (whatever it can figure out). Then it makes good guesses for
> what PostgreSQL parameters to use and reconfigures PostgreSQL.
>
> If it was part of the installation, then even better.
/*
Sample for Win32 machines. It should be a snap for some UNIX guru to
write one for UNIX type systems.
*/
#include <windows.h>
#include <stdio.h>
// Use to change the divisor from Kb to Mb.
#define DIV 1024
// #define DIV 1
char *divisor = "K";
// char *divisor = "";
// Handle the width of the field in which to print numbers this way to
// make changes easier. The asterisk in the print format specifier
// "%*ld" takes an int from the argument list, and uses it to pad and
// right-justify the number being formatted.
#define WIDTH 7
int main(int argc, char *argv[])
{
MEMORYSTATUS stat;
GlobalMemoryStatus(&stat);
printf("%ld percent of memory is in use.\n",
stat.dwMemoryLoad);
printf("There are %*ld total %sbytes of physical memory.\n",
WIDTH, stat.dwTotalPhys / DIV, divisor);
printf("There are %*ld free %sbytes of physical memory.\n",
WIDTH, stat.dwAvailPhys / DIV, divisor);
printf("There are %*ld total %sbytes of paging file.\n",
WIDTH, stat.dwTotalPageFile / DIV, divisor);
printf("There are %*ld free %sbytes of paging file.\n",
WIDTH, stat.dwAvailPageFile / DIV, divisor);
printf("There are %*lx total %sbytes of virtual memory.\n",
WIDTH, stat.dwTotalVirtual / DIV, divisor);
printf("There are %*lx free %sbytes of virtual memory.\n",
WIDTH, stat.dwAvailVirtual / DIV, divisor);
return 0;
}
/*
C:\tmp>ramsize
74 percent of memory is in use.
There are 523744 total Kbytes of physical memory.
There are 131796 free Kbytes of physical memory.
There are 1539616 total Kbytes of paging file.
There are 646924 free Kbytes of paging file.
There are 1fff80 total Kbytes of virtual memory.
There are 1feb54 free Kbytes of virtual memory.
*/
On 6/11/03 10:39 AM, "Justin Clift" <justin@postgresql.org> wrote: > Tell him to test it with decent settings (try about 4000 for each as an > initial start), and he'll find that a decently tuned PostgreSQL matches > the speed of a MySQL installation with any table type. An in > write-intensive applications, the MySQL server will always fall behind. > _Especially_ as the number of simultaneous clients rises. MySQL falls > behind, as does Oracle 8i a bit further on (not sure about 9i and 9iR2), > and PostgreSQL keeps on performing at pretty much the same throughput > for far higher numbers of client connections. > > And _that_, is for real. > Are there *any* recent benchmarks that show all this? The most recent ones I can find are a couple of years old. Now, eWeek did run a database benchmark some time in the last year, in which they compared a variety of commercial engines and an OS engine. We can guess which was the open source db. MySQL of course. If anyone in the advocacy area wants to write to the eWeek author who did the report, Timothy Dyck, it'd be good to push for a benchmark that includes postgres. Of course, since we're all involved in advocacy in some way, I could do it myself... Anyway, original question ... Any recent benchmarks that show how postgres performs against others, especially under load? -- sgl ======================================================= Steve Lane Vice President The Moyer Group 14 North Peoria St Suite 2H Chicago, IL 60607 Voice: (312) 433-2421 Email: slane@moyergroup.com Fax: (312) 850-3930 Web: http://www.moyergroup.com =======================================================
Justin Clift wrote: > The _very first_ thing to do with any PostgreSQL installation is bump up > the memory settings (at least "sort_mem" and "shared_buffers") in the > postgresql.conf and restart it. > > Tell him to test it with decent settings (try about 4000 for each as an > initial start), Perhaps that should be in the message that 'make install' echoes and in comments in the conf file itself?
I could really use some links to these performance comparisons. Oracle performance statistics are problematic though,as allcontracts to use Oracle require the user to NEVER share performance information to the public. Someone should sue themfor that. Or figure out a way to influence the market to start being pissed off about that. Anyway, I'm going to write a paper for my masters on using PGSL vs. Oracle vs. SQL Server vs. DB2. Any and all sources ofperformance, usage, configurations, etc of these would be most welcome. Steve Lane wrote: > On 6/11/03 10:39 AM, "Justin Clift" <justin@postgresql.org> wrote: > > >>Tell him to test it with decent settings (try about 4000 for each as an >>initial start), and he'll find that a decently tuned PostgreSQL matches >>the speed of a MySQL installation with any table type. An in >>write-intensive applications, the MySQL server will always fall behind. >>_Especially_ as the number of simultaneous clients rises. MySQL falls >>behind, as does Oracle 8i a bit further on (not sure about 9i and 9iR2), >>and PostgreSQL keeps on performing at pretty much the same throughput >>for far higher numbers of client connections. >> >>And _that_, is for real. >> > > > Are there *any* recent benchmarks that show all this? The most recent ones I > can find are a couple of years old. Now, eWeek did run a database benchmark > some time in the last year, in which they compared a variety of commercial > engines and an OS engine. We can guess which was the open source db. MySQL > of course. > > If anyone in the advocacy area wants to write to the eWeek author who did > the report, Timothy Dyck, it'd be good to push for a benchmark that includes > postgres. Of course, since we're all involved in advocacy in some way, I > could do it myself... > > Anyway, original question ... Any recent benchmarks that show how postgres > performs against others, especially under load? > > -- sgl > > > ======================================================= > Steve Lane > > Vice President > The Moyer Group > 14 North Peoria St Suite 2H > Chicago, IL 60607 > > Voice: (312) 433-2421 Email: slane@moyergroup.com > Fax: (312) 850-3930 Web: http://www.moyergroup.com > ======================================================= > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Bruce Momjian" <pgman@candle.pha.pa.us> Cc: "Kaarel" <kaarel@future.ee>; "Justin Clift" <justin@postgresql.org>; "Jay O'Connor" <joconnor@cybermesa.com>; <pgsql-general@postgresql.org> Sent: Wednesday, June 11, 2003 9:37 PM Subject: Re: [GENERAL] Postgres performance comments from a MySQL user > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Tom Lane wrote: > >> Having said that, though, I do wonder whether we couldn't bump them up a > >> little. Are there still any live platforms where the default SHMMAX is > >> less than 4 MB? (There was discussion of this a month or two back on > >> pghackers, but no conclusion.) > > > Could we have per-platforms defaults? > > Hmm, that might be a nice solution. Could we arrange for the template > file to supply defaults for shared_buffers and so forth? They'd have > to be inserted into postgresql.conf during installation or initdb, but > we already do some editing of postgresql.conf during initdb ... > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > There is project on gborg(or somewhere else), written as deamon that monitors server and calculates the best configuration based not only on platform, but also on server load.(damn, I can't remember the project name!). I think that monitoring is done using pg_stat. If group think that this is important isue, we can try to do something like this, or even include that project in contrib ? I know that I did downlad the source, but never tried if it works. Maybe I can try it I let You know ? Even beter if author is reading this list ! Regards !
Some databases (MySQL I think) ship several example configurations sized for different installations. The default is very safe, but it's simply a matter of choosing between "small.conf", "medium.conf", "big.conf", "huge.conf" and copying it over the standard "tiny.conf" file. Each config file contains comments near the top of the file that specify suggested hardware requirements for using it. Providing a similar series of config files for postgres would probably cut the traffic to the performance mailing list significantly and end the need for discussions such as this. (not that I mind the discussion) -- Matthew Nuzum www.bearfruit.org cobalt@bearfruit.org > -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Wednesday, June 11, 2003 3:28 PM > To: Kaarel > Cc: Justin Clift; Jay O'Connor; pgsql-general@postgresql.org > Subject: Re: Postgres performance comments from a MySQL user > > Kaarel <kaarel@future.ee> writes: > > This makes me wonder why is the default configuration so conservative? > > There are still a lot of platforms where desirable settings will cause > the database to fail to start, because the default kernel limits on > shared memory are still set for 1980s-vintage hardware. > > We have had a policy for a long time that we'd rather the out-of-the-box > settings be guaranteed to start than that they be optimal for > performance. No one is going to get as far as testing performance > if the system won't start for them. > > Having said that, though, I do wonder whether we couldn't bump them up a > little. Are there still any live platforms where the default SHMMAX is > less than 4 MB? (There was discussion of this a month or two back on > pghackers, but no conclusion.) > > regards, tom lane
The problem with this is that in troubleshooting there's no frame of
reference. Having a stock config file, or stock config file options allows
a person to write to the list and say, "hey, I'm using medium.conf and I
have x ram..."
The alternative is, "hey, see my attached .conf file..." which takes a lot
more effort.
--
Matthew Nuzum
www.bearfruit.org
cobalt@bearfruit.org
> -----Original Message-----
> From: Dann Corbit [mailto:DCorbit@connx.com]
> Sent: Wednesday, June 11, 2003 4:05 PM
> To: Dann Corbit; Tom Lane; Kaarel
> Cc: Justin Clift; Jay O'Connor; pgsql-general@postgresql.org
> Subject: Re: Postgres performance comments from a MySQL user
>
> > -----Original Message-----
> > From: Dann Corbit
> > Sent: Wednesday, June 11, 2003 12:35 PM
> > To: Tom Lane; Kaarel
> > Cc: Justin Clift; Jay O'Connor; pgsql-general@postgresql.org
> > Subject: Re: [GENERAL] Postgres performance comments from a
> > MySQL user
> >
> >
> > > -----Original Message-----
> > > From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> > > Sent: Wednesday, June 11, 2003 12:28 PM
> > > To: Kaarel
> > > Cc: Justin Clift; Jay O'Connor; pgsql-general@postgresql.org
> > > Subject: Re: [GENERAL] Postgres performance comments from a
> > > MySQL user
> > >
> > >
> > > Kaarel <kaarel@future.ee> writes:
> > > > This makes me wonder why is the default configuration so
> > > conservative?
> > >
> > > There are still a lot of platforms where desirable settings
> > > will cause the database to fail to start, because the default
> > > kernel limits on shared memory are still set for
> > > 1980s-vintage hardware.
> > >
> > > We have had a policy for a long time that we'd rather the
> > > out-of-the-box settings be guaranteed to start than that they
> > > be optimal for performance. No one is going to get as far as
> > > testing performance if the system won't start for them.
> > >
> > > Having said that, though, I do wonder whether we couldn't
> > > bump them up a little. Are there still any live platforms
> > > where the default SHMMAX is less than 4 MB? (There was
> > > discussion of this a month or two back on pghackers, but no
> > > conclusion.)
> >
> > I think this would be very, very nice:
> > Config_tool.exe runs, and examines:
> > Operating system, available memory, disk, cpu speed, etc.
> > (whatever it can figure out). Then it makes good guesses for
> > what PostgreSQL parameters to use and reconfigures PostgreSQL.
> >
> > If it was part of the installation, then even better.
>
> /*
> Sample for Win32 machines. It should be a snap for some UNIX guru to
> write one for UNIX type systems.
> */
>
>
> #include <windows.h>
> #include <stdio.h>
> // Use to change the divisor from Kb to Mb.
>
> #define DIV 1024
> // #define DIV 1
>
> char *divisor = "K";
> // char *divisor = "";
>
> // Handle the width of the field in which to print numbers this way to
> // make changes easier. The asterisk in the print format specifier
> // "%*ld" takes an int from the argument list, and uses it to pad and
> // right-justify the number being formatted.
> #define WIDTH 7
>
> int main(int argc, char *argv[])
> {
> MEMORYSTATUS stat;
>
> GlobalMemoryStatus(&stat);
>
> printf("%ld percent of memory is in use.\n",
> stat.dwMemoryLoad);
> printf("There are %*ld total %sbytes of physical memory.\n",
> WIDTH, stat.dwTotalPhys / DIV, divisor);
> printf("There are %*ld free %sbytes of physical memory.\n",
> WIDTH, stat.dwAvailPhys / DIV, divisor);
> printf("There are %*ld total %sbytes of paging file.\n",
> WIDTH, stat.dwTotalPageFile / DIV, divisor);
> printf("There are %*ld free %sbytes of paging file.\n",
> WIDTH, stat.dwAvailPageFile / DIV, divisor);
> printf("There are %*lx total %sbytes of virtual memory.\n",
> WIDTH, stat.dwTotalVirtual / DIV, divisor);
> printf("There are %*lx free %sbytes of virtual memory.\n",
> WIDTH, stat.dwAvailVirtual / DIV, divisor);
> return 0;
> }
> /*
> C:\tmp>ramsize
> 74 percent of memory is in use.
> There are 523744 total Kbytes of physical memory.
> There are 131796 free Kbytes of physical memory.
> There are 1539616 total Kbytes of paging file.
> There are 646924 free Kbytes of paging file.
> There are 1fff80 total Kbytes of virtual memory.
> There are 1feb54 free Kbytes of virtual memory.
> */
We are currently in the middle of a process of moving from SQL Server 7 to PostgreSQL. We are running PostgreSQL 7.3.2 *untuned* on a 384M single CPU machine and it beats a 4 CPU server with 2GB of memory running SQL Server 7. This application is using stored procedures returning result sets quite heavily and I was extremely and positively surprised from PostgreSQL's performance. Bottom line is that you have to try it yourself and see how it performs in your environment and in your application. Avi On Wednesday, Jun 11, 2003, at 11:49 America/Chicago, Tom Lane wrote: > "Jay O'Connor" <joconnor@cybermesa.com> writes: >> One comment he got from the architect of another web site is as >> follows > > As best I can tell, this comment is based on ancient information. > Postgres has gotten very substantially faster over the years ... and > it also helps a lot to know something about how to tune it (the > out-of-the-box settings are excessively conservative). I would not > put a lot of stock in hearsay evaluations of performance, especially > not from someone who hasn't tested recent PG releases.
Could you clarify are you moving from MySQL or Microsoft SQL 7.0?
-----Original Message-----
From: Avi Schwartz [mailto:avi@CFFtechnologies.com]
Sent: Wednesday, June 11, 2003 7:21 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Postgres performance comments from a MySQL user
We are currently in the middle of a process of moving from SQL Server 7
to PostgreSQL. We are running PostgreSQL 7.3.2 *untuned* on a 384M
single CPU machine and it beats a 4 CPU server with 2GB of memory
running SQL Server 7. This application is using stored procedures
returning result sets quite heavily and I was extremely and positively
surprised from PostgreSQL's performance.
Bottom line is that you have to try it yourself and see how it performs
in your environment and in your application.
Avi
On Wednesday, Jun 11, 2003, at 11:49 America/Chicago, Tom Lane wrote:
> "Jay O'Connor" <joconnor@cybermesa.com> writes:
>> One comment he got from the architect of another web site is as
>> follows
>
> As best I can tell, this comment is based on ancient information.
> Postgres has gotten very substantially faster over the years ... and
> it also helps a lot to know something about how to tune it (the
> out-of-the-box settings are excessively conservative). I would not
> put a lot of stock in hearsay evaluations of performance, especially
> not from someone who hasn't tested recent PG releases.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
From Microsoft SQL Server 7. We looked briefly at MySQL but realized that the features we need will not be out until at least version 5 while PostgreSQL has them now. Avi On Wednesday, Jun 11, 2003, at 21:34 America/Chicago, Maksim Likharev wrote: > Could you clarify are you moving from MySQL or Microsoft SQL 7.0? > > > -----Original Message----- > From: Avi Schwartz > Sent: Wednesday, June 11, 2003 7:21 PM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Postgres performance comments from a MySQL user > > > We are currently in the middle of a process of moving from SQL Server 7 > to PostgreSQL.
My I ask on what operations PG better than MSSQL? cause out of my observations only spatial data types, could influence a choice between MSSQL and PG. Thank you. -----Original Message----- From: Avi Schwartz [mailto:avi@CFFtechnologies.com] Sent: Wednesday, June 11, 2003 8:12 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Postgres performance comments from a MySQL user From Microsoft SQL Server 7. We looked briefly at MySQL but realized that the features we need will not be out until at least version 5 while PostgreSQL has them now. Avi On Wednesday, Jun 11, 2003, at 21:34 America/Chicago, Maksim Likharev wrote: > Could you clarify are you moving from MySQL or Microsoft SQL 7.0? > > > -----Original Message----- > From: Avi Schwartz > Sent: Wednesday, June 11, 2003 7:21 PM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Postgres performance comments from a MySQL user > > > We are currently in the middle of a process of moving from SQL Server 7 > to PostgreSQL. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
The biggest improvement we noticed was in two areas. We have some very complex selects that join 7-8 tables which perform 30-40% faster under PGSQL. The second thing that performs better are the above mentioned result set returning stored procedures. BTW, the reason we started looking at PSQL is not due to performance, this just came as a pleasant surprise. The real reason is in the fact the we are 90% linux in our server environment and the only Windows component is SQL Server 7. SS7 does not play nicely with Linux and lacks any tools to use from the Linux environment. Windows itself is hard to manage remotely and since the production environment is co-located it became a real issue. Avi On Wednesday, Jun 11, 2003, at 22:24 America/Chicago, Maksim Likharev wrote: > My I ask on what operations PG better than MSSQL? > > cause out of my observations only spatial data types, > could influence a choice between MSSQL and PG. > > Thank you.
On Mié 11 Jun 2003 12:29, Jonathan Bartlett wrote:
>
> 3) If your point was to move to a relational database, then you should
> choose Postgres. MySQL, although it's SQL, hardly qualifies as
> relational
MySQL doesn't have relations at all, unless you put the InnoDB module,
which stamps down performance.
An example I tried to do on a MySQL without InnoDB was:
CREATE TABLE testing (
id INT,
word VARCHAR(20) REFERENCES other_table("word")
);
(knowing that other_table exists (I prefiously created it) and has word as
a VARCHAR(20) field).
An error is what I got.
--
Porqué usar una base de datos relacional cualquiera,
si podés usar PostgreSQL?
-----------------------------------------------------------------
Martín Marqués | mmarques@unl.edu.ar
Programador, Administrador, DBA | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------
On Mié 11 Jun 2003 16:01, Kaarel wrote:
> > The _very first_ thing to do with any PostgreSQL installation is bump
> > up the memory settings (at least "sort_mem" and "shared_buffers") in
> > the postgresql.conf and restart it.
>
> This makes me wonder why is the default configuration so conservative?
> If this is the very first thing with any postgre installation then
> wouldn't it make more sense to have higher default memory settings and
> these who then have problems could lower the memory settings.
You can't expect to have a good DB server if you didn't take the time to
read the performance tips on the PostgreSQL Administration Guide, and
gone through the postgresql.conf file.
The same goes for any server, httpd, MTA, etc. For small load a default
Apache, or default Postfix will work OK, but on heavy load you have to
make tuning, or the system will fall to pieces.
--
Porqué usar una base de datos relacional cualquiera,
si podés usar PostgreSQL?
-----------------------------------------------------------------
Martín Marqués | mmarques@unl.edu.ar
Programador, Administrador, DBA | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------
Joseph Shraibman wrote: > Justin Clift wrote: > >> The _very first_ thing to do with any PostgreSQL installation is bump >> up the memory settings (at least "sort_mem" and "shared_buffers") in >> the postgresql.conf and restart it. >> >> Tell him to test it with decent settings (try about 4000 for each as >> an initial start), > > Perhaps that should be in the message that 'make install' echoes and in > comments in the conf file itself? > Probably it's a good idea to have some mention of this, as even though we should alter the source to higher defaults forour next release, there are potentially people that would read a message like this and go "wow, didn't know that", then tune their existing installations as well. With Solaris, 4000 for those memory settings is a good place to start and it's generally fine to just leave them at thatunless there's a definite reason for performance tuning. No idea with other OS's. Anyone feel like submitting a patch to alter the default settings to a higher mark? Don't think it's been done yet, andit'd be a shame to forget it before feature freeze time of the next release. Regards and best wishes, Justin Clift -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
For what its worth, a while ago, I was on http://www.tpc.org trying to find some useful comparisons of at least a test swing of code & and sample data that I could use in my lab for my own tests. I don't remember getting that far with it- I simply gave the guy the article about PG being used to serve .org :) Maybe there is more information the now... Quoting Dennis Gearon <gearond@cvc.net>: > I could really use some links to these performance comparisons. Oracle > performance statistics are problematic though,as all contracts to use Oracle > require the user to NEVER share performance information to the public. > Someone should sue them for that. Or figure out a way to influence the market > to start being pissed off about that. > > Anyway, I'm going to write a paper for my masters on using PGSL vs. Oracle > vs. SQL Server vs. DB2. Any and all sources of performance, usage, > configurations, etc of these would be most welcome. > > Steve Lane wrote: > > > On 6/11/03 10:39 AM, "Justin Clift" <justin@postgresql.org> wrote: > > > > > >>Tell him to test it with decent settings (try about 4000 for each as an > >>initial start), and he'll find that a decently tuned PostgreSQL matches > >>the speed of a MySQL installation with any table type. An in > >>write-intensive applications, the MySQL server will always fall behind. > >>_Especially_ as the number of simultaneous clients rises. MySQL falls > >>behind, as does Oracle 8i a bit further on (not sure about 9i and 9iR2), > >>and PostgreSQL keeps on performing at pretty much the same throughput > >>for far higher numbers of client connections. > >> > >>And _that_, is for real. > >> > > > > > > Are there *any* recent benchmarks that show all this? The most recent ones > I > > can find are a couple of years old. Now, eWeek did run a database > benchmark > > some time in the last year, in which they compared a variety of commercial > > engines and an OS engine. We can guess which was the open source db. MySQL > > of course. > > > > If anyone in the advocacy area wants to write to the eWeek author who did > > the report, Timothy Dyck, it'd be good to push for a benchmark that > includes > > postgres. Of course, since we're all involved in advocacy in some way, I > > could do it myself... > > > > Anyway, original question ... Any recent benchmarks that show how postgres > > performs against others, especially under load? > > > > -- sgl > > > > > > ======================================================= > > Steve Lane > > > > Vice President > > The Moyer Group > > 14 North Peoria St Suite 2H > > Chicago, IL 60607 > > > > Voice: (312) 433-2421 Email: slane@moyergroup.com > > Fax: (312) 850-3930 Web: http://www.moyergroup.com > > ======================================================= > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > -- Keith C. Perry Director of Networks & Applications VCSN, Inc. http://vcsn.com ____________________________________ This email account is being host by: VCSN, Inc : http://vcsn.com
On Thu, 2003-06-12 at 08:05, Martin Marques wrote:
> On Mié 11 Jun 2003 12:29, Jonathan Bartlett wrote:
> >
> > 3) If your point was to move to a relational database, then you should
> > choose Postgres. MySQL, although it's SQL, hardly qualifies as
> > relational
>
> MySQL doesn't have relations at all, unless you put the InnoDB module,
> which stamps down performance.
>
> An example I tried to do on a MySQL without InnoDB was:
>
> CREATE TABLE testing (
> id INT,
> word VARCHAR(20) REFERENCES other_table("word")
> );
>
> (knowing that other_table exists (I prefiously created it) and has word as
> a VARCHAR(20) field).
>
> An error is what I got.
A table *is* a relation. You seem to be referring to foreign keys.
Maybe MySQL has a different syntax?
--
+-----------------------------------------------------------+
| Ron Johnson, Jr. Home: ron.l.johnson@cox.net |
| Jefferson, LA USA http://members.cox.net/ron.l.johnson |
| |
| Regarding war zones: "There's nothing sacrosanct about a |
| hotel with a bunch of journalists in it." |
| Marine Lt. Gen. Bernard E. Trainor (Retired) |
+-----------------------------------------------------------+
On 12 Jun 2003 08:50:30 -0500 Ron Johnson <ron.l.johnson@cox.net> wrote:
> A table *is* a relation. You seem to be referring to foreign keys.
> Maybe MySQL has a different syntax?
last time i checked, MySQL didn't do referential integrity. it did accept
some syntax for describing foreign keys, but treated it as a no-op. this
could be different in MySQL 4.x, i haven't looked into it. i rarely need to
work with MySQL, most of my clients who want me to do database stuff are
using PostgreSQL.
richard
--
Richard Welty rwelty@averillpark.net
Averill Park Networking 518-573-7592
Unix, Linux, IP Network Engineering, Security
How about adding the mesage about tuning to the: 1/ 'don't kill the postmaster message at the bottom of emails' 2/ The install messages. 3/ The build messages. 4/ The login screen for the superuser of the databse? Justin Clift wrote: > > Joseph Shraibman wrote: > > Justin Clift wrote: > > > >> The _very first_ thing to do with any PostgreSQL installation is bump > >> up the memory settings (at least "sort_mem" and "shared_buffers") in > >> the postgresql.conf and restart it. > >> > >> Tell him to test it with decent settings (try about 4000 for each as > >> an initial start), > > > > Perhaps that should be in the message that 'make install' echoes and in > > comments in the conf file itself? > > > > Probably it's a good idea to have some mention of this, as even though we should alter the source to higher defaults forour next release, there are potentially > people that would read a message like this and go "wow, didn't know that", then tune their existing installations as well. > > With Solaris, 4000 for those memory settings is a good place to start and it's generally fine to just leave them at thatunless there's a definite reason for > performance tuning. No idea with other OS's. > > Anyone feel like submitting a patch to alter the default settings to a higher mark? Don't think it's been done yet, andit'd be a shame to forget it before > feature freeze time of the next release. > > Regards and best wishes, > > Justin Clift > > -- > "My grandfather once told me that there are two kinds of people: those > who work and those who take the credit. He told me to try to be in the > first group; there was less competition there." > - Indira Gandhi > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
On Thursday 12 June 2003 15:50, Ron Johnson wrote:
> On Thu, 2003-06-12 at 08:05, Martin Marques wrote:
> > On Mié 11 Jun 2003 12:29, Jonathan Bartlett wrote:
> > > 3) If your point was to move to a relational database, then you should
> > > choose Postgres. MySQL, although it's SQL, hardly qualifies as
> > > relational
> >
> > MySQL doesn't have relations at all, unless you put the InnoDB module,
> > which stamps down performance.
> >
> > An example I tried to do on a MySQL without InnoDB was:
> >
> > CREATE TABLE testing (
> > id INT,
> > word VARCHAR(20) REFERENCES other_table("word")
> > );
> >
> > (knowing that other_table exists (I prefiously created it) and has word
> > as a VARCHAR(20) field).
> >
> > An error is what I got.
>
> A table *is* a relation. You seem to be referring to foreign keys.
> Maybe MySQL has a different syntax?
It accepts syntax like this:
CREATE TABLE exmpl6 (
id INT,
blah TEXT,
INDEX(id),
CONSTRAINT id_fkey FOREIGN KEY (id) REFERENCES exmpl5(id) ON DELETE
NO ACTION
);
but ignores it silently if you omit the "type=innodb" after the table
definition (at least in 3.23.x versions).
(When developing for MySQL I have a list of gotchas which I
attach to the monitor, saves me many happy afternoons of
headscratching ;-)
Ian Barwick
barwick@gmx.net
On Jue 12 Jun 2003 10:50, Ron Johnson wrote:
> On Thu, 2003-06-12 at 08:05, Martin Marques wrote:
> > On Mié 11 Jun 2003 12:29, Jonathan Bartlett wrote:
> > > 3) If your point was to move to a relational database, then you
> > > should choose Postgres. MySQL, although it's SQL, hardly qualifies
> > > as relational
> >
> > MySQL doesn't have relations at all, unless you put the InnoDB
> > module, which stamps down performance.
> >
> > An example I tried to do on a MySQL without InnoDB was:
> >
> > CREATE TABLE testing (
> > id INT,
> > word VARCHAR(20) REFERENCES other_table("word")
> > );
> >
> > (knowing that other_table exists (I prefiously created it) and has
> > word as a VARCHAR(20) field).
> >
> > An error is what I got.
>
> A table *is* a relation. You seem to be referring to foreign keys.
> Maybe MySQL has a different syntax?
Sorry, you are right about that. I was talking about references of primary
keys/foreign keys.
Any way, the syntax was right, the InnoDB module was missing, as is said
here:
http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html
--
Porqué usar una base de datos relacional cualquiera,
si podés usar PostgreSQL?
-----------------------------------------------------------------
Martín Marqués | mmarques@unl.edu.ar
Programador, Administrador, DBA | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------
On Wednesday 11 June 2003 2:37 pm, Matthew Nuzum wrote: > The problem with this is that in troubleshooting there's no frame of > reference. Having a stock config file, or stock config file options allows > a person to write to the list and say, "hey, I'm using medium.conf and I > have x ram..." > > The alternative is, "hey, see my attached .conf file..." which takes a lot > more effort. The postfix mail transport agent has a command "postconf" which allows you to read or change entries in the config file. Like postgresql, postfix uses certain defaults when there is no corresponding configuration entry. Running "postconf" displays all settings while "postconf -n" command displays only non-default configuration settings. The output of "postconf -n" is generally one of the first things requested when someone asks a question. (Being a mail server, most questions are ultimately configuration related - postgresql questions are more varied.) You can also change a setting with "postconf parameter=value". Perhaps a similar command would be useful for postgresql. Just please don't call it "postconf". :) Note: this would also provide a nice core interface for all sorts of purposes like updating configuration entries based on automatic analysis of database size/memory/cpu or based on asking the admin questions about the database use profile and then using the command (say pg_conf) to update specified options, save current settings and restore everything to default for testing/troubleshooting then restore the original settings, etc. Looking through the useful options for postconf, I think a useful base set of features for pg_conf would be pg_conf -D datadir [-n] [-h] [-d] [parameter...] Output (unless -h is specified) would look like: foo = 8192 bar = 0 ... where: -n = only output values that are different than the default -h = show the value only (useful for extracting values into scripts, eg, pg_conf -h foo would return "8192" instead of "foo = 8192". Note: I just borrowed -h from postconf - choose any logical letter.) -d = show the default value for the parameter The optional parameter list limits the display to that/those parameter(s). I haven't checked the source code, yet, but I suspect that most of the necessary code for such a command is already in whatever module reads postgresql.conf. Thoughts? Cheers, Steve
Hi Dave, Are we able to get more messages added to the list of mailing list end tags, so we can mention something about server tuning or such? The rest of these things will probably have to be done in the PG source code itself. :-) Regards and best wishes, Justin Clift Dennis Gearon wrote: > How about adding the mesage about tuning to the: > > 1/ 'don't kill the postmaster message at the bottom of emails' > 2/ The install messages. > 3/ The build messages. > 4/ The login screen for the superuser of the databse? > > Justin Clift wrote: > >>Joseph Shraibman wrote: >> >>>Justin Clift wrote: >>> >>> >>>>The _very first_ thing to do with any PostgreSQL installation is bump >>>>up the memory settings (at least "sort_mem" and "shared_buffers") in >>>>the postgresql.conf and restart it. >>>> >>>>Tell him to test it with decent settings (try about 4000 for each as >>>>an initial start), >> >> > >> >>>Perhaps that should be in the message that 'make install' echoes and in >>>comments in the conf file itself? >>> >> >>Probably it's a good idea to have some mention of this, as even though we should alter the source to higher defaults forour next release, there are potentially >>people that would read a message like this and go "wow, didn't know that", then tune their existing installations as well. >> >>With Solaris, 4000 for those memory settings is a good place to start and it's generally fine to just leave them at thatunless there's a definite reason for >>performance tuning. No idea with other OS's. >> >>Anyone feel like submitting a patch to alter the default settings to a higher mark? Don't think it's been done yet, andit'd be a shame to forget it before >>feature freeze time of the next release. >> >>Regards and best wishes, >> >>Justin Clift >> >>-- >>"My grandfather once told me that there are two kinds of people: those >>who work and those who take the credit. He told me to try to be in the >>first group; there was less competition there." >>- Indira Gandhi >> >>---------------------------(end of broadcast)--------------------------- >>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
I am _definitely_ not trying to sell MSSQL Server, but the version of TDS (Tabular Data Stream) that MSSQL Server users hasbeen pretty thoroughly reverse engineered by the FreeTDS (http://www.freetds.org) group. I use it to access Sybase serversin production but have used it against MSSQL Server 7 with good results. Ooohh. ... I just had a fantasy about a migration tool that uses FreeTDS to bring over tables and data... Ian >>> Avi Schwartz <avi@CFFtechnologies.com> 06/11/03 08:38PM >>> The biggest improvement we noticed was in two areas. We have some very complex selects that join 7-8 tables which perform 30-40% faster under PGSQL. The second thing that performs better are the above mentioned result set returning stored procedures. BTW, the reason we started looking at PSQL is not due to performance, this just came as a pleasant surprise. The real reason is in the fact the we are 90% linux in our server environment and the only Windows component is SQL Server 7. SS7 does not play nicely with Linux and lacks any tools to use from the Linux environment. Windows itself is hard to manage remotely and since the production environment is co-located it became a real issue. Avi On Wednesday, Jun 11, 2003, at 22:24 America/Chicago, Maksim Likharev wrote: > My I ask on what operations PG better than MSSQL? > > cause out of my observations only spatial data types, > could influence a choice between MSSQL and PG. > > Thank you. ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
At 15:20 12.06.2003, Justin Clift said:
--------------------[snip]--------------------
>Probably it's a good idea to have some mention of this, as even though we
>should alter the source to higher defaults for our next release, there are
>potentially
>people that would read a message like this and go "wow, didn't know that",
>then tune their existing installations as well.
--------------------[snip]--------------------
Be careful with increasing memory defaults - I just took over a RH7.2
server that still had the SHMALL and SHMMAX settings left at their default
(2MB), for a 2x1000/1GB machine! Turning up the shared_buffers and sort_mem
parameters immediately caused postmaster to fail, of course. Could turn out
messy with newbies, IMHO.
Of course I immediately gained a recognizable performance boost by stuffing
up the 2 OS parameters to 128MB, and setting both shared_buffers and
sort_mem to 4000, even before I dropped in two indexes on some heavily
filled tables where queries were executing sequential searches for 2 rows
out of a million... *sigh*
I believe the idea of an intelligent install script would be near perfect.
It should check the current system hardware and OS memory settings, make
intelligent decisions for manipulation of SHMALL and SHMMAX as well as
shared_buffers and sort_mem, and ask the user/installer to let it modify
these values. Should be a valuable tool, even for finetuning performance
later on.
--
>O Ernest E. Vogelsinger
(\) ICQ #13394035
^ http://www.vogelsinger.at/
> -----Original Message----- > From: Ernest E Vogelsinger [mailto:ernest@vogelsinger.at] > Sent: Thursday, June 12, 2003 12:38 PM > To: Justin Clift > Cc: Joseph Shraibman; pgsql-general@postgresql.org > Subject: Re: [GENERAL] Postgres performance comments from a MySQL user > > > At 15:20 12.06.2003, Justin Clift said: > --------------------[snip]-------------------- > >Probably it's a good idea to have some mention of this, as > even though > >we > >should alter the source to higher defaults for our next > release, there are > >potentially > >people that would read a message like this and go "wow, > didn't know that", > >then tune their existing installations as well. > --------------------[snip]-------------------- > > Be careful with increasing memory defaults - I just took over > a RH7.2 server that still had the SHMALL and SHMMAX settings > left at their default (2MB), for a 2x1000/1GB machine! > Turning up the shared_buffers and sort_mem parameters > immediately caused postmaster to fail, of course. Could turn > out messy with newbies, IMHO. > > Of course I immediately gained a recognizable performance > boost by stuffing up the 2 OS parameters to 128MB, and > setting both shared_buffers and sort_mem to 4000, even before > I dropped in two indexes on some heavily filled tables where > queries were executing sequential searches for 2 rows out of > a million... *sigh* > > I believe the idea of an intelligent install script would be > near perfect. It should check the current system hardware and > OS memory settings, make intelligent decisions for > manipulation of SHMALL and SHMMAX as well as shared_buffers > and sort_mem, and ask the user/installer to let it modify > these values. Should be a valuable tool, even for finetuning > performance later on. How about (for POSIX systems) calling sar and vmstat or inqiring against limits.h and using sysconf() in a C program or something similar to that? It seems that it should not be too difficult to collect all the information necessary to create a nearly optimal or at least fairly well fitted set of installation parameters.
On Thu, Jun 12, 2003 at 10:15:32AM -0300, Martin Marques wrote: > You can't expect to have a good DB server if you didn't take the time to > read the performance tips on the PostgreSQL Administration Guide, and > gone through the postgresql.conf file. > The same goes for any server, httpd, MTA, etc. For small load a default > Apache, or default Postfix will work OK, but on heavy load you have to > make tuning, or the system will fall to pieces. Is there a performance section in the *Admin* guide? I only see one in the user's guide. Also, something that would be very helpful to document for the performance settings is: what are the trade-offs? (It would also be better if there was more documentation on exactly what each setting did). It's very hard to know if I should up my FSM settings, for example, because I don't know what the memory trade-off is. In fact, unless I RTFS, I have very little idea of how FSM even works. I've added a comment (actually 2, thanks to an errant reload button click) to http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=runtime-config.html, hopefully this will help a bit. -- Jim C. Nasby (aka Decibel!) jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
I think that feature, plus filling in some of the holes in the object model, like more complete inheritance, would make PostgresVERY much more useful, not that it isn't today. It just would have awider audience. Sven Koehler wrote: > Hi, > > one of the biggest disease of PostGreSQL is, that i can't change the definition of a column. > > In order to do that, i'd have to drop any keys, drop the column and create a new one with all indexes etc. > > Are there any plans to overcome that problem? > Even simple changes like varchar(20) to varchar(200) are not allowed. > > I asked this question about 2 years ago, and there were only some guys, that told me that i wouldn't need to change myDB f i'd plan it well. > > So my DB is planned well, but i have to change it every now and than because i must implement the changes that my clientdemands me to do, and have some extra work that nobody will pay me for, if there's no way to change a column. > > This is the only missing feature, that prevent me to use this DBMS - i'd love to, because it's an ORDBMS and that's whati'd have needed sometimes. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
On Jue 12 Jun 2003 18:52, Jim C. Nasby wrote:
> On Thu, Jun 12, 2003 at 10:15:32AM -0300, Martin Marques wrote:
> > You can't expect to have a good DB server if you didn't take the time
> > to read the performance tips on the PostgreSQL Administration Guide,
> > and gone through the postgresql.conf file.
> > The same goes for any server, httpd, MTA, etc. For small load a
> > default Apache, or default Postfix will work OK, but on heavy load
> > you have to make tuning, or the system will fall to pieces.
>
> Is there a performance section in the *Admin* guide? I only see one in
> the user's guide.
Section 3.4 and 3.5 (really all 3) should be read after installing
PostgreSQL to get better performance.
I can't remember well, but I think Bruce Momjian wrote something about
performace tips.
--
Porqué usar una base de datos relacional cualquiera,
si podés usar PostgreSQL?
-----------------------------------------------------------------
Martín Marqués | mmarques@unl.edu.ar
Programador, Administrador, DBA | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------
Actually this sounds like a good idea to me. Type: <popular app #1>, <popular app #2>, OLAP, OLTP? Server: Small, Regular, Large. That way one can see a pattern and have a better guess of how to tune things. My concern is not so much of getting things perfectly right. It's more like avoiding "terribly wrong" settings e.g. postgresql running many times slower than it could on a given system. I mean how would one tune for single user, huge (e.g. stuff >> mem, cache) aggregating selects vs tuning for many users, lots of updates? How about tuning for big inserts/COPYs? How should the WAL logs be adjusted if at all? Would bumping up sort mem help if the stuff you're sorting is a lot more than the RAM you have? Link. At 05:33 PM 6/11/2003 -0400, Matthew Nuzum wrote: >Some databases (MySQL I think) ship several example configurations sized for >different installations. The default is very safe, but it's simply a matter >of choosing between "small.conf", "medium.conf", "big.conf", "huge.conf" and >copying it over the standard "tiny.conf" file. > >Each config file contains comments near the top of the file that specify >suggested hardware requirements for using it. > >Providing a similar series of config files for postgres would probably cut >the traffic to the performance mailing list significantly and end the need >for discussions such as this. (not that I mind the discussion)
On Thu, 12 Jun 2003, Ernest E Vogelsinger wrote: > At 15:20 12.06.2003, Justin Clift said: > --------------------[snip]-------------------- > >Probably it's a good idea to have some mention of this, as even though we > >should alter the source to higher defaults for our next release, there are > >potentially > >people that would read a message like this and go "wow, didn't know that", > >then tune their existing installations as well. > --------------------[snip]-------------------- > > Be careful with increasing memory defaults - I just took over a RH7.2 > server that still had the SHMALL and SHMMAX settings left at their default > (2MB), for a 2x1000/1GB machine! Turning up the shared_buffers and sort_mem > parameters immediately caused postmaster to fail, of course. Could turn out > messy with newbies, IMHO. I don't think that's the default. On my RH 7.2 box it says shmmax is 33554432 which is 32 Megs. SHMALL is 2097152, but that isn't in bytes, it's in pages, which are 4k on 7.2 for 32 bit intel. That comes out to something insane like 80 gig On RedHat boxes since 7.0 (and maybe before) the default max per segment has been 32 Megs. While that's big enough for good overall performance in workgroups, it's still awefully small for a "real server". > Of course I immediately gained a recognizable performance boost by stuffing > up the 2 OS parameters to 128MB, and setting both shared_buffers and > sort_mem to 4000, even before I dropped in two indexes on some heavily > filled tables where queries were executing sequential searches for 2 rows > out of a million... *sigh* You may wanna check out what you set and make sure you're using the right units. Remember, SHMMAX is in bytes, but SHMALL is in pages. Depending on your platform, pages may be 4k or larger, but most installations of linux, even on 64 bit sparcs and what not, are still configured for 4k pages. > I believe the idea of an intelligent install script would be near perfect. > It should check the current system hardware and OS memory settings, make > intelligent decisions for manipulation of SHMALL and SHMMAX as well as > shared_buffers and sort_mem, and ask the user/installer to let it modify > these values. Should be a valuable tool, even for finetuning performance > later on. Good idea. Don't forget to make sure it sets effective_cache_size while we're at it. Just add up the kernel cache size and the free memory on a machine to get an approximation. Til it gets done, maybe the idea of a couple of different postgresql.conf files (light, medium, heavy, big_iron) will likely do the trick. And, if we provide different default .conf files, then the ones that are for heavy / big_iron can have notes on where to go to find information on tuning your OS to handle those configurations.
At 22:10 13.06.2003, scott.marlowe said:
--------------------[snip]--------------------
>Good idea. Don't forget to make sure it sets effective_cache_size while
>we're at it. Just add up the kernel cache size and the free memory on a
>machine to get an approximation.
--------------------[snip]--------------------
Scott - while we're at it, a quick question.
On that machine, cat /proc/meminfo reports "Cached: 1740844 kB" (which is
approx. 1.6GB cache), the machine has 1GB memory installed. So I did what
you suggested before:
# 1.6GB ~= 200000 pages at 8k
effective_cache_size = 200000
I have a query that runs for 17secs for the first time. Now, when executed
a second time, it consumes 270 msec. From the third execution it's running
with 29 msec.
However this only holds for the open connection. When connecting anew, it
starts with 270 msec and drops to 29 msec again.
Any clues about this interesting behaviour?
--
>O Ernest E. Vogelsinger
(\) ICQ #13394035
^ http://www.vogelsinger.at/
Another benefit to this technique is that I get to choose the setup that I want more easily. For example, if I have a system capable of running "huge.conf" but postgres isn't the main app on this server I can choose the "medium.conf" or "small.conf" and get the results I want. Additionally, a binary distribution will ship with all of the default configs, while using a makefile or similar that guesses my config will be useless when installing from RPM. -- Matthew Nuzum www.bearfruit.org cobalt@bearfruit.org > > Actually this sounds like a good idea to me. > > Type: <popular app #1>, <popular app #2>, OLAP, OLTP? > Server: Small, Regular, Large. > > > Link. > > At 05:33 PM 6/11/2003 -0400, Matthew Nuzum wrote: > > >Some databases (MySQL I think) ship several example configurations sized > for > >different installations. The default is very safe, but it's simply a > matter > >of choosing between "small.conf", "medium.conf", "big.conf", "huge.conf" > and > >copying it over the standard "tiny.conf" file. > > > >Each config file contains comments near the top of the file that specify > >suggested hardware requirements for using it.
Interesting - I've noticed this too I did wonder if its the Linux buffer cache manager being over aggressive about flushing...? regards Mark Ernest E Vogelsinger wrote: >I have a query that runs for 17secs for the first time. Now, when executed >a second time, it consumes 270 msec. From the third execution it's running >with 29 msec. > >However this only holds for the open connection. When connecting anew, it >starts with 270 msec and drops to 29 msec again. > >Any clues about this interesting behaviour? > > > >
> -----Original Message----- > From: Justin Clift [mailto:justin@postgresql.org] > Sent: 12 June 2003 18:35 > To: Dennis Gearon > Cc: Joseph Shraibman; pgsql-general@postgresql.org; Dave Page > Subject: Re: [GENERAL] Postgres performance comments from a MySQL user > > > Hi Dave, > > Are we able to get more messages added to the list of mailing > list end tags, so we can mention something about server > tuning or such? > > The rest of these things will probably have to be done in the > PG source code itself. > Hi Justin, I can update the pgadmin list tags, but not the pgsql ones. Marc was looking for more of them a while ago so I'm sure he'll have no problem adding a few more. Regards, Dave.
Aren't you guys forgetting that problably the larger part of the "users that are unaware of the tuningoptions" will also be unaware of (or at least not subscribed to) this mailinglist? And thus, the addition becomes a bit less usefull. It's nice to have such messages added of course, but if I see "TIP 7: don't forget to increase your free space map settings" I wonder what it does and how much I should increase it. When I was looking for references in the manual, I failed to find them... At least I couldn't find any information about _how_much_ to increase that value and such. I'd be very pleased if the documentation got a clear upgrade on how to improve your performance and how to determine good values for such settings. It's a bit hard to search for all the options and possible settings in the mailinglist, although I've seen Tom (for instance) give a few handy tips. Another nice addition might be the tool that is already being discussed which also does a short benchmark to estimate the tuple-cost and the cpu-power, which could improve the quality of postgresql's queryplanner. Regards, Arjen > -----Oorspronkelijk bericht----- > Van: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] Namens Dave Page > Verzonden: vrijdag 13 juni 2003 9:13 > Aan: Justin Clift; Dennis Gearon > CC: Joseph Shraibman; pgsql-general@postgresql.org > Onderwerp: Re: [GENERAL] Postgres performance comments from a > MySQL user > > > > > > -----Original Message----- > > From: Justin Clift [mailto:justin@postgresql.org] > > Sent: 12 June 2003 18:35 > > To: Dennis Gearon > > Cc: Joseph Shraibman; pgsql-general@postgresql.org; Dave Page > > Subject: Re: [GENERAL] Postgres performance comments from a > MySQL user > > > > > > Hi Dave, > > > > Are we able to get more messages added to the list of mailing > > list end tags, so we can mention something about server > > tuning or such? > > > > The rest of these things will probably have to be done in the > > PG source code itself. > > > > Hi Justin, > > I can update the pgadmin list tags, but not the pgsql ones. Marc was > looking for more of them a while ago so I'm sure he'll have no problem > adding a few more. > > Regards, Dave. > > ---------------------------(end of > broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings >
On Fri, 13 Jun 2003, Ernest E Vogelsinger wrote: > At 22:10 13.06.2003, scott.marlowe said: > --------------------[snip]-------------------- > >Good idea. Don't forget to make sure it sets effective_cache_size while > >we're at it. Just add up the kernel cache size and the free memory on a > >machine to get an approximation. > --------------------[snip]-------------------- > > Scott - while we're at it, a quick question. > > On that machine, cat /proc/meminfo reports "Cached: 1740844 kB" (which is > approx. 1.6GB cache), the machine has 1GB memory installed. So I did what > you suggested before: > # 1.6GB ~= 200000 pages at 8k > effective_cache_size = 200000 Are you sure? You shouldn't be able to cache more than how much memory you have, so I'd guess you either have more than 1gig of ram or the cached memory is less thatn 1.6 gig. > I have a query that runs for 17secs for the first time. Now, when executed > a second time, it consumes 270 msec. From the third execution it's running > with 29 msec. > > However this only holds for the open connection. When connecting anew, it > starts with 270 msec and drops to 29 msec again. > > Any clues about this interesting behaviour? So, once the machine's been up a while, the 17 second behaviour goes away completely, but the .27 second -> .03 second behaviour remains? Hmmmm. Sounds like the very first time you run it the data set moves from disk to kernel cache. Then on each new connect postgresql pulls it from kernel cache to postgresql's buffer, and it's faster still. Not sure what all you can do about it, since postgresql drops the data from its buffer on disconnect.
> -----Oorspronkelijk bericht----- > Van: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] Namens scott.marlowe > Verzonden: maandag 16 juni 2003 21:00 > Aan: Ernest E Vogelsinger > CC: Justin Clift; Joseph Shraibman; pgsql-general@postgresql.org > Onderwerp: Re: [GENERAL] Postgres performance comments from a > MySQL user > > I have a query that runs for 17secs for the first time. Now, when > > executed a second time, it consumes 270 msec. From the > third execution > > it's running with 29 msec. > > > > However this only holds for the open connection. When > connecting anew, > > it starts with 270 msec and drops to 29 msec again. > > > > Any clues about this interesting behaviour? > > So, once the machine's been up a while, the 17 second > behaviour goes away > completely, but the .27 second -> .03 second behaviour remains? > > Hmmmm. Sounds like the very first time you run it the data > set moves from > disk to kernel cache. Then on each new connect postgresql > pulls it from > kernel cache to postgresql's buffer, and it's faster still. Recent postgresql versions (7.3.3 perhaps? Dunno when it got included) cache the query-plans on the connection that was used for that connection. So postgres won't have to replan the entire query and that allows a speedup, although the difference in time is quite large, perhaps there is more cacheing with the connection than just the queryplan. Regards, Arjen
Arjen van der Meijden <acm@tweakers.net> writes:
> Recent postgresql versions (7.3.3 perhaps? Dunno when it got included)
> cache the query-plans on the connection that was used for that
> connection.
There is absolutely no truth to the above statement. There is no plan
caching (except in plpgsql, which has had it from the beginning).
You can get the effect of plan caching with prepared statements ...
but the OP didn't say anything about using PREPARE ...
regards, tom lane
Well, then there is not. It would still be nice, however, to know why queries are faster the second time they're run, even if there is a 100% cachehit for the first running query. For a group of eleven queries I put together to form a simple benchmark the first run of the batch shows total times like: Total time: 92.174ms Total time: 93.502ms Total time: 92.719ms While second and later runs on the same connection runs like: Total time: 53.162ms Total time: 52.870ms Total time: 52.974ms Total time: 52.855ms These timings do not include forking off the connection and such hidden timeconsumers, just the query and retrieving its results (which don't change over time, nor on different connections). It would be very nice if postgresql was able to get the 53ms timings every time the queries got executed, even if the it's the first run on the connection. Another odd thing here is that even if I'd change some parameter (like looking up the user with uid 20000 instead of 1) it's always faster when it is run as the second query, then when it is run as the first one. But another (like uid 50000) is run as fast as the second query. So if there is no query-plan cacheing (why not? Wouldn't it improve performance?), where do the time differences come from? The silly thing is I really recall having it read somewhere (as an improvement for pgsql 7.4?), but it might have been the cacheing of plpgsql/prepared statements :) Regards, Arjen van der Meijden > Tom Lane wrote: > > Arjen van der Meijden <acm@tweakers.net> writes: > > Recent postgresql versions (7.3.3 perhaps? Dunno when it > got included) > > cache the query-plans on the connection that was used for that > > connection. > > There is absolutely no truth to the above statement. There > is no plan caching (except in plpgsql, which has had it from > the beginning). > > You can get the effect of plan caching with prepared > statements ... but the OP didn't say anything about using PREPARE ... > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 8: explain analyze is your friend >
Arjen van der Meijden <acm@tweakers.net> writes:
> Well, then there is not. It would still be nice, however, to know why
> queries are faster the second time they're run, even if there is a 100%
> cachehit for the first running query.
Well, there are non-plan caches involved --- specifically the catalog
cache and relation cache. The first query issued against a given table
in a session will incur the cost to load the cache entries needed, and
the first few queries in a session incur quite a few cache loads to suck
in basic information like pg_operator and pg_proc entries for common
functions and operators.
For example, on my machine with the regression database, the time for
explain select * from tenk1 where unique1 = 42;
drops from ~18ms first time to ~5ms subsequent times. AFAICS the only
thing that could cause that difference is catcache/relcache loading.
regards, tom lane
At 01:35 17.06.2003, Tom Lane said:
--------------------[snip]--------------------
>Arjen van der Meijden <acm@tweakers.net> writes:
>> Well, then there is not. It would still be nice, however, to know why
>> queries are faster the second time they're run, even if there is a 100%
>> cachehit for the first running query.
>
>Well, there are non-plan caches involved --- specifically the catalog
>cache and relation cache. The first query issued against a given table
>in a session will incur the cost to load the cache entries needed, and
>the first few queries in a session incur quite a few cache loads to suck
>in basic information like pg_operator and pg_proc entries for common
>functions and operators.
>
>For example, on my machine with the regression database, the time for
> explain select * from tenk1 where unique1 = 42;
>drops from ~18ms first time to ~5ms subsequent times. AFAICS the only
>thing that could cause that difference is catcache/relcache loading.
>
> regards, tom lane
--------------------[snip]--------------------
But this wouldn't explain the huge differences I just posted (first query -
1500 msecs, follow-ups - 10 msec)
Just now I had the server sit for approx 20 minutes, rerunning the same
query resulted in 3155 msec, a followup again 10.85 msec.
--
>O Ernest E. Vogelsinger
(\) ICQ #13394035
^ http://www.vogelsinger.at/
Ernest E Vogelsinger <ernest@vogelsinger.at> writes:
> But this wouldn't explain the huge differences I just posted (first query -
> 1500 msecs, follow-ups - 10 msec)
No, I was just responding to Arjen's wondering where 40 or so msec had
gone ... that's of the right order of magnitude to be cache load effects.
> Just now I had the server sit for approx 20 minutes, rerunning the same
> query resulted in 3155 msec, a followup again 10.85 msec.
It's really hard to believe that you could see that kind of ratio from
any sort of cache effects, even kernel disk buffer cache which is
normally pretty large. Are you sure you were getting the same plan each
time? I'd like to see EXPLAIN ANALYZE output from all three cases.
regards, tom lane
At 02:04 17.06.2003, Tom Lane said:
--------------------[snip]--------------------
>It's really hard to believe that you could see that kind of ratio from
>any sort of cache effects, even kernel disk buffer cache which is
>normally pretty large. Are you sure you were getting the same plan each
>time? I'd like to see EXPLAIN ANALYZE output from all three cases.
--------------------[snip]--------------------
I have EXPLAIN ANALYZE VERBOSE available. I noticed that even _removing_
the ilike clause from the query, thus using _only_ index columns, did NOT
change this behaviour.
test=# explain analyze verbose
test-# select distinct t1.owid
test-# from rv2_mdata t1
test-# where t1.dcid='ADDR' and t1.dsid='DICT' and t1.drid=110 and t1.usg &
16 = 16
test-# and t1.nxid = 0
test-# and t1.cst ilike '%redist%'
test-# and t1.owid > 10
test-# ;
NOTICE: QUERY DUMP:
{ UNIQUE :startup_cost 3.84 :total_cost 3.84 :rows 1 :width 4 :qptargetlist
({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname
owid :reskey 0 :reskeyop 0 :ressortgroupref 1 :resjunk false } :expr { VAR
:varno 1 :varattno 3 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 3}}) :qpqual <> :lefttree { SORT :startup_cost 3.84 :total_cost
3.84 :rows 1 :width 4 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno
1 :restype 23 :restypmod -1 :resname owid :reskey 1 :reskeyop 97
:ressortgroupref 1 :resjunk false } :expr { VAR :varno 1 :varattno 3
:vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 3}})
:qpqual <> :lefttree { INDEXSCAN :startup_cost 0.00 :total_cost 3.83 :rows
1 :width 4 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype
23 :restypmod -1 :resname owid :reskey 0 :reskeyop 0 :ressortgroupref 1
:resjunk false } :expr { VAR :varno 1 :varattno 3 :vartype 23 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 3}}) :qpqual ({ EXPR :typeOid 16
:opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ EXPR
:typeOid 23 :opType op :oper { OPER :opno 1880 :opid 1898 :opresulttype 23
} :args ({ VAR :varno 1 :varattno 8 :vartype 23 :vartypmod -1 :varlevelsup
0 :varnoold 1 :varoattno 8} { CONST :consttype 23 :constlen 4 :constbyval
true :constisnull false :constvalue 4 [ 16 0 0 0 ] })} { CONST :consttype
23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 16 0 0
0 ] })} { EXPR :typeOid 16 :opType op :oper { OPER :opno 1631 :opid 1633
:opresulttype 16 } :args ({ VAR :varno 1 :varattno 16 :vartype 1043
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 16} { CONST :consttype
25 :constlen -1 :constbyval false :constisnull false :constvalue 12 [ 12 0
0 0 37 114 101 100 105 115 116 37 ] })} { EXPR :typeOid 16 :opType op
:oper { OPER :opno 521 :opid 147 :opresulttype 16 } :args ({ VAR :varno 1
:varattno 3 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 3} { CONST :consttype 23 :constlen 4 :constbyval true
:constisnull false :constvalue 4 [ 10 0 0 0 ] })}) :lefttree <> :righttree
<> :extprm () :locprm () :initplan <> :nprm 0 :scanrelid 1 :indxid (
22424505) :indxqual (({ EXPR :typeOid 16 :opType op :oper { OPER :opno
1062 :opid 1070 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1
:vartype 1043 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 5} {
CONST :consttype 1043 :constlen -1 :constbyval false :constisnull false
:constvalue 8 [ 8 0 0 0 65 68 68 82 ] })} { EXPR :typeOid 16 :opType op
:oper { OPER :opno 1062 :opid 1070 :opresulttype 16 } :args ({ VAR :varno 1
:varattno 2 :vartype 1043 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 6} { CONST :consttype 1043 :constlen -1 :constbyval false
:constisnull false :constvalue 8 [ 8 0 0 0 68 73 67 84 ] })} { EXPR
:typeOid 16 :opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 }
:args ({ VAR :varno 1 :varattno 3 :vartype 23 :vartypmod -1 :varlevelsup 0
:varnoold 1 :varoattno 7} { CONST :consttype 23 :constlen 4 :constbyval
true :constisnull false :constvalue 4 [ 110 0 0 0 ] })} { EXPR :typeOid 16
:opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ VAR
:varno 1 :varattno 4 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 11} { CONST :consttype 23 :constlen 4 :constbyval true
:constisnull false :constvalue 4 [ 0 0 0 0 ] })})) :indxqualorig (({ EXPR
:typeOid 16 :opType op :oper { OPER :opno 1062 :opid 1070 :opresulttype 16
} :args ({ VAR :varno 1 :varattno 5 :vartype 1043 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 5} { CONST :consttype 1043 :constlen
-1 :constbyval false :constisnull false :constvalue 8 [ 8 0 0 0 65 68 68
82 ] })} { EXPR :typeOid 16 :opType op :oper { OPER :opno 1062 :opid 1070
:opresulttype 16 } :args ({ VAR :varno 1 :varattno 6 :vartype 1043
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 6} { CONST :consttype
1043 :constlen -1 :constbyval false :constisnull false :constvalue 8 [ 8 0
0 0 68 73 67 84 ] })} { EXPR :typeOid 16 :opType op :oper { OPER :opno 96
:opid 65 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 7 :vartype 23
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 7} { CONST :consttype
23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 110 0 0
0 ] })} { EXPR :typeOid 16 :opType op :oper { OPER :opno 96 :opid 65
:opresulttype 16 } :args ({ VAR :varno 1 :varattno 11 :vartype 23
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 11} { CONST :consttype
23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 0 0 0 0
] })})) :indxorderdir 1 } :righttree <> :extprm () :locprm () :initplan <>
:nprm 0 :keycount 1 } :righttree <> :extprm () :locprm () :initplan <>
:nprm 0 :numCols 1 :uniqColIdx 1 }
psql:x:8: NOTICE: QUERY PLAN:
Unique (cost=3.84..3.84 rows=1 width=4) (actual time=3155.24..3155.27
rows=11 loops=1)
-> Sort (cost=3.84..3.84 rows=1 width=4) (actual time=3155.24..3155.25
rows=11 loops=1)
-> Index Scan using id_mdata_dictid_string on rv2_mdata t1
(cost=0.00..3.83 rows=1 width=4) (actual time=210.96..3155.00 rows=11 loops=1)
Total runtime: 3155.42 msec
EXPLAIN
test=# explain analyze verbose
test-# select distinct t1.owid
test-# from rv2_mdata t1
test-# where t1.dcid='ADDR' and t1.dsid='DICT' and t1.drid=110 and t1.usg &
16 = 16
test-# and t1.nxid = 0
test-# and t1.cst ilike '%redist%'
test-# and t1.owid > 10
test-# ;
NOTICE: QUERY DUMP:
{ UNIQUE :startup_cost 3.84 :total_cost 3.84 :rows 1 :width 4 :qptargetlist
({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname
owid :reskey 0 :reskeyop 0 :ressortgroupref 1 :resjunk false } :expr { VAR
:varno 1 :varattno 3 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 3}}) :qpqual <> :lefttree { SORT :startup_cost 3.84 :total_cost
3.84 :rows 1 :width 4 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno
1 :restype 23 :restypmod -1 :resname owid :reskey 1 :reskeyop 97
:ressortgroupref 1 :resjunk false } :expr { VAR :varno 1 :varattno 3
:vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 3}})
:qpqual <> :lefttree { INDEXSCAN :startup_cost 0.00 :total_cost 3.83 :rows
1 :width 4 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype
23 :restypmod -1 :resname owid :reskey 0 :reskeyop 0 :ressortgroupref 1
:resjunk false } :expr { VAR :varno 1 :varattno 3 :vartype 23 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 3}}) :qpqual ({ EXPR :typeOid 16
:opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ EXPR
:typeOid 23 :opType op :oper { OPER :opno 1880 :opid 1898 :opresulttype 23
} :args ({ VAR :varno 1 :varattno 8 :vartype 23 :vartypmod -1 :varlevelsup
0 :varnoold 1 :varoattno 8} { CONST :consttype 23 :constlen 4 :constbyval
true :constisnull false :constvalue 4 [ 16 0 0 0 ] })} { CONST :consttype
23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 16 0 0
0 ] })} { EXPR :typeOid 16 :opType op :oper { OPER :opno 1631 :opid 1633
:opresulttype 16 } :args ({ VAR :varno 1 :varattno 16 :vartype 1043
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 16} { CONST :consttype
25 :constlen -1 :constbyval false :constisnull false :constvalue 12 [ 12 0
0 0 37 114 101 100 105 115 116 37 ] })} { EXPR :typeOid 16 :opType op
:oper { OPER :opno 521 :opid 147 :opresulttype 16 } :args ({ VAR :varno 1
:varattno 3 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 3} { CONST :consttype 23 :constlen 4 :constbyval true
:constisnull false :constvalue 4 [ 10 0 0 0 ] })}) :lefttree <> :righttree
<> :extprm () :locprm () :initplan <> :nprm 0 :scanrelid 1 :indxid (
22424505) :indxqual (({ EXPR :typeOid 16 :opType op :oper { OPER :opno
1062 :opid 1070 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1
:vartype 1043 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 5} {
CONST :consttype 1043 :constlen -1 :constbyval false :constisnull false
:constvalue 8 [ 8 0 0 0 65 68 68 82 ] })} { EXPR :typeOid 16 :opType op
:oper { OPER :opno 1062 :opid 1070 :opresulttype 16 } :args ({ VAR :varno 1
:varattno 2 :vartype 1043 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 6} { CONST :consttype 1043 :constlen -1 :constbyval false
:constisnull false :constvalue 8 [ 8 0 0 0 68 73 67 84 ] })} { EXPR
:typeOid 16 :opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 }
:args ({ VAR :varno 1 :varattno 3 :vartype 23 :vartypmod -1 :varlevelsup 0
:varnoold 1 :varoattno 7} { CONST :consttype 23 :constlen 4 :constbyval
true :constisnull false :constvalue 4 [ 110 0 0 0 ] })} { EXPR :typeOid 16
:opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ VAR
:varno 1 :varattno 4 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 11} { CONST :consttype 23 :constlen 4 :constbyval true
:constisnull false :constvalue 4 [ 0 0 0 0 ] })})) :indxqualorig (({ EXPR
:typeOid 16 :opType op :oper { OPER :opno 1062 :opid 1070 :opresulttype 16
} :args ({ VAR :varno 1 :varattno 5 :vartype 1043 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 5} { CONST :consttype 1043 :constlen
-1 :constbyval false :constisnull false :constvalue 8 [ 8 0 0 0 65 68 68
82 ] })} { EXPR :typeOid 16 :opType op :oper { OPER :opno 1062 :opid 1070
:opresulttype 16 } :args ({ VAR :varno 1 :varattno 6 :vartype 1043
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 6} { CONST :consttype
1043 :constlen -1 :constbyval false :constisnull false :constvalue 8 [ 8 0
0 0 68 73 67 84 ] })} { EXPR :typeOid 16 :opType op :oper { OPER :opno 96
:opid 65 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 7 :vartype 23
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 7} { CONST :consttype
23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 110 0 0
0 ] })} { EXPR :typeOid 16 :opType op :oper { OPER :opno 96 :opid 65
:opresulttype 16 } :args ({ VAR :varno 1 :varattno 11 :vartype 23
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 11} { CONST :consttype
23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 0 0 0 0
] })})) :indxorderdir 1 } :righttree <> :extprm () :locprm () :initplan <>
:nprm 0 :keycount 1 } :righttree <> :extprm () :locprm () :initplan <>
:nprm 0 :numCols 1 :uniqColIdx 1 }
psql:x:8: NOTICE: QUERY PLAN:
Unique (cost=3.84..3.84 rows=1 width=4) (actual time=10.70..10.73 rows=11
loops=1)
-> Sort (cost=3.84..3.84 rows=1 width=4) (actual time=10.69..10.70
rows=11 loops=1)
-> Index Scan using id_mdata_dictid_string on rv2_mdata t1
(cost=0.00..3.83 rows=1 width=4) (actual time=0.60..10.62 rows=11 loops=1)
Total runtime: 10.85 msec
--
>O Ernest E. Vogelsinger
(\) ICQ #13394035
^ http://www.vogelsinger.at/
Ernest E Vogelsinger <ernest@vogelsinger.at> writes:
> At 02:04 17.06.2003, Tom Lane said:
>> It's really hard to believe that you could see that kind of ratio from
>> any sort of cache effects, even kernel disk buffer cache which is
>> normally pretty large.
> I have EXPLAIN ANALYZE VERBOSE available.
That's the 3-sec-vs-10-msec case though. I can easily believe that that
represents kernel disk caching effects --- that is, 3 sec is what it
really takes to read all the data from disk, but once it's in RAM you
can do the calculations in 10 msec. The number that got my attention
was 20 minutes. I don't see where that could come from, given the same
query plan and no change in other system load.
regards, tom lane
At 02:25 17.06.2003, Tom Lane said:
--------------------[snip]--------------------
>can do the calculations in 10 msec. The number that got my attention
>was 20 minutes. I don't see where that could come from, given the same
>query plan and no change in other system load.
--------------------[snip]--------------------
If I ever wrote that it was a typo, apologies :)
--
>O Ernest E. Vogelsinger
(\) ICQ #13394035
^ http://www.vogelsinger.at/
> can do the calculations in 10 msec. The number that got my attention > was 20 minutes. I don't see where that could come from, given the same The server was idle 20 minutes? "Just now I had the server sit for approx 20 minutes, rerunning the same query resulted in 3155 msec, a followup again 10.85 msec."
On 17/06/2003 10:57 Kaarel wrote: >> can do the calculations in 10 msec. The number that got my attention >> was 20 minutes. I don't see where that could come from, given the same > > The server was idle 20 minutes? > > "Just now I had the server sit for approx 20 minutes, rerunning the same > query resulted in 3155 msec, a followup again 10.85 msec." > mybe after that amount of time, the kernel has marked the cached data as stale and so re-reads it from disk? -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+