How to improve MySQL Server Performance..?

Question

As a MySQL DBA most of the times we are supposed to optimize a poorly performing MySQL Servers.

Now my question is where to start from like we will need to find out many things as

1.Find the duplicate indexes.
2.Find unused indexes on the basis of selectivity.
3.Monitor the Server Parameters(What should be important parameters).
4.Execute MySQL Server performance tuning script.
5.Slow logs

So what should be order of examining the server and what should be exact things that should be monitored/analysed for improving the Performance.

Answer 1

Finding Duplicate Indexes

Back in January 2012, @gbn answer a question about duplicate indexes where he presented 2 views that came from Ronald Bradford's blog. I combined the two views into a single query to present duplicate indexes as follows:

SELECT
    ndx1.TABLE_SCHEMA,ndx1.TABLE_NAME,
    CASE 
        WHEN ndx1.COLUMNS = ndx2.COLUMNS
        AND (ndx1.IS_UNIQUE = ndx2.IS_UNIQUE) 
        THEN GREATEST(ndx1.INDEX_NAME, ndx2.INDEX_NAME) 
        ELSE ndx1.INDEX_NAME 
    END REDUNDANT_INDEX_NAME,
    GROUP_CONCAT(DISTINCT 
        CASE 
            WHEN ndx1.COLUMNS = ndx2.COLUMNS
            AND (ndx1.IS_UNIQUE = ndx2.IS_UNIQUE) 
            THEN LEAST(ndx1.INDEX_NAME, ndx2.INDEX_NAME) 
            ELSE ndx2.INDEX_NAME 
        END
    ) INDEX_NAME 
FROM
(
    SELECT
        TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,INDEX_TYPE,
        IF(NON_UNIQUE, 'NO', 'YES') IS_UNIQUE,
        GROUP_CONCAT(CONCAT('`',COLUMN_NAME,'`')
        ORDER BY IF(INDEX_TYPE='BTREE',SEQ_IN_INDEX,0), COLUMN_NAME
        ) COLUMNS 
    FROM
        information_schema.STATISTICS 
    GROUP BY
        TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,INDEX_TYPE,NON_UNIQUE 
) ndx1 INNER JOIN 
(
    SELECT
        TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,INDEX_TYPE,
        IF(NON_UNIQUE, 'NO', 'YES') IS_UNIQUE,
        GROUP_CONCAT( 
        CONCAT('`',COLUMN_NAME,'`') 
        ORDER BY IF( INDEX_TYPE = 'BTREE'
        , SEQ_IN_INDEX
        , 0) 
        , COLUMN_NAME
        ) COLUMNS 
    FROM
        information_schema.STATISTICS 
    GROUP BY
        TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,INDEX_TYPE,NON_UNIQUE 
) ndx2
ON ndx1.TABLE_SCHEMA = ndx2.TABLE_SCHEMA
AND ndx1.TABLE_NAME = ndx2.TABLE_NAME 
AND ndx1.INDEX_NAME != ndx2.INDEX_NAME
AND ndx1.INDEX_TYPE = ndx2.INDEX_TYPE 
AND CASE 
WHEN ndx1.COLUMNS = ndx2.COLUMNS
AND (ndx1.IS_UNIQUE = 'NO'
OR ndx1.IS_UNIQUE = ndx2.IS_UNIQUE)
THEN TRUE 
WHEN ndx1.INDEX_TYPE = 'BTREE' -- when BTREE 
AND INSTR(ndx2.COLUMNS, ndx1.COLUMNS) = 1
AND ndx1.IS_UNIQUE = 'NO'
THEN TRUE 
ELSE FALSE 
END 
GROUP BY ndx1.TABLE_SCHEMA,ndx1.TABLE_NAME,REDUNDANT_INDEX_NAME
;

Obviously, the indexes with the least column per grouping need to be eliminated

Find unused indexes on the basis of selectivity.

I have not done much with unused indexes in my developer days. I try to make only necesssary indexes that match the following clauses:

• WHERE
• GROUP BY
• ORDER BY

In the event you have to cleanup a database by hunting down unused indexes, please read these:

• Use Percona-Server rather than MySQL because Percona-Server has addition information_schema tables that record index usage since mysql startup.
  • http://www.mysqlperformanceblog.com/2009/06/26/check-unused-keys-a-tool-to-interact-with-index_statistics/
  • https://stackoverflow.com/a/3243517/491757
• A very ancient tool called mysqlidxchk

Monitor the Server Parameters (What should be important parameters)

• Innodb_buffer_pool_pages_dirty*100.0/Innodb_buffer_pool_pages_total : Pct of InnoDB Buffer Pool That Needs to Be Flushed (I would keep my eye on server load if this exceeds 5%)
• (100.0 * (Delta(Innodb_buffer_pool_read_requests) - Delta(Innodb_buffer_pool_reads))) / Delta(Innodb_buffer_pool_read_requests : Read Efficiency Of InnoDB
• 100.0 * (1.0 - (Delta([Key_reads]) / Delta([Key_read_requests])) : Read Efficiency Of MyISAM (should be above 90%, anything less look into caching data with memcached or switching to InnoDB)

This is just a sample of the kind of global status values to monitor. Please read MySQL Documentation on the Server Status Variables.

Execute MySQL Server performance tuning script

Most straightforward script is mysqltuner.pl Just get it and run it

# wget mysqtuner.pl
# perl mysqltuner.pl

Slow logs

Slow logs can be quite helpful in a low-traffic environment. Unfortunately, I have seen too many occurrences of the following

• High number of DB Connections
• All Connections Running the Same Type of Query
• Query in one connections blocking dozens of others needing the same table or row (even with InnoDB due to deadlocking issues associated with the Clustered Index when doing UPDATEs)

Given this scenario, I have queries that work standalone with blazing speed grind to a halt when an inundiation of queries needing common tables.

IMHO the slow query log actually does you no good becase it records completed queries that are regarded as slow. What you really want to do is catch long-running queries in the act of being long-running. Therefore, I would recommend using pt-query-digest to pool the processlist (or tmpdump) for queries running amok. I wrote a post back in December 2011 on how to script a crontab job that polls the processlist every 20 minutes using mk-query-digest (pt-query-digest can be inserted in it place).