Re: [HACKERS] [GENERAL] query log corrupted-looking entries
On Wednesday 23 May 2007 1:04 pm, George Pavlov wrote: Hoping to resurrect this thread. I am seeing more and more of this as the database gets more usage and it really messes up query log analysis. A quick summary: When I posted this was getting corrupted query log entries. I still am. They look like this (the problem line + one line before and after): 2007-05-15 14:24:52 PDT [3859]: [968-1] LOG: duration: 0.071 ms 2007-05-15 14:24:52 PDT [3859]: [969-1] LOG: statement2007-05-15 14:24:52 PDT [3885]: [208-1] LOG: duration: 11.800 ms FWIW, I've also been seeing this sort of query log corruption for as long as I can remember, 7.1 through 8.2, HPUX (parisc, ia64), Linux on intel, amd... Ed ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [GENERAL] Image storage questions
I asked this on general, but got no answer on this particular point, maybe someone here knows. Are blobs are stored in the shared memory cache upon retrieval? I ask because we're trying to decide whether to store an enormous number of images in PostgreSQL, and I'd be concerned that frequent retrieval of those images would effectively empty the cache of the rest of the non-image data. I watched the pg_statio* numbers during some test retrievals, and it appears the reads and cache hits on the binary image data are not being counted. I'm wondering if that is the truth of what's going on, or just an accounting oversight. Thanks. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] Image storage questions
On Friday May 20 2005 10:20 am, Tom Lane wrote: Ed L. [EMAIL PROTECTED] writes: I asked this on general, but got no answer on this particular point, maybe someone here knows. Are blobs are stored in the shared memory cache upon retrieval? pg_largeobject is treated exactly the same as any other table, if that's what you are asking. Yes, that was my question. Are large object rows also vacuumed just as the other tables/rows? Thanks. Ed ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] RFC: built-in historical query time profiling
Hackers,
I'd like to pose a problem we are facing (historical query time
profiling) and see if any of you interested backend gurus have
an opinion on the promise or design of a built-in backend
solution (optional built-in historical query time stats), and/or
willingness to consider such a patch submission.
Our Problem: We work with 75+ geographically distributed pg
clusters; it is a significant challenge keeping tabs on
performance. We see degradations from rogue applications,
vacuums, dumps, bloating indices, I/O and memory shortages, and
so on. Customers don't generally tell us when applications are
slow, so we need to know for ourselves in a timely manner. At
present, we can remotely and systematically query system
relations for diskspace usage, detailed I/O usage,
index/sequential scans, and more. But our _ultimate_ DB
performance measure is query execution time. Obviously, you can
measure that now in an ad hoc fashion with EXPLAIN ANALYZE, and
by examining historical logs. But we need to be able to see the
history in a timely fashion to systematically identify
customer-experienced execution time degradations for query
patterns of interest without any visual log inspection
whatsoever, and correlate those with other events. We can do
this by writing programs to periodically parse log files for
queries and durations, and then centralizing that information
into a db for analysis, similar to pqa's effort. Short of a
backend solution, that's what we'll do.
Backend solution?
But being able to query the database itself for historical
execution time statistics for query patterns of interest is
very attractive. Such functionality would seem generally very
useful for other deployments. Below is a rough novice sketch of
an **optional** scheme for doing so in the backend (I'm sure
it's incomplete/faulty in this presentation; I'm really trying
to determine if there are any fatal short-comings over the
log-parsing approach).
Suppose there were some sort of system relations like these:
pg_query_profile (
id integer,
namevarchar not null unique,
sql_regex varchar not null unique,
enabled boolean not null
)
pg_query_profile_history (
profile_id integer not null, -- refs
pg_query_profile.id
count integer, -- number of matches in period
avgdur float not null, -- avg duration in secs
mindur float not null, -- min duration
maxdur float not null, -- max duration
errors bigint not null, -- errors in period
period_starttimestamp not null,
period_end timestamp not null
)
Each row in pg_query_profile_history would represent execution
time stats for queries matching a given regex for a given
interval. The sql_regex column would be a user-specified value
matching queries of interest. For example, if I were
interested in profiling all queries of the form
SELECT * FROM result WHERE key = 123
, then maybe my sql_regex would basically be
INSERT INTO pg_query_profile (name, sql_regex)
VALUES ('Result Queries',
'SELECT * FROM result WHERE key = \d+');
Then, as each query completed, that query was (optionally!)
checked against existing pg_query_profile.sql_regex values for a
patten match, and any matching pg_query_profile rows for that
period were then updated with the duration data. I can imagine
wishing to collect this data for 10-20 most-common queries in
5-minute intervals for the past 24 hours or so.
One could then systematically identify degradations beyond 1.0
seconds with a query similar to the following:
SELECT COUNT(1)
FROM pg_query_profile_view
WHERE name = 'Result Queries'
AND avgdur 1.0;
Once the data is there, it opens up a lot of possibilities for
systematic monitoring.
Some possible objections (O) and answers (A):
1) O: But wouldn't this impose too much overhead in the backend
for transactions for folks who don't want/need this feature? A:
Not if it were completely optional, right?
2) O: If enabled, there is no way you'd want to impose an
update query on each select query! A: True. I envision the
query profile as cached in shared memory and only written to
disk a user-configurable every so often.
3) O: Regular expression evaluation is computationally
expensive! A: I'm imagining it might add a few milliseconds to
each query, which would be well worth the benefit to us in
having the most important metric easily accessible.
GUC variables might include:
query_profile : boolean on/off for profiling
query_profile_interval : how often to write out stats
Re: [HACKERS] RFC: built-in historical query time profiling
On Wednesday March 23 2005 4:11, Mark Kirkwood wrote: Is enabling the various postgresql.conf stats* options and taking regular snapshots of pg_stat_activity a possible way to get this? I don't see how; the duration is the key measurement I'm after, and I don't believe it is available anywhere but the logs. Thanks. Ed ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] RFC: built-in historical query time profiling
On Wednesday March 23 2005 3:34, Tom Lane wrote: This is going to fall down on exactly the same objections that have been made to putting the log messages themselves into tables. The worst one is that a failed transaction would fail to make any entry whatsoever. There are also performance, locking, and security issues to think about. (Example: heaven help you if someone decides to VACUUM FULL the log table --- that would block every other process due to exclusive lock.) I see your point. The ugliness of log-parsing beckons. Thanks. Ed ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] RFC: built-in historical query time profiling
On Wednesday March 23 2005 5:14, Mark Kirkwood wrote: - decide on a snapshot interval (e.g. 30 seconds) - capture pg_stat_activity every interval and save the results in a timestamped copy of this view (e.g. add a column 'snap_time') That might serve for some purposes, but log-parsing sounds far simpler and closer to our objectives. For example, I want to be able to identify a query that normally takes 10ms but is now taking 200ms... Thanks. Ed ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Identifying time of last stat reset via sql
I asked this on general, but didn't receive any responses. Is it possible via SQL to identify the time of the last stat reset (or pg_stat_reset() call)? This is what I'm lacking to be able to measure query activity volume over time via SQL. Maybe a function similar to the fictitious pg_stat_get_last_reset_time() below? select sum(n_tup_ins + n_tup_upd + n_tup_del) / (now() - pg_stat_get_last_reset_time()) as write_qps from pg_stat_all_tables If not, would this be considered worthy of a TODO item? Or is there another built-in approach designed for determining QPS, etc? Ed ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [Slony1-general] Re: [GENERAL] Slony-I 1.0.4 Released
Wow. First, thanks again for all your efforts, Jan. Second, I'm disappointed to hear the slony author and lead developer is leaving the slony leadership. When is that going to happen? And what does that mean with respect to your future involvement in slony? Ed On Friday October 22 2004 7:26, Jan Wieck wrote: Sorry folks, the Slony-I team has produced a great product, but the project management (that's mostly me here) sucks big time! Shortly after giving Chris Browne green light for the 1.0.4 announcement we found a way to guard against bug #896. That being a really bad one I decided to stop the 1.0.4 release and go for 1.0.5 including that fix. But I failed to make sure Chris, Justin and others involved in the announcement process get the message. I have just committed those changes and 1.0.5 should be available later today. I apologize for the confusion and look forward to retire from the Slony-I project leadership position in order to fully focus on the multimaster replication project Afilias has decided to put forward. Jan On 10/21/2004 5:55 PM, Chris Browne wrote: The Slony-I team is proud to present the 1.0.4 release of the most advanced replication solution for the most advanced Open Source Database in the world. The release tarball is available for download http://developer.postgresql.org/~wieck/slony1/download/slony1-1.0.4.tar .gz There are a limited number of new features this release largely in terms of adding in the ability to move or drop tables and sequences from replication: - SET DROP TABLE - drops a table from replication - SET DROP SEQUENCE - does the same for sequences - SET MOVE TABLE - moves a table from one replication set to another - SET MOVE SEQUENCE - moves a sequence from one replication set to another Other changes involve smoothing out the 'sharp edges' found by early adopters, notably including: - Frequently vacuuming pg_listener; growth of dead tuples could hurt performance - A cleanup process for pg_listener resolves cases where old slon processes may have terminated due to network problems, leaving backends around holding onto event notifications - Lowered lock level on sl_event, resolving issues where pg_dump would block Slony-I - Purges CONFIRM entries for nodes that don't exist anymore - Substantially increased documentation - More sophisticated administration scripts - Now uses string comparison for user defined types that do not have a suitable comparison operation - Safer log purging - Various other bug fixes and improved cleanliness. - As of 1.0.4, the slon replication engine refuses to work against any database that does not have the stored procedures for the same version loaded or where the shared object containing the C language support functions and the log trigger does not match the version number. in a cluster must be upgraded at once. See the HISTORY-1.0 file for a detailed list of changes. http://slony.info/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] sequence_name.sequence_name != sequence_name?
When a sequence is created in 7.3.2, it appears you get a new table for each sequence object. Is it ever possible for the sequence_name in a sequence relation not to match the name of the relation itself? For example, suppose I create a table: CREATE TABLE t1(id serial); A new relation called 't1_id_seq' is created where t1_id_seq.sequence_name = 't1_id_seq' Is that always true? Ed ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] more contrib: log rotator
On Friday April 4 2003 9:16, scott.marlowe wrote: That said, a log rotation capability built right into pg_ctl or thereabouts would be a very nice feature. I.e. 'pg_ctl -r 86400 -l $PGDATA/logs/pgsql start' where -r is the rotation period in seconds. If it's an external program that pg_ctl calls that's fine, and it could even just be a carbon copy of apache's log rotater if their license is compatible (isn't it?) By way of feature ideas, one very convenient but not widely used feature of Apache's log rotator is the ability to specify a strftime() format string for the file extension. For example, if I want to have my logs rollover every 24 hours and be named log.Mon, log.Tue, log.Wed, I say something like pg_ctl start | rotatelogs 86400 %a This causes the logs to overwrite themselves every seven days, taking log maintenance time to very near zero. We also customized our use of it to allow us to automatically move existing logs out of the way to log.1, log.2, or to simply overwrite existing logs. Ed ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] more contrib: log rotator
On Friday April 4 2003 10:04, Ed L. wrote: By way of feature ideas, one very convenient but not widely used feature of Apache's log rotator is the ability to specify a strftime() format string for the file extension. For example, if I want to have my logs rollover every 24 hours and be named log.Mon, log.Tue, log.Wed, I say something like pg_ctl start | rotatelogs 86400 %a More accurately, something like this: pg_ctl start | rotatelogs 86400 log.%a Ed ---(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: [HACKERS] more contrib: log rotator
On Friday April 4 2003 10:19, Tom Lane wrote: I feel we really ought to have *some* rotator included in the standard distro, just so that the Admin Guide can point to a concrete solution instead of having to arm-wave about what you can get off the net. If someone can offer a better alternative than Andrew's, great, let's see it. Out of curiosity, are there issues preventing inclusion of Apache's log rotation code? It seems you'd be hard-pressed to find a more battle-hardened log rotator. Obviously some people also wish to rotate based on log file size, so adding both to contrib at least seems sensible. Ed ---(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: [HACKERS] sequence_name.sequence_name != sequence_name?
On Friday April 4 2003 10:24, Tom Lane wrote: Ed L. [EMAIL PROTECTED] writes: When a sequence is created in 7.3.2, it appears you get a new table for each sequence object. Is it ever possible for the sequence_name in a sequence relation not to match the name of the relation itself? In general I'd counsel that you should ignore the sequence_name field anyway. It's vestigial. A related question: Is there a single generalized SQL query which can yield the set of (sequence_name, last_value) pairs for all sequence objects? The fact that each sequence is its own relation seems to block that, and the query constructed from grabbing sequence names from pg_class gets quite long for more than just a few sequence objects... Ed ---(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: [HACKERS] more contrib: log rotator
On Friday April 4 2003 11:58, Tom Lane wrote:
scott.marlowe [EMAIL PROTECTED] writes:
rotatelogs is in my path and all, it just never sees it.
You mean the command fails? Or just that it doesn't capture output?
$po_path ${1+$} /dev/null | $PGPATH/rotatelogs $logfile $DURATION
21
Most if not all of the postmaster's log output goes to stderr, so you'd
need
$po_path ${1+$} /dev/null 21 | $PGPATH/rotatelogs ...
to have any hope of useful results.
Hmmm. I would have agreed 21 was needed, too, but this command seems to
routinely capture all output, including ERRORs:
nohup pg_ctl start | nohup rotatelogs server_log.%a 86400
Ed
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] more contrib: log rotator
On Friday April 4 2003 2:17, scott.marlowe wrote: OK, So I tried putting the 21 before the | and all. No matter what I try, every from the | on is ignored. ps doesn't show it, and neither does pg_ctl status. Both show a command line of /usr/local/pgsql/bin/postmaster as the only input to start the server. Not clear if you're looking at it this way or if this is your problem, but you can't really tell there is log rotation going on just by grepping ps for postmaster because ps does not typically show the postmaster and the rotatelogs together on the same line. I wouldn't expect pg_ctl status to know anything at all about rotatelogs when you pipe it like this. Ed ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] index corruption?
On Feb 13, 2003, Tom Lane wrote: Laurette Cisneros [EMAIL PROTECTED] writes: This is the error in the pgsql log: 2003-02-13 16:21:42 [8843] ERROR: Index external_signstops_pkey is not a btree This says that one of two fields that should never change, in fixed positions in the first block of a btree index, didn't have the right values. I am not aware of any PG bugs that could overwrite those fields. I think the most likely bet is that you've got hardware issues ... have you run memory and disk diagnostics lately? I am seeing this same problem on two separate machines, one brand new, one older. Not sure yet what is causing it, but seems pretty unlikely that it is hardware-related. Ed ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] index corruption?
On Monday March 31 2003 3:38, Ed L. wrote: On Feb 13, 2003, Tom Lane wrote: Laurette Cisneros [EMAIL PROTECTED] writes: This is the error in the pgsql log: 2003-02-13 16:21:42 [8843] ERROR: Index external_signstops_pkey is not a btree This says that one of two fields that should never change, in fixed positions in the first block of a btree index, didn't have the right values. I am not aware of any PG bugs that could overwrite those fields. I think the most likely bet is that you've got hardware issues ... have you run memory and disk diagnostics lately? I am seeing this same problem on two separate machines, one brand new, one older. Not sure yet what is causing it, but seems pretty unlikely that it is hardware-related. I am dabbling for the first time with a (crashing) C trigger, so that may be the culprit here. Ed ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] index corruption?
On Monday March 31 2003 3:54, Tom Lane wrote: Ed L. [EMAIL PROTECTED] writes: I am seeing this same problem on two separate machines, one brand new, one older. Not sure yet what is causing it, but seems pretty unlikely that it is hardware-related. I am dabbling for the first time with a (crashing) C trigger, so that may be the culprit here. Could well be, although past experience has been that crashes in C code seldom lead directly to disk corruption. (First, the bogus code has to overwrite a shared disk buffer. If you follow what I consider the better path of not making your shared buffers a large fraction of the address space, the odds of a wild store happening to hit a disk buffer aren't high. Second, once it's corrupted a shared buffer, it has to contrive to cause that buffer to get written out before the core dump occurs --- in most cases, the fact that the postmaster abandons the contents of shared memory after a backend crash protects us from this kind of failure.) When you find the problem, please take note of whether there's something involved that increases the chances of corruption getting to disk. We might want to try to do something about it ... It is definitely due to some rogue trigger code. Not sure what exactly, but if I remove a certain code segment the problem disappears. Ed ---(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
