Re: [PERFORM] : Create table taking time
We had performed VACUUM FULL and ANALYZE on the whole database. Yes, the CPU is ticking at 99-100% when i see the top command. But, we have 8 CPUs with 6 cores each. Thanks VB On Thu, Sep 29, 2011 at 12:44 AM, Scott Marlowe scott.marl...@gmail.comwrote: On Wed, Sep 28, 2011 at 11:06 AM, Venkat Balaji venkat.bal...@verse.in wrote: Hello Everyone, I am back with an issue (likely). I am trying to create a table in our production database, and is taking 5 seconds. We have executed VACUUM FULL and yet to run ANALYZE. Can i expect the CREATE TABLE to be faster after ANALYZE finishes ? Or is there anything serious ? Please share your thoughts. Are your system tables heavily bloated?
[PERFORM] Select se bloquea
Hola!! Tengo un serio inconveniente, estoy trabajando con postgresql 8.2 y tomcat 5.5.20 en un equipo con Centos 5.3 Y se me presenta un problema con una consulta, si la ejecuto le toma alrededores de 2.6 segundos la ejecución. pero en ocaciones se queda pegada esa consulta y luego para cada usuario (desde la interfaz web) que ejecute la misma consulta se queda en espera pero la primera nunca termina, luego consume casi todo el pool de conexiones y el equipo en general se coloca muy lento al hacer un top encuentro que el uso de memoria es cerca al 7% y el de CPU del 3%, pero aún así las consultas siguen pegadas. lo que no sé es si postgresql en la version 8.2 bloquee consultas (SELECT) Agradezco la colaboración prestada. Numael Vacca Duran
Re: [PERFORM] : Create table taking time
Venkat Balaji venkat.bal...@verse.in wrote: We had performed VACUUM FULL and ANALYZE on the whole database. Since you don't mention REINDEX, it seems likely that you've bloated your indexes (potentially including the indexes on system tables). That could lead to the symptoms you describe. Yes, the CPU is ticking at 99-100% when i see the top command. But, we have 8 CPUs with 6 cores each. If you've pegged 48 CPUs, it might be interesting to get a profile of where the time is being spent. Are you able to run oprofile or something similar? -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Select se bloquea
2011/9/29 Numael Vacca Duran numae...@hotmail.com: Hola!! Tengo un serio inconveniente, estoy trabajando con postgresql 8.2 y tomcat 5.5.20 en un equipo con Centos 5.3 1- 8.2 es viejito 2- Hacen falta muchos más datos. Las consultas en sí, un EXPLAIN y EXLAIN ANALYZE de las consultas, vendrían bien para saber de qué hablás 3- También detalles del hardare, 4- y de la estructura y tamaño de tu base de datos -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] the number of child tables --table partitioning
Hey, Is there a suggested number of child tables for table partitioning, I ran a stress test on a master table (with 800 thousand rows), trying to create 500,000 child tables for it, each child table has 2 indexes and 3 constraints (Primary key and foreign key). I wrote a script to do it: after 17 hours, only 7600 child tables are created. The script is still running, I can see that one child tables is created about every minute. The CPU Usage is 100%. The query speed is really slow now (I set constraint_exclusion=on). This stress test is for the partition plan I'm going to make, since we don't want to add another Field just for partitioning. So is there something I did wrong? Or postgres cannot handle too many Child tables? That way I need to come up with a new partition plan. The system is 32-bit Linux, dual core, 4G memory. Postgres version is 8.1.21. Thanks, John
Re: [PERFORM] the number of child tables --table partitioning
Jian Shi j...@unitrends.com wrote: [moving the last sentence to the top] The system is 32-bit Linux, dual core, 4G memory. Postgres version is 8.1.21. Version 8.1 is out of support and doesn't perform nearly as well as modern versions. http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy The system you're talking about is the same as what I bought as a home computer four years ago. You don't mention your disk system, but that doesn't sound like server-class hardware to me. Is there a suggested number of child tables for table partitioning, Generally, don't go over about 100 partitions per table. I ran a stress test on a master table (with 800 thousand rows), trying to create 500,000 child tables for it, each child table has 2 indexes and 3 constraints (Primary key and foreign key). That probably at least 5 disk files per table, to say nothing of the system table entries and catalog caching. Some file systems really bog down with millions of disk files in a single subdirectory. That is never going to work on the hardware you cite, and is a very, very, very bad design on any hardware. This stress test is for the partition plan I'm going to make, since we don't want to add another Field just for partitioning. Why not? -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] : Create table taking time
On Wed, Sep 28, 2011 at 12:06 PM, Venkat Balaji venkat.bal...@verse.in wrote: Hello Everyone, I am back with an issue (likely). I am trying to create a table in our production database, and is taking 5 seconds. We have executed VACUUM FULL and yet to run ANALYZE. Can i expect the CREATE TABLE to be faster after ANALYZE finishes ? Or is there anything serious ? just ruling out something obvious -- this is vanilla create table, not CREATE TABLE AS SELECT...? also, what's i/o wait -- are you sure your not i/o bound and waiting on transaction commit? merlin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] the number of child tables --table partitioning
Hi, On 30 September 2011 01:08, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Is there a suggested number of child tables for table partitioning, Generally, don't go over about 100 partitions per table. Having 365 partitions per table is fine... -- Ondrej Ivanic (ondrej.iva...@gmail.com) -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Shortcutting too-large offsets?
All, Here's a case which it seems like we ought to be able to optimize for: datamart-# ORDER BY txn_timestamp DESC datamart-# LIMIT 200 datamart-# OFFSET 6000; QUERY PLAN --- Limit (cost=560529.82..560529.82 rows=1 width=145) (actual time=22419.760..22419.760 rows=0 loops=1) - Sort (cost=560516.17..560529.82 rows=5459 width=145) (actual time=22418.076..22419.144 rows=5828 loops=1) Sort Key: lh.txn_timestamp Sort Method: quicksort Memory: 1744kB - Nested Loop Left Join (cost=0.00..560177.32 rows=5459 width=145) (actual time=4216.898..22398.658 rows=5828 loops=1) - Nested Loop Left Join (cost=0.00..88186.22 rows=5459 width=135) (actual time=4216.747..19250.891 rows=5828 loops=1) - Nested Loop Left Join (cost=0.00..86657.26 rows=5459 width=124) (actual time=4216.723..19206.461 rows=5828 loops=1) ... it seems like, if we get as far as the sort and the executors knows that there are less rows than the final offset, it ought to be able to skip the final sort. Is there some non-obvious reason which would make this kind of optimization difficult? Doesn't the executor know at that point how many rows it has? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] array_except -- Find elements that are not common to both arrays
I recently had need of an array_except function but couldn't find
any good/existing examples. Based off the neat array_intersect
function at
http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Intersection_of_arrays,
I put together an array_except version to return the array elements
that are not found in both arrays.
Can anyone think of a faster version of this function? Maybe in C?
The generate_series example takes about 3.5s on the dev db I'm testing
on, which isn't too bad (for my needs at least).
create or replace function array_except(anyarray,anyarray) returns
anyarray as $$
select array_agg(elements)
from(
(select unnest($1) except select unnest($2))
union
(select unnest($2) except select unnest($1))
) as r (elements)
$$ language sql strict immutable;
select
array_except('{this,is,a,test}'::text[],'{also,part,of,a,test,run}'::text[]);
select array_to_relation(arr)
from array_except( (select array_agg(n) from
generate_series(1,100,1) as n),
(select array_agg(n) from
generate_series(5,105,1) as n)
) as arr;
I'm testing on 9.0.4
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] postgres constraint triggers
On Sep 27, 2011, at 6:37 PM, Craig Ringer wrote: On 09/27/2011 12:54 PM, Ben Chobot wrote: My memory is fuzzy but as I recall, a possible downside to using deferred constraints was increased memory usage That's right. PostgreSQL doesn't currently support spilling of pending constraint information to disk; it has to keep it in RAM, and with sufficiently huge deferred updates/inserts/deletes it's possible for the backend to run out of RAM to use. though I cannot see how at the moment. A list of which triggers to run, and on which tuples, must be maintained until those triggers are fired. That list has to be kept somewhere. Well when you put it like that, it's so obvious. :) -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] array_except -- Find elements that are not common to both arrays
On Thursday, September 29, 2011, bricklen brick...@gmail.com wrote:
I recently had need of an array_except function but couldn't find
any good/existing examples. Based off the neat array_intersect
function at
http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Intersection_of_arrays
,
I put together an array_except version to return the array elements
that are not found in both arrays.
Can anyone think of a faster version of this function? Maybe in C?
The generate_series example takes about 3.5s on the dev db I'm testing
on, which isn't too bad (for my needs at least).
create or replace function array_except(anyarray,anyarray) returns
anyarray as $$
select array_agg(elements)
from(
(select unnest($1) except select unnest($2))
union
(select unnest($2) except select unnest($1))
) as r (elements)
$$ language sql strict immutable;
select
array_except('{this,is,a,test}'::text[],'{also,part,of,a,test,run}'::text[]);
select array_to_relation(arr)
from array_except( (select array_agg(n) fro
generate_series(1,100,1) as n),
(select array_agg(n) from
generate_series(5,105,1) as n)
) as arr;
I'm testing on 9.0.4
r
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
*) Prefer union all to union
*) prefer array constructor to array_agg when not grouping.
*) perhaps consider not reusing 'except' name with different semantic
meaning
Well done
merlin (on phone in bed)
Re: [PERFORM] array_except -- Find elements that are not common to both arrays
On Thu, Sep 29, 2011 at 8:08 PM, Merlin Moncure mmonc...@gmail.com wrote:
*) Prefer union all to union
*) prefer array constructor to array_agg when not grouping.
*) perhaps consider not reusing 'except' name with different semantic
meaning
Well done
merlin (on phone in bed)
Hi Merlin,
Thanks for the tips. I have implemented suggestion 1 2, and that has
shaved about 1/2 of a second off of the generate_series example below
(3.52s - 3.48s)
Do you have a suggestion for a better name? I considered array_unique,
array_distinct etc, but those don't really describe what is being
returned IMO. Something that conjures up the return elements that are
not common to both arrays would be nice.
create or replace function array_except(anyarray,anyarray) returns
anyarray as $$
select ARRAY(
(
select r.*
from(
(select unnest($1) except select unnest($2))
union all
(select unnest($2) except select unnest($1))
) as r (elements)
))
$$ language sql strict immutable;
select array_except('{this,is,a,test}'::text[],'{also,part,of,a,test}'::text[]);
select array_to_relation(arr)
from array_except( (select array_agg(n) from
generate_series(1,100,1) as n) , (select array_agg(n) from
generate_series(5,105,1) as n) ) as arr;
More improvement suggestions gladly accepted!
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] : Create table taking time
I did not calculate the IO behavior of the server. What i noticed for the logs is that, the checkpoints are occurring too frequently each checkpoint is taking up to minimum 80 - 200+ seconds to complete write and checkpoint sync is taking 80 - 200+ seconds to sync, which is i believe IO intensive. Thanks VB On Thu, Sep 29, 2011 at 10:22 PM, Merlin Moncure mmonc...@gmail.com wrote: On Wed, Sep 28, 2011 at 12:06 PM, Venkat Balaji venkat.bal...@verse.in wrote: Hello Everyone, I am back with an issue (likely). I am trying to create a table in our production database, and is taking 5 seconds. We have executed VACUUM FULL and yet to run ANALYZE. Can i expect the CREATE TABLE to be faster after ANALYZE finishes ? Or is there anything serious ? just ruling out something obvious -- this is vanilla create table, not CREATE TABLE AS SELECT...? also, what's i/o wait -- are you sure your not i/o bound and waiting on transaction commit? merlin
