[PERFORM] Slow query, where am I going wrong?
Hello all,
I have been pulling my hair out over the last few days trying to get any useful performance out of the following
painfully slow query.
The query is JPA created, I've just cleaned the aliases to make it more
readable.
Using 'distinct' or 'group by' deliver about the same results, but 'distinct'
is marginally better.
Hardware is pretty low end (a test box), but is mostly dedicated to PostgreSQL.
The box spec and configuration is included at the end of this post - Some of the values have been changed just to see if
things get better.
Inserts have also become extremely slow. I was expecting a drop off when the
database grew out of memory, but not this much.
Am I really missing the target somewhere?
Any help and or suggestions will be very much appreciated.
Best regards,
Andy.
http://explain.depesz.com/s/cfb
select distinct tr.nr as tnr
, tr.time_end as tend
, c.id_board as cb
, c.id_board_mini as cbm
, ti.id_test_result as itr
from test_item ti
, test_result tr
, component c
, recipe_version rv
where ti.id_test_result = tr.id
and ti.id_component = c.id
and tr.id_recipe_version = rv.id
and (rv.id_recipe in ('6229bf04-ae38-11e1-a955-0021974df2b2'))
and tr.time_end cast('1970-01-01 01:00:00.000' as timestamp)
and tr.time_begin = cast('2012-10-22 00:00:14.383' as timestamp)
and ti.type = 'Component'
--group by tr.nr , tr.time_end , c.id_board , c.id_board_mini ,
ti.id_test_result
order by tr.time_end asc limit 1
--
-- Table: test_item
-- Table Size2119 MB
-- Indexes Size1845 MB
-- Live Tuples6606871
-- DROP TABLE test_item;
CREATE TABLE test_item
(
id character varying(36) NOT NULL,
angle double precision NOT NULL,
description character varying(1000),
designation character varying(128) NOT NULL,
failed boolean NOT NULL,
node integer NOT NULL,
nr integer NOT NULL,
nr_verified integer,
occurred timestamp without time zone NOT NULL,
ocr character varying(384),
pack_industry_name character varying(255),
passed boolean NOT NULL,
pin character varying(8),
pos_valid boolean NOT NULL,
pos_x double precision NOT NULL,
pos_y double precision NOT NULL,
pos_z double precision NOT NULL,
qref character varying(255) NOT NULL,
reference_id character varying(128) NOT NULL,
repaired boolean NOT NULL,
size_x double precision NOT NULL,
size_y double precision NOT NULL,
sort integer NOT NULL,
subtype character varying(20) NOT NULL,
type character varying(20) NOT NULL,
valid boolean NOT NULL,
version integer,
id_component character varying(36),
id_pack character varying(36),
id_test_item character varying(36),
id_test_result character varying(36) NOT NULL,
CONSTRAINT test_item_pkey PRIMARY KEY (id),
CONSTRAINT fk_test_item_component FOREIGN KEY (id_component)
REFERENCES component (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_test_item_pack FOREIGN KEY (id_pack)
REFERENCES pack (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_test_item_test_item FOREIGN KEY (id_test_item)
REFERENCES test_item (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_test_item_test_result FOREIGN KEY (id_test_result)
REFERENCES test_result (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
-- Index: ix_test_item_c
-- DROP INDEX ix_test_item_c;
CREATE INDEX ix_test_item_c
ON test_item
USING btree
(type COLLATE pg_catalog.default)
WHERE type::text = 'Component'::text;
-- Index: ix_test_item_id_component
-- DROP INDEX ix_test_item_id_component;
CREATE INDEX ix_test_item_id_component
ON test_item
USING btree
(id_component COLLATE pg_catalog.default);
-- Index: ix_test_item_id_test_item
-- DROP INDEX ix_test_item_id_test_item;
CREATE INDEX ix_test_item_id_test_item
ON test_item
USING btree
(id_test_item COLLATE pg_catalog.default);
-- Index: ix_test_item_id_test_result
-- DROP INDEX ix_test_item_id_test_result;
CREATE INDEX ix_test_item_id_test_result
ON test_item
USING btree
(id_test_result COLLATE pg_catalog.default);
-- Index: ix_test_item_type
-- DROP INDEX ix_test_item_type;
CREATE INDEX ix_test_item_type
ON test_item
USING btree
(type COLLATE pg_catalog.default);
-- Table: test_result
-- DROP TABLE test_result;
CREATE TABLE test_result
(
id character varying(36) NOT NULL,
description character varying(255) NOT NULL,
name character varying(100) NOT NULL,
nr integer NOT NULL,
state integer NOT NULL,
time_begin timestamp without time zone NOT NULL,
time_end timestamp without time zone NOT NULL,
version integer,
id_machine character varying(36) NOT NULL,
id_recipe_version character varying(36) NOT NULL,
CONSTRAINT test_result_pkey PRIMARY KEY (id),
CONSTRAINT fk_test_result_machine FOREIGN KEY (id_machine)
REFERENCES machine (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
Re: [PERFORM] Slow query, where am I going wrong?
Andy wrote: I have been pulling my hair out over the last few days trying to get any useful performance out of the following painfully slow query. The query is JPA created, I've just cleaned the aliases to make it more readable. Using 'distinct' or 'group by' deliver about the same results, but 'distinct' is marginally better. Hardware is pretty low end (a test box), but is mostly dedicated to PostgreSQL. The box spec and configuration is included at the end of this post - Some of the values have been changed just to see if things get better. Inserts have also become extremely slow. I was expecting a drop off when the database grew out of memory, but not this much. Am I really missing the target somewhere? Any help and or suggestions will be very much appreciated. Best regards, Andy. http://explain.depesz.com/s/cfb The estimate on the join between recipe_version and test_result is not good. Maybe things will improve if you increase the statistics on test_result.id_recipe_version. If that does not help, maybe the nested loop join that takes all your time can be sped up with the following index: CREATE INDEX any_name ON test_item (id_test_result, type); But I would not expect much improvement there. BTW, you seem to have an awful lot of indexes defined, some of which seem redundant. Yours, Laurenz Albe -- 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] Replaying 48 WAL files takes 80 minutes
On Mon, Oct 29, 2012 at 6:05 AM, Albe Laurenz laurenz.a...@wien.gv.at wrote: I am configuring streaming replication with hot standby with PostgreSQL 9.1.3 on RHEL 6 (kernel 2.6.32-220.el6.x86_64). PostgreSQL was compiled from source. It works fine, except that starting the standby took for ever: it took the system more than 80 minutes to replay 48 WAL files and connect to the primary. Can anybody think of an explanation why it takes that long? Jeff Janes wrote: Could the slow log files be replaying into randomly scattered pages which are not yet in RAM? Do you have sar or vmstat reports? The sar reports from the time in question tell me that I read about 350 MB/s and wrote less than 0.2 MB/s. The disks were fairly busy (around 90%). Jeff Trout wrote: If you do not have good random io performance log replay is nearly unbearable. also, what io scheduler are you using? if it is cfq change that to deadline or noop. that can make a huge difference. We use the noop scheduler. As I said, an identical system performed well in load tests. The sar reports give credit to Jeff Janes' theory. Why does WAL replay read much more than it writes? I thought that pretty much every block read during WAL replay would also get dirtied and hence written out. I wonder why the performance is good in the first few seconds. Why should exactly the pages that I need in the beginning happen to be in cache? And finally: are the numbers I observe (replay 48 files in 80 minutes) ok or is this terribly slow as it seems to me? Yours, Laurenz Albe -- 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] Request for help with slow query
Sean Woolcock wrote: I have a large (3 million row) table called tape that represents files, which I join to a small (100 row) table called filesystem that represents filesystems. I have a web interface that allows you to sort by a number of fields in the tape table and view the results 100 at a time (using LIMIT and OFFSET). The data only changes hourly and I do a vacuum analyze after all changes. An example query that's running slowly for me is: select tape.volser, tape.path, tape.scratched, tape.size, extract(epoch from tape.last_write_date) as last_write_date, extract(epoch from tape.last_access_date) as last_access_date from tape inner join filesystem on (tape.filesystem_id = filesystem.id) order by last_write_date desc limit 100 offset 100; On Postgres 8.1.17 this takes about 60 seconds. I would like it to be faster. Here's a depesz link with that output: http://explain.depesz.com/s/AUR I don't see anything obviously wrong there. At least the sequential scan on tape is necessary. Things I've tried: [...] 3. I ran the query against the same data in Postgres 9.1.6 rather than 8.1.17 using the same hardware and it was about 5 times faster (nice work, whoever did that!). Unfortunately upgrading is not an option, so this is more of an anecdote. I would think the query could go much faster in either environment with some optimization. Can you post EXPLAIN ANALYZE for the query on 9.1.6? Staying on 8.1 is not a good idea, but I guess you know that. Storage details (important for performance and corruption questions): Do you use a RAID controller? No. How many hard disks are connected to the system and what types are they? We use a single Hitachi HDT72102 SATA drive (250GB) 7200 RPM. How are your disks arranged for storage? Postgres lives on the same 100GB ext3 partition as the OS. I'd say that a query like this will always be disk bound. Getting faster storage should help. Yours, Laurenz Albe -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] out of memory
hi i have sql file (it's size are 1GB ) when i execute it then the String is 987098801 bytr too long for encoding conversion error occured . pls give me solution about i have XP 64-bit with 8 GB RAM shared_buffer 1GB check point = 34 with thanks mahavir
[PERFORM] PostgreSQL server failed to start
Hi, When i start my postgres. Iam getting this error. I had installed 8.4 and 9.1 It was working good yesterday but not now. service postgresql restart * Restarting PostgreSQL 8.4databaseserver * The PostgreSQL server failed to start. Please check the log output. If i see the log. it shows yesterday's log report. please give me suggestion Thanks for reply. -- Regards, Vignesh.T -- 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] Slow query, where am I going wrong?
Thanks very much Laurenz. I'll put your suggestions into motion right away and let you know the results. Albe Laurenz *EXTERN* wrote BTW, you seem to have an awful lot of indexes defined, some of which seem redundant. I am in the process of pruning unused/useless indexes on this database - So many of them will be dropped. Most of them are not in production and are past play things on this test system. The actual production test_item table gets about 140k inserts a day (avg). Having this test system slow, dirty and bloated is quite good as it helps us identify potential bottlenecks before they hit production. Partitioning is also on the cards, but solving this current issue is only going to help. Thanks again. Andy -- View this message in context: http://postgresql.1045698.n5.nabble.com/Slow-query-where-am-I-going-wrong-tp5730015p5730025.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- 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] Replaying 48 WAL files takes 80 minutes
On 30.10.2012 10:50, Albe Laurenz wrote: Why does WAL replay read much more than it writes? I thought that pretty much every block read during WAL replay would also get dirtied and hence written out. Not necessarily. If a block is modified and written out of the buffer cache before next checkpoint, the latest version of the block is already on disk. On replay, the redo routine reads the block, sees that the change was applied, and does nothing. I wonder why the performance is good in the first few seconds. Why should exactly the pages that I need in the beginning happen to be in cache? This is probably because of full_page_writes=on. When replay has a full page image of a block, it doesn't need to read the old contents from disk. It can just blindly write the image to disk. Writing a block to disk also puts that block in the OS cache, so this also efficiently warms the cache from the WAL. Hence in the beginning of replay, you just write a lot of full page images to the OS cache, which is fast, and you only start reading from disk after you've filled up the OS cache. If this theory is true, you should see a pattern in the I/O stats, where in the first seconds there is no I/O, but the CPU is 100% busy while it reads from WAL and writes out the pages to the OS cache. After the OS cache fills up with the dirty pages (up to dirty_ratio, on Linux), you will start to see a lot of writes. As the replay progresses, you will see more and more reads, as you start to get cache misses. - Heikki -- 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] out of memory
i have sql file (it's size are 1GB ) when i execute it then the String is 987098801 bytr too long for encoding conversion error occured . pls give me solution about You hit the upper limit of internal memory allocation limit in PostgreSQL. IMO, there's no way to avoid the error except you use client encoding identical to backend. Hackers: The particular limit seem to be set considering TOAST(from include/utils/memutils.h): * XXX This is deliberately chosen to correspond to the limiting size * of varlena objects under TOAST. See VARSIZE_4B() and related macros * in postgres.h. Many datatypes assume that any allocatable size can * be represented in a varlena header. IMO the SQL string size limit is totally different from TOAST. Shouldn't we have different limit for SQL string? (MAX_CONVERSION_GROWTH is different story, of course) -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] How to keep queries low latency as concurrency increases
Catalin Iacob wrote: Hardware: Virtual machine running on top of VMWare 4 cores, Intel(R) Xeon(R) CPU E5645 @ 2.40GHz 4GB of RAM You should carefully test transaction-based pools limited to around 8 DB connections. Experiment with different size limits. http://wiki.postgresql.org/wiki/Number_Of_Database_Connections Disk that is virtual enough that I have no idea what it is, I know that there's some big storage shared between multiple virtual machines. Filesystem is ext4 with default mount options. Can you change to noatime? pgbouncer 1.4.2 installed from Ubuntu's packages on the same machine as Postgres. Django connects via TCP/IP to pgbouncer (it does one connection and one transaction per request) and pgbouncer keeps connections open to Postgres via Unix socket. The Python client is self compiled psycopg2-2.4.5. Is there a good transaction-based connection pooler in Python? You're better off with a good pool built in to the client application than with a good pool running as a separate process between the client and the database, IMO. random_page_cost | 2 For fully cached databases I recommend random_page_cost = 1, and I always recommend cpu_tuple_cost = 0.03. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Seq scan on 10million record table.. why?
Hi all I have a problem with a data import procedure that involve the following query: select a,b,c,d from big_table b join data_sequences_table ds on b.key1 = ds.key1 and b.key2 = ds.key2 where ds.import_id=xx The big table has something like 10.000.000 records ore more (depending on the table, there are more than one of them). The data are uploaded in 20k record blocks, and the keys are written on data_sequences_table. The keys are composite (key1,key2), and every 5-10 sequences (depending on the size of the upload) the data_sequences_table records are deleted. I have indexes on both the key on the big table and the import_id on the sequence table. the query plan evualuate like this: Merge Join (cost=2604203.98..2774528.51 rows=129904 width=20) Merge Cond: big_table.key1)::numeric) = data_sequences_table.key1) AND ((( big_table.key2)::numeric) = data_sequences_table.key2)) - Sort (cost=2602495.47..2635975.81 rows=13392135 width=20) Sort Key: ((big_table.key1)::numeric), ((big_table.key2)::numeric) - Seq Scan on big_table (cost=0.00..467919.35 rows=13392135 width=20) - Sort (cost=1708.51..1709.48 rows=388 width=32) Sort Key: data_sequences_table.key1, data_sequences_table.key2 - Seq Scan on data_sequences_table (cost=0.00..1691.83 rows=388 width=32) Filter: (import_id = 1351592072::numeric) It executes in something like 80 seconds. The import procedure has more than 500 occurrences of this situation. :( Why is the big table evaluated with a seq scan? The result is 0 to 20.000 records (the query returns the records that already exists and should be updated, not inserted).. Can I do something to speed this up? -- Vincenzo. Imola Informatica Ai sensi del D.Lgs. 196/2003 si precisa che le informazioni contenute in questo messaggio sono riservate ed a uso esclusivo del destinatario. Pursuant to Legislative Decree No. 196/2003, you are hereby informed that this message contains confidential information intended only for the use of the addressee. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] High %SYS CPU usage
Hello there, I have PostgreSQL 8.3.18 server running on Centos 6.2 (2.6.32-220.7.1) with this specs: 2x CPU AMD Opteron 6282 128GB RAM Raid 10 (12HD 15k rpm 1GB cache) with data Raid 10 (4HD 15k rpm 1GB cache) with xlog Raid 1 (15k rpm 1GB cache shared with xlog) with system On this server I have only one database with 312GB of data. The database had run fine during 4 months, but from two months ago, during high work load periods, the server is collapsed by %sys type load. For example dstat -ar --socket --tcp during %sys load problem: http://pastebin.com/7zfDNvPh Reboot the server mitigates the problem during few days, but always reappear. Server not is swapping, don't have excessive I/O, don't have %IRQ load. I don't have any ideas... Thank you very much for your help. My sysctl and postgres.conf: sysclt -a: http://pastebin.com/EEVnNxsZ My Postgres.conf: max_connections = 500 # (change requires restart) unix_socket_directory = '/var/run/postgres' # (change requires restart) shared_buffers = 18GB # min 128kB or max_connections*16kB work_mem = 30MB # min 64kB maintenance_work_mem = 1GB # min 1MB max_fsm_pages = 8553600 # min max_fsm_relations*16, 6 bytes each max_fsm_relations = 409000 # min 100, ~70 bytes each fsync = on # turns forced synchronization on or off synchronous_commit = off # immediate fsync at commit wal_buffers = 8MB # min 32kB checkpoint_segments = 64 # in logfile segments, min 1, 16MB each checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0 archive_mode = on # allows archiving to be done archive_command = 'exit 0' effective_cache_size = 100GB constraint_exclusion = on default_text_search_config = 'pg_catalog.spanish' max_locks_per_transaction = 100 -- César Martín Pérez cmart...@gmail.com
Re: [PERFORM] Replaying 48 WAL files takes 80 minutes
On Tue, Oct 30, 2012 at 09:50:44AM +0100, Albe Laurenz wrote: On Mon, Oct 29, 2012 at 6:05 AM, Albe Laurenz laurenz.a...@wien.gv.at wrote: I am configuring streaming replication with hot standby with PostgreSQL 9.1.3 on RHEL 6 (kernel 2.6.32-220.el6.x86_64). PostgreSQL was compiled from source. It works fine, except that starting the standby took for ever: it took the system more than 80 minutes to replay 48 WAL files and connect to the primary. Can anybody think of an explanation why it takes that long? Jeff Janes wrote: Could the slow log files be replaying into randomly scattered pages which are not yet in RAM? Do you have sar or vmstat reports? The sar reports from the time in question tell me that I read about 350 MB/s and wrote less than 0.2 MB/s. The disks were fairly busy (around 90%). Jeff Trout wrote: If you do not have good random io performance log replay is nearly unbearable. also, what io scheduler are you using? if it is cfq change that to deadline or noop. that can make a huge difference. We use the noop scheduler. As I said, an identical system performed well in load tests. The sar reports give credit to Jeff Janes' theory. Why does WAL replay read much more than it writes? I thought that pretty much every block read during WAL replay would also get dirtied and hence written out. I wonder why the performance is good in the first few seconds. Why should exactly the pages that I need in the beginning happen to be in cache? And finally: are the numbers I observe (replay 48 files in 80 minutes) ok or is this terribly slow as it seems to me? Yours, Laurenz Albe Hi, The load tests probably had the important data already cached. Processing a WAL file would involve bringing all the data back into memory using a random I/O pattern. Perhaps priming the file cache using some sequential reads would allow the random I/O to hit memory instead of disk. I may be misremembering, but wasn't there an associated project/program that would parse the WAL files and generate cache priming reads? Regards, Ken -- 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] Replaying 48 WAL files takes 80 minutes
Heikki Linnakangas wrote: Why does WAL replay read much more than it writes? I thought that pretty much every block read during WAL replay would also get dirtied and hence written out. Not necessarily. If a block is modified and written out of the buffer cache before next checkpoint, the latest version of the block is already on disk. On replay, the redo routine reads the block, sees that the change was applied, and does nothing. True. Could that account for 1000 times more reads than writes? I wonder why the performance is good in the first few seconds. Why should exactly the pages that I need in the beginning happen to be in cache? This is probably because of full_page_writes=on. When replay has a full page image of a block, it doesn't need to read the old contents from disk. It can just blindly write the image to disk. Writing a block to disk also puts that block in the OS cache, so this also efficiently warms the cache from the WAL. Hence in the beginning of replay, you just write a lot of full page images to the OS cache, which is fast, and you only start reading from disk after you've filled up the OS cache. If this theory is true, you should see a pattern in the I/O stats, where in the first seconds there is no I/O, but the CPU is 100% busy while it reads from WAL and writes out the pages to the OS cache. After the OS cache fills up with the dirty pages (up to dirty_ratio, on Linux), you will start to see a lot of writes. As the replay progresses, you will see more and more reads, as you start to get cache misses. That makes sense to me. Unfortunately I don't have statistics in the required resolution to verify that. Thanks for the explanations. Yours, Laurenz Albe -- 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] Replaying 48 WAL files takes 80 minutes
k...@rice.edu wrote: If you do not have good random io performance log replay is nearly unbearable. also, what io scheduler are you using? if it is cfq change that to deadline or noop. that can make a huge difference. We use the noop scheduler. As I said, an identical system performed well in load tests. The load tests probably had the important data already cached. Processing a WAL file would involve bringing all the data back into memory using a random I/O pattern. The database is way too big (1 TB) to fit into cache. What are all the data that have to be brought back? Surely only the database blocks that are modified by the WAL, right? Yours, Laurenz Albe -- 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] Replaying 48 WAL files takes 80 minutes
On Tue, Oct 30, 2012 at 02:16:57PM +0100, Albe Laurenz wrote: k...@rice.edu wrote: If you do not have good random io performance log replay is nearly unbearable. also, what io scheduler are you using? if it is cfq change that to deadline or noop. that can make a huge difference. We use the noop scheduler. As I said, an identical system performed well in load tests. The load tests probably had the important data already cached. Processing a WAL file would involve bringing all the data back into memory using a random I/O pattern. The database is way too big (1 TB) to fit into cache. What are all the data that have to be brought back? Surely only the database blocks that are modified by the WAL, right? Yours, Laurenz Albe Right, it would only read the blocks that are modified. Regards, Ken -- 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] Seq scan on 10million record table.. why?
Hi Vincenzo, On Tue, 30 Oct 2012 13:15:10 +0100, Vincenzo Melandri vmelan...@imolinfo.it wrote: I have indexes on both the key on the big table and the import_id on the sequence table. Forgive my quick answer, but it might be that the data you are retrieving is scattered throughout the whole table, and the index scan does not kick in (as it is more expensive to perform lots of random fetches rather than a single scan). To be able to help you though, I'd need to deeply look at the ETL process - I am afraid you need to use a different approach, involving either queues or partitioning. Sorry for not being able to help you more in this case. Cheers, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it - www.2ndQuadrant.it -- 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] Seq scan on 10million record table.. why?
On 10/30/2012 07:15 AM, Vincenzo Melandri wrote: Merge Join (cost=2604203.98..2774528.51 rows=129904 width=20) Merge Cond: big_table.key1)::numeric) = data_sequences_table.key1) AND ((( big_table.key2)::numeric) = data_sequences_table.key2)) - Sort (cost=2602495.47..2635975.81 rows=13392135 width=20) Sort Key: ((big_table.key1)::numeric), ((big_table.key2)::numeric) - Seq Scan on big_table (cost=0.00..467919.35 rows=13392135 width=20) - Sort (cost=1708.51..1709.48 rows=388 width=32) Sort Key: data_sequences_table.key1, data_sequences_table.key2 - Seq Scan on data_sequences_table (cost=0.00..1691.83 rows=388 width=32) Filter: (import_id = 1351592072::numeric) As always, we need to see an EXPLAIN ANALYZE, not just an EXPLAIN. We also need to know the version of PostgreSQL and your server settings. Please refer to this: http://wiki.postgresql.org/wiki/Slow_Query_Questions I see a lot of NUMERIC conversions in there, which suggests you're using NUMERIC for your keys. That's not really recommended practice, but also suggests the possibility that all your types are not the same. So it would be very helpful to also see the actual CREATE TABLE, and CREATE INDEX statements for those tables. We can't help you with this limited information. Sorry. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- 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] How to keep queries low latency as concurrency increases
On 10/30/2012 06:55 AM, Kevin Grittner wrote: Is there a good transaction-based connection pooler in Python? You're better off with a good pool built in to the client application than with a good pool running as a separate process between the client and the database, IMO. Could you explain this a little more? My experience is almost always the exact opposite, especially in large clusters that may have dozens of servers all hitting the same database. A centralized pool has much less duplication and can serve from a smaller pool than having 12 servers each have 25 connections reserved in their own private pool or something. I mean... a pool is basically a proxy server. I don't have 12 individual proxy servers for 12 webservers. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- 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] Slow query, where am I going wrong?
A marginal improvement. http://explain.depesz.com/s/y63 I am going to normalize the table some more before partitioning. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Slow-query-where-am-I-going-wrong-tp5730015p5730059.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- 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] PostgreSQL server failed to start
On Tue, Oct 30, 2012 at 2:24 AM, vignesh vignes...@snovabits.net wrote: Hi, When i start my postgres. Iam getting this error. You may want to ask on the pgsql-general mailing list [1]. This list is just for Postgres performance questions. While, technically, failing to start outright could be considered a performance problem, the general list may be better able to help you. Also, please provide more details when you ask there (e.g., what operating system, how did you install Postgres, what changed between yesterday and now, etc.). [1]: http://archives.postgresql.org/pgsql-general/ -- 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] Slow query, where am I going wrong?
AndyG wrote: A marginal improvement. http://explain.depesz.com/s/y63 That's what I thought. Increasing the statistics for test_result.id_recipe_version had no effect? I am going to normalize the table some more before partitioning. How do you think that partitioning will help? Yours, Laurenz Albe -- 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] Seq scan on 10million record table.. why?
1) Make all types the same 2) If you are using some narrow type for big_table (say, int2) to save space, you can force narrowing conversion, e.g. b.key1=ds.key1::int2. Note that if ds.key1 has any values that don't fit into int2, you will have problems. And of course, use your type used instead of int2. Best regards, Vitalii Tymchyshyn This fixed my problem :) Thanks Vitalii! For the other suggestions made from Gabriele, unfortunately I can't make an accurate data-partitioning 'cause (obviously) it will be quite a big work and the customer finished the budget for this year, so unless I choose to work for free... ;) -- Vincenzo. -- 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] High %SYS CPU usage
Cesar, On this server I have only one database with 312GB of data. The database had run fine during 4 months, but from two months ago, during high work load periods, the server is collapsed by %sys type load. Hmmm. Have you updated Linux any time recently? I'm wondering if this is a PostgreSQL problem at all. It sounds like an OS issue. Can you give us the results of mpstat -P ALL 3 ? The dstat output doesn't tell me much. -- 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
Re: [PERFORM] How to keep queries low latency as concurrency increases
Shaun Thomas wrote: On 10/30/2012 06:55 AM, Kevin Grittner wrote: Is there a good transaction-based connection pooler in Python? You're better off with a good pool built in to the client application than with a good pool running as a separate process between the client and the database, IMO. Could you explain this a little more? My experience is almost always the exact opposite, especially in large clusters that may have dozens of servers all hitting the same database. A centralized pool has much less duplication and can serve from a smaller pool than having 12 servers each have 25 connections reserved in their own private pool or something. I mean... a pool is basically a proxy server. I don't have 12 individual proxy servers for 12 webservers. Sure, if you have multiple web servers and they are not routing their database requests through a common model layer, an external pooler would make sense. Most of the time I've dealt either with one web server or multiple servers routing requests at the transaction level to a single JVM which ran the logic of the transaction -- either of which is a good place to have a connection pool. A dozen different JVMs all making JDBC requests does kind of beg for an external layer to concentrate the requests; if it isn't something that's running the transaction layer, a connection pooler there would be good. -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] set-returning calls and overhead
On Thu, Jul 19, 2012 at 11:07 AM, Jon Nelson jnelson+pg...@jamponi.net wrote: Recently I found myself wondering what was taking a particular query so long. I immediately assumed it was a lack of I/O, because lack of I/O is a thorn in my side. Nope, the I/O was boring. CPU? Well, the process was using 100% of the CPU but the query itself was really very simple. I turned to ltrace (horribly imprecise, I know). ltrace told me this: % time seconds usecs/call calls function -- --- --- - 46.546.789433 69 97766 memcpy 28.164.1083241100 3732 strlen 14.452.107567 564 3732 malloc 9.161.336108 28 46877 memset 0.740.107935 28 3732 strcpy 0.730.107221 28 3732 free 0.160.023687 187 126 write 0.020.003587 28 126 __errno_location 0.020.003075 5952 read 0.010.001523 2952 memcmp -- --- --- - 100.00 14.588460159927 total and this: strlen(SRF multi-call context) strcpy(0xe01d40, SRF multi-call context) malloc(1024) memcpy(...) memset(...) ... memset(...) free(..) repeat. I was rather surprised to learn that (per-row): (1) memcpy of 64 bytes accounted for 46% of the time spent in library calls (2) the (other) costs of strlen, strcpy, malloc, and memset were so huge (in particular, strlen) What, if anything, can be done about this? It seems the overhead for setting up the memory context for the SRF is pretty high. I notice this overhead pretty much every time I use any of the array functions like unnest. Please help me to understand if I'm misinterpreting things here. [x86_64, Linux, PostgreSQL 9.1.4] A followup. Recently, I imported a bunch of data. The import ran in about 30 seconds. The data itself was represented in a way that made more sense - from a relational database perspective - as multiple tables. To accomplish this, I made use of string_to_array and unnest. The initial table creation and copy run in about 30 seconds, but then the creation of the new table (create table ... as select .. unnest(string_to_array())) took over 5 minutes. 10 times as long. What is it about the array functions (actually, all set-returning functions that I've tried) that causes them to be so expensive? The per-call overhead is enormous in some cases. PostgreSQL 9.1.5 on x86_64 (openSUSE 12.2 - but the effect has been observed across several platforms and major/minor releases of PostgreSQL). -- Jon -- Jon -- 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] How to keep queries low latency as concurrency increases
On Mon, Oct 29, 2012 at 5:11 PM, Catalin Iacob iacobcata...@gmail.com wrote: pgbouncer 1.4.2 installed from Ubuntu's packages on the same machine as Postgres. Django connects via TCP/IP to pgbouncer (it does one connection and one transaction per request) and pgbouncer keeps connections open to Postgres via Unix socket. Isn't pgbouncer single-threaded? If you hitting it with tiny queries as fast as possible from 20 connections, I would think that it would become the bottleneck. Cheers, Jeff -- 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] How to keep queries low latency as concurrency increases
Jeff / Catalin -- Jeff Janes wrote: On Mon, Oct 29, 2012 at 5:11 PM, Catalin Iacob iacobcata...@gmail.com wrote: pgbouncer 1.4.2 installed from Ubuntu's packages on the same machine as Postgres. Django connects via TCP/IP to pgbouncer (it does one connection and one transaction per request) and pgbouncer keeps connections open to Postgres via Unix socket. Isn't pgbouncer single-threaded? If you hitting it with tiny queries as fast as possible from 20 connections, I would think that it would become the bottleneck. Cheers, I'm sure pgbouncer has some threshold where it breaks down, but we have servers (postgres 8.4 and 9.1) with connections from runtime (fed via haproxy) to pgbouncer that routinely have tens of thousands of connections in but only 40-70 postgres connections to the postgres cluster itself. Mix of queries but most are simple. Typically a few thousand queries a second to the readonly boxes, about the same to a beefier read / write master. This is a slightly old pgbouncer at that ... used is a fairly basic mode. Greg Williamson -- 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] How to keep queries low latency as concurrency increases
On Tue, Oct 30, 2012 at 4:11 PM, Greg Williamson gwilliamso...@yahoo.com wrote: Jeff / Catalin -- Jeff Janes wrote: On Mon, Oct 29, 2012 at 5:11 PM, Catalin Iacob iacobcata...@gmail.com wrote: pgbouncer 1.4.2 installed from Ubuntu's packages on the same machine as Postgres. Django connects via TCP/IP to pgbouncer (it does one connection and one transaction per request) and pgbouncer keeps connections open to Postgres via Unix socket. Isn't pgbouncer single-threaded? If you hitting it with tiny queries as fast as possible from 20 connections, I would think that it would become the bottleneck. Cheers, I'm sure pgbouncer has some threshold where it breaks down, but we have servers (postgres 8.4 and 9.1) with connections from runtime (fed via haproxy) to pgbouncer that routinely have tens of thousands of connections in but only 40-70 postgres connections to the postgres cluster itself. Mix of queries but most are simple. Typically a few thousand queries a second to the readonly boxes, about the same to a beefier read / write master. This is a slightly old pgbouncer at that ... used is a fairly basic mode. I've used pgbouncer in two different environments now with thousands of connections and hundreds upon hundreds of queries per second and it has yet to be a bottleneck in either place as well. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
