MySQL performance

[Sketchy1]

Hi there,

I have a web app that uses MySQL pretty heavily. I'm wondering if there is a good, easy-to-use profiler/analyzer/problem detector tool I can use to zero in on the bottlenecks where MySQL queries are concerned.

I tried to install mytop, but it gave an error during install. Something about insufficient permissions.

I also looked at the "Runtime information" page of phpMyAdmin, but that shows stats for the whole server, and not just my app. (I'm on SDX semi-dedicated).

Is there anything else?

Thanks!

[Vin DSL]

I also looked at the "Runtime information" page of phpMyAdmin, but that shows stats for the whole server, and not just my app...

Is there anything else?

Heh!

You just answered your own question...

[Ron]

Just some off-the-top thoughts;

You can beg Masood to run something for you maybe that will show slow queries, perhaps.
(In theory they should be interested in keeping the load down on your machine. On the other hand, they might want you to upgrade to a dedicated box )

You could instrument your own software to find out where any bottleneck may be occurring.

Use the EXPLAIN statement on your queries if you have a hunch where the resource hog is... ensure your queries are well written and are using indexes, and if not create appropriate indexes or re-write the queries.

Make sure that you OPTIMIZE or ANALYZE your tables once a week on busy (lots of deletions) tables, monthly otherwise.

[Sketchy1]

So, I did some analyzing using what I had available. I instrumented my application to log every MySQL access, and let it run for 3+ hours. This showed around 5 or 6 queries per second average. In reality, it's only 2 or 3 per second with a large burst of 800-1000 queries every 3 minutes.

During this 3+ hours, I was also monitoring the "runtime information" page on phpMyAdmin which shows the number of queries for the whole server. This showed an average of about 12 queries per second. Tonight it has shot up to 20 queries per second, spiking to 30, and it even caused MySQL to restart at least once. To me, this looks like other customers on the same SDX machine are using MySQL way more than I am, and it crashes my app.

Are my numbers reasonable? How many queries per second should I expect MySQL to handle on a semi-dedicated machine before it crashes? Anyone want to share your phpMyAdmin stats?

[Ron]

It depends on the queries, man! A simple query takes nothing -- a complex query with tons of joins and table scans might take up tons of resources, and flush the caches every time it is run.

If you are seeing performance degredation and can't find any queries of YOURS that might be re-written for optimization, open up a ticket and complain about your performance. Either someone else will be requested to upgrade -- or you will!

[Sketchy1]

My queries have zero joins, but lots of table scans. I'm assuming table scans are like "select * from tbl where id = blah"

I've taken quite a few steps to optimize everything, but I don't know how to get rid of those.

[Ron]

Well, a table scan would be guaranteed with "select * from tbl;"
If you have an index on id example:

create table foo 
(id int not null auto_increment,
bar int not null, 
primary key foobar (id));

and you use "select * from tbl where id=###" you will most likely get a nice index lookup.

[Sketchy1]

Here's a question. I have a lot of queries that do a primary key lookup like that, but I have a couple of extra calculations in the select_expr. Do you think these extra calcs could have a big performance hit? Example:

select *, (UNIX_TIMESTAMP() - tbl.Timestamp) as 
SecondsWithoutActivity from tbl where tbl.Id=whatever

[Ron]

No, not at all. I use the SQL server as a mathematics tool frequently. I figure that even interpreting my text as an equation in compiled C is faster than php interpreting it as an equation then interpreting each step in processing it.

[Sketchy1]

FYI, it turned out that there was another customer on the same server who was spiking and crashing the MySQL service. He/she has been suspended and now everything is smooth sailing with about 8 queries per second, and no slow queries.
Thanks for the info.

[Gwaihir]

Sketchy1, are you on Hammerhead per chance?
Multiple brief outages on Hammerhead

[Sketchy1]

No, different SDX box