How Tuning Mysql Innodb

[andy susanto]

hai,

Today i download Monyong, but when i install it this not give me a clue how to tuning mysql database

what i know is red,yellow dot, i still confuse to use this tool.

is there any auto performance tuning ?

i have a query that takes more or less one minute

Regards,

Andy Susanto

[peterlaursen]

1) For those counters that display some (yellow or red) alert click on the counter name (it is a link) read the advice and try to change the server configuration accordingly (in the [mysqld] section of the server configuration file). This will have to be an iterative process.

2) Use the Query Analyzer to identify slow queries. EXPLAIN those with some interactive client like SQLyog. From the output of EXPLAIN decide indexes to be added or rewrite queries to use existing indexes.

There is no auto performance tuning. If it was so simple the server would probably do it all automatically! Tuning requires configuration changes and/or schema or query changes – and some understanding of how the server works will be required to do this. MONyog will help you to identify problematic configuration parameters and problematic queries. But it takes some database skills to resolve things.

[andy susanto]

hai peterlaursen,

in the Monyong advise it not give how much value that i should store to config, only what i get is 70-80% from actual RAM.

i know explain syntax and but i do not know how to read the result.

can you tell me ?

i have only one box. in that box installed mysql and tomcat using 256M of RAM

Regards,

Andy Susanto

[peterlaursen]

Well .. if you installed MONyog for the first time only a few days ago and if you never worked with prformance tuning before you will have to accept that there are things to learn.

1)

I maybe do not understand this “in the Monyong advise it not give how much value that i should store to config, only what i get is 70-80% from actual RAM”.

But it could be this:

Quote:

Description

InnoDB buffer pool caches both data and index pages. You can set this value to 70-80% of available memory for Innodb-only installations

Formula

innodb_buffer_pool_size

If MONyog advices you to use 70% of avalable RAM for some parameter then you will have to decide fist how much RAM MySQL should be allowed to use (and what should be reserved for Tomcat and other applications) and take 70% of the rest.

Example: You have 3G RAM and decide to let MySQL use around 1G (Also Tomcat is RAM-intensive). If you only or almost only use InnoDB tables the advice applies. Then in configuration ([mysqld] section) you can try to insert

Code:

innodb_buffer_pool_size=712M

.. for instance and restart the server. Compare peformance before and after. Try to put heavy load on the complete system. If it looks like the machine still has free memory you may try with settings that allocate 1.5 or 2G RAM to MySQL.

Actually the 'innodb_buffer_pool_size' is probably the most important thing to configure with production systems using InnoDB so that mySQL and InnoDB will use the resources on the system (but official InnoDB guidelines are that it should not exceed 1G as this will prolong the time for crash recovery. However I have seen tuning experts set it as high as 4G-6G in special situations. But a crash recovery could then easily take the whole night!)

2)

The two most important columns in EXPLAIN output are: *possible indexes* and *index used*.

If there are no *possible indexes* then try to add one and see if it will use it. If *index used* is NULL and *possible indexes* in not empty you can try add FORCE INDEX to the statement and benchmark performance before and after. this is described here: http://dev.mysql.com/doc/refman/5.1/en/index-hints.html. (and do not take it for granted that using an index is always faster – with small tables if is not always and also non-unique indexes often make no difference (and it may then sometimes be slower to use the index))

Also read: http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html

(and search google with 'use mysql indexes' or 'how mysql indexes' etc and you will find lots of stuff.

[Author]

Posts