MySQL performance tuning snippet

By Amazon on 25 Feb 2006 at 03:59 UTC

Hi, we are looking for some people to test out this snippet of code and see if it helps you to tune your MySQL database. Please provide feedback in the comments.

Cheers,
Kieran

Comments

This is a great piece of code

bonobo commented 25 February 2006 at 09:18

My only suggestion has to do with the text in the last section (the "Select Monitor" section).

In the rows for select_full_join and select_scan, the "Warnings" state that "A high value here means that..." -- some indication of what constitutes a high value would be helpful to put these numbers into context.

Thanks for this snippet -- it's pretty sweet.

Cheers,

Bill

-------
http://www.funnymonkey.com
Tools for Teachers

-------
http://www.funnymonkey.com

Improvements in results?

Amazon commented 25 February 2006 at 19:58

Hi, I would be interested in hearing if your site actually ran faster?

Thanks,
Kieran

Kieran Lal

I included the snippet on a

bonobo commented 26 February 2006 at 01:14

fairly low traffic site with a server that's already pretty well configured --

I was more curious about the range of feedback it would give about site performance, because I'm looking at tools that will help with tweaking a few projects we have in the works.

So, I'm not the best person to give you feedback on this, yet. I was looking at the tools provided, as opposed to actually needing to use them now.

Sorry --

Bill

-------
http://www.funnymonkey.com
Tools for Teachers

-------
http://www.funnymonkey.com

Dumb question

agentrickard

he/him

English

Georgia (US)

commented 27 February 2006 at 02:15

I realize... but... where do we put this snippet?

I added it to the top of page.tpl.php, but the book page has no advice in this regard.

--
http://ken.blufftontoday.com/
Search first, ask good questions later

Make it the content of a new

handelaar commented 27 February 2006 at 13:46

Make it the content of a new PHP page.

More dumbness

agentrickard

he/him

English

Georgia (US)

commented 27 February 2006 at 14:15

When I make it stand-alone at http://example.com/mysql_test.php, I have to add these lines to return data:

include_once('includes/bootstrap.inc');
include_once('includes/common.inc');

--
http://ken.blufftontoday.com/
Search first, ask good questions later.

Should have written a README

PunkCut25 commented 27 February 2006 at 20:58

Sorry this got confusing.

So what you need to do to put this up is goto

create content -> page

then select PHP as your input filter.

Then copy and paste the code on this page in there and submit it as a page.

my advice is to create a handy dandy path alias to it - like mysql_test or something so that you can always reach it with
www.my.domain/mysql_test

this is ok, But I think its

insomoz commented 28 February 2006 at 00:55

this is ok, But I think its inferior to this program

http://www.trafficg.com/mysql/

performance gains?

shane commented 28 February 2006 at 01:08

Does anyone have any anecdotal or hard evidence regarding the improvements of either of these methods? I have two "medium" volume sites, and I'm interested in trying them both out.

Any chances these will cause any problems? TIA.

Documentation++

PunkCut25 commented 28 February 2006 at 21:02

Ok, So this script just got a massive update. I'm starting to think I'm going to need to find a module to include it in or make my own or something because its becoming quite large for just a script.

Anyway,

For performance gains you should check out the mysql benchmarking suite.

(4,4.1)
http://dev.mysql.com/doc/refman/5.0/en/mysql-benchmarks.html
(5,5.1)
http://dev.mysql.com/doc/refman/4.1/en/mysql-benchmarks.html

As far as this stuff causing any problems.

When you make the changes by doing

SET GLOBAL/SESSION VARIABLE_NAME=VALUE

This is _NOT_ permanent. These values will be reset after the server is restarted.

To make the changes permanent youll need to add them to your mysql configuration file. my.cnf.

Theres documentation linked to within this script that will show you how to do that.

Ive got a whole boatload of tests I still need to put into this script

Some other stuff Im planning on doing.

Forms and buttons to allow you to do things like change the values of certain options or to flush the cache.

Im working on making all the links dynamic in the script. Right now all the scripts included are for 5.x series of mysql. You can pull the version straight out of your mysql variable tables. So it shouldnt be too difficult to make it so that the links point to either the 5.0 or 4.1 series documentation.

I got a lot of other stuff planned for it and I'm looking for more suggestions.

how about dbtuning.module

shane commented 28 February 2006 at 23:18

This might (or might not) go beyond the scope of your plans. But it'd be nice if this could be turned into a "DB Tuning Module", which would run occasionally, and based on the advice, info - give you the knobs and buttons to tune the DB performance on the back end. It would be an awfully nice module to have. Not entirely certain it's achievable, but would be a great administrators module to have.

Also - the DB tuning code DOES NOT work with Drupal 4.2.0 version. Same exact code, using Drupal 4.6.5 (same backend databse, different table) works just fine.

v/r
Shane