[PERFORM] Join vs Subquery
Hi, I am using postgres 8.1.3 for this. If this has been dealt with later, please disregard. And this is not a complaint or a request, I am just curious, so I know how to best construct my queries. There is a unique index mapping domains to domain_ids. views_ts specifies a partitioned table, where views_ts_2007_04_01 is the only partition matching the range given in the query. My goal is to produce summaries of counts of rows for each day within a given range (can be days, months, years). The issue: the second query results in a lower cost estimate. I am wondering why the second query plan was not chosen for the first query. Thanks! Brian live= explain select ts::date,count(*) from views_ts join domains using (domain_id) where domain = '1234.com' and ts = '2007-04-01' and ts '2007-04-02' group by ts::date; QUERY PLAN HashAggregate (cost=9040.97..9041.00 rows=2 width=8) - Hash Join (cost=6.01..9040.96 rows=2 width=8) Hash Cond: (outer.domain_id = inner.domain_id) - Append (cost=0.00..7738.01 rows=259383 width=16) - Seq Scan on views_ts (cost=0.00..1138.50 rows=1 width=16) Filter: ((ts = '2007-04-01 00:00:00+10'::timestamp with time zone) AND (ts '2007-04-02 00:00:00+10'::timestamp with time zone)) - Seq Scan on views_ts_2007_04_01 views_ts (cost=0.00..6599.51 rows=259382 width=16) Filter: ((ts = '2007-04-01 00:00:00+10'::timestamp with time zone) AND (ts '2007-04-02 00:00:00+10'::timestamp with time zone)) - Hash (cost=6.01..6.01 rows=1 width=8) - Index Scan using domains_domain on domains (cost=0.00..6.01 rows=1 width=8) Index Cond: (domain = '1234.com'::text) (11 rows) live= explain select ts::date,count(*) from views_ts where domain_id = (select domain_id from domains where domain = '1234.com') and ts = '2007-04-01' and ts '2007-04-02' group by ts::date; QUERY PLAN - HashAggregate (cost=1993.93..1995.99 rows=137 width=8) InitPlan - Index Scan using domains_domain on domains (cost=0.00..6.01 rows=1 width=8) Index Cond: (domain = '1234.com'::text) - Result (cost=0.00..1986.69 rows=247 width=8) - Append (cost=0.00..1986.07 rows=247 width=8) - Seq Scan on views_ts (cost=0.00..1245.75 rows=1 width=8) Filter: ((domain_id = $0) AND (ts = '2007-04-01 00:00:00+10'::timestamp with time zone) AND (ts '2007-04-02 00:00:00+10'::timestamp with time zone)) - Bitmap Heap Scan on views_ts_2007_04_01 views_ts (cost=2.86..740.32 rows=246 width=8) Recheck Cond: (domain_id = $0) Filter: ((ts = '2007-04-01 00:00:00+10'::timestamp with time zone) AND (ts '2007-04-02 00:00:00+10'::timestamp with time zone)) - Bitmap Index Scan on views_ts_2007_04_01_domain_id (cost=0.00..2.86 rows=246 width=0) Index Cond: (domain_id = $0)
Re: [PERFORM] Intermitent slow queries
Well, the traditional DBMS way of dealing with this sort of summarization when the tables involved do not fit into RAM is to create a roll up table or tables for the time period commonly summarized over. Since it looks like you've got a table with a row per hour, create another that has a row per day that summarizes hours 1...24. Ditto weekly, monthly, quarterly, or any other time period you frequently summarize over. Yes, this is explicitly a space for time trade-off. DBs are generally not very well suited to time series data. I also find it, errr, =interesting= that your dedicated pg server with 9 GB of RAM never goes up above 1.8 in total usage. That simply does not make sense if your OS and pg conf files are configured correctly. Make sure that you are running 64b RHEL 4 that is patched / configured correctly to use the RAM you have. (with 4 ?multi-core? CPUs, you =are= running a recent 2.6 based kernel, right?) Ditto checking the pg conf file to make sure the values therein are sane. With 9 GB of RAM, you should be able to: =max out shared_buffers at 262143 (2 GB of shared buffers), =set work_mem and maintenance_work_mem to considerably larger than the defaults. (If this query has the box to itself when running, you can set the memory use parameters to values tuned specifically to the query.) =just for giggles, boost max_stack_depth from 2 MB - 4 MB =set effective_cache_size to a realistic value given your HW + OS + the tuning above. The main point here is that most of your RAM should be in use. If you are getting poor performance and most of the RAM is !not! in use, Something's Wrong (tm). Of course, the holy grail is to have the entire data set you are operating over to be RAM resident during the query. If you can manage that, said query should be =fast=. RAM is cheap enough that if you can make this query RAM resident by a reasonable combination of configuration + schema + RAM purchasing, you should do it. Cheers, Ron Peacetree At 03:07 PM 5/2/2007, Parks, Aaron B. wrote: Ron: I'm not sure how the JVM would really affect the issue as it is on a Windows box connecting remotely. As indicated the PG Server itself has 9 gigs of ram and it never goes up above 1.8 total usage. If the PG driver is doing something funny (IE waiting to send requests) that's way out past my ability to fix it, so I will hope that's not it. You can see the CPU slamming doing the queries, then after a while it just stops and all I get is tiny little blips on the usage. AP -Original Message- From: Ron [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 02, 2007 2:55 PM To: Parks, Aaron B. Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Intermitent slow queries Among other possibilities, there's a known problem with slow memory leaks in various JVM's under circumstances similar to those you are describing. The behavior you are describing is typical of this scenario. The increasing delay is caused by longer and longer JVM garbage collection runs as java attempts to reclaim enough memory from a smaller and smaller universe of available memory. The fastest test, and possible fix, is to go and buy more RAM. See if 16MB of RAM, heck even 10MB, makes the problem go away or delays it's onset. If so, there's good circumstantial evidence that you are being bitten by a slow memory leak; most likely in the JVM. Cheers, Ron Peacetree At 11:24 AM 5/2/2007, Parks, Aaron B. wrote: My pg 8.1 install on an AMD-64 box (4 processors) with 9 gigs of ram running RHEL4 is acting kind of odd and I thought I would see if anybody has any hints. I have Java program using postgresql-8.1-409.jdbc3.jar to connect over the network. In general it works very well. I have run batch updates with several thousand records repeatedly that has worked fine. The Program pulls a summation of the DB and does some processing with it. It starts off wonderfully running a query every .5 seconds. Unfortunately, after a while it will start running queries that take 20 to 30 seconds. Looking at the EXPLAIN for the query no sequential scans are going on and everything has an index that points directly at its search criteria. Example: Select sum(whatever) from a inner join b on a.something=b.something WHERE b.day=1 and b.hour=1 Select sum(whatever) from a inner join b on a.something=b.something WHERE b.day=1 and b.hour=2 Select sum(whatever) from a inner join b on a.something=b.something WHERE b.day=1 and b.hour=3 . . Select sum(whatever) from a inner join b on a.something=b.something WHERE b.day=1 and b.hour=23 Select sum(whatever) from a inner join b on a.something=b.something WHERE b.day=1 and b.hour=24 Select sum(whatever) from a inner join b on a.something=b.something WHERE b.day=2 and b.hour=1 Select sum(whatever) from a inner join b on a.something=b.something WHERE b.day=2 and b.hour=2 . . . This query runs fine for a while (up to thousands of times). But what happens is that it starts to
Re: [PERFORM] Join vs Subquery
Brian Herlihy [EMAIL PROTECTED] writes: There is a unique index mapping domains to domain_ids. ... The issue: the second query results in a lower cost estimate. I am wondering why the second query plan was not chosen for the first query. Well the unique index you mentioned is critical to being able to conclude the queries are equivalent. Postgres in the past hasn't been able to use things like unique indexes to make planning decisions because it had no infrastructure to replan if you dropped the index. We do have such infrastructure now so it may be possible to add features like this in the future. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Join vs Subquery
Brian Herlihy [EMAIL PROTECTED] writes: The issue: the second query results in a lower cost estimate. I am wondering why the second query plan was not chosen for the first query. 8.1 is incapable of pushing indexable join conditions down below an Append. Try 8.2. regards, tom lane ---(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
[PERFORM] pg_stat_* collection
Today's survey is: just what are *you* doing to collect up the information about your system made available by the various pg_stat views? I have this hacked together script that dumps them into a file, imports them into another database, and then queries against some of the more interesting data. You would thing there would be an organized project addressing this need around to keep everyone from reinventing that wheel, but I'm not aware of one. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] pg_stat_* collection
On 5/3/07, Greg Smith [EMAIL PROTECTED] wrote: Today's survey is: just what are *you* doing to collect up the information about your system made available by the various pg_stat views? I have this hacked together script that dumps them into a file, imports them into another database, and then queries against some of the more interesting data. You would thing there would be an organized project addressing this need around to keep everyone from reinventing that wheel, but I'm not aware of one. I have a bunch of plugin scripts for Munin (http://munin.projects.linpro.no/) that collect PostgreSQL statistics. Graphs like this are useful: http://purefiction.net/paste/pg_munin_example.png I have been considering tarring them up as a proper release at some point. Anyone interested? Alexander. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Query performance problems with partitioned tables
Andreas Haumer wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi! I'm currently experimenting with PostgreSQL 8.2.4 and table partitioning in order to improve the performance of an application I'm working on. My application is about managing measurement values (lots of!) I have one table t_mv which stores all the measurement values. A single measurement value has a timestamp and belongs to a single time series, so table t_mv looks like this: CREATE TABLE t_mv ( zr integer NOT NULL, -- the time series id ts timestamp with time zone NOT NULL, -- the timestamp ... -- other attributes of a mv ) WITHOUT OIDS; ALTER TABLE t_mv ADD CONSTRAINT pk_mv_zr_ts PRIMARY KEY (zr, ts); Each time series defines several other attributes which are common to all measurement values of this time series (like sampling location, physical parameter, aggregation, cardinality, type, visibility, etc.) The application should be able to handle several thousand different time series and hundreds of millions of measurement values, so table t_mv can get quite large. I have tested installations with up to 70 millions rows in t_mv and PostgreSQL can handle that with a quite good performance even on non high-end machines (operating system is Linux, btw) But as I expect installations witch much more rows in t_mv, I tried to implement a partitioned tables concept using inheritance and CHECK constraints, just like it is described in the docs (e.g. chapter 5.9 in the current PostgreSQL 8.2.4 documentation) I split the t_mv table on the timestamp attribute to build child tables which hold all measurement values for a single month. That way I have several tables called t_mv_MM which all inherit from t_mv. The number of child tables depends on the time period the application has to store the measurement values (which can be several years so I'm expecting up to 100 child tables or even more). For the application everything looks the same: inserts, updates and queries all are against the t_mv parent table, the application is not aware of the fact that this table is actually split into several child tables. This is working fine and for some standard queries it actually gives some performance improvement compared to the standard everything in one big table concept. The performance improvement increases with the number of rows in t_mv, for a small table (less than 10 million rows or so) IMHO it is not really worth the effort or even counter-productive. But I have some special queries where the performance with partitioned tables actually get much worse: those are queries where I'm working with open time intervals, i.e. where I want to get the previous and/or next timestamp from a given interval. A simple example: Get the timestamp of a measurement value for time series 3622 which is right before the measurement value with time stamp '2007-04-22 00:00:00': testdb_std= select ts from mwdb.t_mv where zr=3622 and ts '2007-04-22 00:00:00' order by ts desc limit 1; ts - 2007-04-21 23:00:00+02 (1 row) Im my application there are many queries like this. Such queries also come in several variations, including quite sophisticated joins with lots of other tables above the time series table. Note: as I'm working with (potentially) non-equidistant time series I can not just calculate the timestamps, I have to retrieve them from the database! In the standard case, the query plan for the example query looks like this: testdb_std= explain analyze select ts from mwdb.t_mv where zr=3622 and ts '2007-04-22 00:00:00' order by ts desc limit 1; QUERY PLAN - - Limit (cost=0.00..1.70 rows=1 width=8) (actual time=0.233..0.235 rows=1 loops=1) - Index Scan Backward using pk_mv_zr_ts on t_mv (cost=0.00..21068.91 rows=12399 width=8) (actual time=0.221..0.221 rows=1 loops=1) Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone '2007-04-22 00:00:00+02'::timestamp with time zone)) Total runtime: 0.266 ms (4 rows) If I switch to partitioned tables, the query retrieves the same result (of course): testdb_std= \c testdb_part You are now connected to database testdb_part. testdb_part= select ts from mwdb.t_mv where zr=3622 and ts '2007-04-22 00:00:00' order by ts desc limit 1; ts - 2007-04-21 23:00:00+02 (1 row) But the query plan becomes: testdb_part= explain analyze select ts from mwdb.t_mv where zr=3622 and ts '2007-04-22 00:00:00' order by ts desc limit 1; QUERY PLAN -
Re: [PERFORM] pg_stat_* collection
On Thu, May 03, 2007 at 10:45:48AM -0400, Greg Smith wrote: Today's survey is: just what are *you* doing to collect up the information about your system made available by the various pg_stat views? I have this hacked together script that dumps them into a file, imports them into another database, and then queries against some of the more interesting data. You would thing there would be an organized project addressing this need around to keep everyone from reinventing that wheel, but I'm not aware of one. If you're interested in exposing them with snmp, join the pgsnmpd project :-) //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] pg_stat_* collection
[Alexander Staubo - Thu at 04:52:55PM +0200] I have been considering tarring them up as a proper release at some point. Anyone interested? Yes. Eventually I have my own collection as well: db_activity - counts the number of (all, slow, very slow, stuck idle in transaction) queries in progress; this is one of the better indicators on how busy/overloaded the database is. (I also have a separate script dumping the contents from pg_stat_activity to a log file, which I frequentlymonitor by tail -F). db_commits + db_rollbacks pr database - I'm not sure if those are useful for anything, will eventually remove them. Maybe nice to be able to compare the activity between different databases running on the same host, if they are comparable. db_connections - num of connections compared to max connections. Useful for alarms. db_hanging_transactions - age of oldest transaction. Useful for alarms, since hanging transactions can be very bad for the db performance. db_locks - monitors the number of locks. I've never actually needed this for anything, maybe I should remove it. db_num_backends - number of backends, sorted by databases. Probably not so useful. db_space (one for each database) - monitors space usage, found this script through google. db_xid_wraparound - gives alarms if the databases aren't beeing vacuumed. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] pg_stat_* collection
On Thu, 2007-05-03 at 10:45 -0400, Greg Smith wrote: Today's survey is: just what are *you* doing to collect up the information about your system made available by the various pg_stat views? I have this hacked together script that dumps them into a file, imports them into another database, and then queries against some of the more interesting data. You would thing there would be an organized project addressing this need around to keep everyone from reinventing that wheel, but I'm not aware of one. Is anyone out there collecting their own statistics? What's the easiest way to take statistical samples of the data in a table without reading the entire thing? Regards, Jeff Davis ---(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] Feature Request --- was: PostgreSQL Performance Tuning
The more I think about this thread, the more I'm convinced of 2 things: 1= Suggesting initial config values is a fundamentally different exercise than tuning a running DBMS. This can be handled reasonably well by HW and OS snooping. OTOH, detailed fine tuning of a running DBMS does not appear to be amenable to this approach. So... 2= We need to implement the kind of timer support that Oracle 10g has. Oracle performance tuning was revolutionized by there being micro-second accurate timers available for all Oracle operations. IMHO, we should learn from that. Only the combination of the above looks like it will really be successful in addressing the issues brought up in this thread. Cheers, Ron Peacetree At 01:59 PM 4/27/2007, Josh Berkus wrote: Dan, Exactly.. What I think would be much more productive is to use the great amount of information that PG tracks internally and auto-tune the parameters based on it. For instance: *Everyone* wants this. The problem is that it's very hard code to write given the number of variables. I'm working on it but progress is slow, due to my travel schedule. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(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 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Feature Request --- was: PostgreSQL Performance Tuning
Greg, I'm not fooled--secretly you and your co-workers laugh at how easy this is on Solaris and are perfectly happy with how difficult it is on Linux, right? Don't I wish. There's issues with getting CPU info on Solaris, too, if you get off of Sun Hardware to generic white boxes. The base issue is that there's no standardization on how manufacturers report the names of their CPUs, 32/64bit, or clock speeds. So any attempt to determine how fast a CPU is, even on a 1-5 scale, requires matching against a database of regexes which would have to be kept updated. And let's not even get started on Windows. I joke becuase I've been re-solving some variant on this problem every few years for a decade now and it just won't go away. Last time I checked the right answer was to find someone else who's already done it, packaged that into a library, and appears committed to keeping it up to date; just pull a new rev of that when you need it. For example, for the CPU/memory part, top solves this problem and is always kept current, so on open-source platforms there's the potential to re-use that code. Now that I know that's one thing you're (understandably) fighting with I'll dig up my references on that (again). Actually, total memory is not an issue, that's fairly straight forwards. Nor is # of CPUs. Memory *used* is a PITA, which is why I'd ignore that part and make some assumptions. It would have to be implemented in a per-OS manner, which is what bogged me down. I would advocate focusing on iterative improvements to an existing configuration rather than even bothering with generating a one-off config for exactly this reason. It *is* hard/impossible to get it right in a single shot, because of how many parameters interact and the way bottlenecks clear, so why not assume from the start you're going to do it several times--then you've only got one piece of software to write. Sounds fine to me. To argue against myself for a second, it may very well be the case that writing the simpler tool is the only way to get a useful prototype for building the more complicated one; very easy to get bogged down in feature creep on a grand design otherwise. It's certainly easy for me. ;-) -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Feature Request --- was: PostgreSQL Performance Tunin g
--- Original Message --- From: Josh Berkus [EMAIL PROTECTED] To: pgsql-performance@postgresql.org Sent: 03/05/07, 20:21:55 Subject: Re: [PERFORM] Feature Request --- was: PostgreSQL Performance Tuning And let's not even get started on Windows. WMI is your friend. /D ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Feature Request --- was: PostgreSQL Performance Tuning
On Thu, 3 May 2007, Josh Berkus wrote: Greg, I'm not fooled--secretly you and your co-workers laugh at how easy this is on Solaris and are perfectly happy with how difficult it is on Linux, right? Don't I wish. There's issues with getting CPU info on Solaris, too, if you get off of Sun Hardware to generic white boxes. The base issue is that there's no standardization on how manufacturers report the names of their CPUs, 32/64bit, or clock speeds. So any attempt to determine how fast a CPU is, even on a 1-5 scale, requires matching against a database of regexes which would have to be kept updated. And let's not even get started on Windows. I think the only sane way to try and find the cpu speed is to just do a busy loop of some sort (ideally something that somewhat resembles the main code) and see how long it takes. you may have to do this a few times until you get a loop that takes long enough (a few seconds) on a fast processor David Lang ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Feature Request --- was: PostgreSQL Performance Tuning
CPUs, 32/64bit, or clock speeds. So any attempt to determine how fast a CPU is, even on a 1-5 scale, requires matching against a database of regexes which would have to be kept updated. And let's not even get started on Windows. I think the only sane way to try and find the cpu speed is to just do a busy loop of some sort (ideally something that somewhat resembles the main code) and see how long it takes. you may have to do this a few times until you get a loop that takes long enough (a few seconds) on a fast processor I was going to suggest just that (but then was afraid that again I may have been just being naive) --- I can't remember the exact name, but I remember using (on some Linux flavor) an API call that fills a struct with data on the resource usage for the process, including CPU time; I assume measured with precision (that is, immune to issues of other applications running simultaneously, or other random events causing the measurement to be polluted by random noise). As for 32/64 bit --- doesn't PG already know that information? I mean, ./configure does gather that information --- does it not? Carlos -- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Feature Request --- was: PostgreSQL Performance Tuning
On Thu, 3 May 2007, Carlos Moreno wrote: CPUs, 32/64bit, or clock speeds. So any attempt to determine how fast a CPU is, even on a 1-5 scale, requires matching against a database of regexes which would have to be kept updated. And let's not even get started on Windows. I think the only sane way to try and find the cpu speed is to just do a busy loop of some sort (ideally something that somewhat resembles the main code) and see how long it takes. you may have to do this a few times until you get a loop that takes long enough (a few seconds) on a fast processor I was going to suggest just that (but then was afraid that again I may have been just being naive) --- I can't remember the exact name, but I remember using (on some Linux flavor) an API call that fills a struct with data on the resource usage for the process, including CPU time; I assume measured with precision (that is, immune to issues of other applications running simultaneously, or other random events causing the measurement to be polluted by random noise). since what we are looking for here is a reasonable first approximation, not perfection I don't think we should worry much about pollution of the value. if the person has other things running while they are running this test that will be running when they run the database it's no longer 'pollution' it's part of the environment. I think a message at runtime that it may produce inaccurate results if you have other heavy processes running for the config that won't be running with the database would be good enough (remember it's not only CPU time that's affected like this, it's disk performance as well) As for 32/64 bit --- doesn't PG already know that information? I mean, ./configure does gather that information --- does it not? we're not talking about comiling PG, we're talking about getting sane defaults for a pre-compiled binary. if it's a 32 bit binary assume a 32 bit cpu, if it's a 64 bit binary assume a 64 bit cpu (all hardcoded into the binary at compile time) David Lang ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Feature Request --- was: PostgreSQL Performance Tuning
been just being naive) --- I can't remember the exact name, but I remember using (on some Linux flavor) an API call that fills a struct with data on the resource usage for the process, including CPU time; I assume measured with precision (that is, immune to issues of other applications running simultaneously, or other random events causing the measurement to be polluted by random noise). since what we are looking for here is a reasonable first approximation, not perfection I don't think we should worry much about pollution of the value. Well, it's not as much worrying as it is choosing the better among two equally difficult options --- what I mean is that obtaining the *real* resource usage as reported by the kernel is, from what I remember, equally hard as it is obtaining the time with milli- or micro-seconds resolution. So, why not choosing this option? (in fact, if we wanted to do it the scripted way, I guess we could still use time test_cpuspeed_loop and read the report by the command time, specifying CPU time and system calls time. As for 32/64 bit --- doesn't PG already know that information? I mean, ./configure does gather that information --- does it not? we're not talking about comiling PG, we're talking about getting sane defaults for a pre-compiled binary. if it's a 32 bit binary assume a 32 bit cpu, if it's a 64 bit binary assume a 64 bit cpu (all hardcoded into the binary at compile time) Right --- I was thinking that configure, which as I understand, generates the Makefiles to compile applications including initdb, could plug those values as compile-time constants, so that initdb (or a hypothetical additional utility that would do what we're discussing in this thread) already has them. Anyway, yes, that would go for the binaries as well --- we're pretty much saying the same thing :-) Carlos -- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Feature Request --- was: PostgreSQL Performance Tuning
On Thu, 3 May 2007, Carlos Moreno wrote: been just being naive) --- I can't remember the exact name, but I remember using (on some Linux flavor) an API call that fills a struct with data on the resource usage for the process, including CPU time; I assume measured with precision (that is, immune to issues of other applications running simultaneously, or other random events causing the measurement to be polluted by random noise). since what we are looking for here is a reasonable first approximation, not perfection I don't think we should worry much about pollution of the value. Well, it's not as much worrying as it is choosing the better among two equally difficult options --- what I mean is that obtaining the *real* resource usage as reported by the kernel is, from what I remember, equally hard as it is obtaining the time with milli- or micro-seconds resolution. So, why not choosing this option? (in fact, if we wanted to do it the scripted way, I guess we could still use time test_cpuspeed_loop and read the report by the command time, specifying CPU time and system calls time. I don't think it's that hard to get system time to a reasonable level (if this config tuner needs to run for a min or two to generate numbers that's acceptable, it's only run once) but I don't think that the results are really that critical. do we really care if the loop runs 1,000,000 times per second or 1,001,000 times per second? I'd argue that we don't even care about 1,000,000 times per second vs 1,100,000 times per second, what we care about is 1,000,000 times per second vs 100,000 times per second, if you do a 10 second test and run it for 11 seconds you are still in the right ballpark (i.e. close enough that you really need to move to the stage2 tuneing to figure the exact values) As for 32/64 bit --- doesn't PG already know that information? I mean, ./configure does gather that information --- does it not? we're not talking about comiling PG, we're talking about getting sane defaults for a pre-compiled binary. if it's a 32 bit binary assume a 32 bit cpu, if it's a 64 bit binary assume a 64 bit cpu (all hardcoded into the binary at compile time) Right --- I was thinking that configure, which as I understand, generates the Makefiles to compile applications including initdb, could plug those values as compile-time constants, so that initdb (or a hypothetical additional utility that would do what we're discussing in this thread) already has them. Anyway, yes, that would go for the binaries as well --- we're pretty much saying the same thing :-) I'm thinking along the lines of a script or pre-compiled binary (_not_ initdb) that you could run and have it generate a new config file that has values that are at within about an order of magnatude of being correct. David Lang ---(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] Feature Request --- was: PostgreSQL Performance Tuning
I don't think it's that hard to get system time to a reasonable level (if this config tuner needs to run for a min or two to generate numbers that's acceptable, it's only run once) but I don't think that the results are really that critical. Still --- this does not provide a valid argument against my claim. Ok, we don't need precision --- but do we *need* to have less precision?? I mean, you seem to be proposing that we deliberately go out of our way to discard a solution with higher precision and choose the one with lower precision --- just because we do not have a critical requirement for the extra precision. That would be a valid argument if the extra precision came at a considerable cost (well, or at whatever cost, considerable or not). But my point is still that obtaining the time in the right ballpark and obtaining the time with good precision are two things that have, from any conceivable point of view (programming effort, resources consumption when executing it, etc. etc.), the exact same cost --- why not pick the one that gives us the better results? Mostly when you consider that: I'd argue that we don't even care about 1,000,000 times per second vs 1,100,000 times per second, what we care about is 1,000,000 times per second vs 100,000 times per second Part of my claim is that measuring real-time you could get an error like this or even a hundred times this!! Most of the time you wouldn't, and definitely if the user is careful it would not happen --- but it *could* happen!!! (and when I say could, I really mean: trust me, I have actually seen it happen) Why not just use an *extremely simple* solution that is getting information from the kernel reporting the actual CPU time that has been used??? Of course, this goes under the premise that in all platforms there is such a simple solution like there is on Linux (the exact name of the API function still eludes me, but I have used it in the past, and I recall that it was just three or five lines of code). Carlos -- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Feature Request --- was: PostgreSQL Performance Tuning
On Thu, 3 May 2007, Carlos Moreno wrote: I don't think it's that hard to get system time to a reasonable level (if this config tuner needs to run for a min or two to generate numbers that's acceptable, it's only run once) but I don't think that the results are really that critical. Still --- this does not provide a valid argument against my claim. Ok, we don't need precision --- but do we *need* to have less precision?? I mean, you seem to be proposing that we deliberately go out of our way to discard a solution with higher precision and choose the one with lower precision --- just because we do not have a critical requirement for the extra precision. That would be a valid argument if the extra precision came at a considerable cost (well, or at whatever cost, considerable or not). the cost I am seeing is the cost of portability (getting similarly accruate info from all the different operating systems) But my point is still that obtaining the time in the right ballpark and obtaining the time with good precision are two things that have, from any conceivable point of view (programming effort, resources consumption when executing it, etc. etc.), the exact same cost --- why not pick the one that gives us the better results? Mostly when you consider that: I'd argue that we don't even care about 1,000,000 times per second vs 1,100,000 times per second, what we care about is 1,000,000 times per second vs 100,000 times per second Part of my claim is that measuring real-time you could get an error like this or even a hundred times this!! Most of the time you wouldn't, and definitely if the user is careful it would not happen --- but it *could* happen!!! (and when I say could, I really mean: trust me, I have actually seen it happen) if you have errors of several orders of magnatude in the number of loops it can run in a given time period then you don't have something that you can measure to any accuracy (and it wouldn't matter anyway, if your loops are that variable, your code execution would be as well) Why not just use an *extremely simple* solution that is getting information from the kernel reporting the actual CPU time that has been used??? Of course, this goes under the premise that in all platforms there is such a simple solution like there is on Linux (the exact name of the API function still eludes me, but I have used it in the past, and I recall that it was just three or five lines of code). I think the problem is that it's a _different_ 3-5 lines of code for each OS. if I'm wrong and it's the same for the different operating systems then I agree that we should use the most accurate clock we can get. I just don't think we have that. David Lang ---(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] Feature Request --- was: PostgreSQL Performance Tuning
That would be a valid argument if the extra precision came at a considerable cost (well, or at whatever cost, considerable or not). the cost I am seeing is the cost of portability (getting similarly accruate info from all the different operating systems) Fair enough --- as I mentioned, I was arguing under the premise that there would be a quite similar solution for all the Unix-flavours (and hopefully an equivalent --- and equivalently simple --- one for Windows) ... Whether or not that premise holds, I wouldn't bet either way. error like this or even a hundred times this!! Most of the time you wouldn't, and definitely if the user is careful it would not happen --- but it *could* happen!!! (and when I say could, I really mean: trust me, I have actually seen it happen) Part of my claim is that measuring real-time you could get an if you have errors of several orders of magnatude in the number of loops it can run in a given time period then you don't have something that you can measure to any accuracy (and it wouldn't matter anyway, if your loops are that variable, your code execution would be as well) Not necessarily --- operating conditions may change drastically from one second to the next; that does not mean that your system is useless; simply that the measuring mechanism is way too vulnerable to the particular operating conditions at the exact moment it was executed. I'm not sure if that was intentional, but you bring up an interesting issue --- or in any case, your comment made me drastically re-think my whole argument: do we *want* to measure the exact speed, or rather the effective speed under normal operating conditions on the target machine? I know the latter is almost impossible --- we're talking about an estimate of a random process' parameter (and we need to do it in a short period of time) ... But the argument goes more or less like this: if you have a machine that runs at 1000 MIPS, but it's usually busy running things that in average consume 500 of those 1000 MIPS, would we want PG's configuration file to be obtained based on 1000 or based on 500 MIPS??? After all, the CPU is, as far as PostgreSQL will be able see, 500 MIPS fast, *not* 1000. I think I better stop, if we want to have any hope that the PG team will ever actually implement this feature (or similar) ... We're probably just scaring them!! :-) Carlos -- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Feature Request --- was: PostgreSQL Performance Tuning
On Thu, 3 May 2007, Carlos Moreno wrote: error like this or even a hundred times this!! Most of the time you wouldn't, and definitely if the user is careful it would not happen --- but it *could* happen!!! (and when I say could, I really mean: trust me, I have actually seen it happen) Part of my claim is that measuring real-time you could get an if you have errors of several orders of magnatude in the number of loops it can run in a given time period then you don't have something that you can measure to any accuracy (and it wouldn't matter anyway, if your loops are that variable, your code execution would be as well) Not necessarily --- operating conditions may change drastically from one second to the next; that does not mean that your system is useless; simply that the measuring mechanism is way too vulnerable to the particular operating conditions at the exact moment it was executed. I'm not sure if that was intentional, but you bring up an interesting issue --- or in any case, your comment made me drastically re-think my whole argument: do we *want* to measure the exact speed, or rather the effective speed under normal operating conditions on the target machine? I know the latter is almost impossible --- we're talking about an estimate of a random process' parameter (and we need to do it in a short period of time) ... But the argument goes more or less like this: if you have a machine that runs at 1000 MIPS, but it's usually busy running things that in average consume 500 of those 1000 MIPS, would we want PG's configuration file to be obtained based on 1000 or based on 500 MIPS??? After all, the CPU is, as far as PostgreSQL will be able see, 500 MIPS fast, *not* 1000. I think I better stop, if we want to have any hope that the PG team will ever actually implement this feature (or similar) ... We're probably just scaring them!! :-) simpler is better (or perfect is the enemy of good enough) if you do your sample over a few seconds (or few tens of seconds) things will average out quite a bit. the key is to be going for a reasonable starting point. after that then the full analysis folks can start in with all their monitoring and tuneing, but the 80/20 rule really applies here. 80% of the gain is from getting 'fairly close' to the right values, and that should only be 20% of the full 'tuneing project' David Lang ---(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] Query performance problems with partitioned tables
On 5/3/07, Fei Liu [EMAIL PROTECTED] wrote: Hello, Andreas, I too am having exactly the same issue as you do. Comparing my partitioned and plain table performance, I've found that the plain tables perform about 25% faster than partitioned table. Using 'explain select ...', I see that constraints are being used so in partitioned tables fewer rows are examined. But still partitioned tables are 25% slower, what a let down. That's a little bit harsh. The main use of partitioning is not to make the table faster but to make the maintenance easier. When constraint exclusion works well for a particular query you can get a small boost but many queries will break down in a really negative way. So, you are sacrificing flexibility for easier maintenance. You have to really be careful how you use it. The best case for partitioning is when you can logically divide up your data so that you really only have to deal with one sliver of it at a time...for joins and such. If the OP could force the constraint exclusion (maybe by hashing the timestamp down to a period and using that for where clause), his query would be fine. The problem is it's not always easy to do that. merlin ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Feature Request --- was: PostgreSQL Performance Tuning
On Thu, 3 May 2007, Josh Berkus wrote: So any attempt to determine how fast a CPU is, even on a 1-5 scale, requires matching against a database of regexes which would have to be kept updated. This comment, along with the subsequent commentary today going far astray into CPU measurement land, serves as a perfect example to demonstrate why I advocate attacking this from the perspective that assumes there is already a database around we can query. We don't have to care how fast the CPU is in any real terms; all we need to know is how many of them are (which as you point out is relatively easy to find), and approximately how fast each one of them can run PostgreSQL. Here the first solution to this problem I came up with in one minute of RD: -bash-3.00$ psql postgres=# \timing Timing is on. postgres=# select count(*) from generate_series(1,10,1); count 10 (1 row) Time: 106.535 ms There you go, a completely cross-platform answer. You should run the statement twice and only use the second result for better consistancy. I ran this on all the sytems I was around today and got these results: P4 2.4GHz 107ms Xeon 3GHz 100ms Opteron 275 65ms Athlon X2 4600 61ms For comparison sake, these numbers are more useful at predicting actual application performance than Linux's bogomips number, which completely reverses the relative performance of the Intel vs. AMD chips in this set from the reality of how well they run Postgres. My philosophy in this area is that if you can measure something performance-related with reasonable accuracy, don't even try to estimate it instead. All you have to do is follow some of the downright bizzare dd/bonnie++ results people post here to realize that there can be a vast difference between the performance you'd expect given a particular hardware class and what you actually get. While I'm ranting here, I should mention that I also sigh every time I see people suggest we should ask the user how big their database is. The kind of newbie user people keep talking about helping has *no idea whatsoever* how big the data actually is after it gets into the database and all the indexes are built. But if you tell someone right now this database has 1 million rows and takes up 800MB; what multiple of its current size do you expect it to grow to?, now that's something people can work with. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(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] pg_stat_* collection
On Thu, 3 May 2007, Alexander Staubo wrote: I have a bunch of plugin scripts for Munin that collect PostgreSQL statistics. I have been considering tarring them up as a proper release at some point. Excellent plan. Pop out a tar file, trade good ideas with Tobias, have some other people play with the code and improve it. Let me know if you need a place to put the files at, since I'd like to look at them anyway I could easily dump them onto a web page while I was at it. Munin is a very interesting solution to this class of problem. They've managed to streamline the whole data collection process by layering clever Perl hacks three deep. It's like the anti-SNMP--just build the simplest possible interface that will work and then stop designing. The result is so easy to work with that it's no surprise people like Munin. It's also completely inappropriate for any environment I work in, because there really is no thought of security whatsoever in the whole thing. What I'm still thinking about is whether it's possible to fix that issue while still keeping the essential simplicity that makes Munin so friendly. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] pg_stat_* collection
[Greg Smith - Fri at 12:53:55AM -0400] Munin is a very interesting solution to this class of problem. They've managed to streamline the whole data collection process by layering clever Perl hacks three deep. It's like the anti-SNMP--just build the simplest possible interface that will work and then stop designing. The result is so easy to work with that it's no surprise people like Munin. It's fairly easy to throw in new graphs, and I like that. One of the drawbacks is that it spends a lot of CPU building the graphs etc - if I continue adding graphs in my current speed, and we set up even more servers, soon it will take us more than five minutes generating the graphs. Also, local configuration can be tricky. Locally I fix this by loading a config file with a hard-coded path. Luckily, as long as the postgres munin plugins are run at localhost as the postgres user, most of them don't need any configuration. Still, it can be useful to tune the alarm thresholds. It's also completely inappropriate for any environment I work in, because there really is no thought of security whatsoever in the whole thing. What I'm still thinking about is whether it's possible to fix that issue while still keeping the essential simplicity that makes Munin so friendly. What layers of security do you need? We're using https, basic auth and ssh-tunnels. We've considered the munin data to be regarded as confidential, at the other hand it's nothing ultra-secret there; i.e. securing the backups of the production database probably deserves more attention. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
