[PERFORM] Comparative performance
I'm converting a relatively small database (2 MB) from MySQL to PostgreSQL. It is used to generate web pages using PHP. Although the actual website runs under Linux, the development is done under XP. I've completed most of the data conversion and rewrite of the PHP scripts, so now I'm comparing relative performance. It appears that PostgreSQL is two to three times slower than MySQL. For example, some pages that have some 30,000 characters (when saved as HTML) take 1 to 1 1/2 seconds with MySQL but 3 to 4 seconds with PostgreSQL. I had read that the former was generally faster than the latter, particularly for simple web applications but I was hoping that Postgres' performance would not be that noticeably slower. I'm trying to determine if the difference can be attributed to anything that I've done or missed. I've run VACUUM ANALYZE on the two main tables and I'm looking at the results of EXPLAIN on the query that drives the retrieval of probably 80% of the data for the pages in question. Before I post the EXPLAIN and the table schema I'd appreciate confirmation that this list is the appropriate forum. I'm a relative newcomer to PostgreSQL (but not to relational databases), so I'm not sure if this belongs in the novice or general lists. Joe ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Comparative performance
Magnus Hagander wrote: That actually depends a lot on *how* you use it. I've seen pg-on-windows deployments that come within a few percent of the linux performance. I've also seen those that are absolutely horrible compared. One sure way to kill the performance is to do a lot of small connections. Using persistent connection is even more important on Windows than it is on Unix. It could easily explain a difference like this. I just tried using pg_pconnect() and I didn't notice any significant improvement. What bothers me most is that with Postgres I tend to see jerky behavior on almost every page: the upper 1/2 or 2/3 of the page is displayed first and you can see a blank bottom (or you can see a half-filled completion bar). With MySQL each page is generally displayed in one swoop. Joe ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Comparative performance
PFC wrote: From my experience, the postgres libraries in PHP are a piece of crap, and add a lot of overhead even from small queries. For instance, a simple query like "SELECT * FROM table WHERE primary_key_id=1234" can take the following time, on my laptop, with data in the filesystem cache of course : EXPLAIN ANALYZE<0.1 ms python + psycopg 20.1 ms (damn fast) php + mysql0.3 ms php + postgres1-2 ms (damn slow) As a Trac user I was considering moving to Python, so it's good to know that, but the rewrite is a longer term project. So, if your pages are designed in The PHP Way (ie. a large number of small queries), I might suggest using a language with a decent postgres interface (python, among others), or rewriting your bunches of small queries as Stored Procedures or Joins, which will provide large speedups. Doing >50 queries on a page is always a bad idea, but it's tolerable in php-mysql, not in php-postgres. The pages do use a number of queries to collect all the data for display but nowhere near 50. I'd say it's probably less than a dozen. As an aside, one of my tasks (before the conversion) was to analyze the queries and see where they could be tweaked for performance, but with MySQL that was never a top priority. The schema is fairly simple having two main tables: topic and entry (sort of like account and transaction in an accounting scenario). There are two additional tables that perhaps could be merged into the entry table (and that would reduce the number of queries) but I do not want to make major changes to the schema (and the app) for the PostgreSQL conversion. Joe ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Comparative performance
Gavin Sherry wrote: Please post the table definitions, queries and explain analyze results so we can tell you why the performance is poor. I did try to post that last night but apparently my reply didn't make it to the list. Here it is again: Matthew Nuzum wrote: > This is the right list. Post detail and I'm sure you'll get some suggestions. Thanks, Matthew (and Chris and Gavin). The main table used in the query is defined as follows: CREATE TABLE entry ( entry_id serial PRIMARY KEY, title VARCHAR(128) NOT NULL, subtitle VARCHAR(128), subject_type SMALLINT, subject_id INTEGER REFERENCES topic, actor_type SMALLINT, actor_id INTEGER REFERENCES topic, actor VARCHAR(64), actor_role VARCHAR(64), rel_entry_id INTEGER, rel_entry VARCHAR(64), description VARCHAR(255), quote text, url VARCHAR(255), entry_date CHAR(10), created DATE NOT NULL DEFAULT CURRENT_DATE, updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP) WITHOUT OIDS; CREATE INDEX entry_actor_id ON entry (actor_id); CREATE INDEX entry_subject_id ON entry (subject_id); It has 3422 rows at this time. The query for one of the pages is the following: SELECT entry_id, subject_type AS type, subject_type, subject_id, actor_type, actor_id, actor, actor_role, rel_entry_id, rel_entry, title, subtitle, description, url, quote AS main_quote, NULL AS rel_quote, substring(entry_date from 8) AS dom, substring(entry_date from 1) AS date_ymd, substring(entry_date from 1 for 7) AS date_ym, substring(entry_date from 1 for 4) AS date_y, created, updated FROM entry WHERE subject_id = 1079 UNION SELECT entry_id, actor_type AS type, subject_type, subject_id, actor_type, actor_id, actor, actor_role, rel_entry_id, rel_entry, title, subtitle, description, url, quote AS main_quote, NULL AS rel_quote, substring(entry_date from 8) AS dom, substring(entry_date from 1) AS date_ymd, substring(entry_date from 1 for 7) AS date_ym, substring(entry_date from 1 for 4) AS date_y, created, updated FROM entry WHERE actor_id = 1079 ORDER BY type, title, subtitle; The output of EXPLAIN ANALYZE is: Sort (cost=158.98..159.14 rows=62 width=568) (actual time=16.000..16.000 rows=59 loops=1) Sort Key: "type", title, subtitle -> Unique (cost=153.57..157.14 rows=62 width=568) (actual time=16.000..16.000 rows=59 loops=1) -> Sort (cost=153.57..153.73 rows=62 width=568) (actual time=16.000..16.000 rows=59 loops=1) Sort Key: entry_id, "type", subject_type, subject_id, actor_type, actor_id, actor, actor_role, rel_entry_id, rel_entry, title, subtitle, description, url, main_quote, rel_quote, dom, date_ymd, date_ym, date_y, created, updated -> Append (cost=0.00..151.73 rows=62 width=568) (actual time=0.000..16.000 rows=59 loops=1) -> Subquery Scan "*SELECT* 1" (cost=0.00..17.21 rows=4 width=568) (actual time=0.000..0.000 rows=3 loops=1) -> Index Scan using entry_subject_id on entry (cost=0.00..17.17 rows=4 width=568) (actual time=0.000..0.000 rows=3 loops=1) Index Cond: (subject_id = 1079) -> Subquery Scan "*SELECT* 2" (cost=0.00..134.52 rows=58 width=568) (actual time=0.000..16.000 rows=56 loops=1) -> Seq Scan on entry (cost=0.00..133.94 rows=58 width=568) (actual time=0.000..16.000 rows=56 loops=1) Filter: (actor_id = 1079) Total runtime: 16.000 ms (13 rows) What I don't quite understand is why it's doing a sequential scan on actor_id instead of using the entry_actor_id index. Note that actor_id has 928 non-null values (27%), whereas subject_id has 3089 non-null values (90%). Note that the entry_date column was originally a MySQL date but it had partial dates, i.e., some days and months are set to zero. Eventually I hope to define a PostgreSQL datatype for it and to simplify the substring retrievals. However, I don't think the extra computational time should affect the overall runtime significantly. Gavin, I'm using PostgreSQL 8.0.3, Apache 1.3.28, PHP 4.3.4, MySQL 4.0.16 and I'm comparing both databases on XP (on a Pentium 4, 1.6 GHz, 256 MB RAM). Thanks for any feedback. Joe ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Comparative performance
Andreas Pflug wrote: Hm, if you only have 4 tables, why do you need 12 queries? To reduce queries, join them in the query; no need to merge them physically. If you have only two main tables, I'd bet you only need 1-2 queries for the whole page. There are more than four tables and the queries are not functionally overlapping. As an example, allow me to refer to the page www.freedomcircle.com/topic.php/Economists. The top row of navigation buttons (Life, Liberty, etc.) is created from a query of the 'topic' table. It could've been hard-coded as a PHP array, but with less flexibility. The alphabetical links are from a SELECT DISTINCT substring from topic. It could've been generated by a PHP for loop (originally implemented that way) but again with less flexibility. The listing of economists is another SELECT from topic. The subheadings (Articles, Books) come from a SELECT of an entry_type table --which currently has 70 rows-- and is read into a PHP array since we don't know what headings will be used in a given page. The detail of the entries comes from that query that I posted earlier, but there are three additional queries that are used for specialized entry types (relationships between topics --e.g., Prof. Williams teaches at George Mason, events, and multi-author or multi-subject articles and books). And there's yet another table for the specific book information. Once the data is retrieved it's sorted internally with PHP, at the heading level, before display. Maybe there is some way to merge all the queries (some already fairly complex) that fetch the data for the entries box but I believe it would be a monstrosity with over 100 lines of SQL. Thanks, Joe ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Comparative performance
PFC wrote: Even though this query isn't that optimized, it's still only 16 milliseconds. Why does it take this long for PHP to get the results ? Can you try pg_query'ing this exact same query, FROM PHP, and timing it with getmicrotime() ? Thanks, that's what I was looking for. It's microtime(), BTW. It'll take me some time to instrument it, but that way I can pinpoint what is really slow. You can even do an EXPLAIN ANALYZE from pg_query and display the results in your webpage, to check how long the query takes on the server. You can also try it on a Linux box. My current host only supports MySQL. I contacted hub.org to see if they could assist in this transition but I haven't heard back. This smells like a TCP communication problem. I'm puzzled by that remark. How much does TCP get into the picture in a local Windows client/server environment? Joe ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Comparative performance
PFC wrote:
Even though this query isn't that optimized, it's still only 16
milliseconds.
Why does it take this long for PHP to get the results ?
Can you try pg_query'ing this exact same query, FROM PHP, and timing
it with getmicrotime() ?
That query took about 27 msec in actual PHP execution time. It turns out the
real culprit is the following query, which interestingly enough retrieves zero
rows in the case of the Economists page that I've been using for testing, yet it
uses up about 1370 msec in actual runtime:
SELECT topic_id1, topic_id2, topic_name, categ_id, list_name, t.title, url,
page_type, rel_type, inverse_id, r.description AS rel_descrip, r.created, r.updated
FROM relationship r, topic t, entry_type e
WHERE ((topic_id1 = topic_id AND topic_id2 = 1252) OR (topic_id2 = topic_id and
topic_id1 = 1252)) AND rel_type = type_id AND e.class_id = 2
ORDER BY rel_type, list_name;
The EXPLAIN ANALYZE output, after I ran VACUUM ANALYZE on the three tables, is:
Sort (cost=4035.55..4035.56 rows=1 width=131) (actual time=2110.000..2110.000
rows=0 loops=1)
Sort Key: r.rel_type, t.list_name
-> Nested Loop (cost=36.06..4035.54 rows=1 width=131) (actual
time=2110.000..2110.000 rows=0 loops=1)
Join Filter: ((("inner".topic_id1 = "outer".topic_id) AND
("inner".topic_id2 = 1252)) OR (("inner".topic_id2 = "outer".topic_id) AND
("inner".topic_id1 = 1252)))
-> Seq Scan on topic t (cost=0.00..38.34 rows=1234 width=90) (actual
time=0.000..15.000 rows=1234 loops=1)
-> Materialize (cost=36.06..37.13 rows=107 width=45) (actual
time=0.000..0.509 rows=466 loops=1234)
-> Merge Join (cost=30.31..35.96 rows=107 width=45) (actual
time=0.000..0.000 rows=466 loops=1)
Merge Cond: ("outer".type_id = "inner".rel_type)
-> Index Scan using entry_type_pkey on entry_type e (cost
=0.00..3.94 rows=16 width=4) (actual time=0.000..0.000 rows=15 loops=1)
Filter: (class_id = 2)
-> Sort (cost=30.31..31.48 rows=466 width=43) (actual
time=0.000..0.000 rows=466 loops=1)
Sort Key: r.rel_type
-> Seq Scan on relationship r (cost=0.00..9.66
rows=466 width=43) (actual time=0.000..0.000 rows=466 loops=1)
Total runtime: 2110.000 ms
(14 rows)
The tables are as follows:
CREATE TABLE entry_type (
type_id SMALLINT NOT NULL PRIMARY KEY,
title VARCHAR(32) NOT NULL,
rel_title VARCHAR(32),
class_id SMALLINT NOT NULL DEFAULT 1,
inverse_id SMALLINT,
updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP)
WITHOUT OIDS;
CREATE TABLE topic (
topic_id serial PRIMARY KEY,
topic_name VARCHAR(48) NOT NULL UNIQUE,
categ_id SMALLINT NOT NULL,
parent_entity INTEGER,
parent_concept INTEGER,
crossref_id INTEGER,
list_name VARCHAR(80) NOT NULL,
title VARCHAR(80),
description VARCHAR(255),
url VARCHAR(64),
page_type SMALLINT NOT NULL,
dark_ind BOOLEAN NOT NULL DEFAULT FALSE,
ad_code INTEGER,
created DATE NOT NULL DEFAULT CURRENT_DATE,
updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP)
WITHOUT OIDS;
CREATE TABLE relationship (
topic_id1 INTEGER NOT NULL REFERENCES topic,
topic_id2 INTEGER NOT NULL REFERENCES topic,
rel_type INTEGER NOT NULL,
description VARCHAR(255),
created DATE NOT NULL DEFAULT CURRENT_DATE,
updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (topic_id1, topic_id2, rel_type))
WITHOUT OIDS;
I'm thinking that perhaps I need to set up another index with topic_id2 first
and topic_id1 second. In addition, an index on entry_type.class_id may improve
things. Another possibility would be to rewrite the query as a UNION.
Of course, this doesn't explain how MySQL manages to execute the query in about
9 msec. The only minor differences in the schema are: entry_type.title and
rel_title are char(32) in MySQL, entry_type.class_id is a tinyint, and
topic.categ_id, page_type and dark_ind are also tinyints. MySQL also doesn't
have the REFERENCES.
A couple of interesting side notes from my testing. First is that pg_connect()
took about 39 msec but mysql_connect() took only 4 msec, however, pg_pconnect()
took 0.14 msec while mysql_pconnect() took 0.99 msec (all tests were repeated
five times and the quoted results are averages). Second, is that PostgreSQL's
performance appears to be much more consistent in certain queries. For example,
the query that retrieves the list of subtopics (the names and description of
economists), took 17 msec in PG, with a low of 15 (three times) and a high of
21, whereas MySQL took 60 msec on average but had a low of 22 and a high of 102
msec.
Joe
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [PERFORM] Comparative performance
Hi Jim, Jim C. Nasby wrote: Also, just because no one else has mentioned it, remember that it's very easy to get MySQL into a mode where you have no data integrity. If that's the case it's going to be faster than PostgreSQL (though I'm not sure how much that affects the performance of SELECTs). Yes indeed. When I added the REFERENCES to the schema and reran the conversion scripts, aside from having to reorder the table creation and loading (they used to be in alphabetical order), I also found a few referential integrity errors in the MySQL data. Joe ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Comparative performance
PFC wrote:
- if you use a version before 8, type mismatch will prevent use of the
indexes.
I'm using 8.0.3, but the type mismatch between relationship.rel_type and
entry_type.type_id was unintended. The current databases use SMALLINT for both.
The PostgreSQL schema was derived from an export script stored in Subversion,
apparently before the column datatypes were changed.
CREATE INDEX'es ON
entry_type( class_id )
relationship( topic_id1, rel_type, topic_id2 )which becomes your
new PRIMARY KEY
relationship( topic_id2, rel_type, topic_id1 )
Creating the second relationship index was sufficient to modify the query plan
to cut down runtime to zero:
Sort (cost=75.94..75.95 rows=2 width=381) (actual time=0.000..0.000 rows=0
loops=1)
Sort Key: r.rel_type, t.list_name
-> Nested Loop (cost=16.00..75.93 rows=2 width=381) (actual
time=0.000..0.000 rows=0 loops=1)
Join Filter: ((("outer".topic_id1 = "inner".topic_id) AND
("outer".topic_id2 = 1252)) OR (("outer".topic_id2 = "inner".topic_id) AND
("outer".topic_id1 = 1252)))
-> Nested Loop (cost=16.00..35.11 rows=1 width=169) (actual
time=0.000..0.000 rows=0 loops=1)
Join Filter: ("inner".rel_type = "outer".type_id)
-> Seq Scan on entry_type e (cost=0.00..18.75 rows=4 width=4)
(actual time=0.000..0.000 rows=15 loops=1)
Filter: (class_id = 2)
-> Materialize (cost=16.00..16.04 rows=4 width=167) (actual
time=0.000..0.000 rows=0 loops=15)
-> Seq Scan on relationship r (cost=0.00..16.00 rows=4
width=167) (actual time=0.000..0.000 rows=0 loops=1)
Filter: ((topic_id2 = 1252) OR (topic_id1 = 1252))
-> Seq Scan on topic t (cost=0.00..30.94 rows=494 width=216) (never
executed)
Total runtime: 0.000 ms
(13 rows)
The overall execution time for the Economists page for PostgreSQL is within 4%
of the MySQL time, so for the time being I'll leave the query in its current form.
Thanks for your help.
Joe
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Comparative performance
Jim C. Nasby wrote: Make sure these indexes exist if you'll be updating or inserting into entry: CREATE INDEX topic__subject_id ON topic(subject_id); CREATE INDEX topic__actor_id ON topic(actor_id); Actually, topic's primary key is topic_id. Also, the fact that subject and actor both point to topic along with subject_type and actor_type make me suspect that your design is de-normalized. Of course there's no way to know without more info. Yes, the design is denormalized. The reason is that a book or article is usually by a single author (an "actor" topic) and it will be listed under one main topic (a "subject" topic). There's a topic_entry table where additional actors and subjects can be added. It's somewhat ironic because I used to teach and/or preach normalization and the "goodness" of a 3NF+ design (also about having the database do aggregation and sorting as you mentioned in your other email). FWIW, I usually use timestamptz for both created and updated fields. IIRC 'created' ended up as a DATE because MySQL 4 has a restriction about a single TIMESTAMP column per table taking the default value of current_timestamp. Joe ---(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] Simple query, 10 million records...MySQL ten times faster
Is there a reason you are not using postgis. The R tree indexes are designed for exactly this type of query and should be able to do it very quickly. Hope that helps, Joe > I have this table: > > CREATE TABLE test_zip_assoc ( > id serial NOT NULL, > f_id integer DEFAULT 0 NOT NULL, > lat_radians numeric(6,5) DEFAULT 0.0 NOT NULL, > long_radians numeric(6,5) DEFAULT 0.0 NOT NULL > ); > CREATE INDEX lat_radians ON test_zip_assoc USING btree (lat_radians); > CREATE INDEX long_radians ON test_zip_assoc USING btree > (long_radians); > > > > It's basically a table that associates some foreign_key (for an event, > for instance) with a particular location using longitude and > latitude. I'm basically doing a simple proximity search. I have > populated the database with *10 million* records. I then test > performance by picking 50 zip codes at random and finding the records > within 50 miles with a query like this: > > SELECT id > FROM test_zip_assoc > WHERE > lat_radians > 0.69014816041 > AND lat_radians < 0.71538026567 > AND long_radians > -1.35446228028 > AND long_radians < -1.32923017502 > > > On my development server (dual proc/dual core Opteron 2.8 Ghz with 4GB > ram) this query averages 1.5 seconds each time it runs after a brief > warmup period. In PostGreSQL it averages about 15 seconds. > > Both of those times are too slow. I need the query to run in under a > second with as many as a billion records. I don't know if this is > possible but I'm really hoping someone can help me restructure my > indexes (multicolumn?, multiple indexes with a 'where' clause?) so > that I can get this running as fast as possible. > > If I need to consider some non-database data structure in RAM I will > do that too. Any help or tips would be greatly appreciated. I'm > willing to go to greath lengths to test this if someone can make a > good suggestion that sounds like it has a reasonable chance of > improving the speed of this search. There's an extensive thread on my > efforts already here: > > http://phpbuilder.com/board/showthread.php?t=10331619&page=10 > > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PERFORM] Beowulf Cluster & Postgresql?
Hi all, I was wondering if part or all of Postgres would be able to take advantage of a beowulf cluster to increase performance? If not then why not, and if so then how would/could it benefit from being on a cluster? Thanks for the enlightenment in advance. -Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] [sfpug] DATA directory on network attached storage
Aditya wrote: We have not, AFAICT, had any problems with the traffic over NFS as far as reliability -- I'm sure there is a performance penalty, but the reliability and scalability gains more than offset that. My experience agrees with yours. However we did find one gotcha -- see the thread starting here for details: http://archives.postgresql.org/pgsql-hackers/2004-12/msg00479.php In a nutshell, be careful when using an nfs mounted data directory combined with an init script that creates a new data dir when it doesn't find one. FWIW, if I were to do this anew, I would probably opt for iSCSI over GigE with a NetApp. Any particular reason? Our NetApp technical rep advised nfs over iSCSI, IIRC because of performance. Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] [sfpug] DATA directory on network attached storage
Aditya wrote: On Mon, Apr 11, 2005 at 10:59:51AM -0700, Joe Conway wrote: Any particular reason? Our NetApp technical rep advised nfs over iSCSI, IIRC because of performance. I would mount the Netapp volume(s) as a block level device on my server using iSCSI (vs. a file-based device like NFS) so that filesystem parameters could be more finely tuned and one could really make use of jumbo frames over GigE. Actually, we're using jumbo frames over GigE with nfs too. I'm not sure I understand why NFS would perform better than iSCSI -- in any case, some large Oracle dbs at my current job are moving to iSCSI on Netapp and in that environment both Oracle and Netapp advise iSCSI (probably because Oracle uses the block-level device directly), so I suspend the difference in performance is minimal. We also have Oracle DBs via nfs mounted Netapp, again per the local guru's advice. It might be one of those things that is still being debated even within Netapp's ranks (or maybe our info is dated - worth a check). Thanks, Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Opinions on Raid
We have been running Postgres on a 2U server with 2 disks configured in raid 1 for the os and logs and 4 disks configured in raid 10 for the data. I have since been told raid 5 would have been a better option given our usage of Dell equipment and the way they handle raid 10. I have just a few general questions about raid with respect to Postgres: [1] What is the performance penalty of software raid over hardware raid? Is it truly significant? We will be working with 100s of GB to 1-2 TB of data eventually. [2] How do people on this list monitor their hardware raid? Thus far we have used Dell and the only way to easily monitor disk status is to use their openmanage application. Do other controllers offer easier means of monitoring individual disks in a raid configuration? It seems one advantage software raid has is the ease of monitoring. I truly appreciate any assistance or input. As an additional question, does anyone have any strong recommendations for vendors that offer both consulting/training and support? We are currently speaking with Command Prompt, EnterpriseDB, and Greenplum but I am certainly open to hearing any other recommendations. Thanks, Joe ---(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] Opinions on Raid
Really appreciate all of the valuable input. The current server has the Perc4ei controller. The impression I am taking from the responses is that we may be okay with software raid, especially if raid 1 and 10 are what we intend to use. I think we can collect enough information from the archives of this list to help make decisions for the new machine(s), was just very interested in hearing feedback on software vs. hardware raid. We will likely be using the 2.6.18 kernel. Thanks for everyone's input, Joe -Original Message- From: Scott Marlowe [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 27, 2007 12:56 PM To: Joe Uhl Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Opinions on Raid On Tue, 2007-02-27 at 07:12, Joe Uhl wrote: > We have been running Postgres on a 2U server with 2 disks configured in > raid 1 for the os and logs and 4 disks configured in raid 10 for the > data. I have since been told raid 5 would have been a better option > given our usage of Dell equipment and the way they handle raid 10. Some controllers do no layer RAID effectively. Generally speaking, the cheaper the controller, the worse it's gonna perform. Also, some controllers are optimized more for RAID 5 than RAID 1 or 0. Which controller does your Dell have, btw? > I > have just a few general questions about raid with respect to Postgres: > > [1] What is the performance penalty of software raid over hardware raid? > Is it truly significant? We will be working with 100s of GB to 1-2 TB > of data eventually. For a mostly read system, the performance is generally pretty good. Older linux kernels ran layered RAID pretty slowly. I.e. RAID 1+0 was no faster than RAID 1. The best performance software RAID I found in older linux kernels (2.2, 2.4) was plain old RAID-1. RAID-5 was good at reading, but slow at writing. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Determining server load from client
(forgot to send to list)
Dan Harris wrote:
architecture of the server hardware. It would be very nice if I could
check the load of the server at certain intervals to throttle the
number of concurrent queries and mitigate load problems when other
processes might be already inducing a significant load.
I have seen some other nice back-end things exposed through PG
functions ( e.g. database size on disk ) and wondered if there was
anything applicable to this. Even if it can't return the load average
proper, is there anything else in the pg_* tables that might give me a
clue how "busy" the server is for a period of time?
I have installed munin (http://munin.projects.linpro.no/) on a few
systems. This lets you look at graphs of system resources/load etc. I
have also added python scripts which do sample queries to let me know if
performance/index size is changing dramatically. I have attached an
example script.
Hope that helps,
Joe
#! /usr/bin/python
import psycopg
import sys
def fixName(name):
return name[:19]
if len(sys.argv) > 1 and sys.argv[1] == "config":
print """graph_title Postgresql Index Sizes
graph_vlabel Mb"""
con = psycopg.connect("host=xxx user=xxx dbname=xxx password=xxx")
cur = con.cursor()
cur.execute("select relname, relpages from pg_class where relowner > 10 and relkind='i' and relpages > 256 order by reltuples desc;")
results = cur.fetchall()
for name, pages in results:
print "%s.label %s" % (fixName(name), name)
else:
con = psycopg.connect("host=xxx user=xxx dbname=xxx password=xxx")
cur = con.cursor()
cur.execute("select relname, relpages from pg_class where relowner > 10 and relkind='i' and relpages > 256 order by reltuples desc;")
results = cur.fetchall()
for name, pages in results:
print "%s.value %.2f" % (name[:19], pages*8.0/1024.0)
---(end of broadcast)---
TIP 6: explain analyze is your friend
[PERFORM] Getting Slow
About six months ago, our normally fast postgres server started having performance issues. Queries that should have been instant were taking up to 20 seconds to complete (like selects on the primary key of a table). Running the same query 4 times in a row would yield dramatically different results... 1.001 seconds, 5 seconds, 22 seconds, 0.01 seconds, to complete. At the time we upgraded the hardware and the performance problems went away. But I did not feel like we had solved the underlying problem. Now, six months later, the same thing is happening... and I'm kind of glad because now, I'd like to find out what the real issue is. I'm just starting to diagnose it so I don't know a lot yet, but what I do know, I'll share with you here in the hopes of starting off on the right track. I've already described the main symptom. Here are some other random observations: - The server log shows frequent "archived transaction log file" entries. Usually once every 10 minutes or so, but sometimes 2 or 3 per minute. - The server box seems otherwise to be responsive. CPU sits at about 90% idle. - When queries are especially slow, the server shows a big spike in read/write activity. - This morning I did a VACUUM ANALYZE. It seemed to help for 30 minutes or so, but then it was back to being slowish. I'd hate to schedule these because it feels more like a band-aid. For a long time we've been doing just fine with autovacuum, so why start scheduling vacuums now? Here's info about our configuration. Any advise/pointers would be much appreciated. Thanks! Computer: Mac Pro Dual Core Intel Operating System: Mac OS 10.4.7 Client Memory: 4GB RAM Data Drives: 3 drives in a software RAID (internal) Log/Backup Drive: 1 (the startup disk, internal) Postgres Version: 8.1.4 Data Size: 5.1 GB # of Tables: 60 Size of Tables: Most are under 100,000 records. A few are in the millions. Largest is 7058497. Average Number of Simultaneous Client Connections: 250 max_connections = 500 shared_buffers = 1 work_mem = 2048 max_stack_depth = 6000 effective_cache_size = 3 fsync = on wal_sync_method = fsync archive_command = 'cp -i %p /Users/postgres/officelink/wal_archive/%f max_fsm_pages = 15 stats_start_collector = on stats_row_level = on log_min_duration_statement = 2000 log_line_prefix = '%t %h ' superuser_reserved_connections = 3 autovacuum = on autovacuum_naptime = 60 autovacuum_vacuum_threshold = 150 autovacuum_vacuum_scale_factor = 0.0001 autovacuum_analyze_scale_factor = 0.0001 sudo pico /etc/rc sysctl -w kern.sysv.shmmax=4294967296 sysctl -w kern.sysv.shmall=1048576 sudo pico /etc/sysctl.conf kern.maxproc=2048 kern.maxprocperuid=800 kern.maxfiles=4 kern.maxfilesperproc=3 Processes: 470 total, 2 running, 4 stuck, 464 sleeping... 587 threads 13:34:50 Load Avg: 0.45, 0.34, 0.33 CPU usage: 5.1% user, 5.1% sys, 89.7% idle SharedLibs: num = 157, resident = 26.9M code, 3.29M data, 5.44M LinkEdit MemRegions: num = 15307, resident = 555M + 25.5M private, 282M shared PhysMem: 938M wired, 934M active, 2.13G inactive, 3.96G used, 43.1M free VM: 116G + 90.1M 1213436(0) pageins, 263418(0) pageouts PID COMMAND %CPU TIME #TH #PRTS #MREGS RPRVT RSHRD RSIZE VSIZE 29804 postgres 0.0% 0:03.24 1 927 1.27M 245M 175M 276M 29720 postgres 0.0% 0:01.89 1 927 1.25M 245M 125M 276M 29714 postgres 0.0% 0:03.70 11027 1.30M 245M 215M 276M 29711 postgres 0.0% 0:01.38 11027 1.21M 245M 107M 276M 29707 postgres 0.0% 0:01.27 1 927 1.16M 245M 78.2M 276M 29578 postgres 0.0% 0:01.33 1 927 1.16M 245M 67.8M 276M 29556 postgres 0.0% 0:00.39 1 927 1.09M 245M 91.8M 276M 29494 postgres 0.0% 0:00.19 1 927 1.05M 245M 26.5M 276M 29464 postgres 0.0% 0:01.98 1 927 1.16M 245M 88.8M 276M 29425 postgres 0.0% 0:01.61 1 927 1.17M 245M 112M 276M 29406 postgres 0.0% 0:01.42 1 927 1.15M 245M 118M 276M 29405 postgres 0.0% 0:00.13 1 926 924K 245M 17.9M 276M 29401 postgres 0.0% 0:00.98 11027 1.13M 245M 84.4M 276M 29400 postgres 0.0% 0:00.90 11027 1.14M 245M 78.4M 276M 29394 postgres 0.0% 0:01.56 11027 1.17M 245M 111M 276M
[PERFORM] Dell Hardware Recommendations
We have a 30 GB database (according to pg_database_size) running nicely on a single Dell PowerEdge 2850 right now. This represents data specific to 1 US state. We are in the process of planning a deployment that will service all 50 US states. If 30 GB is an accurate number per state that means the database size is about to explode to 1.5 TB. About 1 TB of this amount would be OLAP data that is heavy-read but only updated or inserted in batch. It is also largely isolated to a single table partitioned on state. This portion of the data will grow very slowly after the initial loading. The remaining 500 GB has frequent individual writes performed against it. 500 GB is a high estimate and it will probably start out closer to 100 GB and grow steadily up to and past 500 GB. I am trying to figure out an appropriate hardware configuration for such a database. Currently I am considering the following: PowerEdge 1950 paired with a PowerVault MD1000 2 x Quad Core Xeon E5310 16 GB 667MHz RAM (4 x 4GB leaving room to expand if we need to) PERC 5/E Raid Adapter 2 x 146 GB SAS in Raid 1 for OS + logs. A bunch of disks in the MD1000 configured in Raid 10 for Postgres data. The MD1000 holds 15 disks, so 14 disks + a hot spare is the max. With 12 250GB SATA drives to cover the 1.5TB we would be able add another 250GB of usable space for future growth before needing to get a bigger set of disks. 500GB drives would leave alot more room and could allow us to run the MD1000 in split mode and use its remaining disks for other purposes in the mean time. I would greatly appreciate any feedback with respect to drive count vs. drive size and SATA vs. SCSI/SAS. The price difference makes SATA awfully appealing. We plan to involve outside help in getting this database tuned and configured, but want to get some hardware ballparks in order to get quotes and potentially request a trial unit. Any thoughts or recommendations? We are running openSUSE 10.2 with kernel 2.6.18.2-34. Regards, Joe Uhl [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Dell Hardware Recommendations
Thanks for the input. Thus far we have used Dell but I would certainly be willing to explore other options. I found a "Reference Guide" for the MD1000 from April, 2006 that includes info on the PERC 5/E at: http://www.dell.com/downloads/global/products/pvaul/en/pvaul_md1000_solutions_guide.pdf To answer the questions below: > How many users do you expect to hit the db at the same time? There are 2 types of users. For roughly every 5000 active accounts, 10 or fewer or those will have additional privileges. Only those more privileged users interact substantially with the OLAP portion of the database. For 1 state 10 concurrent connections was about the max, so if that holds for 50 states we are looking at 500 concurrent users as a top end, with a very small fraction of those users interacting with the OLAP portion. > How big of a dataset will each one be grabbing at the same time? For the OLTP data it is mostly single object reads and writes and generally touches only a few tables at a time. > Will your Perc RAID controller have a battery backed cache on board? > If so (and it better!) how big of a cache can it hold? According to the above link, it has a 256 MB cache that is battery backed. > Can you split this out onto two different machines, one for the OLAP > load and the other for what I'm assuming is OLTP? > Can you physically partition this out by state if need be? Right now this system isn't in production so we can explore any option. We are looking into splitting the OLAP and OLTP portions right now and I imagine physically splitting the partitions on the big OLAP table is an option as well. Really appreciate all of the advice. Before we pull the trigger on hardware we probably will get some external advice from someone but I knew this list would provide some excellent ideas and feedback to get us started. Joe Uhl [EMAIL PROTECTED] On Thu, 9 Aug 2007 16:02:49 -0500, "Scott Marlowe" <[EMAIL PROTECTED]> said: > On 8/9/07, Joe Uhl <[EMAIL PROTECTED]> wrote: > > We have a 30 GB database (according to pg_database_size) running nicely > > on a single Dell PowerEdge 2850 right now. This represents data > > specific to 1 US state. We are in the process of planning a deployment > > that will service all 50 US states. > > > > If 30 GB is an accurate number per state that means the database size is > > about to explode to 1.5 TB. About 1 TB of this amount would be OLAP > > data that is heavy-read but only updated or inserted in batch. It is > > also largely isolated to a single table partitioned on state. This > > portion of the data will grow very slowly after the initial loading. > > > > The remaining 500 GB has frequent individual writes performed against > > it. 500 GB is a high estimate and it will probably start out closer to > > 100 GB and grow steadily up to and past 500 GB. > > > > I am trying to figure out an appropriate hardware configuration for such > > a database. Currently I am considering the following: > > > > PowerEdge 1950 paired with a PowerVault MD1000 > > 2 x Quad Core Xeon E5310 > > 16 GB 667MHz RAM (4 x 4GB leaving room to expand if we need to) > > PERC 5/E Raid Adapter > > 2 x 146 GB SAS in Raid 1 for OS + logs. > > A bunch of disks in the MD1000 configured in Raid 10 for Postgres data. > > > > The MD1000 holds 15 disks, so 14 disks + a hot spare is the max. With > > 12 250GB SATA drives to cover the 1.5TB we would be able add another > > 250GB of usable space for future growth before needing to get a bigger > > set of disks. 500GB drives would leave alot more room and could allow > > us to run the MD1000 in split mode and use its remaining disks for other > > purposes in the mean time. I would greatly appreciate any feedback with > > respect to drive count vs. drive size and SATA vs. SCSI/SAS. The price > > difference makes SATA awfully appealing. > > > > We plan to involve outside help in getting this database tuned and > > configured, but want to get some hardware ballparks in order to get > > quotes and potentially request a trial unit. > > > > Any thoughts or recommendations? We are running openSUSE 10.2 with > > kernel 2.6.18.2-34. > > Some questions: > > How many users do you expect to hit the db at the same time? > How big of a dataset will each one be grabbing at the same time? > Will your Perc RAID controller have a battery backed cache on board? > If so (and it better!) how big of a cache can it hold? > Can you split this out onto two different machines, one for the OLAP > load and the other for what I'm assuming is OLTP? > Can you physically partition this out by state if need be? > > A few comments: &
Re: [PERFORM] SAN vs Internal Disks
Scott Marlowe wrote: > On 9/6/07, Harsh Azad <[EMAIL PROTECTED]> wrote: > >> Hi, >> >> How about the Dell Perc 5/i card, 512MB battery backed cache or IBM >> ServeRAID-8k Adapter? >> > > All Dell Percs have so far been based on either adaptec or LSI > controllers, and have ranged from really bad to fairly decent > performers. There were some recent posts on this list where someone > was benchmarking one, I believe. searching the list archives might > prove useful. > > I am not at all familiar with IBM's ServeRAID controllers. > > Do either of these come with or have the option for battery back > module for the cache? > > >> I hope I am sending relevant information here, I am not too well versed with >> RAID controllers. >> > > Yep. Def look for a chance to evaluate whichever ones you're > considering. The Areca's are in the same price range as the IBM > controller you're considering, maybe a few hundred dollars more. See > if you can get one for review while looking at these other > controllers. > > I'd recommend against Dell unless you're at a company that orders > computers by the hundred lot. My experience with Dell has been that > unless you are a big customer you're just another number (a small one > at that) on a spreadsheet. > If you do go with Dell get connected with an account manager instead of ordering online. You work with the same people every time you have an order and in my experience they can noticeably beat the best prices I can find. This is definitely the way to go if you don't want to get lost in the volume. The group I have worked with for the past ~2 years is very responsive, remembers me and my company across the 3 - 6 month gaps between purchases, and the server/storage person in the group is reasonably knowledgeable and helpful. This is for small lots of machines, our first order was just 2 boxes and i've only placed 4 orders total in the past 2 years. Just my personal experience, i'd be happy to pass along the account manager's information if anyone is interested. > ---(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 > Joe Uhl [EMAIL PROTECTED] ---(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] Need to increase performance of a query
On 06/10/2010 12:56 PM, Anne Rosset wrote: > Craig James wrote: >> create index item_rank_null_idx on item_rank(pf_id) >>where item_rank.pf_id is null; >> >> Craig >> > Hi Craig, > I tried again after adding your suggested index but I didn't see any > improvements: (seems that the index is not used) > Filter: ((rank IS NOT NULL) AND (pf_id IS NULL) AND > ((project_id)::text = 'proj2783'::text)) > Total runtime: 11.988 ms > (6 rows) > > Time: 13.654 ms try: create index item_rank_null_idx on item_rank(pf_id) where rank IS NOT NULL AND pf_id IS NULL; Joe signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Need to increase performance of a query
On 06/10/2010 01:10 PM, Joe Conway wrote: > try: > > create index item_rank_null_idx on item_rank(pf_id) > where rank IS NOT NULL AND pf_id IS NULL; oops -- that probably should be: create index item_rank_null_idx on item_rank(project_id) where rank IS NOT NULL AND pf_id IS NULL; Joe signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Need to increase performance of a query
On 06/10/2010 01:21 PM, Anne Rosset wrote: >> > I tried that and it didn't make any difference. Same query plan. A little experimentation suggests this might work: create index item_rank_project on item_rank(project_id, rank) where pf_id IS NULL; Joe signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Highly Efficient Custom Sorting
On 07/06/2010 12:42 PM, Eliot Gable wrote:
> Thanks for suggesting array_unnest(). I think that will actually prove
> more useful to me than the other example I'm using for extracting my
> data from an array. I was actually planning on computing the order on
> the first call and storing it in a linked list which gets returned one
> item at a time until all rows have been returned. Also, I found a code
> example using Google that showed someone storing data across function
> calls using that pointer. I used their example to produce this:
>
>
> if(SRF_IS_FIRSTCALL()) {
> funcctx = SRF_FIRSTCALL_INIT();
>
> /* This is where we stick or sorted data for returning later */
> funcctx->user_fctx =
> MemoryContextAlloc(funcctx->multi_call_memory_ctx, sizeof(sort_data));
> oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
> data = (sort_data*) funcctx->user_fctx;
>
>
> I have a structure set up that is typedef'd to "sort_data" which stores
> pointers to various things that I need to survive across the calls.
> Since this seems to be what you are suggesting, I assume this is the
> correct approach.
This approach works, but you could also use the SFRM_Materialize mode
and calculate the entire result set in one go. That tends to be simpler.
See, for example crosstab_hash() in contrib/tablefunc for an example.
FWIW, there are also some good examples of array handling in PL/R, e.g.
pg_array_get_r() in pg_conversion.c
HTH,
Joe
--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & Support
signature.asc
Description: OpenPGP digital signature
Re: [PERFORM] Queries with conditions using bitand operator
On 07/13/2010 04:48 AM, Elias Ghanem wrote: > Hi, > I have table "ARTICLE" containing a String a field "STATUS" that > represents a number in binary format (for ex: 10011101). > My application issues queries with where conditions that uses BITAND > operator on this field (for ex: select * from article where status & 4 = > 4). > Thus i'm facing performance problemes with these select queries: the > queries are too slow. > Since i'm using the BITAND operator in my conditions, creating an index > on the status filed is useless > and since the second operator variable (status & 4 = 4; status & 8 = 8; > status & 16 = 16...) a functional index is also usless (because a > functional index require the use of a function that accept only table > column as input parameter: constants are not accepted). > So is there a way to enhance the performance of these queries? You haven't given a lot of info to help us help you, but would something along these lines be useful to you? drop table if exists testbit; create table testbit( id serial primary key, article text, status int ); insert into testbit (article, status) select 'article ' || generate_series::text, generate_series % 256 from generate_series(1,100); create index idx1 on testbit(article) where status & 1 = 1; create index idx2 on testbit(article) where status & 2 = 2; create index idx4 on testbit(article) where status & 4 = 4; create index idx8 on testbit(article) where status & 8 = 8; create index idx16 on testbit(article) where status & 16 = 16; create index idx32 on testbit(article) where status & 512 = 512; update testbit set status = status + 512 where id in (42, 4242, 424242); explain analyze select * from testbit where status & 512 = 512; QUERY PLAN -- Index Scan using idx32 on testbit (cost=0.00..4712.62 rows=5000 width=22) (actual time=0.080..0.085 rows=3 loops=1) Total runtime: 0.170 ms HTH, Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & Support signature.asc Description: OpenPGP digital signature
[PERFORM] Auto ANALYZE criteria
The autovacuum daemon currently uses the number of inserted and
updated tuples to determine if it should run VACUUM ANALYZE on a
table. Why doesn’t it consider deleted tuples as well?
For example, I have a table which gets initially loaded with several
million records. A batch process grabs the records 100 at a time, does
some processing and deletes them from the table in the order of the
primary key. Eventually, performance degrades because an autoanalyze
is never run. The planner decides that it should do a sequential scan
instead of an index scan because the stats don't reflect reality. See
example below.
I can set up a cron job to run the ANALYZE manually, but it seems like
the autovacuum daemon should be smart enough to figure this out on its
own. Deletes can have as big an impact on the stats as inserts and
updates.
Joe Miller
---
testdb=# \d test
Table "public.test"
Column | Type | Modifiers
+-+---
id | integer | not null
data | bytea |
Indexes:
"test_pkey" PRIMARY KEY, btree (id)
testdb=# insert into public.test select s.a, gen_random_bytes(256)
from generate_series(1,1000) as s(a);
INSERT 0 1000
testdb=# SELECT *
FROM pg_stat_all_tables
WHERE schemaname='public' AND relname='test';
relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan |
idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd |
n_live_tup | n_dead_tup | last_vacuum | last_autovacuum | last_analyze
| last_autoanalyze
-++-+--+--+--+---+---+---+---+---+++-+-+--+--
5608158 | public | test|1 |0 |0 |
0 | 1000 | 0 | 0 | 0 |
0 | 0 | | | |
2010-09-20 10:46:37.283775-04
(1 row)
testdb=# explain analyze delete from public.test where id <= 100;
QUERY PLAN
Index Scan using test_pkey on test (cost=0.00..71.63 rows=1000
width=6) (actual time=13.251..22.916 rows=100 loops=1)
Index Cond: (id <= 100)
Total runtime: 23.271 ms
(3 rows)
{ delete records ad nauseum }
testdb=# explain analyze delete from public.test where id <= 7978800;
QUERY PLAN
---
Seq Scan on test (cost=0.00..410106.17 rows=2538412 width=6) (actual
time=48771.772..49681.562 rows=100 loops=1)
Filter: (id <= 7978800)
Total runtime: 49682.006 ms
(3 rows)
testdb=# SELECT *
FROM pg_stat_all_tables
WHERE schemaname='public' AND relname='test';
relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan |
idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd |
n_live_tup | n_dead_tup | last_vacuum |last_autovacuum
| last_analyze | last_autoanalyze
-++-+--+--+--+---+---+---+---+---+++-+---+--+---
5608158 | public | test|1 |0 |54345 |
5433206 | 1000 | 0 | 5433200 | 0 |
5459506 | 725300 | | 2010-09-20 14:45:54.757611-04 |
| 2010-09-20 10:46:37.283775-04
--
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] Auto ANALYZE criteria
I was looking at the autovacuum documentation: http://www.postgresql.org/docs/9.0/interactive/routine-vacuuming.html#AUTOVACUUM For analyze, a similar condition is used: the threshold, defined as: analyze threshold = analyze base threshold + analyze scale factor * number of tuples is compared to the total number of tuples inserted or updated since the last ANALYZE. I guess that should be updated to read "insert, updated or deleted". On Mon, Sep 20, 2010 at 10:12 PM, Tom Lane wrote: > Joe Miller writes: >> The autovacuum daemon currently uses the number of inserted and >> updated tuples to determine if it should run VACUUM ANALYZE on a >> table. Why doesn’t it consider deleted tuples as well? > > I think you misread the code. > > Now there *is* a problem, pre-9.0, if your update pattern is such that > most or all updates are HOT updates. To quote from the 9.0 alpha > release notes: > > Revise pgstat's tracking of tuple changes to > improve the reliability of decisions about when to > auto-analyze. The previous code depended on n_live_tuples + > n_dead_tuples - last_anl_tuples, where all three of these > numbers could be bad estimates from ANALYZE itself. Even > worse, in the presence of a steady flow of HOT updates and > matching HOT-tuple reclamations, auto-analyze might never > trigger at all, even if all three numbers are exactly right, > because n_dead_tuples could hold steady. > > It's not clear to me if that matches your problem, though. > > regards, tom lane > -- 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] Auto ANALYZE criteria
On Mon, Sep 20, 2010 at 6:28 PM, Kevin Grittner wrote: > Joe Miller wrote: > >> I can set up a cron job to run the ANALYZE manually, but it seems >> like the autovacuum daemon should be smart enough to figure this >> out on its own. Deletes can have as big an impact on the stats as >> inserts and updates. > > But until the deleted rows are vacuumed from the indexes, an index > scan must read all the index entries for the deleted tuples, and > visit the heap to determine that they are not visible. Does a > manual run of ANALYZE without a VACUUM change the stats much for > you, or are you running VACUUM ANALYZE? > > -Kevin > The autovacuum is running correctly, so the deleted rows are being removed. All I'm doing is an ANALYZE, not VACUUM ANALYZE. -- 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 count(*) again...
On 10/09/2010 06:54 PM, Mladen Gogala wrote: > In another database, whose name I will not mention, there is a parameter > db_file_multiblock_read_count which specifies how many blocks will be > read by a single read when doing a full table scan. PostgreSQL is in > dire need of something similar and it wouldn't even be that hard to > implement. You're correct in that it isn't particularly difficult to implement for sequential scans. But I have done some testing with aggressive read ahead, and although it is clearly a big win with a single client, the benefit was less clear as concurrency was increased. Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Slow count(*) again...
The biggest single problem with "select count(*)" is that it is seriously overused. People use that idiom to establish existence, which usually leads to a performance disaster in the application using it, unless the table has no more than few hundred records. SQL language, of which PostgreSQL offers an excellent implementation, offers [NOT] EXISTS clause since its inception in the Jurassic era. The problem is with the sequential scan, not with counting. I'd even go as far as to suggest that 99% instances of the "select count(*)" idiom are probably bad use of the SQL language. I agree, I have seen many very bad examples of using count(*). I will go so far as to question the use of count(*) in my examples here. It there a better way to come up with a page list than using count(*)? What is the best method to make a page of results and a list of links to other pages of results? Am I barking up the wrong tree here? One way I have dealt with this on very large tables is to cache the count(*) at the application level (using memcached, terracotta, or something along those lines) and then increment that cache whenever you add a row to the relevant table. On application restart that cache is re-initialized with a regular old count(*). This approach works really well and all large systems in my experience need caching in front of the DB eventually. If you have a simpler system with say a single application/web server you can simply store the value in a variable, the specifics would depend on the language and framework you are using. Another more all-DB approach is to create a statistics tables into which you place aggregated statistics rows (num deleted, num inserted, totals, etc) at an appropriate time interval in your code. So you have rows containing aggregated statistics information for the past and some tiny portion of the new data happening right now that hasn't yet been aggregated. Queries then look like a summation of the aggregated values in the statistics table plus a count(*) over just the newest portion of the data table and are generally very fast. Overall I have found that once things get big the layers of your app stack start to blend together and have to be combined in clever ways to keep speed up. Postgres is a beast but when you run into things it can't do well just find a way to cache it or make it work together with some other persistence tech to handle those cases.
Re: [PERFORM] Auto ANALYZE criteria
Thanks for fixing the docs, but if that's the case, I shouldn't be seeing the behavior that I'm seeing. Should I flesh out this test case a little better and file a bug? Thanks, Joe On Tue, Sep 21, 2010 at 4:44 PM, Tom Lane wrote: > Joe Miller writes: >> I was looking at the autovacuum documentation: >> http://www.postgresql.org/docs/9.0/interactive/routine-vacuuming.html#AUTOVACUUM > >> For analyze, a similar condition is used: the threshold, defined as: >> analyze threshold = analyze base threshold + analyze scale factor * >> number of tuples >> is compared to the total number of tuples inserted or updated since >> the last ANALYZE. > >> I guess that should be updated to read "insert, updated or deleted". > > Mph. We caught the other places where the docs explain what the analyze > threshold is, but missed that one. Fixed, thanks for pointing it out. > > regards, tom lane > -- 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] Dynamically loaded C function performance
Jim C. Nasby wrote: On Fri, May 05, 2006 at 03:47:53PM -0700, Adam Palmblad wrote: Hi, We've got a C function that we use here and we find that for every connection, the first run of the function is much slower than any subsequent runs. ( 50ms compared to 8ms) Besides using connection pooling, are there any options to improve performance? In my experience, connection startup takes a heck of a lot longer than 50ms, so why are you worrying about 50ms for the first run of a function? BTW, sorry, but I don't know a way to speed this up, either. I think Tom nailed the solution already in a nearby reply -- see preload_libraries on this page: http://www.postgresql.org/docs/8.1/interactive/runtime-config-resource.html Joe ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Postgres fsync off (not needed) with NetApp
Dan Gorman wrote: That makes sense. Speaking of NetApp, we're using the 3050C with 4 FC shelfs. Any generic advice other than the NetApp (their NFS oracle tuning options) that might be useful? (e.g. turning off snapshots) I'm not sure if this is in the tuning advice you already have, but we use a dedicated gigabit interface to the NetApp, with jumbo (9K) frames, and an 8K NFS blocksize. We use this for both Oracle and Postgres when the database resides on NetApp. Joe ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Index Being Ignored?
I have a index question. My table has 800K rows and I a doing a basic query on an indexed integer field which takes over 2 seconds to complete because it's ignoring the index for some reason. Any ideas as to why it's ignoring the index? I'm using postgres 8.0.2.SELECT count(*) FROM purchase_order_items WHERE expected_quantity > '0' EXPLAIN ANALYZE reveals that it's not using the index...Aggregate (cost=22695.28..22695.28 rows=1 width=0) (actual time=2205.688..2205.724 rows=1 loops=1) -> Seq Scan on purchase_order_items (cost=0.00..21978.08 rows=286882 width=0) (actual time=0.535..2184.405 rows=7458 loops=1) Filter: (expected_quantity > 0)Total runtime: 2207.203 msHowever, if I use the "SET ENABLE_SEQSCAN TO OFF" trick, then it does use the index and is much faster.SET ENABLE_SEQSCAN TO OFF;EXPLAIN ANALYZE SELECT count(*) FROM purchase_order_items WHERE expected_quantity > '0' Aggregate (cost=1050659.46..1050659.46 rows=1 width=0) (actual time=137.393..137.441 rows=1 loops=1) -> Index Scan using purchase_order_items_expected_quantity_idx on purchase_order_items (cost=0.00..1049942.25 rows=286882 width=0) (actual time=0.756..119.990 rows=7458 loops=1) Index Cond: (expected_quantity > 0)Total runtime: 139.185 msI could understand if this was a really complex query and the planner got confused... but this is such a simple query. Is it OK to use "SET ENABLE_SEQSCAN TO OFF;" in production code? Is there another solution?Thanks! Table Definition --CREATE TABLE purchase_order_items ( id serial NOT NULL, purchase_order_id integer, manufacturer_id integer, quantity integer, product_name character varying(16), short_description character varying(60), expected_quantity integer, received_quantity integer, "position" real, created_at timestamp without time zone DEFAULT now(), updated_at timestamp without time zone);-- Index --CREATE INDEX purchase_order_items_expected_quantity_idx ON purchase_order_items USING btree (expected_quantity);
Re: [PERFORM] Index Being Ignored?
great! Thanks Markus and Tom! On Jun 30, 2006, at 10:29 AM, Markus Schaber wrote: Hi, Joe, Joe Lester wrote: Aggregate (cost=22695.28..22695.28 rows=1 width=0) (actual time=2205.688..2205.724 rows=1 loops=1) -> Seq Scan on purchase_order_items (cost=0.00..21978.08 rows=286882 width=0) (actual time=0.535..2184.405 rows=7458 loops=1) Filter: (expected_quantity > 0) The query planner estimates that your filter will hit 286882 rows, while in reality it hits only 7458 rows. That's why the query planer chooses a sequential scan. It seems that the statistics for the column expected_quantity are off. My suggestions: - make shure that the statistics are current by analyzing the table appropriately (e. G. by using the autovacuum daemon from contrib). - increase the statistics target for this column. - if you run this query very often, an conditional index might make sense: CREATE INDEX purchase_order_having_quantity_idx ON purchase_order_items (expected_quantity) WHERE expected_quantity > 0; HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Big differences in plans between 8.0 and 8.1
Gabriele Turchi wrote: Running an ANALYZE really change the plan, now it is fast as before (8.0). On the production system a VACUUM FULL ANALYZE is run every morning after a clean-up, when the "registrazioni" table is empty. During the day this table fills up (about 500 record any day), and apparently the performances are free-falling very quickly. This behaviour has not changed between the old and the new installation. Can you suggest an easy way to collect and keep up-to-date these statistics in a very low-impact way? Why not just periodically (once an hour?) run "ANALYZE registrazioni;" during the day. This will only update the statistics, and should be very low impact. HTH, Joe ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Big differences in plans between 8.0 and 8.1
Gabriele Turchi wrote: Il giorno sab, 15/07/2006 alle 13.04 -0700, Joe Conway ha scritto: Why not just periodically (once an hour?) run "ANALYZE registrazioni;" during the day. This will only update the statistics, and should be very low impact. This is my "solution" too... but: is enough? Or else: there is a better way to do this? If the performance in the better case is 50 times faster than the worse case, during an hour (50/100 record inserted in "registrazioni") how much the performance can fall before the new "ANALYZE" is run? Otherwise, running ANALYZE more frequently can badly affect the overall performance? One thing I noticed is that in both plans there is a seq scan on registrazioni. Given that performance degrades so quickly as records are inserted into registrazioni, I'm wondering if you're missing an index. What indexes do you have on registrazioni? Joe ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Performance penalty for remote access of postgresql
Stephen Frost wrote: * Guoping Zhang ([EMAIL PROTECTED]) wrote: Obviously, if there is no better solution, the TCP round trip penalty will stop us doing so as we do have performance requirement. Actually, can't you stick multiple inserts into a given 'statement'? ie: insert into abc (123); insert into abc (234); I'm not 100% sure if that solves the round-trip issue, but it might.. Also, it looks like we might have multi-value insert support in 8.2 (I truely hope so anyway), so you could do something like this: insert into abc (123),(234); Yeah, see my post from last night on PATCHES. Something like "insert into abc (123); insert into abc (234); ..." actually seems to work pretty well as long as you don't drive the machine into swapping. If you're doing a very large number of INSERTs, break it up into bite-sized chunks and you should be fine. Joe ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] rapid degradation after postmaster restart
Matthew T. O'Connor wrote: I think you understand correctly. A table with 1,000,000 rows should get vacuumed approx every 2,000,000 changes (assuming default values for -V ). FYI and insert and a delete count as one change, but and update counts as two. Unfortunately, the running with -d2 would show the numbers that pg_autovacuum is using to decide if it when it should vacuum or analyze.Also, are you sure that it vacuumed more than once and wasn't doing analyzes most of the time? Yeah, I'm sure. Snippets from the log: [...lots-o-tables...] [2004-03-14 12:44:48 PM] added table: specdb."public"."parametric_states" [2004-03-14 12:49:48 PM] Performing: VACUUM ANALYZE "public"."transaction_data" [2004-03-14 01:29:59 PM] Performing: VACUUM ANALYZE "public"."transaction_data" [2004-03-14 02:08:26 PM] Performing: ANALYZE "public"."out_of_spec" [2004-03-14 02:08:26 PM] Performing: VACUUM ANALYZE "public"."transaction_data" [2004-03-14 02:22:44 PM] Performing: VACUUM ANALYZE "public"."spc_graphs" [2004-03-14 03:06:45 PM] Performing: VACUUM ANALYZE "public"."out_of_spec" [2004-03-14 03:06:45 PM] Performing: VACUUM ANALYZE "public"."transaction_data" [2004-03-14 03:19:51 PM] Performing: VACUUM ANALYZE "public"."spc_graphs" [2004-03-14 03:21:09 PM] Performing: ANALYZE "public"."parametric_states" [2004-03-14 03:54:57 PM] Performing: ANALYZE "public"."out_of_spec" [2004-03-14 03:54:57 PM] Performing: VACUUM ANALYZE "public"."transaction_data" [2004-03-14 04:07:52 PM] Performing: VACUUM ANALYZE "public"."spc_graphs" [2004-03-14 04:09:33 PM] Performing: ANALYZE "public"."equip_status_history" [2004-03-14 04:09:33 PM] Performing: VACUUM ANALYZE "public"."parametric_states" [2004-03-14 04:43:46 PM] Performing: VACUUM ANALYZE "public"."out_of_spec" [2004-03-14 04:43:46 PM] Performing: VACUUM ANALYZE "public"."transaction_data" [2004-03-14 04:56:35 PM] Performing: VACUUM ANALYZE "public"."spc_graphs" [2004-03-14 04:58:32 PM] Performing: ANALYZE "public"."parametric_states" [2004-03-14 05:28:58 PM] added database: specdb This is the entire period of the first test, with default autovac settings. The table "public"."transaction_data" is the one with 28 million active rows. The entire test run inserts about 600 x 600 = 360,000 rows, out of which roughly two-thirds are later deleted. That's unfortunate as that is the detail we need to see what pg_autovacuum thinks is really going on. We had a similar sounding crash on FreeBSD due to some unitialized variables that were being printed out by the debug code, however that was fixed a long time ago. Any chance you can look into this? I can try. The server belongs to another department, and they are under the gun to get back on track with their testing. Also, they compiled without debug symbols, so I need to get permission to recompile. Yes I would be very curious to see the results with the vacuum delay patch installed (is that patch applied to HEAD?) Any idea where I can get my hands on the latest version. I found the original post from Tom, but I thought there was a later version with both number of pages and time to sleep as knobs. Thanks, Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] rapid degradation after postmaster restart
Tom Lane wrote: Joe Conway <[EMAIL PROTECTED]> writes: Any idea where I can get my hands on the latest version. I found the original post from Tom, but I thought there was a later version with both number of pages and time to sleep as knobs. That was as far as I got. I think Jan posted a more complex version that would still be reasonable to apply to 7.4. I thought that too, but was having trouble finding it. I'll look again. Thanks, Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] rapid degradation after postmaster restart
Matthew T. O'Connor wrote: Strange... I wonder if this is some integer overflow problem. There was one reported recently and fixed as of CVS head yesterday, you might try that, however without the -d2 output I'm only guessing at why pg_autovacuum is vacuuming so much / so often. I'll see what I can do tomorrow to track it down. I have already recommended to the program manager that they switch to 7.4.2 plus the autovacuum patch. Not sure they will be willing to make any changes at this stage in their release process though. If we can't find one, any chance you can do some testing with CVS HEAD just to see if that works any better. I know there has been a fair amount of work done to improve this situation (not just vacuum delay, but ARC etc...) I might do that, but not likely on Solaris. I can probably get a copy of the current database and testing scripts, and give it a try on one of my own machines (all Linux, either RHAS3, RH9, or Fedora). Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] rapid degradation after postmaster restart
Tom Lane wrote: Joe Conway <[EMAIL PROTECTED]> writes: Any idea where I can get my hands on the latest version. I found the original post from Tom, but I thought there was a later version with both number of pages and time to sleep as knobs. That was as far as I got. I think Jan posted a more complex version that would still be reasonable to apply to 7.4. I have tested Tom's original patch now. The good news -- it works great in terms of reducing the load imposed by vacuum -- almost to the level of being unnoticeable. The bad news -- in a simulation test which loads an hour's worth of data, even with delay set to 1 ms, vacuum of the large table exceeds two hours (vs 12-14 minutes with delay = 0). Since that hourly load is expected 7 x 24, this obviously isn't going to work. The problem with Jan's more complex version of the patch (at least the one I found - perhaps not the right one) is it includes a bunch of other experimental stuff that I'd not want to mess with at the moment. Would changing the input units (for the original patch) from milli-secs to micro-secs be a bad idea? If so, I guess I'll get to extracting what I need from Jan's patch. Thanks, Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] rapid degradation after postmaster restart
Matthew T. O'Connor wrote:
If memory serves, the problem is that you actually sleep 10ms even when
you set it to 1. One of the thing changed in Jan's later patch was the
ability to specify how many pages to work on before sleeping, rather
than how long to sleep inbetween every 1 page. You might be able to do
a quick hack and have it do 10 pages or so before sleeping.
I thought I remembered something about that.
It turned out to be less difficult than I first thought to extract the
vacuum delay stuff from Jan's performance patch. I haven't yet tried it
out, but it's attached in case you are interested. I'll report back once
I have some results.
Joe
Index: src/backend/access/nbtree/nbtree.c
===
RCS file: /home/pgsql/CvsRoot/pgsql-server/src/backend/access/nbtree/nbtree.c,v
retrieving revision 1.106
diff -c -b -r1.106 nbtree.c
*** src/backend/access/nbtree/nbtree.c 2003/09/29 23:40:26 1.106
--- src/backend/access/nbtree/nbtree.c 2003/11/03 17:56:54
***
*** 18,23
--- 18,25
*/
#include "postgres.h"
+ #include
+
#include "access/genam.h"
#include "access/heapam.h"
#include "access/nbtree.h"
***
*** 27,32
--- 29,39
#include "storage/smgr.h"
+ extern intvacuum_page_delay;
+ extern intvacuum_page_groupsize;
+ extern intvacuum_page_groupcount;
+
+
/* Working state for btbuild and its callback */
typedef struct
{
***
*** 610,615
--- 617,631
CHECK_FOR_INTERRUPTS();
+ if (vacuum_page_delay > 0)
+ {
+ if (++vacuum_page_groupcount >= vacuum_page_groupsize)
+ {
+ vacuum_page_groupcount = 0;
+ usleep(vacuum_page_delay * 1000);
+ }
+ }
+
ndeletable = 0;
page = BufferGetPage(buf);
opaque = (BTPageOpaque) PageGetSpecialPointer(page);
***
*** 736,741
--- 752,768
Buffer buf;
Pagepage;
BTPageOpaque opaque;
+
+ CHECK_FOR_INTERRUPTS();
+
+ if (vacuum_page_delay > 0)
+ {
+ if (++vacuum_page_groupcount >= vacuum_page_groupsize)
+ {
+ vacuum_page_groupcount = 0;
+ usleep(vacuum_page_delay * 1000);
+ }
+ }
buf = _bt_getbuf(rel, blkno, BT_READ);
page = BufferGetPage(buf);
Index: src/backend/commands/vacuumlazy.c
===
RCS file: /home/pgsql/CvsRoot/pgsql-server/src/backend/commands/vacuumlazy.c,v
retrieving revision 1.32
diff -c -b -r1.32 vacuumlazy.c
*** src/backend/commands/vacuumlazy.c 2003/09/25 06:57:59 1.32
--- src/backend/commands/vacuumlazy.c 2003/11/03 17:57:27
***
*** 37,42
--- 37,44
*/
#include "postgres.h"
+ #include
+
#include "access/genam.h"
#include "access/heapam.h"
#include "access/xlog.h"
***
*** 88,93
--- 90,99
static TransactionId OldestXmin;
static TransactionId FreezeLimit;
+ int vacuum_page_delay = 0; /* milliseconds per page group */
+ int vacuum_page_groupsize = 10; /* group size */
+ int vacuum_page_groupcount = 0; /* current group size count */
+
/* non-export function prototypes */
static void lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,
***
*** 228,233
--- 234,248
CHECK_FOR_INTERRUPTS();
+ if (vacuum_page_delay > 0)
+ {
+ if (++vacuum_page_groupcount >= vacuum_page_groupsize)
+ {
+ vacuum_page_groupcount = 0;
+ usleep(vacuum_page_delay * 1000);
+ }
+ }
+
/*
* If we are close to overrunning the available space for
* dead-tuple TIDs, pause and do a cycle of vacuuming before we
***
*** 469,474
--- 484,498
CHECK_FOR_INTERRUPTS();
+ if (vacuum_page_delay > 0)
+ {
+ if (++vacuum_page_groupcount >= vacuum_page_groupsize)
+ {
+ vacuum_page_groupcount = 0;
+ usleep(vacuum_page_delay * 1000);
+ }
+
Re: [PERFORM] rapid degradation after postmaster restart
Andrew Sullivan wrote: Sorry I haven't had a chance to reply to this sooner. The vacuum delay stuff that you're working on may help, but I can't really believe it's your salvation if this is happening after only a few minutes. No matter how much you're doing inside those functions, you surely can't be causing so many dead tuples that a vacuum is necessary that soon. Did you try not vacuuming for a little while to see if it helps? I discussed it later in the thread, but we're adding about 400K rows per hour and deleting most of them after processing (note this is a commercial app, written and maintained by another department -- I can recommend changes, but this late into their release cycle they are very reluctant to change the app). This is 7 x 24 data collection from equipment, so there is no "slow" time to use as a maintenance window. But since the server in question is a test machine, I was able to shut everything off long enough to do a full vacuum -- it took about 12 hours. I didn't see it anywhere in this thread, but are you quite sure that you're not swapping? Note that vmstat on multiprocessor Solaris machines is not notoriously useful. You may want to have a look at what the example stuff in the SE Toolkit tells you, or what you get from sar. I believe you have to use a special kernel setting on Solaris to mark shared memory as being ineligible for swap. I'm (reasonably) sure there is no swapping. Minimum free memory (from top) is about 800 MB, and "vmstat -S" shows no swap-in or swap-out. I've been playing with a version of Jan's performance patch in the past few hours. Based on my simulations, it appears that a 1 ms delay every 10 pages is just about right. The performance hit is negligible (based on overall test time, and cpu % used by the vacuum process). I still have a bit more analysis to do, but this is looking pretty good. More later... Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] rapid degradation after postmaster restart
Arthur Ward wrote: Jan's vacuum-delay-only patch that nobody can find is here: http://archives.postgresql.org/pgsql-hackers/2003-11/msg00518.php I've been using it in testing & production without any problems. Great to know -- many thanks. I've hacked my own vacuum-delay-only patch form Jan's all_performance patch. It looks like the only difference is that it uses usleep() instead of select(). So far the tests look promising. Thanks, Joe ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] SETOF performance
Jeff wrote: I think it was on this list - someone posted a message about SETOF being slower. Tom replied saying it was because it needed to create an on-disk tuplestore. I was just looking for some clarification - a SETOF function will always write the reslting tuples to disk (Not buffering in say a sort_mem sized buffer)? I think at least part of what you're seeing is normal function call overhead. As far as tuplestores writing to disk, here's what the source says: In src/backend/utils/sort/tuplestore.c 8<--- * maxKBytes: how much data to store in memory (any data beyond this * amount is paged to disk). When in doubt, use work_mem. */ Tuplestorestate * tuplestore_begin_heap(bool randomAccess, bool interXact, int maxKBytes) 8<--- In src/backend/executor/execQual.c:ExecMakeTableFunctionResult(): 8<--- tupstore = tuplestore_begin_heap(true, false, work_mem); 8<--- So up to work_mem (sort_mem in 7.4 and earlier) should be stored in memory. Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Toooo many context switches (maybe SLES8?)
Dirk Lutzebäck wrote: postgresql 7.4.1 a new Dual Xeon MP too much context switches (way more than 100.000) on higher load (meaning system load > 2). I believe this was fixed in 7.4.2, although I can't seem to find it in the release notes. Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Toooo many context switches (maybe SLES8?)
Dirk Lutzebäck wrote: Joe, do you know where I should look in the 7.4.2 code to find this out? I think I was wrong. I just looked in CVS and found the commit I was thinking about: http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/storage/lmgr/s_lock.c.diff?r1=1.22&r2=1.23 http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/include/storage/s_lock.h.diff?r1=1.123&r2=1.124 = Revision 1.23 / (download) - [select for diffs] , Sat Dec 27 20:58:58 2003 UTC (3 months, 2 weeks ago) by tgl Changes since 1.22: +5 -1 lines Diff to previous 1.22 Improve spinlock code for recent x86 processors: insert a PAUSE instruction in the s_lock() wait loop, and use test before test-and-set in TAS() macro to avoid unnecessary bus traffic. Patch from Manfred Spraul, reworked a bit by Tom. = I thought this had been committed to the 7.4 stable branch as well, but it appears not. Joe ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Wierd context-switching issue on Xeon
scott.marlowe wrote: On Mon, 19 Apr 2004, Bruce Momjian wrote: I have BSD on a SuperMicro dual Xeon, so if folks want another hardware/OS combination to test, I can give out logins to my machine. I can probably do some nighttime testing on a dual 2800MHz non-MP Xeon machine as well. It's a Dell 2600 series machine and very fast. It has the moderately fast 533MHz FSB so may not have as many problems as the MP type CPUs seem to be having. I've got a quad 2.8Ghz MP Xeon (IBM x445) that I could test on. Does anyone have a test set that can reliably reproduce the problem? Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Wierd context-switching issue on Xeon
Tom Lane wrote: Here is a test case. To set up, run the "test_setup.sql" script once; then launch two copies of the "test_run.sql" script. (For those of you with more than two CPUs, see whether you need one per CPU to make trouble, or whether two test_runs are enough.) Check that you get a nestloops-with-index-scans plan shown by the EXPLAIN in test_run. Check. In isolation, test_run.sql should do essentially no syscalls at all once it's past the initial ramp-up. On a machine that's functioning per expectations, multiple copies of test_run show a relatively low rate of semop() calls --- a few per second, at most --- and maybe a delaying select() here and there. What I actually see on Josh's client's machine is a context swap storm: "vmstat 1" shows CS rates around 170K/sec. strace'ing the backends shows a corresponding rate of semop() syscalls, with a few delaying select()s sprinkled in. top(1) shows system CPU percent of 25-30 and idle CPU percent of 16-20. Your test case works perfectly. I ran 4 concurrent psql sessions, on a quad Xeon (IBM x445, 2.8GHz, 4GB RAM), hyperthreaded. Heres what 'top' looks like: 177 processes: 173 sleeping, 3 running, 1 zombie, 0 stopped CPU states: cpuusernice systemirq softirq iowaitidle total 35.9%0.0%7.2% 0.0% 0.0%0.0% 56.8% cpu00 19.6%0.0%4.9% 0.0% 0.0%0.0% 75.4% cpu01 44.1%0.0%7.8% 0.0% 0.0%0.0% 48.0% cpu020.0%0.0%0.0% 0.0% 0.0%0.0% 100.0% cpu03 32.3%0.0% 13.7% 0.0% 0.0%0.0% 53.9% cpu04 21.5%0.0% 10.7% 0.0% 0.0%0.0% 67.6% cpu05 42.1%0.0%9.8% 0.0% 0.0%0.0% 48.0% cpu06 100.0%0.0%0.0% 0.0% 0.0%0.0%0.0% cpu07 27.4%0.0% 10.7% 0.0% 0.0%0.0% 61.7% Mem: 4123700k av, 3933896k used, 189804k free, 0k shrd, 221948k buff 2492124k actv, 760612k in_d, 41416k in_c Swap: 2040244k av, 5632k used, 2034612k free 3113272k cached Note that cpu06 is not a postgres process. The output of vmstat looks like this: # vmstat 1 procs memory swap io system cpu r b swpd free buff cache si so bi bo in cs us sy id wa 4 0 5632 184264 221948 3113308 0 000 00 0 0 0 0 3 0 5632 184264 221948 3113308 0 000 112 211894 36 9 55 0 5 0 5632 184264 221948 3113308 0 000 125 222071 39 8 53 0 4 0 5632 184264 221948 3113308 0 000 110 215097 39 10 52 0 1 0 5632 184588 221948 3113308 0 00 96 139 187561 35 10 55 0 3 0 5632 184588 221948 3113308 0 000 114 241731 38 10 52 0 3 0 5632 184920 221948 3113308 0 000 132 257168 40 9 51 0 1 0 5632 184912 221948 3113308 0 000 114 251802 38 9 54 0 Note the test case assumes you've got shared_buffers set to at least 1000; with smaller values, you may get some I/O syscalls, which will probably skew the results. shared_buffers 16384 (1 row) I found that killing three of the four concurrent queries dropped context switches to about 70,000 to 100,000. Two or more sessions brings it up to 200K+. Joe ---(end of broadcast)--- TIP 3: 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] Wierd context-switching issue on Xeon
Joe Conway wrote: In isolation, test_run.sql should do essentially no syscalls at all once it's past the initial ramp-up. On a machine that's functioning per expectations, multiple copies of test_run show a relatively low rate of semop() calls --- a few per second, at most --- and maybe a delaying select() here and there. Here's results for 7.4 on a dual Athlon server running fedora core: CPU states: cpuusernice systemirq softirq iowaitidle total 86.0%0.0% 52.4% 0.0% 0.0%0.0% 61.2% cpu00 37.6%0.0% 29.7% 0.0% 0.0%0.0% 32.6% cpu01 48.5%0.0% 22.7% 0.0% 0.0%0.0% 28.7% procs memory swap io system cpu r b swpd free buff cache si sobibo incs 1 0 120448 25764 48300 109457600 0 124 170 187 1 0 120448 25780 48300 109457600 0 0 15289 2 0 120448 25744 48300 109458000 060 141 78290 2 0 120448 25752 48300 109458000 0 0 131 140326 2 0 120448 25756 48300 109457600 040 122 140100 2 0 120448 25764 48300 109458400 060 133 136595 2 0 120448 24284 48300 109458400 0 200 138 135151 The jump in cs corresponds to starting the query in the second session. Joe ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Looking for ideas on how to speed up warehouse loading
Sean Shanny wrote: explain analyze SELECT t1.id, t2.url FROM referral_temp t2 LEFT OUTER JOIN d_referral t1 ON t2.url = t1.referral_raw_url ORDER BY t1.id; What I would like to know is if there are better ways to do the join? I need to get all the rows back from the referral_temp table as they are used for assigning FK's for the fact table later in processing. When I iterate over the values that I get back those with t1.id = null I assign a new FK and push both into the d_referral table as new entries as well as a text file for later use. The matching records are written to a text file for later use. Would something like this work any better (without disabling index scans): SELECT t1.id, t2.url FROM referral_temp t2, d_referral t1 WHERE t1.referral_raw_url = t2.url; SELECT t1.id, t2.url FROM referral_temp t2 WHERE NOT EXISTS (select 1 FROM d_referral t1 WHERE t1.referral_raw_url = t2.url); ? Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] postgresql and openmosix migration
Bill wrote: Ok, so maybe someone on this group will have a better idea. We have a database of financial information, and this has literally millions of entries. I have installed indicies, but for the rather computationally demanding processes we like to use, like a select query to find the commodity with the highest monthly or annual returns, the computer generally runs unacceptably slow. So, other than clustring, how could I achieve a speed increase in these complex queries? Is this better in mysql or postgresql? If the bottleneck is really computational, not I/O, you might try PL/R in conjunction with the rpvm R package. rpvm allows R to make use of pvm to split its load among a cluster. See: R: http://www.r-project.org/ PL/R: http://www.joeconway.com/plr/ rpvm: http://cran.r-project.org/src/contrib/Descriptions/rpvm.html http://cran.r-project.org/doc/packages/rpvm.pdf I haven't had a chance to play with this myself yet, but I hope to relatively soon. HTH, Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] NAS, SAN or any alternate solution ?
[EMAIL PROTECTED] wrote: Would NAS or SAN be good solutions ? (I've read that NAS uses NFS which could slow down the transfer rate ??) Has anyone ever tried one of these with postgresql ? Not (yet) with Postgres, but my company has run ~100GB Oracle database on NAS (NetApp) for the past couple of years. We've found it to outperform local attached storage, and it has been extremely reliable and flexible. Our DBAs wouldn't give it up without a fight. Joe ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] shared_buffers Question
I'm doing a nightly vacuum... so I don't think that's it, although should I be doing a FULL vacuum instead? The size of my data directory is only about 389 MB. I'll take a closer look at file sizes going forward. echo "VACUUM ANALYZE VERBOSE;" | /Library/PostgreSQL/bin/psql -U postgres officelink 2>> vacuum.log Thanks. From: "Scott Marlowe" Your shared buffers are almost certainly not the problem here. 2000 shared buffers is only 16 Megs of ram, max. More than likely, the database filled up the data directory / partition because it wasn't being vacuumed. On Sat, 2004-07-31 at 10:25, Joe Lester wrote: > I've been running a postgres server on a Mac (10.3, 512MB RAM) with 200 > clients connecting for about 2 months without a crash. However just > yesterday the database and all the clients hung. When I looked at the > Mac I'm using as the postgres server it had a window up that said that > there was no more disk space available to write memory too. I ended up > having to restart the whole machine. I would like to configure postgres > so that is does not rely so heavily on disk-based memory but, rather, > tries to stay within the scope of the 512MB of physical memory in the > Mac.
Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --
Chris Browne wrote: Might we set up the view as: create view combination_of_logs as select * from table_1 where txn_date between 'this' and 'that' union all select * from table_2 where txn_date between 'this2' and 'that2' union all select * from table_3 where txn_date between 'this3' and 'that3' union all select * from table_4 where txn_date between 'this4' and 'that4' union all ... ad infinitum union all select * from table_n where txn_date > 'start_of_partition_n'; and expect that to help, as long as the query that hooks up to this has date constraints? We'd have to regenerate the view with new fixed constants each time we set up the tables, but that sounds like it could work... That's exactly what we're doing, but using inherited tables instead of a union view. With inheritance, there is no need to rebuild the view each time a table is added or removed. Basically, in our application, tables are partitioned by either month or week, depending on the type of data involved, and queries are normally date qualified. We're not completely done with our data conversion (from a commercial RDBMSi), but so far the results have been excellent. Similar to what others have said in this thread, the conversion involved restructuring the data to better suit Postgres, and the application (data analysis/mining vs. the source system which is operational). As a result we've compressed a > 1TB database down to ~0.4TB, and seen at least one typical query reduced from ~9 minutes down to ~40 seconds. Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --
[EMAIL PROTECTED] wrote: Joe Conway <[EMAIL PROTECTED]> wrote on 15.09.2004, 06:30:24: We're not completely done with our data conversion (from a commercial RDBMSi), but so far the results have been excellent. Similar to what others have said in this thread, the conversion involved restructuring the data to better suit Postgres, and the application (data analysis/mining vs. the source system which is operational). As a result we've compressed a > 1TB database down to ~0.4TB, and seen at least one typical query reduced from ~9 minutes down to ~40 seconds. Sounds interesting. The performance gain comes from partition elimination of the inherited tables under the root? I take it the compression comes from use of arrays, avoiding the need for additional rows and key overhead? Sorry, in trying to be concise I was not very clear. I'm using the term compression very generally here. I'll try to give a bit more background, The original data source is a database schema designed for use by an operational application that my company sells to provide enhanced management of equipment that we also sell. The application needs to be very flexible in exactly what data it stores in order to be useful across a wide variety of equipment models and versions. In order to do that there is a very large central "transaction" table that stores name->value pairs in varchar columns. The name->value pairs come from parsed output of the equipment, and as such there is a fair amount of redundancy and unneeded data that ends up getting stored. At each installation in the field this table can get very large (> billion rows). Additionally the application prematerializes a variety of summaries for use by the operators using the GUI. We collect the data exported from each of the systems in the field and accumulate it in a single central database for data mining and analysis. This is the database that is actually being converted. By compression I really mean that unneeded and redundant data is being stripped out, and data known to be of a certain datatype is stored in that type instead of varchar (e.g. values known to be int are stored as int). Also the summaries are not being converted (although we do some post processing to create new materialized summaries). My points in telling this were: - the use of inherited tables to partition this huge number of rows and yet allow simple query access to it seems to work well, at least in early validation tests - had we simply taken the original database and "slammed" it into Postgres with no further thought, we would not have seen the big improvements, and thus the project might have been seen as a failure (even though it saves substantial $) Hope that's a bit more clear. I'm hoping to write up a more detailed case study once we've cut the Postgres system into production and the dust settles a bit. Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --
Josh Berkus wrote: - the use of inherited tables to partition this huge number of rows and yet allow simple query access to it seems to work well, at least in early validation tests - had we simply taken the original database and "slammed" it into Postgres with no further thought, we would not have seen the big improvements, and thus the project might have been seen as a failure (even though it saves substantial $) Any further thoughts on developing this into true table partitioning? Just that I'd love to see it happen ;-) Maybe someday I'll be able to find the time to work on it myself, but for the moment I'm satisfied with the workarounds we've made. Joe ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --
Simon Riggs wrote: Joe, Your application is very interesting. I've just read your OSCON paper. I'd like to talk more about that. Very similar to Kalido. ...but back to partitioning momentarily: Does the performance gain come from partition elimination of the inherited tables under the root? I think the major part of the peformance gain comes from the fact that the source database has different needs in terms of partitioning criteria because of it's different purpose. The data is basically partitioned by customer installation instead of by date. Our converted scheme partitions by date, which is in line with the analytical queries run at the corporate office. Again, this is an argument in favor of not simply porting what you're handed. We might get similar query performance with a single large table and multiple partial indexes (e.g. one per month), but there would be one tradeoff and one disadvantage to that: 1) The indexes would need to be generated periodically -- this is a tradeoff since we currently need to create inherited tables at the same periodicity 2) It would be much more difficult to "roll off" a month's worth of data when needed. The general idea is that each month we create a new monthly table, then archive and drop the oldest monthly table. If all the data were in one big table we would have to delete many millions of rows from a (possibly) multibillion row table, and then vacuum that table -- no thanks ;-) Joe ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --
Iain wrote: That's exactly what we're doing, but using inherited tables instead of a union view. With inheritance, there is no need to rebuild the view each time a table is added or removed. Basically, in our application, tables are partitioned by either month or week, depending on the type of data involved, and queries are normally date qualified. That sounds interesting. I have to admit that I havn't touched iheritance in pg at all yet so I find it hard to imagine how this would work. If you have a chance, would you mind elaborating on it just a little? OK, see below: = create table foo(f1 int, f2 date, f3 float8); create table foo_2004_01() inherits (foo); create table foo_2004_02() inherits (foo); create table foo_2004_03() inherits (foo); create index foo_2004_01_idx1 on foo_2004_01(f2); create index foo_2004_02_idx1 on foo_2004_02(f2); create index foo_2004_03_idx1 on foo_2004_03(f2); insert into foo_2004_02 values(1,'2004-feb-15',3.14); -- needed just for illustration since these are toy tables set enable_seqscan to false; explain analyze select * from foo where f2 = '2004-feb-15'; QUERY PLAN Result (cost=1.00..10061.32 rows=16 width=16) (actual time=0.224..0.310 rows=1 loops=1) -> Append (cost=1.00..10061.32 rows=16 width=16) (actual time=0.214..0.294 rows=1 loops=1) -> Seq Scan on foo (cost=1.00..10022.50 rows=5 width=16) (actual time=0.004..0.004 rows=0 loops=1) Filter: (f2 = '2004-02-15'::date) -> Index Scan using foo_2004_01_idx1 on foo_2004_01 foo (cost=0.00..17.07 rows=5 width=16) (actual time=0.101..0.101 rows=0 loops=1) Index Cond: (f2 = '2004-02-15'::date) -> Index Scan using foo_2004_02_idx1 on foo_2004_02 foo (cost=0.00..4.68 rows=1 width=16) (actual time=0.095..0.101 rows=1 loops=1) Index Cond: (f2 = '2004-02-15'::date) -> Index Scan using foo_2004_03_idx1 on foo_2004_03 foo (cost=0.00..17.07 rows=5 width=16) (actual time=0.066..0.066 rows=0 loops=1) Index Cond: (f2 = '2004-02-15'::date) Total runtime: 0.582 ms (11 rows) create table foo_2004_04() inherits (foo); create index foo_2004_04_idx1 on foo_2004_04(f2); explain analyze select * from foo where f2 = '2004-feb-15'; QUERY PLAN Result (cost=1.00..10078.38 rows=21 width=16) (actual time=0.052..0.176 rows=1 loops=1) -> Append (cost=1.00..10078.38 rows=21 width=16) (actual time=0.041..0.159 rows=1 loops=1) -> Seq Scan on foo (cost=1.00..10022.50 rows=5 width=16) (actual time=0.004..0.004 rows=0 loops=1) Filter: (f2 = '2004-02-15'::date) -> Index Scan using foo_2004_01_idx1 on foo_2004_01 foo (cost=0.00..17.07 rows=5 width=16) (actual time=0.012..0.012 rows=0 loops=1) Index Cond: (f2 = '2004-02-15'::date) -> Index Scan using foo_2004_02_idx1 on foo_2004_02 foo (cost=0.00..4.68 rows=1 width=16) (actual time=0.016..0.022 rows=1 loops=1) Index Cond: (f2 = '2004-02-15'::date) -> Index Scan using foo_2004_03_idx1 on foo_2004_03 foo (cost=0.00..17.07 rows=5 width=16) (actual time=0.008..0.008 rows=0 loops=1) Index Cond: (f2 = '2004-02-15'::date) -> Index Scan using foo_2004_04_idx1 on foo_2004_04 foo (cost=0.00..17.07 rows=5 width=16) (actual time=0.095..0.095 rows=0 loops=1) Index Cond: (f2 = '2004-02-15'::date) Total runtime: 0.443 ms (13 rows) For loading data, we COPY into foo, and have a trigger that redirects the rows to the appropriate partition. Notice that the partitions which do not contain any data of interest are still probed for data, but since they have none it is very quick. In a real life example I got the following results just this afternoon: - aggregate row count = 471,849,665 - total number inherited tables = 216 (many are future dated and therefore contain no data) - select one month's worth of data for one piece of equipment by serial number (49,257 rows) = 526.015 ms Not too bad -- quick enough for my needs. BTW, this is using NFS mounted storage (NetApp NAS). Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --
Christopher Browne wrote: In the last exciting episode, [EMAIL PROTECTED] (Joe Conway) wrote: That's exactly what we're doing, but using inherited tables instead of a union view. With inheritance, there is no need to rebuild the view each time a table is added or removed. Basically, in our application, tables are partitioned by either month or week, depending on the type of data involved, and queries are normally date qualified. Where does the constraint come in that'll allow most of the data to be excluded? Not sure I follow this. Or is this just that the entries are all part of "bigtable" so that the self join is only 2-way? We don't have a need for self-joins in our application. We do use a crosstab function to materialize some transposed views of the data, however. That allows us to avoid self-joins in the cases where we might otherwise need them. Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --
Iain wrote: Joe's example wasn't excluding partions, as he didn't use a predicated UNION ALL view to select from. His queries use an indexed column that allow the various partitions to be probed at low cost, and he was satisfied wth that. Right. My point in my previous post was that you could still do all that that if you wanted to, by building the predicated view with UNION ALL of each of the child tables. Right. It doesn't look that much different: create or replace view foo_vw as select * from foo_2004_01 where f2 >= '2004-jan-01' and f2 <= '2004-jan-31' union all select * from foo_2004_02 where f2 >= '2004-feb-01' and f2 <= '2004-feb-29' union all select * from foo_2004_03 where f2 >= '2004-mar-01' and f2 <= '2004-mar-31' ; -- needed just for illustration since these are toy tables set enable_seqscan to false; explain analyze select * from foo_vw where f2 = '2004-feb-15'; QUERY PLAN -- Subquery Scan foo_vw (cost=0.00..14.54 rows=3 width=16) (actual time=0.022..0.027 rows=1 loops=1) -> Append (cost=0.00..14.51 rows=3 width=16) (actual time=0.019..0.022 rows=1 loops=1) -> Subquery Scan "*SELECT* 1" (cost=0.00..4.84 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=1) -> Index Scan using foo_2004_01_idx2 on foo_2004_01 (cost=0.00..4.83 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=1) Index Cond: ((f2 >= '2004-01-01'::date) AND (f2 <= '2004-01-31'::date) AND (f2 = '2004-02-15'::date)) -> Subquery Scan "*SELECT* 2" (cost=0.00..4.84 rows=1 width=16) (actual time=0.013..0.015 rows=1 loops=1) -> Index Scan using foo_2004_02_idx2 on foo_2004_02 (cost=0.00..4.83 rows=1 width=16) (actual time=0.009..0.010 rows=1 loops=1) Index Cond: ((f2 >= '2004-02-01'::date) AND (f2 <= '2004-02-29'::date) AND (f2 = '2004-02-15'::date)) -> Subquery Scan "*SELECT* 3" (cost=0.00..4.84 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=1) -> Index Scan using foo_2004_03_idx2 on foo_2004_03 (cost=0.00..4.83 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=1) Index Cond: ((f2 >= '2004-03-01'::date) AND (f2 <= '2004-03-31'::date) AND (f2 = '2004-02-15'::date)) Total runtime: 0.188 ms (12 rows) regression=# explain analyze select * from foo where f2 = '2004-feb-15'; QUERY PLAN -- Result (cost=1.00..10073.70 rows=20 width=16) (actual time=0.059..0.091 rows=1 loops=1) -> Append (cost=1.00..10073.70 rows=20 width=16) (actual time=0.055..0.086 rows=1 loops=1) -> Seq Scan on foo (cost=1.00..10022.50 rows=5 width=16) (actual time=0.001..0.001 rows=0 loops=1) Filter: (f2 = '2004-02-15'::date) -> Index Scan using foo_2004_01_idx2 on foo_2004_01 foo (cost=0.00..17.07 rows=5 width=16) (actual time=0.045..0.045 rows=0 loops=1) Index Cond: (f2 = '2004-02-15'::date) -> Index Scan using foo_2004_02_idx2 on foo_2004_02 foo (cost=0.00..17.07 rows=5 width=16) (actual time=0.008..0.009 rows=1 loops=1) Index Cond: (f2 = '2004-02-15'::date) -> Index Scan using foo_2004_03_idx2 on foo_2004_03 foo (cost=0.00..17.07 rows=5 width=16) (actual time=0.029..0.029 rows=0 loops=1) Index Cond: (f2 = '2004-02-15'::date) Total runtime: 0.191 ms (11 rows) The main difference being that the view needs to be recreated every time a table is added or dropped, whereas with the inherited tables method that isn't needed. Joe ---(end of broadcast)--- TIP 3: 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] Alternatives to Dell?
Josh Berkus wrote: Thing is, some companies are required to use 1st-tier or at least 2nd-tier vendors for hardware; they won't home-build. For those people, what vendors do others on this list recommend? What have been your good/bad experiences? I've had very good experiences with IBM hardware, and found their sales and support to be responsive. Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Trying to create multi db query in one large querie
Spiegelberg, Greg wrote: My experience with dblink() is that each dblink() is executed serially Correct. If you really want to do multiple queries simultaneously, you would need to write a function very similar to dblink_record, but using asynchonous libpq calls to both remote hosts. See: http://www.postgresql.org/docs/current/static/libpq-async.html HTH, Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] PostgreSQL vs. Oracle vs. Microsoft
Greg Sabino Mullane wrote: Don't forget your support contract cost, as well as licenses for each of your servers: development, testing, QA, etc. Is it really as "cheap" as 5K? I've heard that for any fairly modern system, it's much more, but that may be wrong. Sort of -- see: http://oraclestore.oracle.com/OA_HTML/ibeCCtpSctDspRte.jsp?section=15105 "It is available on single server systems supporting up to a maximum of 2 CPUs" Also note that most industrial strength features (like table partitioning, RAC, OLAP, Enterprise Manager plugins, etc, etc) are high priced options (mostly $10K to $20K per CPU) and they can only be used with the Enterprise edition (which is $40K/CPU *not* $2.5K/CPU). http://oraclestore.oracle.com/OA_HTML/ibeCCtpSctDspRte.jsp?section=10103 And you are correct, they expect to be paid for each dev, test, and QA machine too. The $5K edition is just there to get you hooked ;-) By the time you add up what you really want/need, figure you'll spend a couple of orders of magnatude higher, and then > 20% per year for ongoing maintenance/upgrades/support. Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] PostgreSQL vs. Oracle vs. Microsoft
Alex Turner wrote: I'm not advocating that people switch to Oracle at all, It's still much more expensive than Postgresql, and for most small and medium applications Postgresql is much easier to manage and maintain. I would just like to make sure people get their facts straight. I worked for a company that selected MS SQL Server because it was 'cheaper' than Oracle, when infact with the correct Oracle pricing, Oracle was cheaper, and had superior features. I would have prefered that they use Postgresql, which for the project in question would have been more appropriate and cost much less in hardware and software requirements, but they had to have 'Industry Standard'. Oracle ended up costing <$10k with licenses at $149 ea for 25 users, and the support contract wasn't that much of a bear - I can't remember exactly how much, I think it was around $1800/yr. My facts were straight, and they come from firsthand experience. The point is, it is easy to get trapped into thinking to yourself, "great, I can get a dual CPU oracle server for ~$10K, that's not too bad...". But then later you figure out you really need table partitioning or RAC, and suddenly you have to jump directly to multiple 6 figures. The entry level Oracle pricing is mainly a marketing gimmick -- it is intended to get you hooked. Also note that the per named user license scheme is subject to per CPU minimums that guarantee you'll never spend less than half the per CPU price. Oracle's licensing is so complex that there are businesses out there that subsist solely on helping companies figure it out to save money, and they take a cut of the savings. Oracle's own account reps had a hard time answering this question -- does a hyperthreaded Intel CPU count as 1 or 2 CPUs from a licensing standpoint? We were eventually told 1, but that the decision was "subject to change in the future". Joe ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] PostgreSQL vs. Oracle vs. Microsoft
Alex Turner wrote: I appreciate your information, but it's not valid. Most people don't need RAC or table partitioning. From a small company perspective, maybe, but not in the least invalid for larger companies. Many of the features in Oracle EE are just not available in Postgresql at all, and many aren't available in any version of SQL Server (table partitioning, bitmap indexes and others). I never claimed otherwise. I said the low end product gets you hooked. Once you're hooked, you'll start to wish for all the wiz-bang features -- after all, that's why you picked Oracle in the first place. Just because Oracle reps are a little clueless sometimes doesn't mean that the product pricing sucks. The minimum user requirement for standard one is 5 users. 5*149=$745, much less than half the price of a dual or single CPU config. And what happens once you need a quad server? I'm sorry that you had a bad experience with Oracle, but Oracle is a fine product, that is available for not alot of $$ if you are willing to use a bit of elbow grease to learn how it works and don't need enterprise features, which many other database product simply don't have, or work very poorly. I never said I had a "bad experience" with Oracle. I pointed out the gotchas. We have several large Oracle boxes running, several MSSQL, and several Postgres -- they all have their strengths and weaknesses. Nuff said -- this thread is way off topic now... Joe ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Flattening a kind of 'dynamic' table
Alexandre Leclerc wrote: Here a normal listing of design.product_department_time: product_id | department_id | req_time +---+-- 906 | A | 3000 906 | C | 3000 906 | D | 1935 907 | A | 1500 907 | C | 1500 907 | D | 4575 924 | A | 6000 924 | C | 1575 Sorry for jumping in on this thread so late -- I haven't been able to keep up with the lists lately. If I understand what you want correctly, you should be able to use crosstab from contrib/tablefunc: create table product_department_time(product_id int, department_id text, req_time int); insert into product_department_time values(906, 'A', 3000); insert into product_department_time values(906, 'C', 3000); insert into product_department_time values(906, 'D', 1935); insert into product_department_time values(907, 'A', 1500); insert into product_department_time values(907, 'C', 1500); insert into product_department_time values(907, 'D', 4575); insert into product_department_time values(924, 'A', 6000); insert into product_department_time values(924, 'C', 1575); select * from crosstab( 'select product_id, department_id, req_time from product_department_time order by 1', 'select ''A'' union all select ''C'' union all select ''D''' ) as (product_id int, a int, c int, d int); product_id | a | c | d +--+--+-- 906 | 3000 | 3000 | 1935 907 | 1500 | 1500 | 4575 924 | 6000 | 1575 | (3 rows) You could make this dynamic for new values of department_id by wrapping it with a PL/pgSQL function. HTH, Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Flattening a kind of 'dynamic' table
Alexandre Leclerc wrote: I'm a little bit confused on how to install this contirb. I know my contrib package is installed, but I don't know how to make it work in postgresql. (Using 7.4.5-1mdk on Mandrake Linux.) Find the file tablefunc.sql and redirect it into your database, e.g. psql mydatabase < /path/to/contrib/scripts/tablefunc.sql I have no idea where that would be on Mandrake, but you could probably do: locate tablefunc.sql On Fedora Core 1 I find it here: /usr/share/pgsql/contrib/tablefunc.sql Also find and read README.tablefunc. HTH, Joe ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] hardware and For PostgreSQL
I realize there are people who discourage looking at Dell, but i've been very happy with a larger ball of equipment we ordered recently from them. Our database servers consist of a PowerEdge 2950 connected to a PowerVault MD1000 with a 1 meter SAS cable. The 2950 tops out at dual quad core cpus, 32 gb ram, and 6 x 3.5" drives. It has a Perc 5/i as the controller of the in-box disks but then also has room for 2 Perc 5/e controllers that can allow connecting up to 2 chains of disk arrays to the thing. In our environment we started the boxes off at 8gb ram with 6 15k SAS disks in the server and then connected an MD1000 with 15 SATA disks to one of the Perc 5/e controllers. Gives tons of flexibility for growth and for tablespace usage depending on budget and what you can spend on your disks. We have everything on the SATA disks right now but plan to start moving the most brutalized indexes to the SAS disks very soon. If you do use Dell, get connected with a small business account manager for better prices and more attention. Joe Ketema Harris wrote: > I am trying to build a very Robust DB server that will support 1000+ > concurrent users (all ready have seen max of 237 no pooling being > used). i have read so many articles now that I am just saturated. I > have a general idea but would like feedback from others. > > I understand query tuning and table design play a large role in > performance, but taking that factor away > and focusing on just hardware, what is the best hardware to get for Pg > to work at the highest level > (meaning speed at returning results)? > > How does pg utilize multiple processors? The more the better? > Are queries spread across multiple processors? > Is Pg 64 bit? > If so what processors are recommended? > > I read this : > http://www.postgresql.org/files/documentation/books/aw_pgsql/hw_performance/node12.html > > POSTGRESQL uses a multi-process model, meaning each database > connection has its own Unix process. Because of this, all multi-cpu > operating systems can spread multiple database connections among the > available CPUs. However, if only a single database connection is > active, it can only use one CPU. POSTGRESQL does not use > multi-threading to allow a single process to use multiple CPUs. > > Its pretty old (2003) but is it still accurate? if this statement is > accurate how would it affect connection pooling software like pg_pool? > > RAM? The more the merrier right? Understanding shmmax and the pg > config file parameters for shared mem has to be adjusted to use it. > Disks? standard Raid rules right? 1 for safety 5 for best mix of > performance and safety? > Any preference of SCSI over SATA? What about using a High speed (fibre > channel) mass storage device? > > Who has built the biggest baddest Pg server out there and what do you > use? > > Thanks! > > > > > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] hardware and For PostgreSQL
Magnus Hagander wrote: > Ron St-Pierre wrote: > >> Joe Uhl wrote: >> >>> I realize there are people who discourage looking at Dell, but i've been >>> very happy with a larger ball of equipment we ordered recently from >>> them. Our database servers consist of a PowerEdge 2950 connected to a >>> PowerVault MD1000 with a 1 meter SAS cable. >>> >>> >>> >> We have a similar piece of equipment from Dell (the PowerEdge), and when >> we had a problem with it we received excellent service from them. When >> our raid controller went down (machine < 1 year old), Dell helped to >> diagnose the problem and installed a new one at our hosting facility, >> all within 24 hours. >> > > 24 hours?! I have a new one for my HP boxes onsite in 4 hours, including > a tech if needed... > > But I assume Dell also has service-agreement deals you can get to get > the level of service you'd want. (But you won't get it for a > non-brand-name server, most likely) > > Bottom line - don't underestimate the service you get from the vendor > when something breaks. Because eventually, something *will* break. > > > //Magnus > Yeah the response time depends on the service level purchased. I generally go with 24 hour because everything is redundant so a day of downtime isn't going to bring services down (though it could make them slow depending on what fails) but you can purchase 4 hr and in some cases even 2 hr. I had a "gold" level support contract on a server that failed awhile back and within 3 net hours they diagnosed and fixed the problem by getting onsite and replacing the motherboard and a cpu. I haven't had any of our 24hr support level devices fail yet so don't have anything to compare there. If you do go with Dell and want the higher support contracts i'll restate that a small business account is the way to go. Typically the prices are better to the point that a support level upgrade appears free when compared to the best shopping cart combo I can come up with. Joe ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] count * performance issue
Gregory, I just joined this listserv and was happy to see this posting. I have a 400GB table that I have indexed (building the index took 27 hours) , Loading the table with 10 threads took 9 hours. I run queries on the data nad get immediate max and min as well as other aggrgate functions very quickly, however a select count(*) of the table takes forever usually nearly an hour or more. Do you have any tuning recommendations. We in our warehouse use the count(*) as our verification of counts by day/month's etc and in Netezza its immediate. I tried by adding oids. BUT the situation I learned was that adding the oids in the table adds a significasnt amount of space to the data AND the index. As you may gather from this we are relatively new on Postgres. Any suggestions you can give me would be most helpful. Cheers, Joe On Mon, Mar 10, 2008 at 11:16 AM, Gregory Stark <[EMAIL PROTECTED]> wrote: > "Tom Lane" <[EMAIL PROTECTED]> writes: > > > Well, scanning an index to get a count might be significantly faster > > than scanning the main table, but it's hardly "instantaneous". It's > > still going to take time proportional to the table size. > > Hm, Mark's comment about bitmap indexes makes that not entirely true. A > bitmap > index can do RLE compression which makes the relationship between the size > of > the table and the time taken to scan the index more complex. In the > degenerate > case where there are no concurrent updates (assuming you can determine > that > quickly) it might actually be constant time. > > > Unless they keep a central counter of the number of index entries; > > which would have all the same serialization penalties we've talked > > about before... > > Bitmap indexes do in fact have concurrency issues -- arguably they're just > a > baroque version of this central counter in this case. > > -- > Gregory Stark > EnterpriseDB http://www.enterprisedb.com > Ask me about EnterpriseDB's Slony Replication support! > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Mirabili et Veritas Joe Mirabal
Re: [PERFORM] crosstab speed
Jeremiah Elliott wrote: ok, I have an application that I am trying to speed up. Its a reporting application that makes heavy use of the crosstab function. here is an example query The crostab function is taking between 5 and 15 seconds to return. Please run the two embedded queries independently, i.e. select ARRAY[site::text,product_line_description::text,report_sls::text,fy_period::text] as COL_HEADER, fy_year, sum(invoice_value) from order_data_tbl where fy_year is not null group by site::text,product_line_description::text,report_sls::text,fy_period::text, fy_year order by site::text,product_line_description::text,report_sls::text,fy_period::text; -- and -- select fy_year from order_data_tbl where fy_year is not null group by fy_year order by fy_year; How long does each take? crosstab cannot run any faster than the sum of these two queries run on their own. If the second one doesn't change often, can you pre-calculate it, perhaps once a day? Joe -- 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 CPU Utilization
Our production database is seeing very heavy CPU utilization - anyone have any ideas/input considering the following? CPU utilization gradually increases during the day until it approaches 90%-100% at our peak time. When this happens our transactions/sec drops and our site becomes very slow. When in this state, I can see hundreds of queries in pg_stat_activity that are not waiting on locks but sit there for minutes. When the database is not in this state, those same queries can complete in fractions of a second - faster that my script that watches pg_stat_activity can keep track of them. This server has dual quad core xeon 5310s, 32 GB RAM, and a few different disk arrays (all managed in hardware by either the Perc5/i or Perc5/e adapter). The Postgres data is on a 14 disk 7.2k SATA raid 10. This server runs nothing but Postgres. The PostgreSQL database (according to pg_database_size) is 55GB and we are running PostgreSQL 8.3.5 and the 2.6.28.7-2 kernel under Arch Linux. Right now (not under peak load) this server is running at 68% CPU utilization and its SATA raid 10 is doing about 2MB/s writes and 11MB/ s reads. When I run dd I can hit 200+MB/s writes and 230+ MB/s reads, so we are barely using the available IO. Further when I run dd the CPU utilization of that process only approaches 20%-30% of one core. Additionally, when I view "top -c" I generally see a dozen or so "idle" postgres processes (they appear and drop away quickly though) consuming very large chunks of CPU (as much as 60% of a core). At any given time we have hundreds of idle postgres processes due to the JDBC connection pooling but most of them are 0% as I would expect them to be. I also see selects and inserts consuming very large percentages of CPU but I view that as less strange since they are doing work. Any ideas as to what is causing our CPUs to struggle? Is the fact that our RAM covers a significant portion of the database causing our CPUs to do a bunch of thrashing as they work with memory while our disk controllers sit idle? According to top we barely use any swap. We currently have max_connections set to 1000 (roughly the sum of the JDBC pools on our application servers). Would decreasing this value help? We can decrease the JDBC pools or switch to pgbouncer for pooling if this is the case. Really just looking for any input/ideas. Our workload is primarily OLTP in nature - essentially a social network. By transactions/sec at the start I am using the xact_commit value in pg_stat_database. Please let me know if this value is not appropriate for getting a tps guess. Right now with the 60% CPU utilization and low IO use xact_commit is increasing at a rate of 1070 a second. I have an identical PITR slave I can pause the PITR sync on to run any test against. I will happily provide any additional information that would be helpful. Any assistance is greatly appreciated. Joe Uhl -- 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 CPU Utilization
Here is vmstat 1 30. We are under peak load right now so I can gather information from the real deal :) Had an almost complete lockup a moment ago, number of non-idle postgres connections was 637. Going to drop our JDBC pool sizes a bit and bounce everything. procs ---memory-- ---swap-- -io -system-- cpu r b swpd free buff cache si sobibo in cs us sy id wa 12 35 95056 11102380 56856 1495494834 669 54112 23 3 54 19 12 39 95056 11092484 56876 1496320400 6740 1204 10066 13277 91 5 0 4 8 42 95056 11081712 56888 1497224400 8620 1168 10659 17020 78 6 0 15 10 30 95052 11069768 56904 1498262800 8944 976 9809 15109 81 6 1 12 4 27 95048 11059576 56916 1499129600 8852 440 7652 13294 63 4 2 32 5 42 95048 11052524 56932 1499649600 4700 384 6383 11249 64 4 4 28 5 33 95048 11047492 56956 1500142800 3852 572 6029 14010 36 4 5 56 7 35 95048 11041184 56960 1500548000 3964 136 5042 10802 40 3 1 56 1 33 95048 11037988 56968 1500924000 3892 168 3384 6479 26 1 3 69 3 28 95048 11029332 56980 1501574400 6724 152 4964 12844 11 2 8 79 0 34 95048 11025880 56988 1502016800 3852 160 3616 8614 11 1 6 82 3 25 95048 10996356 57044 1504479600 7892 456 3126 7115 4 3 8 85 1 26 95048 10991692 57052 1505010000 5188 176 2566 5976 3 2 12 83 0 29 95048 10985408 57060 1505496800 420080 2586 6582 4 1 12 83 1 29 95048 10980828 57064 1505899200 456064 2966 7557 7 2 6 85 2 28 95048 10977192 57072 1506317600 386072 2695 6742 11 1 7 81 2 29 95048 10969120 57088 1506780800 508484 3296 8067 14 1 0 84 0 25 95048 10962096 57104 1507298400 4440 500 2721 6263 12 1 6 80 0 23 95044 10955320 57108 1507926000 5712 232 2678 5990 6 1 6 87 2 25 95044 10948644 57120 1508452400 5120 184 3499 8143 20 3 9 69 3 21 95044 10939744 57128 1509064400 5756 264 4724 10272 32 3 5 60 1 19 95040 10933196 57144 15095024 120 4440 180 2585 5244 13 2 15 70 0 21 95040 10927596 57148 1509868400 3248 136 2973 7292 8 1 9 81 1 20 95040 10920708 57164 1510424400 5192 360 1865 4547 3 1 9 87 1 24 95040 10914552 57172 1510585600 230816 1948 4450 6 1 1 93 0 24 95036 10909148 57176 1511024000 3824 152 1330 2632 3 1 6 90 1 21 95036 10900628 57192 1511633200 5680 180 1898 3986 4 1 11 84 0 19 95036 10888356 57200 1512173600 5952 120 2252 3991 12 1 8 79 2 22 95036 10874336 57204 1512825200 6320 112 2831 6755 5 2 8 85 3 26 95036 10857592 57220 1513402000 5124 216 3067 5296 32 6 3 59 Alan, my apologies if you get this twice. Didn't reply back to the list on first try. On Mar 16, 2009, at 3:52 PM, Alan Hodgson wrote: On Monday 16 March 2009, Joe Uhl wrote: Right now (not under peak load) this server is running at 68% CPU utilization and its SATA raid 10 is doing about 2MB/s writes and 11MB/ s reads. When I run dd I can hit 200+MB/s writes and 230+ MB/s reads, so we are barely using the available IO. Further when I run dd the CPU utilization of that process only approaches 20%-30% of one core. What does vmstat say when it's slow? The output of "vmstat 1 30" would be informative. note: dd is sequential I/O. Normal database usage is random I/O. -- Even a sixth-grader can figure out that you can’t borrow money to pay off your debt -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org ) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- 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 CPU Utilization
I dropped the pool sizes and brought things back up. Things are stable, site is fast, CPU utilization is still high. Probably just a matter of time before issue comes back (we get slammed as kids get out of school in the US). Now when I run vmtstat 1 30 it looks very different (below). Waiting is minimal, user is very high. Under nontrivial load, according to xact_commit in pg_stat_database we are doing 1800+ tps. Appreciate the input and explanation on vmstat. I am going to throw some of these numbers into zabbix so I can keep a better eye on them. This server is a couple years old so the purchase of a new controller and/or disks is not out of the question. On final note, have made several changes to postgresql.conf. Some of those here: max_connections = 1000 shared_buffers = 7680MB work_mem = 30MB synchronous_commit = off checkpoint_segments = 50 effective_cache_size = 2MB procs ---memory-- ---swap-- -io -system-- cpu r b swpd free buff cache si sobibo in cs us sy id wa 9 8 73036 500164 82200 2349774834 669 54111 23 3 54 19 20 4 73036 497252 82200 2350083600 2500 680 11145 15168 91 4 2 2 21 1 73036 491416 82204 2350383200 1916 920 10303 14032 94 4 1 1 23 5 73036 489580 82212 2350586000 1348 3296 11682 15970 94 5 1 0 31 1 73036 481408 82220 2350775200 984 8988 10123 11289 97 3 0 0 25 4 73036 483248 82232 2350942000 1268 1312 10705 14063 96 4 0 0 23 4 73036 480096 82232 2351238000 2372 472 9805 13996 94 5 1 1 24 4 73036 476732 82236 2351519600 2012 720 10365 14307 96 3 1 0 22 1 73036 474468 82236 2351658400 944 3108 9838 12831 95 4 1 0 14 1 73036 455756 82284 2353454800 908 3284 9096 11333 94 4 1 0 10 2 73036 455224 82292 2353630400 1760 416 12454 17736 89 6 3 2 17 0 73036 460620 82292 235300 1292 968 12030 18333 90 7 2 1 13 4 73036 459764 82292 2353972400 332 288 9722 14197 92 5 2 1 17 5 73036 457516 82292 2354217600 1872 17752 10458 15465 91 5 2 1 19 4 73036 450804 82300 2354564000 2980 640 10602 15621 90 6 2 2 24 0 73036 447660 82312 2354764400 1736 10724 12401 15413 93 6 1 0 20 6 73036 444380 82320 2355069200 2064 476 9008 10985 94 4 1 0 22 2 73036 442880 82328 2355364000 2496 3156 10739 15211 93 5 1 1 11 1 73036 441448 82328 2355563200 1452 3552 10812 15337 93 5 2 1 6 2 73036 439812 82348 2355742000 1052 1128 8603 10514 91 3 3 2 6 3 73036 433456 82348 2356086000 2484 656 7636 13033 68 4 14 14 6 3 73036 433084 82348 2356262800 1400 408 6046 11778 70 3 18 9 5 0 73036 430776 82356 2356426400 1108 1300 7549 13754 73 4 16 7 5 2 73036 430124 82360 2356558000 1016 2216 7844 14507 72 4 18 6 4 2 73036 429652 82380 2356748000 1168 2468 7694 15466 58 4 24 14 6 2 73036 427304 82384 2356966800 1132 752 5993 13606 49 5 36 10 7 1 73036 423020 82384 2357193200 1244 824 8085 18072 56 3 30 10 procs ---memory-- ---swap-- -io -system-- cpu r b swpd free buff cache si sobibo in cs us sy id wa 4 0 73036 420816 82392 2357382400 1292 820 5370 10958 46 2 41 10 9 1 73020 418048 82392 23576900 520 1632 2592 5931 11629 60 3 29 8 4 2 73004 415164 82424 23578620 560 1812 4116 7503 14674 71 3 15 12 On Mar 16, 2009, at 4:19 PM, Dave Youatt wrote: Last column "wa" is % cpu time spent waiting (for IO to complete). 80s and 90s is pretty high, probably too high. Might also want to measure the IO/s performance of your RAID controller. From the descriptions, it will be much more important that long sequential reads/writes for characterizing your workload. There are also some disappointing HW RAID controllers out there. Generally, Aretec and Promise are good, Adaptec good, depending on model, and the ones that Dell ship w/their servers haven't had good reviews/reports. On 03/16/2009 01:10 PM, Joe Uhl wrote: Here is vmstat 1 30. We are under peak load right now so I can gather information from the real deal :) Had an almost complete lockup a moment ago, number of non-idle postgres connections was 637. Going to drop our JDBC pool sizes a bit and bounce everything. procs ---memory-- ---swap-- -io -system-- cpu r b swpd free buff cache si sobibo in cs us sy id wa 12 35 95056 11102380 56856 1495494834 669 54112 23 3 54 19 12 39 95056 11092484 56876 1496320400 6740 1204 10066 13277 91 5 0 4 8 42 95056
Re: [PERFORM] High CPU Utilization
On Mar 17, 2009, at 12:19 AM, Greg Smith wrote: On Tue, 17 Mar 2009, Gregory Stark wrote: Hm, well the tests I ran for posix_fadvise were actually on a Perc5 -- though who knows if it was the same under the hood -- and I saw better performance than this. I saw about 4MB/s for a single drive and up to about 35MB/s for 15 drives. However this was using linux md raid-0, not hardware raid. Right, it's the hardware RAID on the Perc5 I think people mainly complain about. If you use it in JBOD mode and let the higher performance CPU in your main system drive the RAID functions it's not so bad. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD I have not yet had a chance to try software raid on the standby server (still planning to) but wanted to follow up to see if there was any good way to figure out what the postgresql processes are spending their CPU time on. We are under peak load right now, and I have Zabbix plotting CPU utilization and CPU wait (from vmstat output) along with all sorts of other vitals on charts. CPU utilization is a sustained 90% - 95% and CPU Wait is hanging below 10%. Since being pointed at vmstat by this list I have been watching CPU Wait and it does get high at times (hence still wanting to try Perc5 in JBOD) but then there are sustained periods, right now included, where our CPUs are just getting crushed while wait and IO (only doing about 1.5 MB/sec right now) are very low. This high CPU utilization only occurs when under peak load and when our JDBC pools are fully loaded. We are moving more things into our cache and constantly tuning indexes/tables but just want to see if there is some underlying cause that is killing us. Any recommendations for figuring out what our database is spending its CPU time on? -- 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 CPU Utilization
On Mar 20, 2009, at 4:29 PM, Scott Marlowe wrote: On Fri, Mar 20, 2009 at 2:26 PM, Joe Uhl wrote: On Mar 17, 2009, at 12:19 AM, Greg Smith wrote: On Tue, 17 Mar 2009, Gregory Stark wrote: Hm, well the tests I ran for posix_fadvise were actually on a Perc5 -- though who knows if it was the same under the hood -- and I saw better performance than this. I saw about 4MB/s for a single drive and up to about 35MB/s for 15 drives. However this was using linux md raid-0, not hardware raid. Right, it's the hardware RAID on the Perc5 I think people mainly complain about. If you use it in JBOD mode and let the higher performance CPU in your main system drive the RAID functions it's not so bad. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD I have not yet had a chance to try software raid on the standby server (still planning to) but wanted to follow up to see if there was any good way to figure out what the postgresql processes are spending their CPU time on. We are under peak load right now, and I have Zabbix plotting CPU utilization and CPU wait (from vmstat output) along with all sorts of other vitals on charts. CPU utilization is a sustained 90% - 95% and CPU Wait is hanging below 10%. Since being pointed at vmstat by this list I have been watching CPU Wait and it does get high at times (hence still wanting to try Perc5 in JBOD) but then there are sustained periods, right now included, where our CPUs are just getting crushed while wait and IO (only doing about 1.5 MB/sec right now) are very low. This high CPU utilization only occurs when under peak load and when our JDBC pools are fully loaded. We are moving more things into our cache and constantly tuning indexes/tables but just want to see if there is some underlying cause that is killing us. Any recommendations for figuring out what our database is spending its CPU time on? What does the cs entry on vmstat say at this time? If you're cs is skyrocketing then you're getting a context switch storm, which is usually a sign that there are just too many things going on at once / you've got an old kernel things like that. cs column (plus cpu columns) of vmtstat 1 30 reads as follows: csus sy id wa 11172 95 4 1 0 12498 94 5 1 0 14121 91 7 1 1 11310 90 7 1 1 12918 92 6 1 1 10613 93 6 1 1 9382 94 4 1 1 14023 89 8 2 1 10138 92 6 1 1 11932 94 4 1 1 15948 93 5 2 1 12919 92 5 3 1 10879 93 4 2 1 14014 94 5 1 1 9083 92 6 2 0 11178 94 4 2 0 10717 94 5 1 0 9279 97 2 1 0 12673 94 5 1 0 8058 82 17 1 1 8150 94 5 1 1 11334 93 6 0 0 13884 91 8 1 0 10159 92 7 0 0 9382 96 4 0 0 11450 95 4 1 0 11947 96 3 1 0 8616 95 4 1 0 10717 95 3 1 0 We are running on 2.6.28.7-2 kernel. I am unfamiliar with vmstat output but reading the man page (and that cs = "context switches per second") makes my numbers seem very high. Our sum JDBC pools currently top out at 400 connections (and we are doing work on all 400 right now). I may try dropping those pools down even smaller. Are there any general rules of thumb for figuring out how many connections you should service at maximum? I know of the memory constraints, but thinking more along the lines of connections per CPU core. -- 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 CPU Utilization
On Mar 20, 2009, at 4:58 PM, Scott Marlowe wrote: On Fri, Mar 20, 2009 at 2:49 PM, Joe Uhl wrote: On Mar 20, 2009, at 4:29 PM, Scott Marlowe wrote: What does the cs entry on vmstat say at this time? If you're cs is skyrocketing then you're getting a context switch storm, which is usually a sign that there are just too many things going on at once / you've got an old kernel things like that. cs column (plus cpu columns) of vmtstat 1 30 reads as follows: csus sy id wa 11172 95 4 1 0 12498 94 5 1 0 14121 91 7 1 1 11310 90 7 1 1 12918 92 6 1 1 10613 93 6 1 1 9382 94 4 1 1 14023 89 8 2 1 10138 92 6 1 1 11932 94 4 1 1 15948 93 5 2 1 12919 92 5 3 1 10879 93 4 2 1 14014 94 5 1 1 9083 92 6 2 0 11178 94 4 2 0 10717 94 5 1 0 9279 97 2 1 0 12673 94 5 1 0 8058 82 17 1 1 8150 94 5 1 1 11334 93 6 0 0 13884 91 8 1 0 10159 92 7 0 0 9382 96 4 0 0 11450 95 4 1 0 11947 96 3 1 0 8616 95 4 1 0 10717 95 3 1 0 We are running on 2.6.28.7-2 kernel. I am unfamiliar with vmstat output but reading the man page (and that cs = "context switches per second") makes my numbers seem very high. No, those aren't really all that high. If you were hitting cs contention, I'd expect it to be in the 25k to 100k range. <10k average under load is pretty reasonable. Our sum JDBC pools currently top out at 400 connections (and we are doing work on all 400 right now). I may try dropping those pools down even smaller. Are there any general rules of thumb for figuring out how many connections you should service at maximum? I know of the memory constraints, but thinking more along the lines of connections per CPU core. Well, maximum efficiency is usually somewhere in the range of 1 to 2 times the number of cores you have, so trying to get the pool down to a dozen or two connections would be the direction to generally head. May not be reasonable or doable though. Thanks for the info. Figure I can tune our pools down and monitor throughput/CPU/IO and look for a sweet spot with our existing hardware. Just wanted to see if tuning connections down could potentially help. I feel as though we are going to have to replicate this DB before too long. We've got an almost identical server doing nothing but PITR with 8 CPU cores mostly idle that could be better spent. Our pgfouine reports, though only logging queries that take over 1 second, show 90% reads. I have heard much about Slony, but has anyone used the newer version of Mammoth Replicator (or looks to be called PostgreSQL + Replication now) on 8.3? From the documentation, it appears to be easier to set up and less invasive but I struggle to find usage information/stories online. -- 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 CPU Utilization
On Mar 20, 2009, at 4:58 PM, Scott Marlowe wrote: On Fri, Mar 20, 2009 at 2:49 PM, Joe Uhl wrote: On Mar 20, 2009, at 4:29 PM, Scott Marlowe wrote: What does the cs entry on vmstat say at this time? If you're cs is skyrocketing then you're getting a context switch storm, which is usually a sign that there are just too many things going on at once / you've got an old kernel things like that. cs column (plus cpu columns) of vmtstat 1 30 reads as follows: csus sy id wa 11172 95 4 1 0 12498 94 5 1 0 14121 91 7 1 1 11310 90 7 1 1 12918 92 6 1 1 10613 93 6 1 1 9382 94 4 1 1 14023 89 8 2 1 10138 92 6 1 1 11932 94 4 1 1 15948 93 5 2 1 12919 92 5 3 1 10879 93 4 2 1 14014 94 5 1 1 9083 92 6 2 0 11178 94 4 2 0 10717 94 5 1 0 9279 97 2 1 0 12673 94 5 1 0 8058 82 17 1 1 8150 94 5 1 1 11334 93 6 0 0 13884 91 8 1 0 10159 92 7 0 0 9382 96 4 0 0 11450 95 4 1 0 11947 96 3 1 0 8616 95 4 1 0 10717 95 3 1 0 We are running on 2.6.28.7-2 kernel. I am unfamiliar with vmstat output but reading the man page (and that cs = "context switches per second") makes my numbers seem very high. No, those aren't really all that high. If you were hitting cs contention, I'd expect it to be in the 25k to 100k range. <10k average under load is pretty reasonable. Our sum JDBC pools currently top out at 400 connections (and we are doing work on all 400 right now). I may try dropping those pools down even smaller. Are there any general rules of thumb for figuring out how many connections you should service at maximum? I know of the memory constraints, but thinking more along the lines of connections per CPU core. Well, maximum efficiency is usually somewhere in the range of 1 to 2 times the number of cores you have, so trying to get the pool down to a dozen or two connections would be the direction to generally head. May not be reasonable or doable though. Turns out we may have an opportunity to purchase a new database server with this increased load. Seems that the best route, based on feedback to this thread, is to go whitebox, get quad opterons, and get a very good disk controller. Can anyone recommend a whitebox vendor? Is there a current controller anyone on this list has experience with that they could recommend? This will be a bigger purchase so will be doing research and benchmarking but any general pointers to a vendor/controller greatly appreciated. -- 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] Utilizing multiple cores in a function call.
Hartman, Matthew wrote: I'm pretty much at that point where I've chewed the fat off of the algorithm, or at least at my personal limits. Occasionally a new idea pops into my head and yields an improvement but it's in the order of 100-250ms. Google came back with "no sir". It seems PostgreSQL is limited to one CPU per query unless I spawn a master/controller like you suggested. Shame.. Although I have never done it myself, you might try using PL/R to perform the algo in R, and make use of snow package to run parallel tasks -- see: http://cran.r-project.org/web/views/HighPerformanceComputing.html Joe -- 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] Best suiting OS
S Arvind wrote: Hi everyone, What is the best Linux flavor for server which runs postgres alone. The postgres must handle greater number of database around 200+. Performance on speed is the vital factor. Is it FreeBSD, CentOS, Fedora, Redhat xxx?? -Arvind S We use Arch Linux and love it. It does not have "versions" - you just keep updating your install and never have to do a major version upgrade. It is a bare bones distribution with excellent package management and repositories, virtually no distribution cruft, and a fantastic community/wiki/forum. As a warning no one offers support for Arch that I know of and the packages are generally very current with the latest which is both a good and bad thing. For a production environment you have to be very careful about when you do upgrades and preferably can test upgrades on QA machines before running on production. You also want to make sure and exclude postgresql from updates so that it doesn't do something like pull down 8.4 over an 8.3.x installation without you being backed up and ready to restore. PostgreSQL is currently at 8.4.1 in their repositories. With that disclaimer out of the way it is my favorite Linux distribution and I am running it on a couple dozens servers at the moment ranging from puny app servers to 8 core, 32GB+ RAM, 30-40 disk database servers. If you are comfortable with Linux it is worth checking out (on your personal machine or QA environment first). I've run dozens of distributions and this works well for us (a startup with nontrivial Linux experience). I imagine at a larger company it definitely would not be an option. Joe Uhl -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Partitioned Tables and ORDER BY
We have been using partitioning for some time with great success. Up until now our usage has not included ordering and now that we are trying to use an ORDER BY against an indexed column a rather significant shortcoming seems to be kicking in. Parent table (have cut all but 4 columns to make it easier to post about) CREATE TABLE people ( person_id character varying(36) NOT NULL, list_id character varying(36) NOT NULL, first_name character varying(255), last_name character varying(255), CONSTRAINT people_pkey (person_id, list_id) ); A partition looks like this: CREATE TABLE people_list1 ( -- inherited columns omitted CONSTRAINT people_list1_list_id_check CHECK (list_id::text = 'the_unique_list_id'::text) ) INHERITS (people); Both the parent and the children have indexes on all 4 columns mentioned above. The parent table is completely empty. If I run this query, directly against the partition, performance is excellent: select * from people_list1 order by first_name asc limit 50; The explain analyze output: Limit (cost=0.00..4.97 rows=50 width=34315) (actual time=49.616..522.464 rows=50 loops=1) -> Index Scan using idx_people_first_name_list1 on people_list1 (cost=0.00..849746.98 rows=8544854 width=34315) (actual time=49.614..522.424 rows=50 loops=1) Total runtime: 522.773 ms If I run this query, against the parent, performance is terrible: select * from people where list_id = 'the_unique_list_id' order by first_name asc limit 50; The explain analyze output: Limit (cost=726844.88..726845.01 rows=50 width=37739) (actual time=149864.869..149864.884 rows=50 loops=1) -> Sort (cost=726844.88..748207.02 rows=8544855 width=37739) (actual time=149864.868..149864.876 rows=50 loops=1) Sort Key: public.people.first_name Sort Method: top-N heapsort Memory: 50kB -> Result (cost=0.00..442990.94 rows=8544855 width=37739) (actual time=0.081..125837.332 rows=8545138 loops=1) -> Append (cost=0.00..442990.94 rows=8544855 width=37739) (actual time=0.079..03.743 rows=8545138 loops=1) -> Index Scan using people_pkey on people (cost=0.00..4.27 rows=1 width=37739) (actual time=0.008..0.008 rows=0 loops=1) Index Cond: ((list_id)::text = 'the_unique_list_id'::text) -> Seq Scan on people_list1 people (cost=0.00..442986.67 rows=8544854 width=34315) (actual time=0.068..109781.308 rows=8545138 loops=1) Filter: ((list_id)::text = 'the_unique_list_id'::text) Total runtime: 149865.411 ms Just to show that partitions are setup correctly, this query also has excellent performance: select * from people where list_id = 'the_unique_list_id' and first_name = 'JOE'; Here is the explain analyze for that: Result (cost=0.00..963.76 rows=482 width=37739) (actual time=6.031..25.394 rows=2319 loops=1) -> Append (cost=0.00..963.76 rows=482 width=37739) (actual time=6.029..21.340 rows=2319 loops=1) -> Index Scan using idx_people_first_name on people (cost=0.00..4.27 rows=1 width=37739) (actual time=0.010..0.010 rows=0 loops=1) Index Cond: ((first_name)::text = 'JOE'::text) Filter: ((list_id)::text = 'the_unique_list_id'::text) -> Bitmap Heap Scan on people_list1 people (cost=8.47..959.49 rows=481 width=34315) (actual time=6.018..20.968 rows=2319 loops=1) Recheck Cond: ((first_name)::text = 'JOE'::text) Filter: ((list_id)::text = 'the_unique_list_id'::text) -> Bitmap Index Scan on idx_people_first_name_list1 (cost=0.00..8.35 rows=481 width=0) (actual time=5.566..5.566 rows=2319 loops=1) Index Cond: ((first_name)::text = 'JOE'::text) Total runtime: 25.991 ms This is Postgres 8.3.7 on the 2.6.28 kernel with constraint_exclusion on. Our partitions are in the 8 - 15 million row range. I realize one option is to hit the partition directly instead of hitting the parent table with the check constraint in the WHERE clause, but up until now we have been able to avoid needing partition-awareness in our code. Perhaps we have hit upon something that will require breaking that cleanliness but wanted to see if there were any workarounds. -- 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] Partitioned Tables and ORDER BY
This seems like a pretty major weakness in PostgreSQL partitioning. I have essentially settled on not being able to do queries against the parent table when I want to order the results. Going to have to use a Hibernate interceptor or something similar to rewrite the statements so they hit specific partitions, will be working on this in the coming week. This weakness is a bummer though as it makes partitions a lot less useful. Having to hit specific child tables by name isn't much different than just creating separate tables and not using partitions at all. Michal Szymanski wrote: I've described our problem here http://groups.google.pl/group/pgsql.performance/browse_thread/thread/54a7419381bd1565?hl=pl# Michal Szymanski http://blog.szymanskich.net http://techblog.freeconet.pl/ -- 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] Performance with sorting and LIMIT on partitioned table
On Mon, Oct 12, 2009 at 10:14 AM, Michal Szymanski wrote: We have performance problem with query on partitioned table when query use order by and we want to use first/last rows from result set. More detail description: We have big table where each row is one telephone call (CDR). Definitnion of this table look like this: CREATE TABLE accounting.cdr_full_partitioned (it is parrent table) ( cdr_id bigint NOT NULL, id_crx_group_from bigint, -- identifier of user start_time_invite timestamp with time zone, -- start call time call_status VARCHAR -- FINF-call finished, FINC-call unfinished ..some extra data.. ) We creating 12 partitions using 'start_time_invite' column, simply we create one partition for each month. We create costraints like this: ALTER TABLE accounting.cdr_y2009_m09 ADD CONSTRAINT y2009m09 CHECK (start_time_invite>= '2009-09-01 00:00:00+02'::timestamp with time zone AND start_time_invite< '2009-10-01 00:00:00+02'::timestamp with time zone); and we define necessery indexes of course CREATE INDEX cdr_full_partitioned_y2009_m09_id_crx_group_to_key1 ON accounting.cdr_full_partitioned_y2009_m09 USING btree (id_crx_group_from, start_time_invite, call_status); The problem appears when we want to select calls for specified user with specified call_Status e.g: SELECT * FROM accounting.cdr_full_partitioned WHERE id_crx_group_from='522921' AND call_status='FINS' AND start_time_invite>='2009-09-28 00:00:00+02' AND start_time_invite<'2009-10-12 23:59:59+02' AND ORDER BY start_time_invite LIMIT '100' OFFSET 0 you can see execution plan http://szymanskich.net/pub/postgres/full.jpg as you see 2 rows were selected and after were sorted what take very long about 30-40s and after sorting it limit result to 100 rows. Using table without partition SELECT * FROM accounting.cdr_fullWHERE (id_crx_group_from='522921') AND ( call_status='FINS' ) AND (start_time_invite>='2009-01-28 00:00:00+02') AND (start_time_invite<'2009-10-12 23:59:59+02') ORDER BY start_time_invite LIMIT '100' OFFSET 0 execution plan is very simple "Limit (cost=0.00..406.40 rows=100 width=456)" " ->Index Scan using cdr_full_crx_group_from_start_time_invite_status_ind on cdr_full (cost=0.00..18275.76 rows=4497 width=456)" "Index Cond: ((id_crx_group_from = 522921::bigint) AND (start_time_invite>= '2009-01-27 23:00:00+01'::timestamp with time zone) AND (start_time_invite< '2009-10-12 23:59:59+02'::timestamp with time zone) AND ((call_status)::text = 'FINS'::text))" it use index to fetch first 100 rows and it is super fast and take less than 0.5s. There is no rows sorting! I've tried to execute the same query on one partition: SELECT * FROM accounting.cdr_full_partitioned_y2009_m09 WHERE (id_crx_group_from='509498') AND ( call_status='FINS' ) AND (start_time_invite>='2009-09-01 00:00:00+02') AND (start_time_invite<'2009-10-12 23:59:59+02') You can see execution plan http://szymanskich.net/pub/postgres/ononeprtition.jpg and query is superfast because there is no sorting. The question is how to speed up query when we use partitioning? So far I have not found solution. I'm wonder how do you solve problems when result from partition must be sorted and after we want to display only first/last 100 rows? We can use own partitioning mechanism and partitioning data using id_crx_group_from and create dynamic query (depending on id_crx_group_from we can execute query on one partition) but it is not most beautiful solution. Yeah - unfortunately the query planner is not real smart about partitioned tables yet. I can't make anything of the JPG link you posted. Can you post the EXPLAIN ANALYZE output for the case that is slow? What PG version is this? ...Robert I have a similar, recent thread titled Partitioned Tables and ORDER BY with a decent break down. I think I am hitting the same issue Michal is. Essentially doing a SELECT against the parent with appropriate constraint columns in the WHERE clause is very fast (uses index scans against correct child table only) but the moment you add an ORDER BY it seems to be merging the parent (an empty table) and the child, sorting the results, and sequential scanning. So it does still scan only the appropriate child table in the end but indexes are useless. Unfortunately the only workaround I can come up with is to query the partitioned child tables directly. In my case the partitions are rather large so the timing difference is 522ms versus 149865ms.
Re: [PERFORM] Performance with sorting and LIMIT on partitioned table
On Mon, Oct 19, 2009 at 6:58 AM, Joe Uhl wrote: I have a similar, recent thread titled Partitioned Tables and ORDER BY with a decent break down. I think I am hitting the same issue Michal is. Essentially doing a SELECT against the parent with appropriate constraint columns in the WHERE clause is very fast (uses index scans against correct child table only) but the moment you add an ORDER BY it seems to be merging the parent (an empty table) and the child, sorting the results, and sequential scanning. So it does still scan only the appropriate child table in the end but indexes are useless. Unfortunately the only workaround I can come up with is to query the partitioned child tables directly. In my case the partitions are rather large so the timing difference is 522ms versus 149865ms. These questions are all solvable depending on what you define 'solution' as. I would at this point be thinking in terms of wrapping the query in a function using dynamic sql in plpgsql...using some ad hoc method of determining which children to hit and awkwardly looping them and enforcing limit, ordering, etc at that level. Yes, it sucks, but it only has to be done for classes of queries constraint exclusion can't handle and you will only handle a couple of cases most likely. For this reason, when I set up my partitioning strategies, I always try to divide the data such that you rarely if ever, have to fire queries that have to touch multiple partitions simultaneously. merlin This definitely sounds like a workable approach. I am doing something a little similar on the insert/update side to trick hibernate into writing data correctly into partitioned tables when it only knows about the parent. For anyone else hitting this issue and using hibernate my solution on the select side ended up being session-specific hibernate interceptors that rewrite the from clause after hibernate prepares the statement. This seems to be working alright especially since in our case the code, while not aware of DB partitioning, has the context necessary to select the right partition under the hood. Thankfully we haven't yet had queries that need to hit multiple partitions so this works okay without too much logic for now. I suppose if I needed to go multi-partition on single queries and wanted to continue down the hibernate interceptor path I could get more creative with the from clause rewriting and start using UNIONs, or switch to a Postgres-level solution like you are describing.
Re: [PERFORM] Advice requested on structuring aggregation queries
On 02/22/2010 07:01 PM, Dave Crooke wrote: > The original data is keyed by a 3-ary tuple of strings to keep the > row size down, in the new data model I'm actually storing 32-bit int's > in Postgres. The new schema for each table looks like this: > > (a integer, > b integer, > c integer, > ts timestamp without timezone, > value double precision) > > with two indexes: (a, b, ts) and (b, ts) [...snip...] > There are about 60 different values of b, and for each such value there > is a exactly one type of rollup. The old code is doing the rollups in > Postgres with 60 bulk "insert into select" statements, hence the > need for the second index. [...snip...] > For better scalability, I am partitioning these tables by time I am > not using PG's table inheritance and triggers to do the partitioning, > but instead dynamically generating the SQL and table names in the > application code (Java). In most cases, the rollups will still happen > from a single source "data_table" and I plan to continue using the > existing SQL, but I have a few cases where the source "data_table" rows > may actually come from two adjacent tables. Without going through your very long set of questions in detail, it strikes me that you might be better off if you: 1) use PostgreSQL partitioning (constraint exclusion) 2) partition by ts range 3) consider also including b in your partitioning scheme 4) create one index as (ts, a) 5) use dynamically generated SQL and table names in the application code to create (conditionally) and load the tables But of course test both this and your proposed method and compare ;-) Also you might consider PL/R for some of your analysis (e.g. mode would be simple, but perhaps not as fast): http://www.joeconway.com/web/guest/pl/r HTH, Joe signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Pgsql - Red Hat Linux - VS MySQL VS MSSQL
Balazs Wellisch wrote: I don't have months to learn the ins and outs of PostgreSQL performance tuning so I looked around to see if there are any preconfigured solutions out there. I don't know of a preconfigured solution. Generally speaking, the best configuration will be highly dependent on your hardware, data, and application. Hat Advanced Server and install Red Hat Database 2.1? (Let's say money is no object) There are many Linux and other OS distributions that will work just fine. You may need to tweak a few kernel configuration parameters, but that's not too difficult; see: http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=kernel-resources.html I would *not* use the default version of Postgres shipped with any particular distribution. Use 7.3.3 because that is the latest released version. Or, as Shridhar mentioned in his post, the are a number of pretty significant performance improvements in 7.4 (which is in feature freeze and scheduled to go into beta on 21 July). If you are in an exploratory/test phase rather than production right now, I'd say use the 7.4 beta for your comparisons. If money is truly not a problem, but time is, my advice is to hire a consultant. There are probably several people on this list that can fill that role for you. Otherwise read the archives and ask lots of specific questions. Joe ---(end of broadcast)--- TIP 3: 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] Pgsql - Red Hat Linux - VS MySQL VS MSSQL
Balazs Wellisch wrote: I would *not* use the default version of Postgres shipped with any particular distribution. Use 7.3.3 because that is the latest released version. Or, as Shridhar mentioned in his post, the are a number of pretty significant performance improvements in 7.4 (which is in feature freeze and scheduled to go into beta on 21 July). If you are in an exploratory/test phase rather than production right now, I'd say use the 7.4 beta for your comparisons. Well, I could start by testing 7.4, however I'd have to go back to the stable version once we're ready to use it a production environment. So, I might as well stick with eveluating the production version. How soon do you think you'll be in production? PostgreSQL beta testing usually seems to run about 2 months or so -- if you won't be in production before October, it is a good bet that Postgres 7.4 will be out or at least in release candidate by then. But it really depends on your specific application. If you use lots of "WHERE foo IN (SELECT ...)" type queries, you'll need to rewrite them in 7.3.3 or earlier, but in 7.4 they will probably work fine. Also, if you do much in the way of aggregate queries for reporting, 7.4 will likely give you a significant performance boost. If money is truly not a problem, but time is, my advice is to hire a consultant. There are probably several people on this list that can fill that role for you. Otherwise read the archives and ask lots of specific questions. Once we're ready to go with postgresql in a production environment we may indeed need to hire a consultant. Any suggestions whom I should contact? (We're in the San Diego area) Um, actually, I live in the San Diego area ;-) Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Hardware performance
Balazs Wellisch wrote: first of all I'd like to thank everyone who responded to my earlier post. I have a much better understanding of postgres performance tuning now. In case anyone's interested we've decided to go with RH9 and PostgreSQL 7.3 and we'll do the OS and DB tuning ourselves. (should be a good learning experience) Good choice! I think you'll find that this list will be a great resource as you learn. One point here is that you should use 7.3.3 (latest release version) instead of the version of Postgres in the distribution. Also, you might want to rebuild the RPMs from source using "--target i686". We have the budget for 5 drives. Does anyone have any real world experience with what hard drive configuration works best for postgres? This is going to be a dedicated DB server. There are going to be a large number of transactions being written to the database. To an extent it depends on how big the drives are and how large you expect the database to get. For maximal performance you want RAID 1+0 for data and WAL; and you want OS, data, and WAL each on their own drives. So with 5 drives one possible configuration is: 1 drive OS: OS on it's own drive makes it easy to upgrade, or restore the OS from CD if needed 2 drives, RAID 1+0: WAL 2 drives, RAID 1+0: data But I've seem reports that with fast I/O subsystems, there was no measurable difference with WAL separated from data. And to be honest, I've never personally found it necessary to separate WAL from data. You may want to test with WAL on the same volume as the data to see if there is enough difference to warrant separating it or not given your load and your actual hardware. If not, use 1 OS drive and 4 RAID 1+0 drives as one volume. You never want find any significant use of hard disk based swap space -- if you see that, you are probably misconfigured, and performance will be poor no matter how you've set up the drives. And there will be some moderately complex queries run concurrently to present this information in the form of various reports on the web. Once you have some data on your test server, and you have complex queries to tune, there will be a few details you'll get asked every time if you don't provide them when posting a question to the list: 1) Have you been running VACUUM and ANALYZE (or VACUUM ANALYZE) at appropriate intervals? 2) What are the table definitions and indexes for all tables involved? 3) What is the output of EXPLAIN ANALYZE? HTH, Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Hardware performance
Hannu Krosing wrote: How do you do RAID 1+0 with just two drives ? Hmm, good point -- I must have been tired last night ;-). With two drives you can do mirroring or striping, but not both. Usually I've seen a pair of mirrored drives for the OS, and a RAID 1+0 array for data. But that requires 6 drives, not 5. On non-database servers usually the data array is RAID 5, and you could get away with 5 drives (as someone else pointed out). As I said, I've never personally found it necessary to move WAL off to a different physical drive. What do you think is the best configuration given the constraint of 5 drives? 1 drive for OS, and 4 for RAID 1+0 for data-plus-WAL? I guess the ideal would be to find enough money for that 6th drive, use the mirrored pair for both OS and WAL. Joe ---(end of broadcast)--- TIP 3: 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] Hardware performance
Adam Witney wrote: I think the issue from the original posters point of view is that the Dell PE2650 can only hold a maximum of 5 internal drives True enough, but maybe that's a reason to be looking at other alternatives. I think he said the hardware hasn't been bought yet. Joe ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Hardware performance
Jean-Luc Lachance wrote: I am currious. How can you have RAID 1+0 with only 2 drives? If you are thinking about partitioning the drives, wont this defeate the purpose? Yeah -- Hannu already pointed out that my mind was fuzzy when I made that statement :-(. See subsequent posts. Joe ---(end of broadcast)--- TIP 3: 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] Hardware performance
Adam Witney wrote: Actually I am going through the same questions myself at the moment I would like to have a 2 disk RAID1 and a 4 disk RAID5, so need at least 6 disks Anybody have any suggestions or experience with other hardware manufacturers for this size of setup? (2U rack, up to 6 disks, 2 processors, ~2GB RAM, if possible) I tend to use either 1U or 4U servers, depending on the application. But I've had good experiences with IBM recently, and a quick look on their site shows the x345 with these specs: • 2U, 2-way server delivers extreme performance and availability for demanding applications • Up to 2 Intel Xeon processors up to 3.06GHz with 533MHz front-side bus speed for outstanding performance • Features up to 8GB of DDR memory, 5 PCI (4 PCI-X) slots and up to 6 hard disk drives for robust expansion • Hot-swap redundant cooling, power and hard disk drives for high availability • Integrated dual Ultra320 SCSI with RAID-1 for data protection This may not wrap well, but here is the url: http://www-132.ibm.com/webapp/wcs/stores/servlet/CategoryDisplay?catalogId=-840&storeId=1&categoryId=2559454&langId=-1&dualCurrId=73 Handles 6 drives; maybe that fits the bill? Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Sanity check requested
Nick Fankhauser wrote: Thanks for the correction- it sounds like this is one where usage can't be precisely controlled in a dynamic user environment & I just need to get a feel for what works under a load that approximates my production system. I think the most important point here is that if you set sort_mem too high, and you have a lot of simultaneous sorts, you can drive the server into swapping, which obviously is a very bad thing. You want it set as high as possible, but not so high given your usage patterns that you wind up swapping. Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] One table or many tables for data set
Castle, Lindsay wrote: The data structure looks like this: element date num1 num2 num3 num4 units There are approx 12,000 distinct elements for a total of about 6 million rows of data. Ahh, that helps! So are the elements evenly distributed, i.e. are there approx 500 rows of each element? If so, it should be plenty quick to put all the data in one table with an index on "element" (and maybe a multicolumn key, depending on other factors). The scanning technology I want to use may need a different number of rows and different columns depending on the scan formula; eg scan1 may need num1, num2 and num3 from the last 200 rows for element "x" scan2 may need num1, units from the last 10 rows for element "y" When you say "last X rows", do you mean sorted by "date"? If so, you might want that index to be on (element, date). Then do: SELECT num1, num2, num3 FROM mytable WHERE element = 'an_element' order by date DESC LIMIT 20; Replace num1, num2, num3 by whatever columns you want, and "LIMIT X" as the number of rows you want. HTH, Joe ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] One table or many tables for data set
Castle, Lindsay wrote: I'm working on a project that has a data set of approximately 6million rows with about 12,000 different elements, each element has 7 columns of data. I'm wondering what would be faster from a scanning perspective (SELECT statements with some calculations) for this type of set up; one table for all the data one table for each data element (12,000 tables) one table per subset of elements (eg all elements that start with "a" in a table) I, for one, am having difficulty understanding exactly what your data looks like, so it's hard to give advice. Maybe some concrete examples of what you are calling "rows", "elements", and "columns" would help. Does each of 6 million rows have 12000 elements, each with 7 columns? Or do you mean that out of 6 million rows, there are 12000 distinct kinds of elements? Can I do anything with Indexing to help with performance? I suspect for the majority of scans I will need to evaluate an outcome based on 4 or 5 of the 7 columns of data. Again, this isn't clear to me -- but maybe I'm just being dense ;-) Does this mean you expect 4 or 5 items in your WHERE clause? Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] EXTERNAL storage and substring on long strings
Scott Cain wrote: Index Scan using feature_pkey on feature (cost=0.00..3.01 rows=1 width=153) (actual time=954.13..954.14 rows=1 loops=1) Index Cond: (feature_id = 1) Total runtime: 954.26 msec (3 rows) Whoa! That's not what I expected, the time to do the query got more that twice as long. So I think, maybe it was just an unlucky section, and overall performance will be much better. So I write a perl script to do substring queries over all of my chromosomes at various positions and lengths (20,000 queries total). For comparison, I also ran the same script, extracting the chromosomes via sql and doing the substring in perl. Here's what happened: Hmmm, what happens if you compare with a shorter substring, e.g.: explain analyze select substring(residues from 100 for 2000) from feature where feature_id=1; I'm just guessing, but it might be that the extra I/O time to read 20K of uncompressed text versus the smaller compressed text is enough to swamp the time saved from not needing to uncompress. Any other ideas out there? Joe ---(end of broadcast)--- TIP 3: 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] EXTERNAL storage and substring on long strings
Scott Cain wrote: So it is possible that if I had a fast scsi drive, the performance might be better? Faster drives are always better ;-) Did you try the comparison with shorter substrings? Also, maybe not related to your specific question, but have you tuned any other postgresql.conf settings? Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
