Re: [PERFORM] Best OS for Postgres 8.2
currently ZFS is only available on Solaris, parts of it have been released under GPLv2, but it doesn't look like enough of it to be ported to Linux (enough was released for grub to be able to access it read-only, but not the full filesystem). there are also patent concerns that are preventing any porting to Linux. I don't know if anyone mentioned this in the thread already, but it looks like ZFS may be coming to MacOSX 10.5 http://news.worldofapple.com/archives/2006/12/17/zfs-file-system-makes-it-to -mac-os-x-leopard/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] estimating the need for VACUUM FULL and REINDEX
Guillaume Cottenceau gc 'at' mnc.ch writes: With that in mind, I've tried to estimate how much benefit would be brought by running VACUUM FULL, with the output of VACUUM VERBOSE. However, it seems that for example the removable rows reported by each VACUUM VERBOSE run is actually reused by VACUUM, so is not what I'm looking for. I've tried to better understand how autovacuum works (we use 7.4) to see if a similar mechanism could be used in 7.4 (e.g. run VACUUM ANALYZE often enough to not end up with a need to VACUUM FULL). The autovacuum daemon uses statistics collected thanks to stats_row_level. However, inside pg_stat_user_tables, the values n_tup_upd and n_tup_del seem to be reported from pg startup and never reset, whereas the information from previous VACUUM would be needed here, if I understand correctly. Is there anything that can be done from that point on with existing pg information, or I'd need e.g. to remember the values of my last VACUUM myself? Thanks. -- Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company Av. de la Gare 10, 1003 Lausanne, Switzerland - direct +41 21 317 50 36 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] estimating the need for VACUUM FULL and REINDEX
Guillaume Cottenceau wrote: Guillaume Cottenceau gc 'at' mnc.ch writes: With that in mind, I've tried to estimate how much benefit would be brought by running VACUUM FULL, with the output of VACUUM VERBOSE. However, it seems that for example the removable rows reported by each VACUUM VERBOSE run is actually reused by VACUUM, so is not what I'm looking for. I've tried to better understand how autovacuum works (we use 7.4) to see if a similar mechanism could be used in 7.4 (e.g. run VACUUM ANALYZE often enough to not end up with a need to VACUUM FULL). The autovacuum daemon uses statistics collected thanks to stats_row_level. However, inside pg_stat_user_tables, the values n_tup_upd and n_tup_del seem to be reported from pg startup and never reset, whereas the information from previous VACUUM would be needed here, if I understand correctly. Is there anything that can be done from that point on with existing pg information, or I'd need e.g. to remember the values of my last VACUUM myself? In 7.4 there was the pg_autovacuum daemon in contrib, wasn't there? No need to write one yourself. AFAIR what it did was precisely to remember the numbers from the last vacuum, which was cumbersome and not very effective (because they were lost on restart for example). Also, the new autovac has some features that the old one didn't have. Ability to set per-table configuration for example. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 1: 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] BUG #3270: limit 16 optimizer behaviour
This should have been asked on the performance list, not filed as a bug. I doubt anyone will have a complete answer to your question without EXPLAIN ANALYZE output from the query. Have you ANALYZE'd the tables recently? Poor statistics is one possible cause of the issue you are having. On Fri, May 11, 2007 at 14:07:57 +, Liviu Ionescu [EMAIL PROTECTED] wrote: The following bug has been logged online: Bug reference: 3270 Logged by: Liviu Ionescu Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.4 Operating system: Linux Description:limit 16 optimizer behaviour Details: I have a table of about 15Mrows, and a query like this: SELECT historianid,storagedate,slotdate,status,value FROM historiandata JOIN rtunodes ON(historiandata.historianid=rtunodes.nodeid) JOIN rtus ON(rtunodes.rtuid=rtus.nodeid) WHERE realmid IN (1119,1422,698,1428) AND historianid in (2996) ORDER BY storagedate desc LIMIT 10 if there are no records with the given historianid, if limit is = 16 the query is quite fast, otherwise it takes forever. my current fix was to always increase the limit to 16, but, although I know the optimizer behaviour depends on LIMIT, I still feel this looks like a bug; if the resultset has no records the value of the LIMIT should not matter. regards, Liviu Ionescu CREATE TABLE historiandata ( historianid int4 NOT NULL, status int2 NOT NULL DEFAULT 0, value float8, slotdate timestamptz NOT NULL, storagedate timestamptz NOT NULL DEFAULT now(), CONSTRAINT historiandata_pkey PRIMARY KEY (historianid, slotdate), CONSTRAINT historianid_fkey FOREIGN KEY (historianid) REFERENCES historians (nodeid) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT ) WITHOUT OIDS; ALTER TABLE historiandata OWNER TO tomcat; -- Index: historiandata_historianid_index -- DROP INDEX historiandata_historianid_index; CREATE INDEX historiandata_historianid_index ON historiandata USING btree (historianid); -- Index: historiandata_slotdate_index -- DROP INDEX historiandata_slotdate_index; CREATE INDEX historiandata_slotdate_index ON historiandata USING btree (slotdate); -- Index: historiandata_storagedate_index -- DROP INDEX historiandata_storagedate_index; CREATE INDEX historiandata_storagedate_index ON historiandata USING btree (storagedate); CREATE TABLE rtunodes ( nodeid int4 NOT NULL, rtuid int4 NOT NULL, no_publicnodeid int4, name varchar(64) NOT NULL, isinvalid bool NOT NULL DEFAULT false, nodetype varchar(16), CONSTRAINT rtunodes_pkey PRIMARY KEY (nodeid), CONSTRAINT nodeid_fkey FOREIGN KEY (nodeid) REFERENCES nodes (nodeid) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT rtuid_fkey FOREIGN KEY (rtuid) REFERENCES rtus (nodeid) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT ) WITHOUT OIDS; ALTER TABLE rtunodes OWNER TO tomcat; CREATE TABLE rtus ( nodeid int4 NOT NULL, passwd varchar(10) NOT NULL, xml text, no_nextpublicnodeid int4 NOT NULL DEFAULT 1, rtudriverid int2, realmid int4 NOT NULL, enablegetlogin bool NOT NULL DEFAULT false, enablegetconfig bool NOT NULL DEFAULT false, businfoxml text, uniqueid varchar(32) NOT NULL, no_publicrtuid int4, loginname varchar(10) NOT NULL, protocolversion varchar(8) DEFAULT '0.0'::character varying, isinvalid bool DEFAULT false, CONSTRAINT rtus_pkey PRIMARY KEY (nodeid), CONSTRAINT nodeid_fkey FOREIGN KEY (nodeid) REFERENCES nodes (nodeid) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT realmid_fkey FOREIGN KEY (realmid) REFERENCES realms (nodeid) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT rtudriverid_fkey FOREIGN KEY (rtudriverid) REFERENCES rtudrivers (rtudriverid) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT rtus_loginname_unique UNIQUE (loginname), CONSTRAINT rtus_uniqueid_unique UNIQUE (uniqueid) ) WITHOUT OIDS; ALTER TABLE rtus OWNER TO tomcat; -- Index: rtus_realmid_index -- DROP INDEX rtus_realmid_index; CREATE INDEX rtus_realmid_index ON rtus USING btree (realmid); -- Index: rtus_rtudriverid_index -- DROP INDEX rtus_rtudriverid_index; CREATE INDEX rtus_rtudriverid_index ON rtus USING btree (rtudriverid); ---(end of broadcast)--- TIP 1: 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Best OS for Postgres 8.2
On Tuesday 08 May 2007 23:31, Greg Smith wrote:
On Tue, 8 May 2007, Tom Lane wrote:
What Debian has done is set up an arrangement that lets you run two (or
more) different PG versions in parallel. Since that's amazingly helpful
during a major-PG-version upgrade, most of the other packagers are
scheming how to do something similar.
I alluded to that but it is worth going into more detail on for those not
familiar with this whole topic. I normally maintain multiple different PG
versions in parallel already, mostly using environment variables to switch
between them with some shell code. Debian has taken an approach where
commands like pg_ctl are wrapped in multi-version/cluster aware scripts,
so you can do things like restarting multiple installations more easily
than that.
My issue wasn't with the idea, it was with the implementation. When I
have my newbie hat on, it adds a layer of complexity that isn't needed for
simple installs.
I think I would disagree with this. The confusion comes from the fact that it
is different, not that it is more complex. For new users what seems to be
most confusing is getting from install to initdb to logging in... if you tell
them to use pg_ctlcluster rather than pg_ctl, it isn't more confusing, there
just following directions at that point anyway. If the upstream project were
to switch to debian's system, I think you'd end most of the confusion, make
it easier to run concurrent servers and simplify the upgrade process for
source installs, and give other package maintiners a way to achive what
debian has. Maybe in PG 9...
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
