mysql查询qps

一、mysql查询qps脚本

调用：./mysql_qps.sh 127.0.0.1 3306 root password

下载：mysql_qps.zip

#!/bin/bash

[ $# -lt 3 ] && {
    echo "usage: command host port user [password]"
    exit
}

if [ $# -eq 3 ];then
    connection="mysql -h$1 -P $2 -u$3"
else
    connection="mysql -h$1 -P $2 -u$3 -p$4"
fi

query() {
$connection <<EOF
    show global status where variable_name='Com_insert';
    show global status where variable_name='Com_select';
    show global status where variable_name='Com_update';
    show global status where variable_name='Com_replace';
    show global status where variable_name='Com_delete';
    #show global status where variable_name='Com_drop_db';
    #show global status where variable_name='Com_create_table';
EOF
}

querywrap() {
    echo -e "Variable_name\tValue"
    query | sed -n '/Variable_name/!p'
}

create() {
$connection <<EOF
    drop database if exists feidb;
    create database feidb default charset=utf8 collate=utf8_bin;
    use feidb; #1 select
    create table feitable(name varchar(32), age int);
EOF
}

insert() {
$connection <insert
}

select_() {
$connection <select_
}

update() {
$connection <update 
}

replace() {
$connection <replace 
}

delete() {
$connection <delete
}

getcount() {
    echo -e "$1" | awk "\$0~/$2/{printf(\$2)}"
}

query_begin() {
    result=`querywrap`
    insert_count=`getcount "$result" "Com_insert"`
    select_count=`getcount "$result" "Com_select"`
    update_count=`getcount "$result" "Com_update"`
    replace_count=`getcount "$result" "Com_replace"`
    delete_count=`getcount "$result" "Com_delete"`
}
query_begin

dosql() {
    create
    insert
    select_ >/dev/null
    update
    replace
    delete
}
dosql

query_end() {
    result=`querywrap`
    insert_count_new=`getcount "$result" "Com_insert"`
    select_count_new=`getcount "$result" "Com_select"`
    update_count_new=`getcount "$result" "Com_update"`
    replace_count_new=`getcount "$result" "Com_replace"`
    delete_count_new=`getcount "$result" "Com_delete"`
}
query_end

show_qps() {
    insert_diff=$((insert_count_new - insert_count))
    select_diff=$((select_count_new - select_count + 1))
    update_diff=$((update_count_new - update_count))
    replace_diff=$((replace_count_new - replace_count))
    delete_diff=$((delete_count_new - delete_count))
    echo -e "insert\t$insert_diff"
    echo -e "select\t$select_diff"
    echo -e "update\t$update_diff"
    echo -e "replace\t$replace_diff"
    echo -e "delete\t$delete_diff"
}
show_qps