Environment. PG v8. (Opteron 2CPU. Raid 5 disks 1TB. 12GB Ram)
Environment would be one master feeding 3 slaves. Similar configs.
New transactions coming into master. Cust Service Reps using that box.
Analysis being done on slave boxes.
Hi Folks,
I'm looking at PG vs MySql for a high volume site.
Few 10's of millions inserts per day.
Also some deletes (1% - say 250,000 per day).
Also updates (perhaps 10% say 2.5m per day)
Lots of indexes on master table.
When I test vacuum it seems very slow.
I understand I need to do vacuums on a regular basis.
Two reasons right...1. space and 2. better performance.
#1
I'm thinking with that much activity I should do it nightly and not
wait for Saturday nights since the #rows affected will be very large
by that time. Any thoughts?
#2
How does vacuum work in a replication environment.
If I implement replication so that users can get to 'copy' for their
analysis I presume the same overhead will apply to replicated box?
Does the same lock on table on slave box apply?
Will delay hit other tables not being vacuumed...I'd suspect it would.
E.g. I vacuum main table and it takes 15minutes. Are inserts to other
tables delayed by the 15minutes the vacuum takes on slave?
#3
15minute lock is a long time.
I don't want to tell the CSR's they will have to put customers on hold
each night for 15mins if customer updates are the basis of their call.
Any typical workarounds for such a problem?
I can imagine writing to a temp table and applying the changes after
the lock is released but tnose changes would notbe visiable to other
parts of the app...and we don't have access to the source code to
include looking at both tables.
#4
Does Mysql have a similar issue. Does it use something like 'vacuum'.
And if so does it suffer the same delays. Any thoughts?
#5
Speaking of replication...I see lots of technologies I can use.
Slony appears very active group. Which can be a good thing or mean its
still a work in progress!!
Any pro's con's or suggestions on the most popular.
Thanks in advance.
I'll loop back with results of what I find as I get around to actually
doing some tests.
David