Re: [ADMIN] Interpreting query debug output
> From: Tom Lane <[EMAIL PROTECTED]> > Date: Tue, 18 May 2004 21:06:43 -0400 > To: Steve Lane <[EMAIL PROTECTED]> > Cc: [EMAIL PROTECTED] > Subject: Re: [ADMIN] Interpreting query debug output > > Steve Lane <[EMAIL PROTECTED]> writes: >> I have a database that is exhibiting sluggishness under load. Suspecting >> that some queries may be poorly optimized, I turned on a fair amount of >> debugging output in the logs. But I could use some help interpreting it. > > I think you're going at this all wrong. EXPLAIN ANALYZE should be the > first tool you turn to, not low-level stats. Browsing the archives of > the pgsql-performance mailing list may help you get started. > > regards, tom lane > Hmm. When I do a process listing I can see that there are postgres processes occupying large chunks of CPU, sometimes in the 60-99% range, for long enough to be noticeable in the process list. I'd like to capture those process IDs and then correlate them with the stats captured in the log to see why they take so much CPU. If I want to EXPLAIN ANALYZE, I have to pick individual queries. The query logic of the application is distributed across many source files. I'd have to do quite some combing to recover a list of all queries the system runs. I figure distilling the log output might be the best approach. Given these points, is this still the wrong approach? Even if so, I'd still love to know how to read the debug output. -- sgl ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Set Timestamp
Maybe this will help. http://www.postgresql.org/docs/7.4/static/datatype-datetime.html On Tue, 2004-05-18 at 09:17, Hemapriya wrote: > Hi, > > Does anyone know how to set timestamp values in > postgres. > > Thanks in Advance. > > - Priya > > > > > __ > Do you Yahoo!? > SBC Yahoo! - Internet access at a great low price. > http://promo.yahoo.com/sbc/ > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Interpreting query debug output
Steve Lane <[EMAIL PROTECTED]> writes: > I have a database that is exhibiting sluggishness under load. Suspecting > that some queries may be poorly optimized, I turned on a fair amount of > debugging output in the logs. But I could use some help interpreting it. I think you're going at this all wrong. EXPLAIN ANALYZE should be the first tool you turn to, not low-level stats. Browsing the archives of the pgsql-performance mailing list may help you get started. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] "IDENT authentication failed" but I'm not using ident
Nico De Ranter <[EMAIL PROTECTED]> writes: > \connect: FATAL: IDENT authentication failed for user "nico" > I changed /etc/postgresql/pg_hba.conf so it only contains > localall all trust=20 > and restarted postgres but I still get the same error message. > Any idea why the import complains about IDENT authentication when=20 > I'm not even using it and how to get around it? If you're getting that message then you *are* using IDENT auth. My bet is that you changed the wrong config file. /etc/postgresql is not a very standard name for a Postgres data directory ... > BTW: is there a way to create a database and then change ownership > to somebody else? No, but you can do it in one step. http://www.postgresql.org/docs/7.4/static/sql-createdatabase.html regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] Interpreting query debug output
Hello all: I have a database that is exhibiting sluggishness under load. Suspecting that some queries may be poorly optimized, I turned on a fair amount of debugging output in the logs. But I could use some help interpreting it. For the record, this is Postgres 7.2.1. I've already been rightly chastised for using such an old version, and the upgrade is scheduled. Here's some sample output: 2004-05-18 15:46:04 [27129] DEBUG: connection: host=127.0.0.1 user=postgres database=iep_db 2004-05-18 15:46:04 [26914] DEBUG: query: SELECT CAST(name_first || ' ' || name_last AS TEXT) FROM iep_personnel WHERE id_personnel = $1 2004-05-18 15:46:08 [27015] DEBUG: query: select * from iep_student where id_student = '1002863'; 2004-05-18 15:46:08 [26914] DEBUG: query: SELECT $1 2004-05-18 15:46:08 [26914] DEBUG: query: SELECT CAST(name_county AS TEXT) FROM iep_county WHERE id_county = $1 2004-05-18 15:46:05 [26892] DEBUG: QUERY STATISTICS ! system usage stats: ! 106.317726 elapsed 21.08 user 0.24 system sec ! [21.11 user 0.25 sys total] ! 0/0 [0/0] filesystem blocks in/out ! 7959/191 [8515/747] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent ! 0/0 [0/0] voluntary/involuntary context switches ! postgres usage stats: ! Shared blocks: 0 read, 0 written, buffer hit rate = 100.00% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written Some questions: 1) The statistics appear to be reporting on a series of queries. These queries are all from inside a function, hence a single transaction. Are the query statistics being reported here on a per-transaction basis? Two numbers leap out: 106 seconds for the query, and a high number on the page faults line. 2) 106 seconds for the operation is too horrible for words. All of the operations are where-clauses involving single, index id fields. The server load is heavy, but I can't account for this slowness due to stupidly-written queries. Anyone see anything suspect in the queries? 3) I don't know how to read the page faults line. Is this referring to system virtual memory, or postgres buffer management? What does this line mean, and what parameter does it imply needs tuning? I have RAM to spare on this box, as follows: [EMAIL PROTECTED] log]# cat /proc/meminfo total:used:free: shared: buffers: cached: Mem: 6161747968 3970502656 21912453120 13320192 3259764736 Swap: 2089209856 47116288 2042093568 MemTotal: 6017332 kB MemFree: 2139888 kB MemShared: 0 kB Buffers: 13008 kB Cached:3138220 kB SwapCached: 45144 kB Active:1362996 kB Inactive: 2210704 kB HighTotal: 5177216 kB HighFree: 1643716 kB LowTotal: 840116 kB LowFree:496172 kB SwapTotal: 2040244 kB SwapFree: 1994232 kB Some key postgresql.conf parameters: max_connections = 150 shared_buffers = 175000# 2*max_connections, min 16 sort_mem = 32000# min 32 effective_cache_size = 10 # default in 8k pages log_connections = true log_timestamp = true log_pid = true debug_print_query = true show_query_stats = true Sorry for the big dump. I'm feeling a bit at sea in this information and I need to know if I'm swimming in the right direction. Thanks for any help. -- sgl ---(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
[ADMIN] pg_hba.conf and groups
I am trying to allow individuals within a PostgreSQL (v7.4) group to
connect to the backend. The relevant line in pg_hba.conf is (I
believe):
host+/32 md5
Note that I have replaced the contents of the real fields with <...>,
but that these match across what follows. I understand the + to allow
access to members of the group.
The contents of the system catalogs include the following:
# select * from pg_catalog.pg_shadow;
usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil
| useconfig
-+--+-+--+---++--+---
| 105 | f | f| f | md5... | |
# select * from pg_catalog.pg_group;
groname | grosysid |grolist
-+--+---
| 100 | {100,101,102,104,105}
Nevertheless, the following command fails:
psql --host --dbname= --username=
psql: FATAL: no pg_hba.conf entry for host "", user "",
database "", SSL off
Have I set this up incorrectly? Is there any way to query the backend
in order to identify who it thinks has access to what?
Thanks for your help.
Cheers,
Brook
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] Postgresql ssl mode
Mario Alberto Soto Cordones wrote: Hi, the certificate was create in the directori of data, an the permision its to user postgres. but the database not start Not sure if this is supposed to go on the list, so send me your pg_hba.conf and postgresql.conf files (off-list). I believe you have an error in one of them, and that is your problem with startup. Moderator: can we share config files on the list or is that stuff to be done in private? -- Mitch ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Postgresql ssl mode
Hi, the certificate was create in the directori of data, an the permision its to user postgres. but the database not start Thank Mario > Hi Mario, > > Did you remember to create SSL certificates, place them in your > pgsql/data directory and set permissions on those files? > > This is what keeps PG from firing up in SSL mode (on my setup, at > least). > > HTH, > > -- Mitch > > Mario Alberto Soto Cordones wrote: > >> Hi don´t say error only not start >> >> Thank >> >> >>>Mario Alberto Soto Cordones wrote: >>> I have installed a postgresql 7.4.2, and when try to up in ssl mode this not up. then comment the ssl line in postgresql.conf and this start correctly. >>> >>>Tell us the error messages, then we can maybe help you. > > > ---(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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] Postgresql ssl mode
Hi Mario, Did you remember to create SSL certificates, place them in your pgsql/data directory and set permissions on those files? This is what keeps PG from firing up in SSL mode (on my setup, at least). HTH, -- Mitch Mario Alberto Soto Cordones wrote: Hi don´t say error only not start Thank Mario Alberto Soto Cordones wrote: I have installed a postgresql 7.4.2, and when try to up in ssl mode this not up. then comment the ssl line in postgresql.conf and this start correctly. Tell us the error messages, then we can maybe help you. ---(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: [ADMIN] Postgresql ssl mode
Hi don´t say error only not start Thank > Mario Alberto Soto Cordones wrote: >> I have installed a postgresql 7.4.2, and when try to up in ssl mode >> this not up. then comment the ssl line in postgresql.conf and this >> start correctly. > > Tell us the error messages, then we can maybe help you. > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Postgresql ssl mode
Mario Alberto Soto Cordones wrote: > I have installed a postgresql 7.4.2, and when try to up in ssl mode > this not up. then comment the ssl line in postgresql.conf and this > start correctly. Tell us the error messages, then we can maybe help you. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] cannot connect to postgres db, strange error!
Hi, Im running postgres 7.4.3 on HP-UX 11.11. When I want to connect to my db, I have the following error: psql: FATAL: _mdfd_getrelnfd: cannot open relation pg_class: Permission denied Do you have any idea where i have to look. I already looked at pg_hba.conf file and it looks ok. Regards __ Do you Yahoo!? SBC Yahoo! - Internet access at a great low price. http://promo.yahoo.com/sbc/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] Postgresql ssl mode
Hi... first sorry by my english. I have installed a postgresql 7.4.2, and when try to up in ssl mode this not up. then comment the ssl line in postgresql.conf and this start correctly. what can do ??? Thank -- Ing. Mario Soto Cordones Venezolana de Avaluos www.venezolanadeavaluos.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[ADMIN] HEC Montreal use Postgres
Interesting article on May Linux Journal ( pag 44 ): [...] we installed Spamity, which parses mail logs from the four Postfix servers and update a Postgresql database running on the test server [...] Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] Set Timestamp
Hi, Does anyone know how to set timestamp values in postgres. Thanks in Advance. - Priya __ Do you Yahoo!? SBC Yahoo! - Internet access at a great low price. http://promo.yahoo.com/sbc/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] GNUmakefile size 0
On Thu, May 13, 2004 at 04:19:36PM +0200, Laurens Wagemakers wrote: > Hai Tom, > > I just talked to the developers and we can use 7.1 now. I'd still counsel you very strongly to upgrade somewhat higher. There are an awful lot of bugs from those days. But in any case, you might want to grovel through recentish archives (last 3 months or so) for a post from Chris Browne. He posted a workaround for configure woes on Sol 8, and I bet it'll work for 9 also. A -- Andrew Sullivan | [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] pgplsql cookbook
Hi all I am looking for the cookbook mentioned in techdocs. I have been trying for a few days now and keep getting en error 500 from the website whether I go through the proxy at work or via dialup. Is it maybe mirrored somewhere else or available as a pdf? Kind Regards Craig ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] "IDENT authentication failed" but I'm not using ident
Hi, I'm trying to upgrade postgres to 7.4.2. I've dumped the database on 7.2.1 but when I try to recreate it by doing psql -e template1 (SELECT datdba FROM pg_database WHERE datname = 'template0'); DELETE 15 CREATE USER "ids" WITH SYSID 103 PASSWORD 'Yil8t8Wq1yFth' NOCREATEDB NOCREATEUSER; CREATE USER CREATE USER "nico" WITH SYSID 32 CREATEDB CREATEUSER; CREATE USER [...] DELETE FROM pg_group; DELETE 0 \connect: FATAL: IDENT authentication failed for user "nico" I changed /etc/postgresql/pg_hba.conf so it only contains localall all trust and restarted postgres but I still get the same error message. Any idea why the import complains about IDENT authentication when I'm not even using it and how to get around it? BTW: is there a way to create a database and then change ownership to somebody else? (e.g. I want to give a user 1 database to play with but I don't want to allow him to make any other databases) Thanks in advance, Nico -- - "It has been said that there are only two businesses that refer to customers as users: illegal drug trade and the computer industry." - Nico De Ranter Senior System Administrator Sony Service Center (NSCE/VPE-B) The Corporate Village, Da Vincilaan 7-D1 B-1935 Zaventem, Belgium Telephone: +32 (0)2 706 43 11 Fax: +32 (0)2 700 86 22 signature.asc Description: This is a digitally signed message part
FW: [ADMIN] Upgrading from 7.2 to 7.4.1 on Redhat 7
Hello Scott, thank you for this link. But I wonder, do I need to do this if postgres has never actually been used by any user of this server in the past? This is what happened when I tried each step of that page: 2. pg_dumpall > file.txt I received this error: pg_dumpall: could not connect to database template1: could not connect to server : No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"? 3. kill -INT `cat /usr/local/pgsql/data/postmaster.pid` postmaster.pid doesn't exist anywhere on my server. Since all of that failed, I'm not sure if I should move ahead with: mv /usr/local/pgsql /usr/local/pgsql.old "After you have installed PostgreSQL 7.4, create a new database directory and start the new server. Remember that you must execute these commands while logged in to the special database user account (which you already have if you are upgrading). /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data" Thanks for all the help that I have received so far, it is very much appreciated. :) Jeremy -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of scott.marlowe Sent: Tuesday, February 10, 2004 11:43 AM To: Jeremy Smith Cc: [EMAIL PROTECTED] Subject: Re: [ADMIN] Upgrading from 7.2 to 7.4.1 on Redhat 7 Hi Jeremy. Updating major versions (i.e. 7.2 to 7.3 or 7.4) requires you to dump and restore your database. This page explains it: http://www.postgresql.org/docs/7.4/static/install-upgrading.html If you still need some more help, feel free to ask, we're a pretty responsive community. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] How to determine if ODBC was compiled in?
Jared Evans wrote: > perhaps you misunderstood my question. I downloaded a binary package > off the Debian website. How do I determine what options it was > compiled with? That is my question. Look inside the source package in debian/rules. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
