Thread: Commercial postgresql
Hi .. Well after discussing how bugs are handled in PG, and the development culture in both MySQL and Postgresql, my boss agress that it may be a good aletenertive til MySQL 4.x (InnoDB), but ... He likes to know about others using Postgres and simple replication (we need it in at 24/7 system), to know how postgres performs, and how stable it is regarding its data and the backup feature etc. Are there some detailed busisness cases, that he/we can read to feel more assured that PG is a good choice ? /BL
Bo Lorentsen writes: > Are there some detailed busisness cases, that he/we can read to feel > more assured that PG is a good choice ? Start at <http://advocacy.postgresql.org/casestudies/>. There aren't many, but it's usually difficult to get customers to agree to this sort of thing. -- Peter Eisentraut peter_e@gmx.net
On Sat, 30 Aug 2003 16:18:14 +0200 (CEST), peter_e@gmx.net (Peter Eisentraut) wrote: >many, but it's usually difficult to get customers to agree to this sort of >thing. Why is that?
Oops! Jacob Hanson <jacobhanson@firsthealth.com> was seen spray-painting on a wall:
> On Sat, 30 Aug 2003 16:18:14 +0200 (CEST), peter_e@gmx.net (Peter
> Eisentraut) wrote:
>>many, but it's usually difficult to get customers to agree to this sort of
>>thing.
>
> Why is that?
Because:
1. What tools they use may be something they consider a "competitive
advantage."
2. If they go on a list like this, they become _obvious_ targets for
Oracle sales people and such.
Those reasons may occasionally be overcome, but that doesn't make them
irrelevant.
--
select 'cbbrowne' || '@' || 'cbbrowne.com';
http://cbbrowne.com/info/linux.html
"Popularity is the hallmark of mediocrity." --Niles Crane, "Frasier"
>>>>> "BL" == Bo Lorentsen <bl@netgroup.dk> writes: BL> Hi .. BL> He likes to know about others using Postgres and simple replication (we BL> need it in at 24/7 system), to know how postgres performs, and how BL> stable it is regarding its data and the backup feature etc. I use it in 24/7/365 system which is heavily written to and read from. The drawbacks I have are: 1) upgrade to major versions require dump/restore which is a significant amount of downtime for a large DB. 2) the need to run vacuum on tables to keep them from bloating too much. on my system which is very busy, sometimes running vacuum pushes the disk beyond its limits and slows the whole system to a crawl. 3) Index bloat is apparently a bigger problem than I thought. Yesterday I took the hit of reindexing all my tables. On a 2.7Gb database (those of you who notice my numbers keep changing... this is the correct value -- I read wrongly before) I just shaved 900Mb of 'dead' index pages. Unfortunately, my largest table of 92M rows takes about 43 minutes *per index* to reindex. I think 2 is probably amplified by 3. We'll see how the system holds up this week after the indexes have been optimized. BL> Are there some detailed busisness cases, that he/we can read to feel BL> more assured that PG is a good choice ? If you want commercial support, it is out there. There are at least two companies offering it. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
On Monday 01 September 2003 22:08, Vivek Khera wrote: > I use it in 24/7/365 system which is heavily written to and read > from. The drawbacks I have are: Nitpik: that should be 24/7/52, since there aren't 365 weeks in a year. > 1) upgrade to major versions require dump/restore which is a > significant amount of downtime for a large DB. I have harped on this at length. Maybe one day we'll get real upgrading. Search the archives for the discussions; there are many, and they are long threads. -- Lamar Owen Director of Information Technology Pisgah Astronomical Research Institute
Lamar Owen wrote: > Nitpik: that should be 24/7/52, since there aren't 365 weeks in a year. Oh, great. It's just that 7*52 = 364. That leaves us with a full day to idle and still honor the SLA, right? Sorry, couldn't resist :) cl.
On Tue, 2003-09-02 at 04:08, Vivek Khera wrote: > I use it in 24/7/365 system which is heavily written to and read > from. The drawbacks I have are: How depressing, may I ask that PG version you are using ? > 1) upgrade to major versions require dump/restore which is a > significant amount of downtime for a large DB. Ok, this is not a thing you do very often, and it would help is we got a "diff" (since last backup) pg_dump. As one could install the new DB in parallel with produktion, and then just apply the diff dump on the db swap. > 2) the need to run vacuum on tables to keep them from bloating too > much. on my system which is very busy, sometimes running vacuum > pushes the disk beyond its limits and slows the whole system to a > crawl. How often does this vacuum run, and how many delete/updates are there in between ? > 3) Index bloat is apparently a bigger problem than I thought. This does not sound too nice ! > If you want commercial support, it is out there. There are at least > two companies offering it. But you have not been unsing any of there services ? /BL
On 2 Sep 2003 at 11:49, Bo Lorentsen wrote: > > 3) Index bloat is apparently a bigger problem than I thought. > This does not sound too nice ! That's fixed in 7.4. Vacuum in 7.4 prevents index bloat as well. Couple with autovacuum daemon or scheduled vacuums, things (hopefully) will be lot better than earlier days.. Bye Shridhar -- QOTD: If you're looking for trouble, I can offer you a wide selection.
On Tue, 2003-09-02 at 11:55, Shridhar Daithankar wrote: > That's fixed in 7.4. Vacuum in 7.4 prevents index bloat as well. Couple with > autovacuum daemon or scheduled vacuums, things (hopefully) will be lot better > than earlier days.. Are there a list of things that will be done in 7.4, or better a development roadmap like the one the Mozilla folks are using ? These things are quite important when you have to convince your boss :-) /BL
On Sat, Aug 30, 2003 at 01:59:20PM +0200, Bo Lorentsen wrote:
> He likes to know about others using Postgres and simple replication (we
> need it in at 24/7 system), to know how postgres performs, and how
> stable it is regarding its data and the backup feature etc.
The replication system released last week (erserver: see gborg) is
the one that we originally used on our production systems. It held
up under load for 2 years. If you do a whois for .info or .org
domain names, you are hitting a PostgreSQL database. You can check
out the SLAs on those systems on the ICANN site, since they post all
the contracts.
A
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
> These things are quite important when you have to convince your boss :-) Convincing boss just say: "Caugh board or use postgresql". Meaning of this is that you can't compare comercical or free ware. Probably you want to use postgre because of its free, if this isn't case why you won use oracle ? "Bo Lorentsen" <bl@netgroup.dk> wrote in message news:1062498330.25825.7057.camel@localhost... > On Tue, 2003-09-02 at 11:55, Shridhar Daithankar wrote: > > > That's fixed in 7.4. Vacuum in 7.4 prevents index bloat as well. Couple with > > autovacuum daemon or scheduled vacuums, things (hopefully) will be lot better > > than earlier days.. > Are there a list of things that will be done in 7.4, or better a > development roadmap like the one the Mozilla folks are using ? > > These things are quite important when you have to convince your boss :-) > > /BL > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
On 2 Sep 2003 at 12:25, Bo Lorentsen wrote: > On Tue, 2003-09-02 at 11:55, Shridhar Daithankar wrote: > > > That's fixed in 7.4. Vacuum in 7.4 prevents index bloat as well. Couple with > > autovacuum daemon or scheduled vacuums, things (hopefully) will be lot better > > than earlier days.. > Are there a list of things that will be done in 7.4, or better a > development roadmap like the one the Mozilla folks are using ? Yes. Do a CVS checkout and check History and TODO files. There are fairly well maintained. Also check pending patches. http://developers.postgresql.org is the palce to start. 7.4 Documentation is another place you could check for new features.. I think it could have been better organised. A huge bugzilla like KDE uses could be a great thing. But for sure, there is nothing missing. You just need to get tuned to the way postgresql gets developed. Hang on on hackers for some time and you will pick up. > These things are quite important when you have to convince your boss :-) I know.:-) Bye Shridhar -- Hand, n.: A singular instrument worn at the end of a human arm and commonly thrust into somebody's pocket. -- Ambrose Bierce, "The Devil's Dictionary"
>>>>> "BL" == Bo Lorentsen <bl@netgroup.dk> writes: BL> On Tue, 2003-09-02 at 04:08, Vivek Khera wrote: >> I use it in 24/7/365 system which is heavily written to and read >> from. The drawbacks I have are: BL> How depressing, may I ask that PG version you are using ? Currently 7.2 in production, 7.4b2 in testing on the new system... >> 1) upgrade to major versions require dump/restore which is a >> significant amount of downtime for a large DB. BL> Ok, this is not a thing you do very often, and it would help is we got a BL> "diff" (since last backup) pg_dump. As one could install the new DB in BL> parallel with produktion, and then just apply the diff dump on the db BL> swap. Well, the thing is for a large DB which is very active, it still requires significant down-time, since you can't do this 'live'. >> 2) the need to run vacuum on tables to keep them from bloating too >> much. on my system which is very busy, sometimes running vacuum >> pushes the disk beyond its limits and slows the whole system to a >> crawl. BL> How often does this vacuum run, and how many delete/updates are there in BL> between ? There are *at least* 1 million inserts and 1 million updates per day. Every two weeks, I purge some old data, which means something like 25 to 30 million rows deleted across several tables (thank $DIETY for cascade delete). >> 3) Index bloat is apparently a bigger problem than I thought. BL> This does not sound too nice ! No, like I said, I shaved 900Mb of index table size this weekend by re-indexing. Unfortunately it meant I was partially down for about 45 minutes per index on my largest table, and about 15 per index on the second largest table, and 5 per index on the third largest, then about 90 seconds total for the rest of the tables ;-) >> If you want commercial support, it is out there. There are at least >> two companies offering it. BL> But you have not been unsing any of there services ? yes. but for a very specific type of support.
On 2 Sep 2003 at 9:36, Vivek Khera wrote: > >>>>> "BL" == Bo Lorentsen <bl@netgroup.dk> writes: > > BL> On Tue, 2003-09-02 at 04:08, Vivek Khera wrote: > >> 3) Index bloat is apparently a bigger problem than I thought. > BL> This does not sound too nice ! > > No, like I said, I shaved 900Mb of index table size this weekend by > re-indexing. Unfortunately it meant I was partially down for about 45 > minutes per index on my largest table, and about 15 per index on the > second largest table, and 5 per index on the third largest, then about > 90 seconds total for the rest of the tables ;-) Umm.. Since you have only 2.7GB of data, all inclusive, would it be real downtime if you reindex in a transaction, assuming the "downtime" was not due to crunch of IO bandwidth.. Just a thought.. Bye Shridhar -- Beauty: What's in your eye when you have a bee in your hand.
>>>>> "SD" == Shridhar Daithankar <shridhar_daithankar@persistent.co.in> writes: >> second largest table, and 5 per index on the third largest, then about >> 90 seconds total for the rest of the tables ;-) SD> Umm.. Since you have only 2.7GB of data, all inclusive, would it SD> be real downtime if you reindex in a transaction, assuming the SD> "downtime" was not due to crunch of IO bandwidth.. Reindexing a table takes an exclusive table lock. If I did it inside a transaction, wouldn't it still take that lock and block out all other access? I just did one index at a time, waited a few minutes did the next for my big tables, than just reindexed the others all in a row. Last time I did this must have been 9 or 10 months. One index went from 500000 pages to under 220000. Right now the system is screamingly fast. Perhaps I need to write an 'auto_reindex' script to notice when this is necessary and schedule one to run at the wee hours in the morning at the end of the week... -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
On 2 Sep 2003 at 10:19, Vivek Khera wrote: > >>>>> "SD" == Shridhar Daithankar <shridhar_daithankar@persistent.co.in> writes: > > >> second largest table, and 5 per index on the third largest, then about > >> 90 seconds total for the rest of the tables ;-) > > SD> Umm.. Since you have only 2.7GB of data, all inclusive, would it > SD> be real downtime if you reindex in a transaction, assuming the > SD> "downtime" was not due to crunch of IO bandwidth.. > > Reindexing a table takes an exclusive table lock. If I did it inside > a transaction, wouldn't it still take that lock and block out all > other access? Well, you donm't need to reindex as such. You can create a new index from scratch and drop the old one inside a transaction. That will be perfectly non-blocking I believe.. > I just did one index at a time, waited a few minutes did the next for > my big tables, than just reindexed the others all in a row. Last time > I did this must have been 9 or 10 months. One index went from 500000 > pages to under 220000. Right now the system is screamingly fast. > > Perhaps I need to write an 'auto_reindex' script to notice when this > is necessary and schedule one to run at the wee hours in the morning > at the end of the week... Once again, with 7.4, not needed anymore.. Bye Shridhar -- Anthony's Law of the Workshop: Any tool when dropped, will roll into the least accessible corner of the workshop.Corollary: On the way to the corner, any dropped tool will first strike your toes.
"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in> writes:
> On 2 Sep 2003 at 10:19, Vivek Khera wrote:
>> Reindexing a table takes an exclusive table lock.
> Well, you donm't need to reindex as such. You can create a new index from
> scratch and drop the old one inside a transaction.
> That will be perfectly non-blocking I believe..
It still blocks updates to the table while the new index is being built.
regards, tom lane
On Tue, Sep 02, 2003 at 12:25:31 +0200, Bo Lorentsen <bl@netgroup.dk> wrote: > Are there a list of things that will be done in 7.4, or better a > development roadmap like the one the Mozilla folks are using ? For a quick look see the current version of the release notes on the web at: http://developer.postgresql.org/docs/postgres/release.html This will be a little easier than checking out a copy from CVS. However, I think the HISTORY file has more detail in it.
Bruno Wolff III <bruno@wolff.to> writes: > Bo Lorentsen <bl@netgroup.dk> wrote: >> Are there a list of things that will be done in 7.4, or better a >> development roadmap like the one the Mozilla folks are using ? > For a quick look see the current version of the release notes on the web at: > http://developer.postgresql.org/docs/postgres/release.html > This will be a little easier than checking out a copy from CVS. However, > I think the HISTORY file has more detail in it. I don't believe Bruce has yet made any effort to update the SGML release-notes file for 7.4. Instead look at the CVS-tip HISTORY file: http://developer.postgresql.org/cvsweb.cgi/pgsql-server/HISTORY regards, tom lane
On Tue, 2003-09-02 at 22:21, Bruno Wolff III wrote: > This will be a little easier than checking out a copy from CVS. However, > I think the HISTORY file has more detail in it. I think you are right, maybe a cvsview utility would help, regarding this problem ? But a bugzilla tool, may also be nice :-) /BL
On Tue, 2003-09-02 at 23:32, Tom Lane wrote: > I don't believe Bruce has yet made any effort to update the SGML > release-notes file for 7.4. Instead look at the CVS-tip HISTORY file: > http://developer.postgresql.org/cvsweb.cgi/pgsql-server/HISTORY Ok thanks, this is very usefull ! /BL
On Tue, 2 Sep 2003, Bo Lorentsen wrote: > On Tue, 2003-09-02 at 22:21, Bruno Wolff III wrote: > > > This will be a little easier than checking out a copy from CVS. However, > > I think the HISTORY file has more detail in it. > I think you are right, maybe a cvsview utility would help, regarding > this problem ? But a bugzilla tool, may also be nice :-) > > /BL There is cvsweb available for the repository. I think it's linked from somewhere on http://developers.postgresql.org/ -- Nigel J. Andrews
On Tue, 2003-09-02 at 23:38, Nigel J. Andrews wrote: > There is cvsweb available for the repository. I think it's linked from > somewhere on http://developers.postgresql.org/ Thanks, found it, very usefull. /BL
>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes: TL> I don't believe Bruce has yet made any effort to update the SGML TL> release-notes file for 7.4. Instead look at the CVS-tip HISTORY file: Definitely not since the 7.4b2 INSTALL docs refer to it being version 7.3. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
>>>>> "SD" == Shridhar Daithankar <shridhar_daithankar@persistent.co.in> writes: >> Reindexing a table takes an exclusive table lock. If I did it inside >> a transaction, wouldn't it still take that lock and block out all >> other access? SD> Well, you donm't need to reindex as such. You can create a new index from SD> scratch and drop the old one inside a transaction. What about the primary keys? How do you create a new primary key without first dropping the old one? -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
After a long battle with technology,lowen@pari.edu (Lamar Owen), an earthling, wrote: > On Monday 01 September 2003 22:08, Vivek Khera wrote: >> I use it in 24/7/365 system which is heavily written to and read >> from. The drawbacks I have are: > > Nitpik: that should be 24/7/52, since there aren't 365 weeks in a year. Actually, it usually gets presented as 7x24x365, which has the same sort of insensible transposition as gets used in American dates. It's a buzzword, so how you imagine it _should_ get spelled isn't relevant :-). >> 1) upgrade to major versions require dump/restore which is a >> significant amount of downtime for a large DB. > > I have harped on this at length. Maybe one day we'll get real upgrading. > Search the archives for the discussions; there are many, and they are long > threads. Another strategy would involve replicating to a database running the new version; this could seriously diminish the need for downtime. You would have an instance of the old version and one of the new; the new copy might get set up for replication over some fairly substantial period of time, and once it got caught up, you'd switch over to the replicated copy, which might only take minutes. That's a _real_ dynamic approach, one that involves serious preparations. Wildly more involved than other approaches... -- let name="cbbrowne" and tld="ntlug.org" in name ^ "@" ^ tld;; http://cbbrowne.com/info/lisp.html Appendium to the Rules of the Evil Overlord #1: "I will not build excessively integrated security-and-HVAC systems. They may be Really Cool, but are far too vulnerable to breakdowns."
After takin a swig o' Arrakan spice grog, shridhar_daithankar@persistent.co.in ("Shridhar Daithankar") belched out...:
> On 2 Sep 2003 at 10:19, Vivek Khera wrote:
>
>> >>>>> "SD" == Shridhar Daithankar <shridhar_daithankar@persistent.co.in> writes:
>>
>> >> second largest table, and 5 per index on the third largest, then about
>> >> 90 seconds total for the rest of the tables ;-)
>>
>> SD> Umm.. Since you have only 2.7GB of data, all inclusive, would it
>> SD> be real downtime if you reindex in a transaction, assuming the
>> SD> "downtime" was not due to crunch of IO bandwidth..
>>
>> Reindexing a table takes an exclusive table lock. If I did it inside
>> a transaction, wouldn't it still take that lock and block out all
>> other access?
>
> Well, you donm't need to reindex as such. You can create a new index
> from scratch and drop the old one inside a transaction.
>
> That will be perfectly non-blocking I believe..
That won't block _reads_ on the table.
It will block writes to the table during the duration of the
transaction.
After all, if you insert a row into the table whilst the index
creation is taking place, there's a bit of a conflict:
-> For the system to remain consistent, that row's data either must
be added to the index-in-progress, or be deferred 'til later;
-> Since the index creation is inside the transaction, the insert
shouldn't be able to "see" the index yet.
The insert obviously can't affect an index that it can't yet see, so
what happens in practice is that PostgreSQL blocks the insert until
the index is complete.
>> Perhaps I need to write an 'auto_reindex' script to notice when
>> this is necessary and schedule one to run at the wee hours in the
>> morning at the end of the week...
> Once again, with 7.4, not needed anymore..
I haven't had a chance to verify the non-necessity on real data; I
would very much like to see more of our apps testing on 7.4 so as to
verify this, but there's enough work validating that it's all good on
7.3.4...
--
select 'cbbrowne' || '@' || 'acm.org';
http://www.ntlug.org/~cbbrowne/nonrdbms.html
When I die, I'd like to go peacefully in my sleep like my grandfather,
not screaming in terror like his passengers...