WebFaction
Community site: login faq

Hi,

I'm trying to profile the efficacy of my caching setup, so I'd like to see how much my site is hitting mySQL. I'm still using the Webfaction shared mySQL instance, so things like "SHOW STATUS" shows the number queries for the entire server by all users. How can I get just the queries for my database?

Thanks!

asked 31 Oct '12, 11:40

sb56637
13813
accept rate: 0%


You could log into your database using mysql from an ssh console session and use the SHOW PROCESSLIST or SHOW STATUS commands. That should give you what you are looking for.

permanent link

answered 31 Oct '12, 12:29

bmeyer71 ♦♦
1.5k3613
accept rate: 33%

Thanks for the reply. I don't think this works though. SHOW STATUS seems to show the queries for the entire database with all its shared users.

(31 Oct '12, 12:45) sb56637

How are you connecting to your database? If you are using mysql --user=<username> --password=<password> you will be connected to your database with the same name as the username specified. Then SHOW STATUS should only show you stats on that database as far as I am aware.

(31 Oct '12, 12:50) bmeyer71 ♦♦

I was using phpymyadmin. But when I connect to my DB from the command line and run SHOW STATUS, it simply returns nothing.

(31 Oct '12, 13:03) sb56637

Make sure you append a ; to the end of your command. eg: SHOW STATUS;

(31 Oct '12, 13:08) bmeyer71 ♦♦

Ooops. :/

OK, it still seems to be reporting queries for the entire server. First of all I put my site in offline mode, so it shouldn't be accepting DB connections. Then I connected with

mysql --user=me_dbmydb --password=123 me_dbmydb

and ran SHOW STATUS, and it reported 148324521 queries. I ran the same command about 30 seconds later, and it reported 148372870 queries. I doubt my site made 48,000 queries in 30 seconds in offline mode.

(31 Oct '12, 13:17) sb56637

hmmmm... seems the information I found was incorrect then. I checked around with my co-workers, and it seems there may not be a way to do it for an individual database.

(31 Oct '12, 13:30) bmeyer71 ♦♦

Ok, thanks for confirming this. If possible, it would be very useful to enable this feature somehow.

(31 Oct '12, 13:43) sb56637

I think that would have to be something the mysql developers would have to implement. The only way I can think of to do this would be to have a mysql instance for each database, which really isn't desirable.

(31 Oct '12, 13:50) bmeyer71 ♦♦
showing 5 of 8 show 3 more comments
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×168
×19
×9
×1

question asked: 31 Oct '12, 11:40

question was seen: 6,177 times

last updated: 31 Oct '12, 13:50

                              
WEBFACTION
REACH US
SUPPORT
LEGAL
© COPYRIGHT 2003-2021 SWARMA LIMITED - WEBFACTION IS A SERVICE OF SWARMA LIMITED
REGISTERED IN ENGLAND AND WALES 5729350 - VAT REGISTRATION NUMBER 877397162
5TH FLOOR, THE OLD VINYL FACTORY, HAYES, UB3 1HA, UNITED KINGDOM