Thread: URGENT: Performance tuning

Hi ,

I need info about how to fine tune my postgresql.

I make a little test and the results was not good for posgresql.

I will to use the server in production and need this info.

So, the test:

first machine:

Linux Mandrake 8.2 and posgresql on it.

Dual Athlon MP 1,6 GHz , 1 GB RAM , IDE 7200 40 GB HDD.

I create a litle table (field1 varchar(100),field2 varchar(100), field3 int) and fill

200 000 x ('aaaaaaaaaaaaa1','bbbbbbbbbbbbbbb1',10);

200 000 x ('aaaaaaaaaaaaa2','bbbbbbbbbbbbbbb2',20);

200 000 x ('aaaaaaaaaaaaa3','bbbbbbbbbbbbbbb3',30);

200 000 x ('aaaaaaaaaaaaa4','bbbbbbbbbbbbbbb4',40);

200 000 x ('aaaaaaaaaaaaa5','bbbbbbbbbbbbbbb5',50);

After that i make : select distinct field1 from mytable.

As result I get 5 rows and it is OK, but the time is 3 min 30 sec.

The processor usage is 50% - it is OK , and the system use the maximum of the first processor.

The second machine:

Athlon XP 1,5 GHz , 512 MB RAM , 7200 HDD 40 GB

WinNT 4, Oracle 8i.

I make the same test.

The result come for 15 sec !!!!.

Ok, I am sure : the diferent can not be so big.

I do not make any special setup an postgres and on linux, anly the standart install.

Any info or example will be great.

Many thanks in advance.

http://phpbuilder.com/columns/smith20010821.php3

On Thu, 2002-06-20 at 13:21, Peter Dimov wrote:
>
> Hi ,
>
> I need info about how to fine tune my postgresql.
>
> I make a little test and the results was not good for posgresql.
>
> I will to use the server in production and need this info.
>
> So, the test:
>
> first machine:
>
> Linux Mandrake 8.2 and posgresql on it.
>
> Dual Athlon MP 1,6 GHz , 1 GB RAM , IDE 7200 40 GB HDD.
>
>
>
> I create a litle table (field1 varchar(100),field2 varchar(100), field3 int) and fill
>
> 200 000 x ('aaaaaaaaaaaaa1','bbbbbbbbbbbbbbb1',10);
>
> 200 000 x ('aaaaaaaaaaaaa2','bbbbbbbbbbbbbbb2',20);
>
> 200 000 x ('aaaaaaaaaaaaa3','bbbbbbbbbbbbbbb3',30);
>
> 200 000 x ('aaaaaaaaaaaaa4','bbbbbbbbbbbbbbb4',40);
>
> 200 000 x ('aaaaaaaaaaaaa5','bbbbbbbbbbbbbbb5',50);
>
>
>
> After that i make : select distinct field1 from mytable.
>
> As result I get 5 rows and it is OK, but the time is 3 min 30 sec.
>
> The processor usage is 50% - it is OK , and the system use the maximum of the first processor.
>
> The second machine:
>
> Athlon XP 1,5 GHz , 512 MB RAM , 7200 HDD 40 GB
>
> WinNT 4, Oracle 8i.
>
> I make the same test.
>
> The result come for 15 sec !!!!.
>
> Ok, I am sure : the diferent can not be so big.
>
> I do not make any special setup an postgres and on linux, anly the standart install.
>
> Any info or example will be great.
>
> Many thanks in advance.
>
>
>
>
>
>
>
> ---------------------------------
> Do You Yahoo!?
> Sign-up for Video Highlights of 2002 FIFA World Cup

On Thu, Jun 20, 2002 at 10:21:21AM -0700, Peter Dimov wrote:
>
> Hi ,
>
> I need info about how to fine tune my postgresql.

Then you need to give us info about your configuration.  Did you
ANALYSE?  &c.

> I do not make any special setup an postgres and on linux, anly the
> standart install.

The defaults on Postgres are basically there to allow it to run on
anything.  It'll run out of the box on a 486 with 8 Meg, I think.
You need to make some changes.  A cursory inspection of the archives
would help.  Also, see
<http://www.ca.postgresql.org/docs/momjian/hw_performance/>

A

--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

http://phpbuilder.com/columns/smith20010821.php3

On Thu, 2002-06-20 at 13:21, Peter Dimov wrote: 

Hi ,

I need info about how to fine tune my postgresql.

I make a little test and the results was not good for posgresql.

I will to use the server in production and need this info.

So, the test:

first machine:

Linux Mandrake 8.2 and posgresql on it.

Dual Athlon MP 1,6 GHz , 1 GB RAM , IDE 7200 40 GB HDD.


I create a litle table (field1 varchar(100),field2 varchar(100), field3 int) and fill

200 000 x ('aaaaaaaaaaaaa1','bbbbbbbbbbbbbbb1',10);

200 000 x ('aaaaaaaaaaaaa2','bbbbbbbbbbbbbbb2',20);

200 000 x ('aaaaaaaaaaaaa3','bbbbbbbbbbbbbbb3',30);

200 000 x ('aaaaaaaaaaaaa4','bbbbbbbbbbbbbbb4',40);

200 000 x ('aaaaaaaaaaaaa5','bbbbbbbbbbbbbbb5',50);


After that i make : select distinct field1 from mytable.

As result I get 5 rows and it is OK, but the time is 3 min 30 sec.

The processor usage is 50% - it is OK , and the system use the maximum of the first processor.

The second machine:

Athlon XP 1,5 GHz , 512 MB RAM , 7200 HDD 40 GB

WinNT 4, Oracle 8i.

I make the same test.

The result come for 15 sec !!!!.

Ok, I am sure : the diferent can not be so big.

I do not make any special setup an postgres and on linux, anly the standart install.

Any info or example will be great.

Many thanks in advance.





---------------------------------
Do You Yahoo!?
Sign-up for Video Highlights of 2002 FIFA World Cup   

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command   (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) 

On Thu, 2002-06-20 at 14:45, Joshua D. Drake wrote:
> Hello,
>
>  Did you create an index? What is your shared buffers? Are you using
> Fsync, if so --- which one?

The default shared buffers that come with pgsql is very small. So you
need to really ramp this up. Read the article for some more tips. Also
check the techdocs.postgresql.org site for some good info.

For this purpose, you have to have indexes, yes.

I did not change my default Fsync settings, which i think mainly applies
to updates anyway.

- Ericson Smith
eric@did-it.com
http://www.did-it.com
>
>
>
>
> Ericson Smith wrote:
>
> >http://phpbuilder.com/columns/smith20010821.php3
> >
> >On Thu, 2002-06-20 at 13:21, Peter Dimov wrote:
> >
> >
> >>Hi ,
> >>
> >>I need info about how to fine tune my postgresql.
> >>
> >>I make a little test and the results was not good for posgresql.
> >>
> >>I will to use the server in production and need this info.
> >>
> >>So, the test:
> >>
> >>first machine:
> >>
> >>Linux Mandrake 8.2 and posgresql on it.
> >>
> >>Dual Athlon MP 1,6 GHz , 1 GB RAM , IDE 7200 40 GB HDD.
> >>
> >>
> >>
> >>I create a litle table (field1 varchar(100),field2 varchar(100), field3 int) and fill
> >>
> >>200 000 x ('aaaaaaaaaaaaa1','bbbbbbbbbbbbbbb1',10);
> >>
> >>200 000 x ('aaaaaaaaaaaaa2','bbbbbbbbbbbbbbb2',20);
> >>
> >>200 000 x ('aaaaaaaaaaaaa3','bbbbbbbbbbbbbbb3',30);
> >>
> >>200 000 x ('aaaaaaaaaaaaa4','bbbbbbbbbbbbbbb4',40);
> >>
> >>200 000 x ('aaaaaaaaaaaaa5','bbbbbbbbbbbbbbb5',50);
> >>
> >>
> >>
> >>After that i make : select distinct field1 from mytable.
> >>
> >>As result I get 5 rows and it is OK, but the time is 3 min 30 sec.
> >>
> >>The processor usage is 50% - it is OK , and the system use the maximum of the first processor.
> >>
> >>The second machine:
> >>
> >>Athlon XP 1,5 GHz , 512 MB RAM , 7200 HDD 40 GB
> >>
> >>WinNT 4, Oracle 8i.
> >>
> >>I make the same test.
> >>
> >>The result come for 15 sec !!!!.
> >>
> >>Ok, I am sure : the diferent can not be so big.
> >>
> >>I do not make any special setup an postgres and on linux, anly the standart install.
> >>
> >>Any info or example will be great.
> >>
> >>Many thanks in advance.
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>---------------------------------
> >>Do You Yahoo!?
> >>Sign-up for Video Highlights of 2002 FIFA World Cup
> >>
> >>
> >
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 2: you can get off all lists at once with the unregister command
> >    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
> >
>

As I wrote,

I do not make any changes in the default setup.

I make the test with index and got the same result.

On Oracle I do not have any indexes created.

Can you point me to any good structured docs about performance tuning in postgres.

Also what is the goot time that i can expect for this test.

If you have good worked and tuned postgres, can you make this test on your compute (it is very simple) and

replay the results?

I nedd this info to know if it is possible to get more from posgres or to go to oracle integration.

My wish is to work with postgres!

thanks.

  Ericson Smith <eric@did-it.com> wrote:

On Thu, 2002-06-20 at 14:45, Joshua D. Drake wrote:
> Hello,
>
> Did you create an index? What is your shared buffers? Are you using
> Fsync, if so --- which one?

The default shared buffers that come with pgsql is very small. So you
need to really ramp this up. Read the article for some more tips. Also
check the techdocs.postgresql.org site for some good info.

For this purpose, you have to have indexes, yes.

I did not change my default Fsync settings, which i think mainly applies
to updates anyway.

- Ericson Smith
eric@did-it.com
http://www.did-it.com
>
>
>
>
> Ericson Smith wrote:
>
> >http://phpbuilder.com/columns/smith20010821.php3
> >
> >On Thu, 2002-06-20 at 13:21, Peter Dimov wrote:
> >
> >
> >>Hi ,
> >>
> >>I need info about how to fine tune my postgresql.
> >>
> >>I make a little test and the results was not good for posgresql.
> >>
> >>I will to use the server in production and need this info.
> >>
> >>So, the test:
> >>
> >>first machine:
> >>
> >>Linux Mandrake 8.2 and posgresql on it.
> >>
> >>Dual Athlon MP 1,6 GHz , 1 GB RAM , IDE 7200 40 GB HDD.
> >>
> >>
> >>
> >>I create a litle table (field1 varchar(100),field2 varchar(100), field3 int) and fill
> >>
> >>200 000 x ('aaaaaaaaaaaaa1','bbbbbbbbbbbbbbb1',10);
> >>
> >>200 000 x ('aaaaaaaaaaaaa2','bbbbbbbbbbbbbbb2',20);
> >>
> >>200 000 x ('aaaaaaaaaaaaa3','bbbbbbbbbbbbbbb3',30);
> >>
> >>200 000 x ('aaaaaaaaaaaaa4','bbbbbbbbbbbbbbb4',40);
> >>
> >>200 000 x ('aaaaaaaa aaaaa5','bbbbbbbbbbbbbbb5',50);
> >>
> >>
> >>
> >>After that i make : select distinct field1 from mytable.
> >>
> >>As result I get 5 rows and it is OK, but the time is 3 min 30 sec.
> >>
> >>The processor usage is 50% - it is OK , and the system use the maximum of the first processor.
> >>
> >>The second machine:
> >>
> >>Athlon XP 1,5 GHz , 512 MB RAM , 7200 HDD 40 GB
> >>
> >>WinNT 4, Oracle 8i.
> >>
> >>I make the same test.
> >>
> >>The result come for 15 sec !!!!.
> >>
> >>Ok, I am sure : the diferent can not be so big.
> >>
> >>I do not make any special setup an postgres and on linux, anly the standart install.
> >>
> >>Any info or example will be great.
> >>
> >>Many thanks in advance.
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>---------------------------------
> >>Do You Yahoo!?
> >>Sign-up for Video Highlights of 2002 FIFA World Cup
> >>
> >>
> >
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
> >
>



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

On 20 Jun 2002, Ericson Smith wrote:

> For this purpose, you have to have indexes, yes.

Why? Given that there are only five values in the column in question
in a table with over a million rows, the selectivity of the index
would be so low I can't see how postgres would ever use it.

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC

Hi,

Curt, you are rigth. I tested it.

My idea was only to compare postgres with oracle and to see if it is good.

I got the simplest example with simple data inserting.

I do not have any answer on my questin:

Is postgres good as performace compared to oracle and if yes how to fine tune it.

I readet all docs that Ifound, changed shared buffers and sort chache but can not

get good result.

And the second question:

Was enyone from the list a good perofmed postgres and if yes can I receiv this test result.

If no one had good tuned postgres I do not see the need to use it. For me it can be only "play", demo or...?

Thanks.

  Curt Sampson <cjs@cynic.net> wrote:

On 20 Jun 2002, Ericson Smith wrote:

> For this purpose, you have to have indexes, yes.

Why? Given that there are only five values in the column in question
in a table with over a million rows, the selectivity of the index
would be so low I can't see how postgres would ever use it.

cjs
--
Curt Sampson +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

On Sat, 22 Jun 2002, Peter Dimov wrote:

> I do not have any answer on my
> questin: Is postgres good as performace compared to oracle...

The answer to that is, "it depends." Some applications will work
just as well, others will not.

> ...and if yes how to fine tune it.

A few points on tuning:

1. Try to use tests that reflect as accurately as possible what
your application will do, and try to tune your application as well.
For example, if your application really does do 25,000 inserts in
a single transaction, perhaps it would be better off using the COPY
command instead.

2. When you're new to postgres, or any database system, it will
take time and experimentation before you figure out the system's
idiosyncrasies and learn how to tune it. If you really need to be
up and running right away, you should probably just go out and buy
the system you're most familiar with.

3. Becuase you're saving so much money in software costs, you may
have a bit of extra money to throw at hardware. So you should
consider perhaps adding a few more disks to your system, and moving
at least the log files on to a separate disk.

> And the second question: Was enyone from the list a good perofmed
> postgres and if yes can I receiv this test result.

Yes, I've gotten excellent performance out of postgres from time
to time.  However, the test results would be meaningless for you;
they probably don't relate to your application at all.

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC

Hi ,

Curt, thanks for answer.

I am realy new to postgres. I try it from 3-4 moths.

But I am not new to databases and SQL and I know very good how good is all

working on oracle or ms sql ( I do not love it).

You are in pricpe right, I can change the hardware and I make it.

First I tested with P II 400 MHz 256 MB RAM.

And second Dual Athlon MP , 1.6 GHz , 1 or 2 GB RAM.

I expected to get big advantage from the second system and

was very supprise from the results : I wan only 30 - 40% faster as the first.

After that I make the described test and compare all to oracle.

But I will be realy happy to get postgres working well and I will continue to search

the answer.

It is not possible to develope a db system so many yers as postgres development team and to

so big performance diferent to oracle.

Ok, I know oracle is comersial .

I think the mistake is by tuning or by version, but can not be in the postgres.

regards and thanks.

  Curt Sampson <cjs@cynic.net> wrote:

On Sat, 22 Jun 2002, Peter Dimov wrote:

> I do not have any answer on my
> questin: Is postgres good as performace compared to oracle...

The answer to that is, "it depends." Some applications will work
just as well, others will not.

> ...and if yes how to fine tune it.

A few points on tuning:

1. Try to use tests that reflect as accurately as possible what
your application will do, and try to tune your application as well.
For example, if your application really does do 25,000 inserts in
a single transaction, perhaps it would be better off using the COPY
command instead.

2. When you're new to postgres, or any database system, it will
take time and experimentation before you figure out the system's
idiosyncrasies and learn how to tune it. If you really need to be
up and running right away, you should probably just go out and buy
the system you're most familiar with.

3. Becuase you're saving so much money in software costs, you may
have a bit of extra money to throw at hardware. So you should
consider perhaps adding a few more disks to your system, and moving
at least the log files on to a separate disk.

> And the second question: Was enyone from the list a good perofmed
> postgres and if yes can I receiv this test result.

Yes, I've gotten excellent performance out of postgres from time
to time. However, the test results would be meaningless for you;
they probably don't relate to your application at all.

cjs
--
Curt Sampson +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

On Sat, 22 Jun 2002, Peter Dimov wrote:

> First I tested with P II 400 MHz 256 MB RAM.
> And second Dual Athlon MP , 1.6 GHz , 1 or 2 GB RAM.
> I expected to get big advantage from the second system and
> was very supprise from the results : I wan only 30 - 40% faster as the first.

Well, did you analyze your load carefully? How much of your
application is CPU-bound, and how much is disk I/O bound? How much
memory do you need to cache the working set, if it can be cached
at all?

Disks are often very important for database work, far more so than
CPU or memory. I have an application with queries that run about
the same on a 600 MHz Pentium III and a dual 2 GHz Xeon system, if
you use the same disks.

> I think the mistake is by tuning....

Very likely.

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC

Disk can be the problem.

All the system have the same disk type. It was WD 40 GB , IDE 7200.

I calculate the memory so, that all the date to be cached and so to get better performance.

My idea was, that in production we can install the needed RAM - 3 - 4 GB, so we will cache the data -

realy only for selects.

The CPU usage is 100 on P II one processor system and 50% on Athlon DUAL system, but for athlon

it is ok because the postgres can start the task on only one from this ( or I am wrong?).

I will change the disks to SCSI and will check the result.

Suppresed was that oracle ran on the same system and was so much faster.

thanks and regards.

  Curt Sampson <cjs@cynic.net> wrote:

On Sat, 22 Jun 2002, Peter Dimov wrote:

> First I tested with P II 400 MHz 256 MB RAM.
> And second Dual Athlon MP , 1.6 GHz , 1 or 2 GB RAM.
> I expected to get big advantage from the second system and
> was very supprise from the results : I wan only 30 - 40% faster as the first.

Well, did you analyze your load carefully? How much of your
application is CPU-bound, and how much is disk I/O bound? How much
memory do you need to cache the working set, if it can be cached
at all?

Disks are often very important for database work, far more so than
CPU or memory. I have an application with queries that run about
the same on a 600 MHz Pentium III and a dual 2 GHz Xeon system, if
you use the same disks.

> I think the mistake is by tuning....

Very likely.

cjs
--
Curt Sampson +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

Peter Dimov wrote:
>
> --0-1863467882-1024751590=:63919
> Content-Type: text/plain; charset=us-ascii
>
>  Hi,
> Curt, you are rigth. I tested it.
> My idea was only to compare postgres with oracle and to see if it is good.
> I got the simplest example with simple data inserting.
> I do not have any answer on my questin:
> Is postgres good as performace compared to oracle and if yes how to fine tune it.
> I readet all docs that Ifound, changed shared buffers and sort chache but can not
> get good result.
>
> And the second question:
> Was enyone from the list a good perofmed postgres and if yes can I receiv this test result.
> If no one had good tuned postgres I do not see the need to use it. For me it can be only "play", demo or...?
>
In many ways, yes indeed, Oracle is better than Postgres, and many other
db's as well.  But it is very expensive, and Postgres is free.  So what
do you want?

Mr. Sandman wrote:
> Peter Dimov wrote:
>
>>--0-1863467882-1024751590=:63919
>>Content-Type: text/plain; charset=us-ascii
>>
>> Hi,
>>Curt, you are rigth. I tested it.
>>My idea was only to compare postgres with oracle and to see if it is good.

I tested both, postgresql is far good (I mean better in my
opinion)

>>I got the simplest example with simple data inserting.
>>I do not have any answer on my questin:

>>Is postgres good as performace compared to oracle and if yes how to fine tune it.



tuning in postgres is done a bit at the daemon side
(starting options) and a lot at the hardware side
(the bottleneck is often the hardisks)
As postgres is free (as beer) you have more money to get
some better material (more ram, raid crontroller/ scsi
disks) etc....
(better reliability or general performance)

this is how I do:
- First of all, I look at my query (bad sql queries are slow).
- I use transactions to do many insertions (each transaction
inserting about 1000 to 3000 records)

- I optimized the postgresql.conf:

the main optimization is the fsync which requieres an UPS
plugged to the server to prevent dataloss in case of power down

I read this too:
http://www.ca.postgresql.org/docs/momjian/hw_performance/


>I readet all docs that Ifound, changed shared buffers and sort chache but can not
>>get good result.
try the fsync

>>And the second question:
>>Was enyone from the list a good perofmed postgres and if yes can I receiv this test result.
>>If no one had good tuned postgres I do not see the need to use it. For me it can be only "play", demo or...?

The most of our queries don't exceed 0.6s to perform (on
tables of 100,000 to 200,000 records on a middle size server
(bi-800MHz-scsiHardRAID5:   (1723.59 BogoMIPS) ).
We tested postgres on tables of more than 10,000,000 records
to make our mind... quickly :]

Mr. Sandman wrote:
 > Peter Dimov wrote:
 >
 >>--0-1863467882-1024751590=:63919
 >>Content-Type: text/plain; charset=us-ascii
 >>
 >> Hi,
 >>Curt, you are rigth. I tested it.
 >>My idea was only to compare postgres with oracle and to
see if it is good.

I tested both, postgresql is far good (I mean better in my
opinion)

 >>I got the simplest example with simple data inserting.
 >>I do not have any answer on my questin:

 >>Is postgres good as performace compared to oracle and if
yes how to fine tune it.



tuning in postgres is done a bit at the daemon side
(starting options) and a lot at the hardware side
(the bottleneck is often the hardisks)
As postgres is free (as beer) you have more money to get
some better material (more ram, raid crontroller/ scsi
disks) etc....
(better reliability or general performance)

this is how I do:
- First of all, I look at my query (bad sql queries are slow).
- I use transactions to do many insertions (each transaction
inserting about 1000 to 3000 records)

- I optimized the postgresql.conf:

the main optimization is the fsync which requieres an UPS
plugged to the server to prevent dataloss in case of power down

I read this too:
http://www.ca.postgresql.org/docs/momjian/hw_performance/


 >I readet all docs that Ifound, changed shared buffers and
sort chache but can not
 >>get good result.
try the fsync

 >>And the second question:
 >>Was enyone from the list a good perofmed postgres and if
yes can I receiv this test result.
 >>If no one had good tuned postgres I do not see the need
to use it. For me it can be only "play", demo or...?

The most of our queries don't exceed 0.6s to perform (on
tables of 100,000 to 200,000 records on a middle size server
(bi-800MHz-scsiHardRAID5:   (1723.59 BogoMIPS) ).
We tested postgres on tables of more than 10,000,000 records
to make our mind... quickly :]