MySQL noob question: how do you measure mysql performance?

As variant, write 'top' in console and look at 'load average', 'cpu' and '%wa'. You can write here these counters and somebody (smarter than me :)), maybe, will comment them.

Also, you can try to use 'mytop' tool:

apt-get install mytop
mytop -u username -p password -d mysql

Yeah, QPS is more a factor of how many queries your app is making.

Your app can probably tell you how long queries are taking to execute. Alternatively, you could enable slow query logging and set the slow query time to 0, which would log every query. I wouldn't leave that enabled forever, but it could give you some useful data.

I've been using mytop a little.

My load averages are very low. (At the moment, sitting at 0.0, but it's Sat. night and the site is very quiet)

But neither of these really tell me if my performance tuning is having a positive result. There might be some numbers in mytop that would do that, but I don't know which ones.

I have the slow queries log enabled, but the queries it catches are going to be bad queries… again, not really related to how well my mysql is tuned.

What would be of some help though is seeing how long particular queries take to execute then running those again after tweaking and compare.

The ideal might be some kind of benchmarking set of queries.

But I could probably come up with something eventually and paste it into to phpmyadmin. PMA always gives a time elapsed at the end of a manual query.

enable slow query log, any queries > 1 second need attention.

aharown07, don't use PhpMyAdmin on 'production' server, it's big security hole.

And it's better to play with settings on dev-server (in virtualbox, for example), because settings will be changed only after mysql restart.

Some of settings can be tested only on load.

If your 'LA', '%wa', CPU usage are low and pages generates quickly - nothing to worry about :)

My advice is: set keybuffer to 20-25% of RAM size, and set maxconnections to 30-50 instead of default 100 (if your linode isn't 2048).

Thanks

About phpmyadmin… are there any tricks to make it more secure? I really don't want to live without it (not to say "can't live").

For example, is it possible to disable it and then re-enable when needed?

I don' t need it often, but when I need it, I need it fairly quickly.

If you are using apache, you can create .htaccess with

Order deny,allow
Deny from all
Allow from your.ip.here

But it's like store dynamite in closed garage - lock of garage can be hacked sometime :)

Add basic auth: http://httpd.apache.org/docs/2.0/howto/auth.html

PHPMyAdmin is securable. There are a few ways of doing it too. Here's another:

1. In PHPMyAdmin's virtual host settings, restrict access to localhost (127.0.0.1)

2. Then, when you want to access PHPMyAdmin, SSH tunnel into your Linode

ssh -L 8080:localhost:80 yourusername@yourdomain.com

3. And type http://localhost:8080/phpmyadmin in your web browser

This approach requires an SSH connection to your Linode in order to access PHPMyAdmin. If you have SSH keys (let's hope you do), it's far less likely someone could hack into PHPMyAdmin then.

If you have multiple services you may want to try openvpn instead of ssh it takes a little more setup but tends to be easier to use afterwards.

@jzimmerlin:

PHPMyAdmin is securable. There are a few ways of doing it too. Here's another:

1. In PHPMyAdmin's virtual host settings, restrict access to localhost (127.0.0.1)

2. Then, when you want to access PHPMyAdmin, SSH tunnel into your Linode

ssh -L 8080:localhost:80 yourusername@yourdomain.com

3. And type http://localhost:8080/phpmyadmin in your web browser

This approach requires an SSH connection to your Linode in order to access PHPMyAdmin. If you have SSH keys (let's hope you do), it's far less likely someone could hack into PHPMyAdmin then.

That helps, thanks.

Thanks to the other posts also.

I'm actually using nginx and I've got my phpmyadmin set up as a virtual server with its own subdomain. But it's still accessible via web if you know the domain… and then all I have is the fairly weak (from what I've read) login barrier to keep ppl out.

I'm not entirely clear on how to set up the ssh in nginx but can play with it till something works.

I wonder what happens if I just "break" the virtual server by putting a string of nonsense in the config file or something. And just remove that when I want to use phpmyadmin.

(Probably fill up an error log somewhere? … or break my nginx. I think I'll not try that.)

Uses the same htpasswd as Apache:

http://wiki.nginx.org/HttpAuthBasicModule