Jump to content


Photo

Monitor database queries?


  • Please log in to reply
4 replies to this topic

#1 lark

lark
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 01 October 2006 - 02:12 AM

Hi everyone,

I'm looking for a way to monitor queries to my databases. I'd like to optimize my code to minimize the load to my databases, but I currently have no way of telling when I'm making improvements. I'm on a managed server, so don't have root access to error logs etc.

Is there a simple way to do this? I considered making a function called query_count() for example, which would replace all of my mysql_query calls and track each query, but I hate to load the databases down with even more queries just to find out what the traffic is.

Any suggestions would be greatly appreciated.

Thanks!

#2 printf

printf
  • Staff Alumni
  • Advanced Member
  • 889 posts

Posted 01 October 2006 - 02:11 PM

I would tell you to implement something in scripting, so you can time each query in a normal operating environment and store those results. Timing always gives you a good idea of how your database is doing in regards to a query. After you have these results you can use the query data to run these queries using the databases onboard diagnostic tools. Most all databases have query diagnostic tools which can explain a query. But remember this, no diagnostic tool can help you fix problems like using the wrong column types, over or under indexing, use of to many table relations and usages of a wrong function type! These are logic errors, if you have a lot of those and don't really understand how to fix them, then you should hire a consultant because any service running a database designed system needs to have that system structured properly!


me!

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 01 October 2006 - 02:36 PM

If you really want to profile your code, you need to insert some timing breakpoints and see what's eating up most of the time.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 lark

lark
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 01 October 2006 - 03:53 PM

I think for now I just want to monitor gross traffic in and out of the databases -- so purely just the number of queries... then focus on how efficient and time-consuming the queries are later.

Fenway, what's the best way to insert "timing breakpoints"

Thanks for your help!

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 01 October 2006 - 04:12 PM

Well, ideally, if you write a wrapper layer, you can add whatever extra code you want (log files, timings, messages, etc.) only while profiling.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users