Re: [PERFORM] Replication: Slony-I vs. Mammoth Replicator vs. ?
One more point for your list: Choose Slony if Replicator doesn't support your platform. :-) -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-301-869-4449 x806 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] Strange problems with more memory.
Hi all, I'm running postgres 7.3.4 on a quad Xeon 2.8 GHz with Mem: 1057824768 309108736 7487160320 12242944 256413696 Swap: 518053888 8630272 509423616 on Linux version 2.4.26-custom Data directory is mounted with noatime. Nothing else but one 11GB database is running on this machine. When the database was created, I changed the following defaults : shared_buffers = 24415 sort_mem = 5120 vacuum_mem = 10240 commit_delay = 5000 commit_siblings = 100 These settings worked fine, but were not optimal, I thought, and processing stuff on this database was a bit slow. The machine is not nearly used to it's capacity, and I realized that disk IO is what's slowing me down. So I decided to give postgres more shared memory and much more sort memory, as it does a lot of group by's and order by's during the nightly processing. These were the new settings I tried : shared_buffers = 61035 sort_mem = 97657 I thought because it's only one process that runs queries exclusively at night, I should be able to set the sort_mem this high without worrying about running out of memory. It seems I was mistaking, as I started getting these kind of errors in dmesg : VM: killing process postmaster __alloc_pages: 0-order allocation failed (gfp=0x1d2/0) __alloc_pages: 0-order allocation failed (gfp=0x1d2/0) VM: killing process postmaster and I kept on getting these postgres errors : ERROR: Index is not a btree I systematically reduced the shared buffers back down to 24415, and this kept on happening. As soon as I reduced sort_mem back to under 1,the problem stopped. But the database is just as slow as before. (By slow I mean not as fast as it should be on such a powerful machine compared to much worse machines running the same processes) What can I do to make this database run faster on this machine. Can anyone suggest how I would go about speeding up this database. I need to prepare a database three times the size of this one, running the same processes, and I don't know what improvements I can do on hardware to make this possible. On the current machine I can easily get another 1GB or 2GB of memory, but will that help at all? Without going into the details of exactly the queries that run on this machine, what would be needed to make postgres run very fast on this machine? Please help. Kind Regards Stefan ---(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] Strange problems with more memory.
Tom Lane mentioned : = Turn off = memory overallocation in your kernel to get more stable behavior when = pushing the limits of available memory. I think this will already help a lot. Thanks!! = If your concern is with a single nightly process, then that quad Xeon is = doing squat for you, because only one of the processors will be working. = See if you can divide up the processing into several jobs that can run = in parallel. (Of course, if the real problem is that you are disk I/O = bound, nothing will help except better disk hardware. Way too many = people think they should buy a super-fast CPU and attach it to = consumer-grade IDE disks. For database work you're usually better off = spending your money on good disks...) Got 3 1 rpm SCSI raid5 on here. I doubt I will get much better than that without losing both arms and legs... I think I'll try and even out the disk IO a bit and get 4 processes running in parallel. At least I can move forward again. Thanks again! Kind Regards Stefan ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Index type
Ilia, If I create btree index on all columns (A,B,C..), here is what explain analyze gives me: - Index Scan using all_ind on test2 (cost=0.00..4.51 rows=1 width=24) (actual ti me=0.000..0.000 rows=5 loops=1) Index Cond: ((a = '2004-07-20 23:50:50'::timestamp without time zone) AND (a = '2004-07-21 23:50:50'::timestamp without time zone) AND (b = '2004-07-20 23 :50:50'::timestamp without time zone) AND (b = '2004-07-21 : 23:50:50'::timestamp without time zone) AND (c = '2004-07-20 23:50:50'::timestamp without time zone ) AND (c = '2004-07-21 23:50:50'::timestamp without time zone)) Looks good to me. It's a fully indexed search, which it should be with BETWEEN. The only thing you need to ask yourself is whether or not you've selected the columns in the most selective order (e.g. most selective column first). -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Timestamp-based indexing
Josh Berkus [EMAIL PROTECTED] writes: monitor=# explain analyze select * from eventtable where timestamp CURRENT_TIMESTAMP - INTERVAL '10 minutes'; Hmmm. What verison of PostgreSQL are you running? I seem to remember an issue in one version with selecting comparisons against now(). I'm also wondering about the exact datatype of the timestamp column. If it's timestamp without timezone, then the above is a cross-datatype comparison (timestamp vs timestamptz) and hence not indexable before 8.0. This could be fixed easily by using the right current-time function, viz LOCALTIMESTAMP not CURRENT_TIMESTAMP. (Consistency has obviously never been a high priority with the SQL committee :-(.) Less easily but possibly better in the long run, change the column type to timestamp with time zone. IMHO, columns representing definable real-world time instants should always be timestamptz, because the other way leaves you open to serious confusion about what the time value really means. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
