Re: [PERFORM] Performance Bottleneck
Scott Marlowe wrote: On Fri, 2004-08-06 at 22:02, Martin Foster wrote: Scott Marlowe wrote: On Fri, 2004-08-06 at 17:24, Gaetano Mendola wrote: Martin Foster wrote: Gaetano Mendola wrote: Let start from your postgres configuration: shared_buffers = 8192 This is really too small for your configuration sort_mem = 2048 wal_buffers = 128 This is really too small for your configuration effective_cache_size = 16000 change this values in: shared_buffers = 5 sort_mem = 16084 wal_buffers = 1500 effective_cache_size = 32000 to bump up the shm usage you have to configure your OS in order to be allowed to use that ammount of SHM. This are the numbers that I feel good for your HW, the second step now is analyze your queries These changes have yielded some visible improvements, with load averages rarely going over the anything noticeable. However, I do have a question on the matter, why do these values seem to be far higher then what a frequently pointed to document would indicate as necessary? http://www.varlena.com/GeneralBits/Tidbits/perf.html I am simply curious, as this clearly shows that my understanding of PostgreSQL is clearly lacking when it comes to tweaking for the hardware. Unfortunately there is no a wizard tuning for postgres so each one of us have a own school. The data I gave you are oversized to be sure to achieve improvements. Now you can start to decrease these values ( starting from the wal_buffers ) in order to find the good compromise with your HW. FYI, my school of tuning is to change one thing at a time some reasonable percentage (shared_buffers from 1000 to 2000) and measure the change under simulated load. Make another change, test it, chart the shape of the change line. It should look something like this for most folks: shared_buffers | q/s (more is better) 100 | 20 200 | 45 400 | 80 1000 | 100 ... levels out here... 8000 | 110 1 | 108 2 | 40 3 | 20 Note it going back down as we exceed our memory and start swapping shared_buffers. Where that happens on your machine is determined by many things like your machine's memory, memory bandwidth, type of load, etc... but it will happen on most machines and when it does, it often happens at the worst times, under heavy parallel load. Unless testing shows it's faster, 1 or 25% of mem (whichever is less) is usually a pretty good setting for shared_buffers. Large data sets may require more than 1, but going over 25% on machines with large memory is usually a mistake, especially servers that do anything other than just PostgreSQL. You're absolutely right about one thing, there's no automatic wizard for tuning this stuff. Which rather points out the crux of the problem. This is a live system, meaning changes made need to be as informed as possible, and that changing values for the sake of testing can lead to potential problems in service. But if you make those changes slowly, as I was showing, you should see the small deleterious effects like I was showing long before they become catastrophic. To just jump shared_buffers to 5 is not a good idea, especially if the sweet spot is likely lower than that. As you can see 5 are less then 20% of his total memory and I strongly fell that 5 is not oversized for his hardware ( as wal_buffers isn't), may be could be for his database activity but for sure that value ( values ) can not be source of problems. I'd like to have a wizard that could be run also for hours in order to find the good compromise for all GUC parameters , may be a genetic algoritm can help. Regards Gaetano Mendola ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Performance Bottleneck
Tom Lane wrote: Martin Foster [EMAIL PROTECTED] writes: Gaetano Mendola wrote: change this values in: shared_buffers = 5 sort_mem = 16084 wal_buffers = 1500 This value of wal_buffers is simply ridiculous. Instead I think is ridiculous a wal_buffers = 8 ( 64KB ) by default. There isn't any reason to set wal_buffers higher than the amount of WAL log data that will be generated by a single transaction, because whatever is in the buffers will be flushed at transaction commit. If you are mainly dealing with heavy concurrency then it's the mean time between transaction commits that matters, and that's even less than the average transaction length. I partially agree with you, tell me how decide that value without even now the typical queries, the tipical load ... nothing. I suggested to OP to keep the wal_buffers so high in order to eliminate one freedom of degree in his performance problems. You can see from following reply, Gaetano Mendola wrote: Unfortunately there is no a wizard tuning for postgres so each one of us have a own school. The data I gave you are oversized to be sure to achieve improvements. Now you can start to decrease these values ( starting from the wal_buffers ) in order to find the good compromise with your HW. However wal_buffers = 1500 means ~12 MB that are not so expensive considering a server with 2GB of ram and I think that is a good compromise if you are not starving for RAM. I had a discussion about how fine tuning a postgres server with a client, my question was: are you planning to have someone that periodically take a look at your server activities in order to use your hardware at the best? Easy answer: No, because when the server is overloaded I will buy a bigger one that is less expensive that pay someone, considering also that shareolders prefer increase the capex that pay salaries ( if the company close the hardware can be selled :-( ). This is the real world out there. Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Performance Bottleneck
This value of wal_buffers is simply ridiculous. Instead I think is ridiculous a wal_buffers = 8 ( 64KB ) by default. There is no point making WAL buffers higher than 8. I have done much testing of this and it makes not the slightest difference to performance that I could measure. Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Performance Bottleneck
On 8/3/2004 2:05 PM, Martin Foster wrote: I run a Perl/CGI driven website that makes extensive use of PostgreSQL (7.4.3) for everything from user information to formatting and display of specific sections of the site. The server itself, is a dual processor AMD Opteron 1.4Ghz w/ 2GB Ram and 2 x 120GB hard drives mirrored for redundancy running under FreeBSD 5.2.1 (AMD64). Recently loads on the site have increased during peak hours to the point of showing considerable loss in performance.This can be observed when connections move from the 120 concurrent connections to PostgreSQL to roughly 175 or more. Essentially, the machine seems to struggle to keep up with continual requests and slows down respectively as resources are tied down. Have you taken a look at pgpool? I know, it sounds silly to *reduce* the number of DB connections through a connection pool, but it can help. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Performance Bottleneck
Jan Wieck wrote: On 8/3/2004 2:05 PM, Martin Foster wrote: I run a Perl/CGI driven website that makes extensive use of PostgreSQL (7.4.3) for everything from user information to formatting and display of specific sections of the site. The server itself, is a dual processor AMD Opteron 1.4Ghz w/ 2GB Ram and 2 x 120GB hard drives mirrored for redundancy running under FreeBSD 5.2.1 (AMD64). Recently loads on the site have increased during peak hours to the point of showing considerable loss in performance.This can be observed when connections move from the 120 concurrent connections to PostgreSQL to roughly 175 or more. Essentially, the machine seems to struggle to keep up with continual requests and slows down respectively as resources are tied down. Have you taken a look at pgpool? I know, it sounds silly to *reduce* the number of DB connections through a connection pool, but it can help. Jan I am currently making use of Apache::DBI which overrides the DBI::disconnect call and keeps a pool of active connections for use when need be. Since it offloads the pooling to the webserver, it seems more advantageous then pgpool which while being able to run on a external system is not adding another layer of complexity. Anyone had any experience with both Apache::DBI and pgpool? For my needs they seem to do essentially the same thing, simply that one is invisible to the code while the other requires adding the complexity of a proxy. Martin Foster Creator/Designer Ethereal Realms [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Performance Bottleneck
Christopher Kings-Lynne wrote: This value of wal_buffers is simply ridiculous. Instead I think is ridiculous a wal_buffers = 8 ( 64KB ) by default. There is no point making WAL buffers higher than 8. I have done much testing of this and it makes not the slightest difference to performance that I could measure. Chris No point? I had it at 64 if memory serves and logs were warning me that raising this value would be desired because of excessive IO brought upon from the logs being filled far too often. It would seem to me that 8 is a bit low in at least a few circumstances. Martin Foster Creator/Designer Ethereal Realms [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
