Обсуждение: Query caching (with 8.3)
Hello list,
i was wondering is there is some way of speeding up results of a query in postgresql 8.3 (upgrading is not an option
forthe moment).
Basically this is a small function querying information_schema for tables, columns satisfying specific criteria :
CREATE OR REPLACE FUNCTION xid_tables_cols(OUT table_name TEXT, OUT column_name TEXT, OUT data_type TEXT) RETURNS SETOF
record AS $$
DECLARE
BEGIN RETURN QUERY SELECT c.table_name::text,c.column_name::text,c.data_type::text FROM
information_schema.columnsc WHERE c.table_schema='public' AND c.table_name LIKE '%_tmp' AND c.data_type IN
('bytea','text') AND EXISTS (SELECT 1 FROM information_schema.columns c2 WHERE c2.table_schema='public' AND
c2.table_name=c.table_nameAND c2.column_name='xid'); RETURN;
END;
$$ LANGUAGE plpgsql STABLE;
The whole point is to be able to calculate row/columns sizes based on data type, by automatically finding all those
tables that apply to our specific technique/architecture (all tables whose name end in _tmp, and in addition who have
atleast one column named "xid"). This query is slow in 8.3. In 9.2 this is a
non-issue.
The above structure rarely changes, it changes only when we add new tables, ending in _tmp, and also having a column
"xid".
So the aim here is to speed up this query. I could materialize the result in some table, that i would refresh over
nightvia cron,
i was just wandering if there was some better way. I already made the function STABLE with no performance gain.
I was also wondering if i could trick postgresql to think that the output is always the same by making it IMMUTABLE,
but this also gave no performance gain.
So, is there anything i could do, besides overnight materialization?
Thanx.
--
Achilleas Mantzios
On 12/16/2013 11:58 AM, Achilleas Mantzios wrote: > postgresql 8.3 (upgrading is not an option for the moment) When your version is no longer getting security fixes and data corruption fixes, upgrading is not only an option but a duty. Please upgrade to at least 9.0 (8.4 will be EOL too soon for the effort to be worthwhile). -- Vik
On 17/12/2013 01:21, Vik Fearing wrote: > On 12/16/2013 11:58 AM, Achilleas Mantzios wrote: >> postgresql 8.3 (upgrading is not an option for the moment) > When your version is no longer getting security fixes and data > corruption fixes, upgrading is not only an option but a duty. > > Please upgrade to at least 9.0 (8.4 will be EOL too soon for the effort > to be worthwhile). > We run several versions of PostgreSQL (on shore and at sea) and if you search the archives you will see that we are one ofthe first to deploy PostgreSQL in a commercial marine environment (since 2001), and also to develop our own version of DBMirrorspecifically tailored for marine/satellite communications, to provide FK-dependency oriented, row grained, Conditional, Asynchronous, Lazy replication solution. Since you mention it, this is installed on about 90 vessels at sea, and if we assume 3000 EUR (tickets only) for a trained person to get on board and perform the upgrade, this amounts to 270,000 EUR. -- Achilleas Mantzios
On 12/17/2013 09:09 AM, Achilleas Mantzios wrote: > On 17/12/2013 01:21, Vik Fearing wrote: >> On 12/16/2013 11:58 AM, Achilleas Mantzios wrote: >>> postgresql 8.3 (upgrading is not an option for the moment) >> When your version is no longer getting security fixes and data >> corruption fixes, upgrading is not only an option but a duty. >> >> Please upgrade to at least 9.0 (8.4 will be EOL too soon for the effort >> to be worthwhile). >> > > We run several versions of PostgreSQL (on shore and at sea) and if you > search the archives you will see that we are one of the > first to deploy PostgreSQL in a commercial marine environment (since > 2001), and also to develop our own version of DBMirror specifically > tailored for marine/satellite communications, to provide FK-dependency > oriented, row grained, Conditional, Asynchronous, > Lazy replication solution. > Since you mention it, this is installed on about 90 vessels at sea, > and if we assume 3000 EUR (tickets only) for a > trained person to get on board and perform the upgrade, this amounts > to 270,000 EUR. That's nice. How much is the data worth? -- Vik
On 17/12/2013 12:32, Vik Fearing wrote: > On 12/17/2013 09:09 AM, Achilleas Mantzios wrote: >> On 17/12/2013 01:21, Vik Fearing wrote: >>> On 12/16/2013 11:58 AM, Achilleas Mantzios wrote: >>>> postgresql 8.3 (upgrading is not an option for the moment) >>> When your version is no longer getting security fixes and data >>> corruption fixes, upgrading is not only an option but a duty. >>> >>> Please upgrade to at least 9.0 (8.4 will be EOL too soon for the effort >>> to be worthwhile). >>> >> That's nice. How much is the data worth? I think we have deviated from the subject. For the interested reader, the path we took is to materialize the query and update it over-night. -- Achilleas Mantzios
On 18/12/13 03:54, Achilleas Mantzios wrote: > On 17/12/2013 12:32, Vik Fearing wrote: >> On 12/17/2013 09:09 AM, Achilleas Mantzios wrote: >>> On 17/12/2013 01:21, Vik Fearing wrote: >>>> On 12/16/2013 11:58 AM, Achilleas Mantzios wrote: >>>>> postgresql 8.3 (upgrading is not an option for the moment) >>>> When your version is no longer getting security fixes and data >>>> corruption fixes, upgrading is not only an option but a duty. >>>> >>>> Please upgrade to at least 9.0 (8.4 will be EOL too soon for the >>>> effort >>>> to be worthwhile). >>>> >>> That's nice. How much is the data worth? > > I think we have deviated from the subject. For the interested reader, > the path we took is to > materialize the query and update it over-night. > I think you are dodging the last question, which is a very important hint. It looks like you are totally ignoring the "security fixes and data corruption fixes" that have been added in subsequent versions of PostgreSQL. If your data is as important as it appears to be, then even a million Euro, is not too much to cough up. What would be the impact of of data corruption, or an attack by a criminal gang (or possible more likely, a government agency)? Note that sometimes data corruption can go undetected for a long time. Cheers, Gavin
On Mon, Dec 16, 2013 at 2:58 AM, Achilleas Mantzios
<achill@matrix.gatewaynet.com> wrote:
[...]
> BEGIN
> RETURN QUERY SELECT
> c.table_name::text,c.column_name::text,c.data_type::text FROM
> information_schema.columns c WHERE c.table_schema='public' AND c.table_name
> LIKE '%_tmp' AND c.data_type IN ('bytea','text') AND EXISTS (SELECT 1 FROM
> information_schema.columns c2 WHERE c2.table_schema='public' AND
> c2.table_name=c.table_name AND c2.column_name='xid');
[...]
> So the aim here is to speed up this query. I could materialize the result in
> some table, that i would refresh over night via cron,
> i was just wandering if there was some better way. I already made the
> function STABLE with no performance gain.
> I was also wondering if i could trick postgresql to think that the output is
> always the same by making it IMMUTABLE,
> but this also gave no performance gain.
>
> So, is there anything i could do, besides overnight materialization?
You can try to increase work_mem first, because if the returning data
set is big enough it might start working with your disk drive, that
might cause to significant slowdowns. Another thing is that, IIRC,
there were no plan caching for RETURN QUERY in PL/PgSQL, so try to
rewrite it like FOR ... LOOP RETURN NEXT ... END LOOP. IMHO, these are
the only non-quirky ways to improve things.
ps.
> Lazy replication solution.
> Since you mention it, this is installed on about 90 vessels at sea, and if
> we assume 3000 EUR (tickets only) for a
> trained person to get on board and perform the upgrade, this amounts to
> 270,000 EUR.
Wow, I just wonder how do you guys manage to support/maintain these DB
servers then?
--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA
http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@gmail.com
On 17/12/2013 22:26, Sergey Konoplev wrote: > You can try to increase work_mem first, because if the returning data set is big enough it might start working with yourdisk drive, that might cause to significant slowdowns. Another thing is that, > IIRC, there were no plan caching for RETURN QUERY in PL/PgSQL, so try to rewrite it like FOR ... LOOP RETURN NEXT ... ENDLOOP. IMHO, these are the only non-quirky ways to improve things. ps. Thanx, good to know that. >> Lazy replication solution. >> Since you mention it, this is installed on about 90 vessels at sea, and if >> we assume 3000 EUR (tickets only) for a >> trained person to get on board and perform the upgrade, this amounts to >> 270,000 EUR. > Wow, I just wonder how do you guys manage to support/maintain these DB > servers then? We periodically (daily) have partial backups of data which reside only on the vessel side. In other words, we back up only data which do not exist in the master site. In case of disaster we prepare a new vessel database, and then incrementally run the local restore created from the periodic local backup mentioned above. Taking into account that during the last 10 years, this has happened about 2-3 times, i'd say the cost is hard to justify. If/when we upgrade, it would be to improve performance, mainly, along the rest of obvious benefits, <joking> and not because some bad governmental agency would want to hack the vessels systems.... (we work for governments in the first place, they have much more civil and simple ways to get our data) </joking> Anyway, thing is, PostgreSQL 8.3 has been performing like a real beast, and i think it could be used as a case for advertising its long term stability, in a almost military environment (vibrations, etc...), and most importantly 99.99% unmanned. -- Achilleas Mantzios