Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 670
  • Last Modified:

Postgres slows down...find the guilty query

I vacuum 7.4.3 postgres often.
Our application uses pgxx library (no odbc).
I can log transactions and generally look at stats
BUT what I really need to do is to see which queries are slowing down postgres. I get to a point where postgres is using 90% of the CPU (linux), but the test I am running is repetitive (expect postgres to remain constant).

Is there a way to gather stats query by query?

Thanks in advance.
0
JerryNorton
Asked:
JerryNorton
  • 6
  • 6
  • 5
  • +1
1 Solution
 
gheistCommented:
Such situation is easily reached by not closing postgresql connections up to the limit that all the client daemons allocate their sort areas and exceed memory reasonably usable by applications on system running postgresql database server.
0
 
JerryNortonAuthor Commented:
I have only 4 fixed clients attaching to the database... 2 from my application (one to users database, another to billing db) and another 2 from Java using jdbc to same two db's...so there is nowhere near limit of clients and they are static anyway.
0
 
m1tk4Commented:
do you ANALYZE the databases after vacuuming?

You could try collecting information by using EXPLAIN on every query you have - this will predict the time it takes for the query to execute. Very good tool to identify missing/unused indices.

When CPU hits 90%, what happens to your RAM and swap?
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
earth man2Commented:
There is a statistics collector that you have to enable ( with some overhead )

http://www.postgresql.org/docs/8.1/static/monitoring-stats.html

This probably does not give you the real time monitoring that say Oracle gives you.  You pays your money and you gets your choice....
0
 
JerryNortonAuthor Commented:
Hi,
For some reason, only got notified today that y'all had answered.

>> Stats collector...yes that is what i am looking for information on.  I can see stats, but not something that will help locate the fault.
Had been doing VACUUM FULL on reboot, VACUUM ANALYZE on two most heavily changed tables.
When CPU is at 90%, RAM looks ok, but swap is being used.
0
 
gheistCommented:
What does vmstat 1 5 and iostat 1 5 show when system is 90% loaded?
0
 
earth man2Commented:
I would recommend upgrading to version PostgreSQL 8.1
7.4 has issues.
0
 
m1tk4Commented:
>> I would recommend upgrading to version PostgreSQL 8.1
>> 7.4 has issues.

Can you be more specific about the issues? I've been running several quite data-intensive applications on 7.4 for about a year and have seen none so far.
0
 
gheistCommented:
0
 
earth man2Commented:
Vacuum in 8.0 can be done wihout locking tables.  I've had 7.4 corrupt databases.
0
 
m1tk4Commented:
gheist:

[root@darth ~]# rpm -q postgresql
postgresql-7.4.8-1.RHEL4.1

the release you are linking to is from 2004.
0
 
gheistCommented:
Not your question - go away.
0
 
JerryNortonAuthor Commented:
Ok, the best statement was >> Can you be more specific about the issues?
I am looking for information on how to determine what specific query is running slowly while I use postgres.
My application is doing the usual things...inserting new records, updating them, deleting them. It has several tables of data it queries that are more or less static. I can log the queries, but this is HUGE amount of data and parsing that info would be onerous. I have taken a rudimentary look at the stats info, and it seems that there should be a way to track the time it takes for each insert/delete/query ... That's what this question is about.
0
 
gheistCommented:
You say over time it slows down.
Memory leak explains that slowdown without need for digging up queries.

Basically for queries you enable all runtime statistics.
Then create a query which multiplies table row count, row size and scan count from those tables.

Or you may log queries too.
0
 
m1tk4Commented:
http://archives.postgresql.org/pgsql-admin/2002-05/msg00106.php
http://www.postgresql.org/docs/7.4/interactive/runtime-config.html#RUNTIME-CONFIG-LOGGING

see 16.4.5.3 What To Log for log_duration

Before searching for memory leaks I would confirm that there is one. A sure sign of a memory leak as a problem is if your PostgreSQL server is faster right after the server reboot and then the performace drops down as you use it.
0
 
JerryNortonAuthor Commented:
m1tk4: YOU GOT IT... almost there, one quick add-on.... I would like to log only those statements that take longer than, say 500 ms . I think i did this correctly, but I still get ALL statements...so syslog fills quite quickly. What did I do wrong? Or can I divert these to some named file on another partition?

#---------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#---------------------------------------------------------------------------

# - Syslog -

syslog = 2                      # range 0-2; 0=stdout; 1=both; 2=syslog
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'

# - When to Log -
#log_statement=true
#client_min_messages = notice   # Values, in order of decreasing detail:
                                #   debug5, debug4, debug3, debug2, debug1,
                                #   log, info, notice, warning, error

#log_min_messages = notice      # Values, in order of decreasing detail:
                                #   debug5, debug4, debug3, debug2, debug1,
                                #   info, notice, warning, error, log, fatal,
                                #   panic

log_error_verbosity = default   # terse, default, or verbose messages

#log_min_error_statement = panic # Values in order of increasing severity:
                                 #   debug5, debug4, debug3, debug2, debug1,
                                 #   info, notice, warning, error, panic(off)

log_min_duration_statement = 500 # Log all statements whose
                                 # execution time exceeds the value, in
                                 # milliseconds.  Zero prints all queries.
                                 # Minus-one disables.

#silent_mode = false             # DO NOT USE without Syslog!

# - What to Log -

#debug_print_parse = false
#debug_print_rewritten = false
#debug_print_plan = false
#debug_pretty_print = false
#log_connections = false
log_duration = true
#log_pid = false
#log_statement = false
#log_timestamp = false
#log_hostname = false
#log_source_port = false
0
 
m1tk4Commented:
see log_min_duration_statement , same place (http://www.postgresql.org/docs/7.4/interactive/runtime-config.html#RUNTIME-CONFIG-LOGGING).

You can redirect it to a different file either by switching to stdout output and capturing it, or by changing the syslog_facility and configuring a new filter in /etc/syslog.conf.

I think you can tweak the verbosity of your output either with pretty_print or log_error_verbosity options.

Anyways, you should NOT have logging enabled at all times on a production system. This is something you enable to troubleshoot a problem temporarily.

0
 
m1tk4Commented:
I think log_statement and log_pid need to be enabled along with log_min_duration_statement for it to work, otherwise your config seems fine to me. Maybe forgot to restart?
0
 
JerryNortonAuthor Commented:
Alrighty, i'll just live with the constant output (it is all on a testbed, but it takes ~250000 transactions to occur, so hard to flog through all that).

0
 
gheistCommented:
You can import your log into relational database doing SQL....
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 6
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now