Re: [PERFORM] enable database user login/logout information
On Thu, Jun 9, 2011 at 10:50, muthu krishnan muthu.krishnan.li...@gmail.com wrote: Please guide me how to get only the database user connection (without SQL statements) information in logfile. log_statement = none -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgresql on itanium server
Thursday, June 9, 2011, 12:03:12 PM you wrote: Is there any option to turn on flush to zero mode in itanium cpu while compiling postgresql from source? configure will complain when specifying './configure CFLAGS=-ffast-math'. make won't, so a 'make CFLAGS='-O2 -Wall -ffast-math' after doing a normal './configure' should do the trick. But maybe one of the experts should explain if this will in fact work... -- Jochen Erwied | home: joc...@erwied.eu +49-208-38800-18, FAX: -19 Sauerbruchstr. 17 | work: j...@mbs-software.de +49-2151-7294-24, FAX: -50 D-45470 Muelheim | mobile: jochen.erw...@vodafone.de +49-173-5404164 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgresql on itanium server
On Thu, Jun 9, 2011 at 13:03, muthu krishnan muthu.krishnan.li...@gmail.com wrote: Thank you for suggesting the valuable URL, we are getting 3 floating point assist fault error for every second, will it impact the performance for postgresql? Probably. The kernel throttles these messages, so you're probably performing many more of these calculations than the number of messages. Is there any option to turn on flush to zero mode in itanium cpu while compiling postgresql from source? As the URL mentions, you can build with CFLAGS=-ffast-math, that should work for PostgreSQL too. But since you know you're operating with denormal numbers, you WILL get different results to queries. Whether that's a problem for you depends on your application. You could start getting division by zero errors for instance. Regards, Marti -- 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] expanding to SAN: which portion best to move
On Mon, May 16, 2011 at 10:19 AM, Robert Klemme shortcut...@googlemail.comwrote: On Fri, May 13, 2011 at 9:04 PM, Robert Haas robertmh...@gmail.com wrote: Separating index and tables might not be a totally good idea generally. Richard Foote has an excellent article about Oracle but I assume at least a few things do apply to PostgreSQL as well - it's at least worth as something to check PostgreSQL's access patterns against: http://richardfoote.wordpress.com/2008/04/16/separate-indexes-from-tables-some-thoughts-part-i-everything-in-its-right-place/ I would probably rather try to separate data by the nature and frequency of accesses. One reasonable separation would be to leave all frequently accessed tables *and* their indexes on local RAID and moving less frequently accessed data to the SAN. This separation could be easily identified if you have separate tables for current and historic data. Well, after reading your article i have been reading some materail about it on the internet, stating that separating indexes from data for performance benefits is a myth. I found your comment So then a single query will only ever access one of both at a time. very smart (no sarcasm there). I also found a threadhttp://asktom.oracle.com/pls/asktom/f?p=100:11:0P11_QUESTION_ID:901906930328on AskTom that said mainly the goal is to achieve even io. (that makes absolute sense) In my situation, where i need extra space on a SAN, it seems logical to separate the tables from the indexes, to achieve just that: roughly even IO.. (put tables on san, leave indexes on raid10 cluster) Or am i being silly? Cheers, WBL -- Patriotism is the conviction that your country is superior to all others because you were born in it. -- George Bernard Shaw
Re: [PERFORM] poor performance when recreating constraints on large tables
On Wed, Jun 8, 2011 at 10:57 PM, Greg Smith g...@2ndquadrant.com wrote: Samuel Gendler wrote: Sure, but if it is a query that is slow enough for a time estimate to be useful, odds are good that stats that are that far out of whack would actually be interesting to whoever is looking at the time estimate, so showing some kind of 'N/A' response once things have gotten out of whack wouldn't be unwarranted. The next question is what are you then going to do with that information? The ability to track some measure of progress relative to expectations is mainly proposed as something helpful when a query has gone out of control. When that's happened, the progress meter normally turns out to be fundamentally broken; the plan isn't happening at all as expected. So, as you say, you will get an N/A response that says the query is out of control, when in the cases where this sort of thing is expected to be the most useful. Well, in my case, the use I'd put it to is a query that is necessarily long running (aggregations over large quantities of data that take a minute or two to complete), and the stats are accurate enough that it would potentially let me show a progress meter of some kind in the few places where such queries are run interactively rather than on a schedule. Not that I'm really thinking seriously about doing so, but there are places in code I maintain where such a thing could prove useful if its accuracy is reasonable for the queries in question. ENough to at least toy with the suggested sequence method and see what happens when I've got some spare time to play.
[PERFORM] Triggering autovacuum
Hi, everyone. Some people with whom I'm working, and who have an 8.3 system running under Windows, asked me to look into their performance issues. They have a 1.5 GB database with a few dozen tables, and 500,000 records at most. They said that their system has been running for a few days, doing lots of INSERTs and SELECTs, and that the performance has gotten worse and worse over time. (I don't have numbers to share.) It's true that the computer is being used by other processes as part of a black-box manufacturing system, but those are pretty constant in CPU, disk, and memory needs, so I don't think that we would expect to see degradation over time as a result of that work. I looked at the system, and found that we need to change effective_cache_size, such that it'll match the system cache number in the Windows performance monitor. So yes, we'll take care of that, and I expect to see some improvement. But the really surprising thing to me was that autovacuum hadn't run at all in the last three days. I checked, and the autovacuum parameter was set in postgresql.conf, and using show in psql shows me that it was set. But when I looked at pg_stat_user_tables, there was no indication of autovacuum *ever* having run.We also fail to see any autovacuum processes in the Windows process listing. Could this be because we're only doing INSERTs and SELECTs? In such a case, then we would never reach the threshold of modified tuples that autovacuum looks for, and thus it would never run. That would, by my reasoning, mean that we'll never tag dead tuples (which isn't a big deal if we're never deleting or updating rows), but also that we'll never run ANALYZE as part of autovacuum. Which would mean that we'd be running with out-of-date statistics. I ran a manual vacuum analyze, by the way, and it's taking a really long time (1.5 hours, as of this writing) to run, but it's clearly doing something. Moreover, when we went to check on our vacuum process after about an hour, we saw that autovacuum had kicked in, and was now running. Could it be that our manual invocation of vacuum led to autovacuum running? I have a feeling that our solution is going to have to involve a cron type of job, running vacuum at regular intervals (like in the bad old days), because autovacuum won't get triggered. But hey, if anyone has any pointers to offer on this topic, I'd certainly appreciate it. Reuven -- Reuven M. Lerner -- Web development, consulting, and training Mobile: +972-54-496-8405 * US phone: 847-230-9795 Skype/AIM: reuvenlerner -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgresql on itanium server
Jochen Erwied joc...@erwied.eu writes: Thursday, June 9, 2011, 12:45:06 PM you wrote: As the URL mentions, you can build with CFLAGS=-ffast-math, that should work for PostgreSQL too. I just tried this with the source for 9.0.4, at least with this version the build will not complete since there is a check in src/backend/utils/adt/date.c throwing an error if FAST_MATH is active. Yeah. See http://archives.postgresql.org/pgsql-bugs/2002-09/msg00169.php and following discussions, which eventually led to adding the #error. Now this was all done in regards to PG's original floating-point timestamp implementation. It's possible that in an integer-datetimes build (which is now the default) we don't need to forbid -ffast-math to prevent strange datetime results. But nobody's done the work to prove that, because there isn't any particularly good reason to enable -ffast-math in a database in the first place. (Other than coping with brain-dead platforms, I guess.) However ... I'm not sure I believe that this is related to the OP's problem anyway. Postgres doesn't normally work with any denormalized numbers, so the messages he's seeing probably stem from some other sort of shortcoming in the hardware FP support. It would be interesting to see specific examples of SQL operations that trigger the kernel message. 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] Triggering autovacuum
Reuven M. Lerner wrote: Could this be because we're only doing INSERTs and SELECTs? In such a case, then we would never reach the threshold of modified tuples that autovacuum looks for, and thus it would never run. That would, by my reasoning, mean that we'll never tag dead tuples (which isn't a big deal if we're never deleting or updating rows), but also that we'll never run ANALYZE as part of autovacuum. Which would mean that we'd be running with out-of-date statistics. The computation for whether the auto-analyze portion of autovacuum runs takes into account INSERT traffic, so the stats don't go too far out of data on this style of workload. The one for the vacuum work only considers dead rows. So your case should be seeing regular entries for the last auto-analyze, but possibly not for last auto-vacuum. Eventually autovacuum will kick in anyway for transaction id wraparound, and that might be traumatic when it does happen. You might want to schedule periodic manual vacuum on these tables to at least have that happen at a good time. Wraparound autovacuum has this bad habit of finally kicking in only during periods of peak busy on the server. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us PostgreSQL 9.0 High Performance: http://www.2ndQuadrant.com/books -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [GENERAL] [PERFORM] [PERFORMANCE] expanding to SAN: which portion best to move
On 06/09/2011 07:43 AM, Willy-Bas Loos wrote: Well, after reading your article i have been reading some materail about it on the internet, stating that separating indexes from data for performance benefits is a myth. I found your comment So then a single query will only ever access one of both at a time. very smart (no sarcasm there). I also found a thread http://asktom.oracle.com/pls/asktom/f?p=100:11:0P11_QUESTION_ID:901906930328 on AskTom that said mainly the goal is to achieve even io. (that makes absolute sense) The idea that separating indexes and tables from one another via a tablespace is inherently good is a myth. Particularly nowadays, where the fastest part of a drive is nearly twice as fast as the slowest one in sequential transfers, and the ratio between sequential and random I/O is huge. Trying to get clever about breaking out a tablespace is unlikely to outsmart what you'd get if you just let the OS deal with that stuff. What is true is that when you have multiple tiers of storage speeds available, allocating the indexes and tables among them optimally is both difficult and potentially worthwhile. A customer of mine has two drive arrays, one of which is about 50% faster than the other; second was added as expansion once the first filled. Nowadays, both are 75% full, and I/O on each has to be carefully balanced. Making sure the heavily hit indexes are on the fast array, and that less critical things are not placed there, is the difference between that site staying up or going down. The hidden surprise in this problem for most people is the day they discover that *the* most popular indexes, the ones they figured had to go on the fastest storage around, are actually sitting in RAM all the time anyway. It's always fun and sad at the same time to watch someone spend a fortune on some small expensive storage solution, move their most performance critical data to it, and discover nothing changed. Some days that works great; others, it's no faster all, because that data was already in memory. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us PostgreSQL 9.0 High Performance: http://www.2ndQuadrant.com/books
Re: [PERFORM] Oracle v. Postgres 9.0 query performance
* Tony Capobianco (tcapobia...@prospectiv.com) wrote: HashAggregate (cost=4391163.81..4391288.05 rows=9939 width=12) - Hash Join (cost=14.78..4344767.23 rows=9279316 width=12) Hash Cond: (o.emailcampaignid = s.emailcampaignid) - Seq Scan on openactivity o (cost=0.00..3529930.67 rows=192540967 width=12) - Hash (cost=8.79..8.79 rows=479 width=4) - Seq Scan on ecr_sents s (cost=0.00..8.79 rows=479 width=4) Yikes. Two sequential scans. Err, isn't that more-or-less exactly what you want here? The smaller table is going to be hashed and then you'll traverse the bigger table and bounce each row off the hash table. Have you tried actually running this and seeing how long it takes? The bigger table doesn't look to be *that* big, if your i/o subsystem is decent and you've got a lot of memory available for kernel cacheing, should be quick. Just out of curiosity, is there any chance that this kind of query is speeding up in 9.1 because of following changes? * Allow FULL OUTER JOIN to be implemented as a hash join, and allow either side of a LEFT OUTER JOIN or RIGHT OUTER JOIN to be hashed (Tom Lane) Previously FULL OUTER JOIN could only be implemented as a merge join, and LEFT OUTER JOIN and RIGHT OUTER JOIN could hash only the nullable side of the join. These changes provide additional query optimization possibilities. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Oracle v. Postgres 9.0 query performance
Tatsuo Ishii is...@postgresql.org writes: Just out of curiosity, is there any chance that this kind of query is speeding up in 9.1 because of following changes? * Allow FULL OUTER JOIN to be implemented as a hash join, and allow either side of a LEFT OUTER JOIN or RIGHT OUTER JOIN to be hashed (Tom Lane) The given query wasn't an outer join, so this wouldn't affect it. 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
