FAQ

PostgreSQL Tweaking

Renier
Apr 9, 2010 at 8:40 am
Hi There. Is there anyone in the SA PostgreSQL community that is willing to assist us.

We started trials on PostgreSQL just over a year ago, so we used on old desktop lying around as our database server, which served fine for testing purposes. We eventually bought a new server (2x2.93GHZ Quad core Xeon, 32GB RAM, 6x450GB SAS 15000RPM drives etc), quite a decent machine, but the performance on this machine is the same, if not worse, than it is on our old “server”. We tried tweaking the postgres.conf file but we do not really have enough knowledge of this file and performance does not increase, or the service wont start.

Is there anyone out there with enough experience in postgreSQL tweaking that might be willing to assist in some db tuning, and help us learn?

Regards,
Renier


__________ Information from ESET NOD32 Antivirus, version of virus signature database 5012 (20100409) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com
reply

Search Discussions

11 responses

  • Corrie Strydom at Apr 9, 2010 at 9:01 am

    On Fri, Apr 9, 2010 at 10:27 AM, renier wrote:

    Hi There. Is there anyone in the SA PostgreSQL community that is willing
    to assist us.



    We started trials on PostgreSQL just over a year ago, so we used on old
    desktop lying around as our database server, which served fine for testing
    purposes. We eventually bought a new server (2x2.93GHZ Quad core Xeon, 32GB
    RAM, 6x450GB SAS 15000RPM drives etc), quite a decent machine, but the
    performance on this machine is the same, if not worse, than it is on our old
    “server”. We tried tweaking the postgres.conf file but we do not really
    have enough knowledge of this file and performance does not increase, or the
    service wont start.



    Is there anyone out there with enough experience in postgreSQL tweaking
    that might be willing to assist in some db tuning, and help us learn?


    Regards,

    Renier
    Hi Renier, you'll have to be bit more specific regarding the setup there?

    1) What version of postgres?
    2) What type of application will be using it? Website/intranet site/custom
    app ect?
    3) How many connections are we talking here?
    4) Maybe post your current config files to the web somewhere, where people
    can have a look?

    More information you can give, the easier people can help you guys?

    Corrie
  • Renier at Apr 9, 2010 at 10:16 am
    Thanks Bell. One of the users on the group already responded, conversation attached which will give some more details, maybe more users can benefit from this conversation


    -----------------Corrie reply---------------
    Hi Renier, you'll have to be bit more specific regarding the setup there?

    1) What version of postgres?
    2) What type of application will be using it? Website/intranet site/custom app ect?
    3) How many connections are we talking here?
    4) Maybe post your current config files to the web somewhere, where people can have a look?

    More information you can give, the easier people can help you guys?

    Corrie


    -----------------My reply---------------

    Hi Corrie.

    Thanks for the reply. I did not want to give too much info and bore the people that will not be interested to help. But now that you mention it, I will probably also have to send out all these details that I’m sending you, to all willing participants.

    We are running a PostgresPlus Standard Server, the latest stable 8.4 release with all updates. After the install (Whether we select Dedicated Server/Mixed) we need to replace the original config file as the service will not start up. I think it has to do with the amount of shared memory allocated.
    The server runs Windows Server 2008 x64. It will be mainly used for internal development (for now), and we also plan to set up a VM on the machine for hosting some ASP applications, mainly for demo purposes at clients, as it takes time to configure IIS on individual laptops all the time. We will probably never have more than 10 connections, say 20 to provide a bit for the future. We are only 4 developers that will use it on a day to day basis. The DB will mainly serve our own desktop applications. And then the occasional demo or 2 at a client.

    At the moment we are using the default config file as our tweaking does not seem to serve any purpose. Most of the time the service won’t even start (usually complaining about not having enough shared memory or something similar). Running our “benchmark query”, (735000 spatial records), the CPU does not go above 1 or 2 % usage, but the query takes 3 ½ minutes to complete on the localhost. Running it over the network, it runs in 48seconds, the same as on our dektop test pc. Kinda makes us wonder why we just forked out 100k for a server?
    This server also acts as a secondary domain controller, and we run VMWare on it (this will be for our Web environment). We could dedicate up to 16GB of RAM for postgres

    Thanks!
    Renier

    -----Original Message-----
    From: Alastair Turner
    Sent: 09 April 2010 12:09 PM
    To: renier
    Cc: jnbpug@postgresql.org
    Subject: Re: [jnbpug] PostgreSQL Tweaking
    On Fri, Apr 9, 2010 at 10:27 AM, renier wrote:
    Hi There. Is there anyone in the SA PostgreSQL community that is willing to
    assist us.



    We started trials on PostgreSQL just over a year ago, so we used on old
    desktop lying around as our database server, which served fine for testing
    purposes. We eventually bought a new server (2x2.93GHZ Quad core Xeon, 32GB
    RAM, 6x450GB SAS 15000RPM drives etc), quite a decent machine, but the
    performance on this machine is the same, if not worse, than it is on our old
    “server”. We tried tweaking the postgres.conf file but we do not really
    have enough knowledge of this file and performance does not increase, or the
    service wont start.



    Is there anyone out there with enough experience in postgreSQL tweaking that
    might be willing to assist in some db tuning, and help us learn?

    Regards,

    Renier

    __________ Information from ESET NOD32 Antivirus, version of virus signature
    database 5012 (20100409) __________

    The message was checked by ESET NOD32 Antivirus.

    http://www.eset.com
    Hi Renier

    Firstly, thanks for turning to the mailing list for assistance, it's
    great to be getting some traffic.

    You say that the server won't start after some of the config changes.
    If these are to the shared memory settings then you're going to have
    to tweak the kernel's shm_max and shm_all settings (assuming that this
    is on a Linux system). The doc at
    http://www.postgresql.org/docs/8.4/interactive/runtime-config-resource.html
    gives some good tips on the memory settings.

    Exactly which of the bits you'll benefit from tweaking does depend
    very much on your workload. Are you doing a lot of sorts, are you
    doing a lot of inserts/updates ... ?

    Regards

    Bell.
  • Alastair Turner at Apr 9, 2010 at 10:32 am
    Could you post your benchmark query and an EXPLAIN ANALYSE of it?
    On Fri, Apr 9, 2010 at 12:13 PM, renier wrote:
    Thanks Bell.  One of the users on the group already responded, conversation attached which will give some more details, maybe more users can benefit from this conversation


    -----------------Corrie reply---------------
    Hi Renier, you'll have to be bit more specific regarding the setup there?

    1) What version of postgres?
    2)  What type of application will be using it? Website/intranet site/custom app ect?
    3) How many connections are we talking here?
    4) Maybe post your current config files to the web somewhere, where people can have a look?

    More information you can give, the easier people can help you guys?

    Corrie


    -----------------My reply---------------

    Hi Corrie.

    Thanks for the reply.  I did not want to give too much info and bore the people that will not be interested to help.   But now that you mention it, I will probably also have to send out all these details that I’m sending you, to all willing participants.

    We are running a PostgresPlus Standard Server, the latest stable 8.4 release with all updates. After the install (Whether we select Dedicated Server/Mixed) we need to replace the original config file as the service will not start up. I think it has to do with the amount of shared memory allocated.
    The server runs Windows Server 2008 x64.  It will be mainly used for internal development (for now), and we also plan to set up a VM on the machine for hosting some ASP applications, mainly for demo purposes at clients, as it takes time to configure IIS on individual laptops all the time.  We will probably never have more than 10 connections, say 20 to provide a bit for the future. We are only 4 developers that will use it on a day to day basis.  The DB will mainly serve our own desktop applications.  And then the occasional demo or 2 at a client.

    At the moment we are using the default config file as our tweaking does not seem to serve any purpose. Most of the time the service won’t even start (usually complaining about not having enough shared memory or something similar).  Running our “benchmark query”, (735000 spatial records), the CPU does not go above 1 or 2 % usage, but the query takes 3 ½ minutes to complete on the localhost.  Running it over the network, it runs in 48seconds, the same as on our dektop test pc.  Kinda makes us wonder why we just forked out 100k for a server?
    This server also acts as a secondary domain controller, and we run VMWare on it (this will be for our Web environment).  We could dedicate up to 16GB of RAM for postgres

    Thanks!
    Renier

    -----Original Message-----
    From: Alastair Turner
    Sent: 09 April 2010 12:09 PM
    To: renier
    Cc: jnbpug@postgresql.org
    Subject: Re: [jnbpug] PostgreSQL Tweaking
    On Fri, Apr 9, 2010 at 10:27 AM, renier wrote:
    Hi There.  Is there anyone in the SA PostgreSQL community that is willing to
    assist us.



    We started trials on PostgreSQL just over a year ago, so we used on old
    desktop lying around as our database server, which served fine for testing
    purposes. We eventually bought a new server (2x2.93GHZ Quad core Xeon, 32GB
    RAM, 6x450GB SAS 15000RPM drives etc), quite a decent machine, but the
    performance on this machine is the same, if not worse, than it is on our old
    “server”.  We tried tweaking the postgres.conf file but we do not really
    have enough knowledge of this file and performance does not increase, or the
    service wont start.



    Is there anyone out there with enough experience in postgreSQL tweaking that
    might be willing to assist in some db tuning, and help us learn?

    Regards,

    Renier

    __________ Information from ESET NOD32 Antivirus, version of virus signature
    database 5012 (20100409) __________

    The message was checked by ESET NOD32 Antivirus.

    http://www.eset.com
    Hi Renier

    Firstly, thanks for turning to the mailing list for assistance, it's
    great to be getting some traffic.

    You say that the server won't start after some of the config changes.
    If these are to the shared memory settings then you're going to have
    to tweak the kernel's shm_max and shm_all settings (assuming that this
    is on a Linux system). The doc at
    http://www.postgresql.org/docs/8.4/interactive/runtime-config-resource.html
    gives some good tips on the memory settings.

    Exactly which of the bits you'll benefit from tweaking does depend
    very much on your workload. Are you doing a lot of sorts, are you
    doing a lot of inserts/updates ... ?

    Regards

    Bell.
  • Corrie Strydom at Apr 9, 2010 at 10:32 am

    On Fri, Apr 9, 2010 at 12:13 PM, renier wrote:

    Thanks Bell. One of the users on the group already responded, conversation
    attached which will give some more details, maybe more users can benefit
    from this conversation


    -----------------Corrie reply---------------
    Hi Renier, you'll have to be bit more specific regarding the setup there?

    1) What version of postgres?
    2) What type of application will be using it? Website/intranet site/custom
    app ect?
    3) How many connections are we talking here?
    4) Maybe post your current config files to the web somewhere, where people
    can have a look?

    More information you can give, the easier people can help you guys?

    Corrie


    -----------------My reply---------------

    Hi Corrie.

    Thanks for the reply. I did not want to give too much info and bore the
    people that will not be interested to help. But now that you mention it, I
    will probably also have to send out all these details that I’m sending you,
    to all willing participants.

    We are running a PostgresPlus Standard Server, the latest stable 8.4
    release with all updates. After the install (Whether we select Dedicated
    Server/Mixed) we need to replace the original config file as the service
    will not start up. I think it has to do with the amount of shared memory
    allocated.
    The server runs Windows Server 2008 x64. It will be mainly used for
    internal development (for now), and we also plan to set up a VM on the
    machine for hosting some ASP applications, mainly for demo purposes at
    clients, as it takes time to configure IIS on individual laptops all the
    time. We will probably never have more than 10 connections, say 20 to
    provide a bit for the future. We are only 4 developers that will use it on a
    day to day basis. The DB will mainly serve our own desktop applications.
    And then the occasional demo or 2 at a client.

    At the moment we are using the default config file as our tweaking does not
    seem to serve any purpose. Most of the time the service won’t even start
    (usually complaining about not having enough shared memory or something
    similar). Running our “benchmark query”, (735000 spatial records), the CPU
    does not go above 1 or 2 % usage, but the query takes 3 ½ minutes to
    complete on the localhost. Running it over the network, it runs in
    48seconds, the same as on our dektop test pc. Kinda makes us wonder why we
    just forked out 100k for a server?
    This server also acts as a secondary domain controller, and we run VMWare
    on it (this will be for our Web environment). We could dedicate up to 16GB
    of RAM for postgres

    Thanks!
    Renier

    -----Original Message-----
    From: Alastair Turner
    Sent: 09 April 2010 12:09 PM
    To: renier
    Cc: jnbpug@postgresql.org
    Subject: Re: [jnbpug] PostgreSQL Tweaking
    On Fri, Apr 9, 2010 at 10:27 AM, renier wrote:
    Hi There. Is there anyone in the SA PostgreSQL community that is willing to
    assist us.



    We started trials on PostgreSQL just over a year ago, so we used on old
    desktop lying around as our database server, which served fine for testing
    purposes. We eventually bought a new server (2x2.93GHZ Quad core Xeon, 32GB
    RAM, 6x450GB SAS 15000RPM drives etc), quite a decent machine, but the
    performance on this machine is the same, if not worse, than it is on our old
    “server”. We tried tweaking the postgres.conf file but we do not really
    have enough knowledge of this file and performance does not increase, or the
    service wont start.



    Is there anyone out there with enough experience in postgreSQL tweaking that
    might be willing to assist in some db tuning, and help us learn?

    Regards,

    Renier

    __________ Information from ESET NOD32 Antivirus, version of virus signature
    database 5012 (20100409) __________

    The message was checked by ESET NOD32 Antivirus.

    http://www.eset.com
    Hi Renier

    Firstly, thanks for turning to the mailing list for assistance, it's
    great to be getting some traffic.

    You say that the server won't start after some of the config changes.
    If these are to the shared memory settings then you're going to have
    to tweak the kernel's shm_max and shm_all settings (assuming that this
    is on a Linux system). The doc at
    http://www.postgresql.org/docs/8.4/interactive/runtime-config-resource.html
    gives some good tips on the memory settings.

    Exactly which of the bits you'll benefit from tweaking does depend
    very much on your workload. Are you doing a lot of sorts, are you
    doing a lot of inserts/updates ... ?

    Regards

    Bell.


    --
    Sent via jnbpug mailing list (jnbpug@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/jnbpug


    Hi Renier, I'm not sure on what you have read up in the internet regarding
    postgres performance boosting, see
    http://ashleyangell.com/2009/10/postgresql-performance-optimization/ , I've
    used this a while back, and it helped.

    the "work_mem" parameter in the .conf has given me trouble, if you make it
    to big, performance takes a huge knock, example on our 8Gb Win2003 server, I
    use 512mb.

    Look at the sections describing "sharred_buffers".

    Also, check your windows user "postgres" I remember there was a setting on
    the 2003 server I changed to help as well (not a great answer, but have a
    poke around)

    also have a look at :
    http://www.varlena.com/GeneralBits/Tidbits/perf.html#shbuf
    http://www.linuxjournal.com/article/4791

    A excellent cheet sheat is available at
    http://www.alberton.info/postgresql_cheat_sheet.html , see the queries
    against the INFORMATION_SCHEMA, helped in the tuning.

    Remember, to tune and tweak the postgres server takes a few reboots and
    changes, there isn't a quick solution sorry. But in doing this, you'll learn
    so much more on how it works.

    Corrie
  • Alastair Turner at Apr 9, 2010 at 10:17 am

    On Fri, Apr 9, 2010 at 10:27 AM, renier wrote:
    Hi There.  Is there anyone in the SA PostgreSQL community that is willing to
    assist us.



    We started trials on PostgreSQL just over a year ago, so we used on old
    desktop lying around as our database server, which served fine for testing
    purposes. We eventually bought a new server (2x2.93GHZ Quad core Xeon, 32GB
    RAM, 6x450GB SAS 15000RPM drives etc), quite a decent machine, but the
    performance on this machine is the same, if not worse, than it is on our old
    “server”.  We tried tweaking the postgres.conf file but we do not really
    have enough knowledge of this file and performance does not increase, or the
    service wont start.



    Is there anyone out there with enough experience in postgreSQL tweaking that
    might be willing to assist in some db tuning, and help us learn?

    Regards,

    Renier

    __________ Information from ESET NOD32 Antivirus, version of virus signature
    database 5012 (20100409) __________

    The message was checked by ESET NOD32 Antivirus.

    http://www.eset.com
    Hi Renier

    Firstly, thanks for turning to the mailing list for assistance, it's
    great to be getting some traffic.

    You say that the server won't start after some of the config changes.
    If these are to the shared memory settings then you're going to have
    to tweak the kernel's shm_max and shm_all settings (assuming that this
    is on a Linux system). The doc at
    http://www.postgresql.org/docs/8.4/interactive/runtime-config-resource.html
    gives some good tips on the memory settings.

    Exactly which of the bits you'll benefit from tweaking does depend
    very much on your workload. Are you doing a lot of sorts, are you
    doing a lot of inserts/updates ... ?

    Regards

    Bell.
  • Renier at Apr 9, 2010 at 10:57 am
    OK, after running the Explain Analyse, the execution time jumped from +- 230000ms t0 +- 14000ms on the localhost.... can someone explain this? (pun intended... :)
    Running it from my PC over the network, it constantly executes at about 48000ms, regardless of the server used (New Server, old desktop PC or Ubuntu Virtual server)

    select * from assetregistericon

    Explain Analyse results:
    "Seq Scan on assetregistericon (cost=0.00..41106.50 rows=738050 width=428) (actual time=0.036..667.962 rows=738050 loops=1)"
    "Total runtime: 1209.181 ms"

    -- Executing query:

    BEGIN;
    EXPLAIN ANALYZE select * from assetregistericon
    ;
    ROLLBACK;
    Query result with 2 rows will be returned.


    2 rows retrieved.

    -- Executing query:
    select * from assetregistericon
    Total query runtime: 14420 ms.
    738050 rows retrieved.

    -- Executing query:

    BEGIN;
    EXPLAIN ANALYZE select * from assetregistericon
    ;
    ROLLBACK;
    Query result with 2 rows will be returned.


    2 rows retrieved.

    -- Executing query:
    select * from assetregistericon
    Total query runtime: 14910 ms.
    738050 rows retrieved.

    -- Executing query:
    select * from assetregistericon
    Total query runtime: 16345 ms.
    738050 rows retrieved.


    -----Original Message-----
    From: jnbpug-owner@postgresql.org On Behalf Of Alastair Turner
    Sent: 09 April 2010 12:25 PM
    To: renier
    Cc: jnbpug@postgresql.org
    Subject: Re: [jnbpug] PostgreSQL Tweaking

    Could you post your benchmark query and an EXPLAIN ANALYSE of it?
    On Fri, Apr 9, 2010 at 12:13 PM, renier wrote:
    Thanks Bell. One of the users on the group already responded, conversation attached which will give some more details, maybe more users can benefit from this conversation


    -----------------Corrie reply---------------
    Hi Renier, you'll have to be bit more specific regarding the setup there?

    1) What version of postgres?
    2) What type of application will be using it? Website/intranet site/custom app ect?
    3) How many connections are we talking here?
    4) Maybe post your current config files to the web somewhere, where people can have a look?

    More information you can give, the easier people can help you guys?

    Corrie


    -----------------My reply---------------

    Hi Corrie.

    Thanks for the reply. I did not want to give too much info and bore the people that will not be interested to help. But now that you mention it, I will probably also have to send out all these details that I’m sending you, to all willing participants.

    We are running a PostgresPlus Standard Server, the latest stable 8.4 release with all updates. After the install (Whether we select Dedicated Server/Mixed) we need to replace the original config file as the service will not start up. I think it has to do with the amount of shared memory allocated.
    The server runs Windows Server 2008 x64. It will be mainly used for internal development (for now), and we also plan to set up a VM on the machine for hosting some ASP applications, mainly for demo purposes at clients, as it takes time to configure IIS on individual laptops all the time. We will probably never have more than 10 connections, say 20 to provide a bit for the future. We are only 4 developers that will use it on a day to day basis. The DB will mainly serve our own desktop applications. And then the occasional demo or 2 at a client.

    At the moment we are using the default config file as our tweaking does not seem to serve any purpose. Most of the time the service won’t even start (usually complaining about not having enough shared memory or something similar). Running our “benchmark query”, (735000 spatial records), the CPU does not go above 1 or 2 % usage, but the query takes 3 ½ minutes to complete on the localhost. Running it over the network, it runs in 48seconds, the same as on our dektop test pc. Kinda makes us wonder why we just forked out 100k for a server?
    This server also acts as a secondary domain controller, and we run VMWare on it (this will be for our Web environment). We could dedicate up to 16GB of RAM for postgres

    Thanks!
    Renier

    -----Original Message-----
    From: Alastair Turner
    Sent: 09 April 2010 12:09 PM
    To: renier
    Cc: jnbpug@postgresql.org
    Subject: Re: [jnbpug] PostgreSQL Tweaking
    On Fri, Apr 9, 2010 at 10:27 AM, renier wrote:
    Hi There. Is there anyone in the SA PostgreSQL community that is willing to
    assist us.



    We started trials on PostgreSQL just over a year ago, so we used on old
    desktop lying around as our database server, which served fine for testing
    purposes. We eventually bought a new server (2x2.93GHZ Quad core Xeon, 32GB
    RAM, 6x450GB SAS 15000RPM drives etc), quite a decent machine, but the
    performance on this machine is the same, if not worse, than it is on our old
    “server”. We tried tweaking the postgres.conf file but we do not really
    have enough knowledge of this file and performance does not increase, or the
    service wont start.



    Is there anyone out there with enough experience in postgreSQL tweaking that
    might be willing to assist in some db tuning, and help us learn?

    Regards,

    Renier

    __________ Information from ESET NOD32 Antivirus, version of virus signature
    database 5012 (20100409) __________

    The message was checked by ESET NOD32 Antivirus.

    http://www.eset.com
    Hi Renier

    Firstly, thanks for turning to the mailing list for assistance, it's
    great to be getting some traffic.

    You say that the server won't start after some of the config changes.
    If these are to the shared memory settings then you're going to have
    to tweak the kernel's shm_max and shm_all settings (assuming that this
    is on a Linux system). The doc at
    http://www.postgresql.org/docs/8.4/interactive/runtime-config-resource.html
    gives some good tips on the memory settings.

    Exactly which of the bits you'll benefit from tweaking does depend
    very much on your workload. Are you doing a lot of sorts, are you
    doing a lot of inserts/updates ... ?

    Regards

    Bell.
    --
    Sent via jnbpug mailing list (jnbpug@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/jnbpug
  • Alastair Turner at Apr 9, 2010 at 11:21 am
    On Fri, Apr 9, 2010 at 12:54 PM, renier wrote:
    .......
    Explain Analyse results:
    "Seq Scan on assetregistericon  (cost=0.00..41106.50 rows=738050 width=428) (actual time=0.036..667.962 rows=738050 loops=1)"
    "Total runtime: 1209.181 ms"
    ............
    -- Executing query:
    select * from assetregistericon
    Total query runtime: 16345 ms.
    738050 rows retrieved.
    My first thought looking at the difference between the number given by
    the analyse and the number given by (I presume) the client application
    is that the transfer of the data between the server and the client
    application is what's taking the time. Which would explain the near
    constant total time for remote clients on different servers.

    I presume there is some sort of id column on the table. What are the
    execution times on the different servers for SELECT SUM(id) FROM
    assetregistericon; ?
  • Renier at Apr 9, 2010 at 11:00 am
    Thanks a lot Corrie. I will have a look at the links you posted in the next hour or two, but it seems that there could be some valuable info!

    From: jnbpug-owner@postgresql.org On Behalf Of Corrie Strydom
    Sent: 09 April 2010 12:32 PM
    To: renier
    Cc: Alastair Turner; jnbpug@postgresql.org
    Subject: Re: [jnbpug] PostgreSQL Tweaking

    On Fri, Apr 9, 2010 at 12:13 PM, renier wrote:
    Thanks Bell. One of the users on the group already responded, conversation attached which will give some more details, maybe more users can benefit from this conversation


    -----------------Corrie reply---------------
    Hi Renier, you'll have to be bit more specific regarding the setup there?

    1) What version of postgres?
    2) What type of application will be using it? Website/intranet site/custom app ect?
    3) How many connections are we talking here?
    4) Maybe post your current config files to the web somewhere, where people can have a look?

    More information you can give, the easier people can help you guys?

    Corrie


    -----------------My reply---------------

    Hi Corrie.

    Thanks for the reply. I did not want to give too much info and bore the people that will not be interested to help. But now that you mention it, I will probably also have to send out all these details that I’m sending you, to all willing participants.

    We are running a PostgresPlus Standard Server, the latest stable 8.4 release with all updates. After the install (Whether we select Dedicated Server/Mixed) we need to replace the original config file as the service will not start up. I think it has to do with the amount of shared memory allocated.
    The server runs Windows Server 2008 x64. It will be mainly used for internal development (for now), and we also plan to set up a VM on the machine for hosting some ASP applications, mainly for demo purposes at clients, as it takes time to configure IIS on individual laptops all the time. We will probably never have more than 10 connections, say 20 to provide a bit for the future. We are only 4 developers that will use it on a day to day basis. The DB will mainly serve our own desktop applications. And then the occasional demo or 2 at a client.

    At the moment we are using the default config file as our tweaking does not seem to serve any purpose. Most of the time the service won’t even start (usually complaining about not having enough shared memory or something similar). Running our “benchmark query”, (735000 spatial records), the CPU does not go above 1 or 2 % usage, but the query takes 3 ½ minutes to complete on the localhost. Running it over the network, it runs in 48seconds, the same as on our dektop test pc. Kinda makes us wonder why we just forked out 100k for a server?
    This server also acts as a secondary domain controller, and we run VMWare on it (this will be for our Web environment). We could dedicate up to 16GB of RAM for postgres

    Thanks!
    Renier
    -----Original Message-----
    From: Alastair Turner
    Sent: 09 April 2010 12:09 PM
    To: renier
    Cc: jnbpug@postgresql.org
    Subject: Re: [jnbpug] PostgreSQL Tweaking
    On Fri, Apr 9, 2010 at 10:27 AM, renier wrote:
    Hi There. Is there anyone in the SA PostgreSQL community that is willing to
    assist us.



    We started trials on PostgreSQL just over a year ago, so we used on old
    desktop lying around as our database server, which served fine for testing
    purposes. We eventually bought a new server (2x2.93GHZ Quad core Xeon, 32GB
    RAM, 6x450GB SAS 15000RPM drives etc), quite a decent machine, but the
    performance on this machine is the same, if not worse, than it is on our old
    “server”. We tried tweaking the postgres.conf file but we do not really
    have enough knowledge of this file and performance does not increase, or the
    service wont start.



    Is there anyone out there with enough experience in postgreSQL tweaking that
    might be willing to assist in some db tuning, and help us learn?

    Regards,

    Renier

    __________ Information from ESET NOD32 Antivirus, version of virus signature
    database 5012 (20100409) __________

    The message was checked by ESET NOD32 Antivirus.

    http://www.eset.com
    Hi Renier

    Firstly, thanks for turning to the mailing list for assistance, it's
    great to be getting some traffic.

    You say that the server won't start after some of the config changes.
    If these are to the shared memory settings then you're going to have
    to tweak the kernel's shm_max and shm_all settings (assuming that this
    is on a Linux system). The doc at
    http://www.postgresql.org/docs/8.4/interactive/runtime-config-resource.html
    gives some good tips on the memory settings.

    Exactly which of the bits you'll benefit from tweaking does depend
    very much on your workload. Are you doing a lot of sorts, are you
    doing a lot of inserts/updates ... ?

    Regards

    Bell.


    --
    Sent via jnbpug mailing list (jnbpug@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/jnbpug



    Hi Renier, I'm not sure on what you have read up in the internet regarding postgres performance boosting, see http://ashleyangell.com/2009/10/postgresql-performance-optimization/ , I've used this a while back, and it helped.

    the "work_mem" parameter in the .conf has given me trouble, if you make it to big, performance takes a huge knock, example on our 8Gb Win2003 server, I use 512mb.

    Look at the sections describing "sharred_buffers".

    Also, check your windows user "postgres" I remember there was a setting on the 2003 server I changed to help as well (not a great answer, but have a poke around)

    also have a look at :
    http://www.varlena.com/GeneralBits/Tidbits/perf.html#shbuf
    http://www.linuxjournal.com/article/4791

    A excellent cheet sheat is available at http://www.alberton.info/postgresql_cheat_sheet.html , see the queries against the INFORMATION_SCHEMA, helped in the tuning.

    Remember, to tune and tweak the postgres server takes a few reboots and changes, there isn't a quick solution sorry. But in doing this, you'll learn so much more on how it works.

    Corrie



    __________ Information from ESET NOD32 Antivirus, version of virus signature database 5012 (20100409) __________

    The message was checked by ESET NOD32 Antivirus.

    http://www.eset.com
  • Renier at Apr 9, 2010 at 12:01 pm
    I would fully agree with your statement looking at the figures now. The results are from PGAdmin, just running the query.
    What really puzzled me, is that it took 4 times longer running the query on the local pgadmin, than it takes to run it over the network. After running the explain analyse query, the speeds improved a lot. But I could not simulate those speeds again. They are back to > 200000ms, even after running Explain Analyse. It seems I cannot get a constant speed.

    We did some tests on Ubuntu in a VM on the new server. Specs were 2 CPU (2.93x64 Xeon, 8gb ram) and the same query ran in 10sec on localhost.

    2 reasons we don’t want to go with Ubuntu (or any linux for that matter)
    1: We need to host another VM. We do not want to use too much system resources on VM's. We need to host a Web server in a VM and a SQL Server testing environment in a VM for a client. Therefore running it on the host is better.
    2: Since we are quite a small company, we do not have any Linux specialists, and should problems arise, we need some technical expertise in troubleshooting.


    The first result is the same on local or over the network
    -- Executing query:
    SELECT COUNT(componentid) from assetregistericon
    Total query runtime: 210 ms.
    1 row retrieved.

    -- Executing query:
    SELECT * from assetregistericon
    Total query runtime: 213929 ms.
    738050 rows retrieved.

    -- Executing query:

    BEGIN;
    EXPLAIN ANALYZE SELECT * from assetregistericon;
    ;
    ROLLBACK;
    Query result with 2 rows will be returned.


    -----Original Message-----
    From: Alastair Turner
    Sent: 09 April 2010 01:13 PM
    To: renier
    Cc: jnbpug@postgresql.org
    Subject: Re: [jnbpug] PostgreSQL Tweaking

    On Fri, Apr 9, 2010 at 12:54 PM, renier wrote:
    .......
    Explain Analyse results:
    "Seq Scan on assetregistericon (cost=0.00..41106.50 rows=738050 width=428) (actual time=0.036..667.962 rows=738050 loops=1)"
    "Total runtime: 1209.181 ms"
    ............
    -- Executing query:
    select * from assetregistericon
    Total query runtime: 16345 ms.
    738050 rows retrieved.
    My first thought looking at the difference between the number given by
    the analyse and the number given by (I presume) the client application
    is that the transfer of the data between the server and the client
    application is what's taking the time. Which would explain the near
    constant total time for remote clients on different servers.

    I presume there is some sort of id column on the table. What are the
    execution times on the different servers for SELECT SUM(id) FROM
    assetregistericon; ?
  • Alastair Turner at Apr 9, 2010 at 12:51 pm

    On Fri, Apr 9, 2010 at 1:58 PM, renier wrote:
    I would fully agree with your statement looking at the figures now.  The results are from PGAdmin, just running the query.
    What really puzzled me, is that it took 4 times longer running the query on the local pgadmin, than it takes to run it over the network.  After running the explain analyse query, the speeds improved a lot.  But I could not simulate those speeds again.  They are back to > 200000ms, even after running Explain Analyse.  It seems I cannot get a constant speed.
    Ok, now we're looking at the specific issue - why does the local
    loopback take forever?. A couple more questions:
    - What address are you using to connect to the database host from
    itself, does it perform the same for the other addresses?
    - What is the execution time for the query and iterating through the
    resultset from your scripting language - how does it differ between
    machines?
    - Is this query really representative of what your application does with the db?

    Bell
  • Renier at Apr 9, 2010 at 1:19 pm
    Ok, now we're looking at the specific issue - why does the local
    loopback take forever?. A couple more questions:


    - What address are you using to connect to the database host from
    itself, does it perform the same for the other addresses?

    - What is the execution time for the query and iterating through the
    resultset from your scripting language - how does it differ between
    machines?

    I'm not sure I understand the questions properly, but I run the query on 'localhost' (or 127.0.0.1) in PGADmin
    When running it from different computers on the network, new server being the host, I get between 46 and 52 seconds. The difference is fine considering network traffic will not have the same throughput every time.
    Using our old desktop "server" as the host, running query from my notebook, 48sec, running the query from the new server, on the new server, 213sec. It appears there is something odd on the New server. Keep in mind it is a new machine, only delivered 2 weeks ago, so it’s a fresh install of windows too.


    - Is this query really representative of what your application does with the db?
    Yes, this is an asset register for one of our clients, and the query will indeed run on all records

Related Discussions

Discussion Navigation
viewthread | post