利用MySQL系统数据库做性能负载诊断

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

SELECT 
     database_name,
     SUM(compressed_size)/1024/1024  AS allocated_memory,
     SUM(data_size)/1024/1024  AS data_memory,
     SUM(is_hashed)*16/1024 AS is_hashed_memory,
     SUM(is_old)*16/1024 AS is_old_memory
FROM 
(SELECT 
        case when INSTR(TABLE_NAME,'.')>0 then replace(SUBSTRING(TABLE_NAME,1,INSTR(TABLE_NAME,'.')-1),'`','')
        else     'system_database' end as database_name,
        case when INSTR(TABLE_NAME,'.')>0 then replace(SUBSTRING(TABLE_NAME,INSTR(TABLE_NAME,'.')+1),'`','')
        ELSE 'system_obj' END AS table_name,
        if(compressed_size = 0, 16384, compressed_size) AS compressed_size,
        data_size,
        if(is_hashed = 'YES',1,0) is_hashed,
        if(is_old = 'YES',1,0)  is_old
    FROM information_schema.innodb_buffer_page
    WHERE TABLE_NAME IS NOT NULL
) t
GROUP BY database_name
ORDER BY allocated_memory DESC
LIMIT 10;

SELECT 
    SUM(modified_database_pages) AS total_modified_database_pages,
    SUM(pages_made_young) AS total_pages_made_young,
    SUM(pages_not_made_young) AS total_pages_not_made_young,
    SUM(hit_rate)/COUNT(hit_rate)*1000 AS hit_rate
FROM

(SELECT 
        pool_id,
        pool_size,
        database_pages,
        old_database_pages,
        modified_database_pages,
        pages_made_young,
        pages_not_made_young,
        hit_rate
    FROM information_schema.innodb_buffer_pool_stats
)t；

./mysqlslap -uroot -proot -h127.0.0.1 -P8000 --concurrency=100 --iterations=10000 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=innodb --number-of-queries=10000

import pymysql
import logging
import time
import decimal


def execute_query(conn_dict,sql):
    conn = pymysql.connect(host=conn_dict['host'],
                           port=conn_dict['port'],
                           user=conn_dict['user'],
                           passwd=conn_dict['password'],
                           db=conn_dict['db'])
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    cursor.execute(sql)
    list = cursor.fetchall()
    cursor.close()
    conn.close()
    return list

def check_innodb_buffer_pool_stats(flag,conn_dict):
    result = execute_query(conn_dict, '''SELECT 
                                            modified_database_pages,
                                            pages_made_young,
                                            pages_not_made_young,
                                            hit_rate
                                        FROM information_schema.innodb_buffer_pool_stats;''')
    if result:
        column = result[0].keys()
        current_row = ''
        if(flag<=0):
            for key in column:
                current_row += str(key) + "    "
            print(current_row)

        for row in result:
            current_row = ''
            for key in row.values():
                current_row += str(key) + "    "
            print(current_row)

if __name__ == '__main__':
    conn  = {'host': '127.0.0.1', 'port': my_port, 'user': 'root', 'password': '***', 'db': 'mysql', 'charset': 'utf8mb4'}
    flag = 0
    while 1>0:
        check_innodb_buffer_pool_stats(flag,conn)
        time.sleep(3)
        flag = 1

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

SELECT 
    schema_name,
    digest_text,
    count_star,
    avg_timer_wait/1000000000000 AS avg_timer_wait,
    max_timer_wait/1000000000000 AS max_timer_wait,
    sum_lock_time/count_star/1000000000000 AS avg_lock_time ,
    sum_rows_affected/count_star AS avg_rows_affected,
    sum_rows_sent/count_star AS avg_rows_sent ,
    sum_rows_examined/count_star AS avg_rows_examined,
    sum_created_tmp_disk_tables/count_star AS avg_create_tmp_disk_tables,
    sum_created_tmp_tables/count_star AS avg_create_tmp_tables,
    sum_select_full_join/count_star AS avg_select_full_join,
    sum_select_full_range_join/count_star AS avg_select_full_range_join,
    sum_select_range/count_star AS avg_select_range,
    sum_select_range_check/count_star AS avg_select_range,
    first_seen,
    last_seen
FROM performance_schema.events_statements_summary_by_digest
WHERE last_seen>date_add(NOW(), interval -1 HOUR)
ORDER BY 
max_timer_wait
-- avg_timer_wait
-- sum_rows_affected/count_star 
-- sum_lock_time/count_star
-- avg_lock_time
-- avg_rows_sent
DESC
limit 10;

CREATE DEFINER=`root`@`%` PROCEDURE `create_test_data`(IN `loopcnt` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    -- START TRANSACTION; 
        while loopcnt>0 do
            insert into test_mrr(rand_id,create_date) values (RAND()*100000000,now(6));
            set loopcnt=loopcnt-1;
        end while;
    -- commit;
END

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

select 
    schema_name,
    digest_text,
    count_star,
    first_seen,
    last_seen
from performance_schema.events_statements_summary_by_digest
where sum_errors>0 or sum_warnings>0 
order by last_seen desc;

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

SELECT SUBSTRING_INDEX(NAME, '/', 1) as wait_type,COUNT(1)  
FROM performance_schema.setup_instruments
GROUP BY 1  
ORDER BY 2 DESC;


SELECT
event_name,
count_star,
sum_timer_wait
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE event_name != 'idle'
order by sum_timer_wait desc
limit 100;