Re: [PERFORM] PostgreSQL vs MySQL, and FreeBSD
On Fri, 16 Nov 2007 11:06:11 -0500 Jonah H. Harris [EMAIL PROTECTED] wrote: On Nov 16, 2007 10:56 AM, Dave Dutcher [EMAIL PROTECTED] wrote: I don't know about that. There are times when it is the right plan: Agreed. IMHO, there's nothing wrong with nested-loop join as long as it's being used properly. Can you explain further please? (I'm not disagreeing with you, just want to know when nested loops are not used properly - does the planner make mistakes that you have to watch out for?) Thx, Josh ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] SQL Monitoring
On Wed, 10 Oct 2007 12:41:49 +0200 Tomáš Vondra [EMAIL PROTECTED] wrote: snip Actually we wrote something similar as pgfounie was not as nice as today, at that time (2005] - you can find that tool on http://opensource.pearshealthcyber.cz/. Actually I'm working on a complete rewrite of that tool into Java (new features, performance etc.) - it's almost done, the alpha release should be ready in two weeks or something like that. If you are interested in this, just let me know and I'll notify you once the first version is available on sf.net. Can you post an announcement here? Josh ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Shared Buffer setting in postgresql.conf
On Wed, 10 Oct 2007 10:20:02 -0500 Scott Marlowe [EMAIL PROTECTED] wrote: In 7.4, using 25% is often too high a setting for it to handle well, and the practical useful maximum is usually under 10,000 shared_buffers, and often closer to 1,000 to 5,000 Scott - interesting reply. Is this also true for 8.1? I currently have mine set to 16384 - server has 3.5 GB of total memory. Josh ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PERFORM] Tuning Help - What did I do wrong?
We have a pretty busy linux server running postgres 8.1.4, waiting to upgrade until 8.3 to avoid dump/restoring twice. # cat /proc/meminfo total:used:free: shared: buffers: cached: Mem: 3704217600 3592069120 1121484800 39460864 2316271616 Swap: 2516918272 270336 2516647936 # cat /proc/cpuinfo processor : 0 vendor_id : GenuineIntel cpu family : 15 model : 4 model name : Intel(R) Xeon(TM) CPU 3.00GHz stepping: 3 cpu MHz : 2992.795 The postgresql.conf was basically the default so I decided to increase the cache size and a couple paramaters to make more use of that memory - here's what I did: shared_buffers = 16384 (was 1000) work_mem = 16384 (was 1024) wal_buffers = 24 (was 8) checkpoint_segments = 5 (was 3) effective_cache_size = 1 (was 1000) stats_command_string = on (was off) stats_block_level = on (was off) stats_row_level = on (was off) In order to do this I had to change /proc/sys/kernel/shmmax to 536870912 (don't have /etc/sysctl) Also, the entire cluster gets vacuumed analyzed nightly. After making these changes, the performance on the server actually worsened. I slowly backed off on some of the paramaters but didn't seem to help. Wondering if those changes are silly? For a server this size I didn't think this would be problematic. Thank you, Josh ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Tuning Help - What did I do wrong?
On Thu, 4 Oct 2007 11:19:22 -0500 Scott Marlowe [EMAIL PROTECTED] wrote: We need to see examples of what's slow, including explain analyze output for slow queries. Also a brief explanation of the type of load your database server is seeing. I.e. is it a lot of little transactions, mostly read, batch processing, lots of users, one user, etc... Right now we don't have enough info to really help you. Sorry, this server is for a few (100+?) websites so it's running along site apache, php. All connections to postgresql (except for the occaional psql console login) are done from php requests, using the same user (basically there are two users, the one php uses and postgres). The bulk of the activity would be reads, but certainly inesrts/updates/deletes would be interspersed in there. Most of the activity is done via auto-commits, not many long transactions. From your followup email: ... you should use tools like vmstat, iostat and top to get an idea of what your server is doing. # vmstat procs memoryswap io system cpu r b w swpd free buff cache si sobibo incs us sy id 3 1 0268 68332 39016 2201436 0 0 3 34 2 3 4 2 sorry about the wrapping... iostat is not found - will see if I can download it. top typically shows postmaster as the top process with 10-15% of the CPU, followed by apache threads. 12:01pm up 104 days, 12:05, 2 users, load average: 9.75, 9.30, 7.70 215 processes: 214 sleeping, 1 running, 0 zombie, 0 stopped CPU states: 0.1% user, 0.0% system, 0.0% nice, 0.4% idle Mem: 3617400K av, 3552784K used, 64616K free, 0K shrd, 37456K buff Swap: 2457928K av,264K used, 2457664K free 2273664K cached PID USER PRI NI SIZE RSS SHARE STAT LIB %CPU %MEM TIME COMMAND 31797 postgres 17 0 28836 28M 1784 S 0 8.5 0.7 10:15 postmaster What kind of drive subsystem do you have? What kind of raid controller? etc... Gathering more information on this - Raid is a software RAID-1. Some information: I believe itI believe it # df -h FilesystemSize Used Avail Use% Mounted on /dev/md0 66G 50G 16G 76% / /dev/sda1 15M 6.6M 8.5M 44% /boot # cat /proc/mdstat Personalities : [raid0] [raid1] read_ahead 1024 sectors md0 : active raid1 sdb3[0] sdc3[1] 70573440 blocks [2/2] [UU] unused devices: none Thanks for your help, I'm more of a developer guy so let me know what else is useful. Josh ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Tuning Help - What did I do wrong?
On Thu, 04 Oct 2007 14:03:07 -0500 Kevin Grittner [EMAIL PROTECTED] wrote: It's kind of silly to tell PostgreSQL that its total cache space is 1 pages when you've got more than that in shared buffers plus all that OS cache space. Try something around 285000 pages for effective_cache_size. Good point. stats_command_string = on (was off) stats_block_level = on (was off) stats_row_level = on (was off) After making these changes, the performance on the server actually worsened. I slowly backed off on some of the paramaters but didn't seem to help. Did you try turning off the collection of those additional statistics? That isn't free. I turned off all but row level since I decided to try turning autovacuum on. You didn't get specific about what you saw in performance problems. If you are seeing occasional freezes of all queries, you are likely looking at a known issue with spikiness of disk output. For some this can be corrected by using very aggressive background writer settings. Some have solved it by disabling OS write delays. Some haven't found a solution and are waiting for 8.3; there have been some serious changes made to attempt to resolve this issue. Thanks - I put some additional information in replies to Scott, but mainly the performance of the web sites that are talking to postgres is the problem - people calling in, etc. I turned on slow query logging to see if I can find if it's particular queries or something else? Josh ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
