Solved

Timing queries/updates non-intrusively in a web application

Posted on 2006-07-12
3
307 Views
Last Modified: 2008-03-17
I have inherited a fairly complicated web application, which I want to make minimal changes to. It is PHP-based. It isn't as fast as it needs to be, so I'm throwing some new hardware at it and will edit the minimum that I can get away with to improve its performance.

Unfortunately, the application's PHP hasn't been structured in a way that makes it easy to time database queries/updates/appends, so I'd like to see if it I get a sense of where the inefficient operations are by looking at the PostgreSQL logs.

I notice that there is a log file analyser written in Ruby that looks to be good for my purposes: http://pqa.projects.postgresql.org/

Would enabling all of the logging required for this analyser be likely to bring the site to its knees? Or do you reckon I could run the site for a week or so with logging enabled to collect a fair picture of the bottle-necks?
0
Comment
Question by:rstaveley
  • 2
3 Comments
 
LVL 22

Accepted Solution

by:
earth man2 earned 250 total points
ID: 17112452
You have not had any replies so far me thinks because your question is analogeous to how long is a piece of string.

Like the uncertainty principle you cannot time queries non-intrusively.  The impact depends on how maxed out your db server is ie cpu bound or disk io limited.

Minimise the effect of statistics collection by making statistics write to a different disk than your DB & indexes & OS.
0
 
LVL 17

Author Comment

by:rstaveley
ID: 17113767
What I'm really trying to get is a list of individual queries / updates which take longer than the rest, so I can focus on them. In MySQL, they have the "slow query log" (see http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html), which would probably suffice for me, if such a thing existed for PostgreSQL. It would be nice also to see which style of query is executed most often. Armed with this knowledge, I can concentrate on optimising those queries alone.

I'm not really worried about the overhead of statistics collection so much as messing around with unfamiliar code prematurely. I don't want to sprinkle the code with timing hooks to find out which bits are slow. If I can see some obvious SQL baddies before digging into the PHP code, it could allow me to locate the offending code more quickly.

An alternative approach would be to look at the Apache logs to see which pages are taking the longest to render, but taking timings from the PostgreSQL logs would be much more focused. It *may* be that no optimisation is required in the SQL and all of the optimisation required would be achieved by using an optimiser with the PHP code... but I'd like to be able to find out.
0
 
LVL 17

Author Comment

by:rstaveley
ID: 17347631
Thanks for the advice, earthman2. I left this to run to see if my question would get any more responses, but it is time to close it now. That Ruby analyser is the best thing I've found, but it isn't ideal for identifying design weaknesses. There's no substitute really for timing the queries within the application and that means improving its organisation so that all queries go via a common call wrapper.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Errors while compiling Postgresql Repmgr 6 690
OS X postgresql logging 1 288
vcenter 6 u2 install question 1 110
put constant from select to insert into field  Posgresql 2 88
Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question