mysql性能监控

 

mysqlslap -hlocalhost -uroot -pmysql --socket=/usr/local/mysql/data_3306/mysql.sock --concurrency=100 --auto-generate-sql --auto-generate-sql-load-type=mixed --engine=innodb --auto-generate-sql-add-autoincrement --number-int-cols=2 --number-char-cols=10 --number-of-queries=100

mysqladmin -P3307 -uroot -pmysql -hlocalhost --socket=/usr/local/mysql/data_3307/mysql.sock -r -i 1 ext |\
awk -F"|" \
"BEGIN{ count=0; }"\
'{ if($2 ~ /Variable_name/ && ((++count)%20 == 1)){\
print "----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --";\
print "---Time---|---QPS---|select insert update delete| read inserted updated deleted| logicalphysical";\
}\
else if ($2 ~ /Queries/){queries=$3;}\
else if ($2 ~ /Com_select /){com_select=$3;}\
else if ($2 ~ /Com_insert /){com_insert=$3;}\
else if ($2 ~ /Com_update /){com_update=$3;}\
else if ($2 ~ /Com_delete /){com_delete=$3;}\
else if ($2 ~ /Innodb_rows_read/){innodb_rows_read=$3;}\
else if ($2 ~ /Innodb_rows_deleted/){innodb_rows_deleted=$3;}\
else if ($2 ~ /Innodb_rows_inserted/){innodb_rows_inserted=$3;}\
else if ($2 ~ /Innodb_rows_updated/){innodb_rows_updated=$3;}\
else if ($2 ~ /Innodb_buffer_pool_read_requests/){innodb_lor=$3;}\
else if ($2 ~ /Innodb_buffer_pool_reads/){innodb_phr=$3;}\
else if ($2 ~ /Uptime / && count >= 2){\
printf(" %s |%9d",strftime("%H:%M:%S"),queries);\
printf("|%6d %6d %6d %6d",com_select,com_insert,com_update,com_delete);\
printf("|%6d %8d %7d %7d",innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted);\
printf("|%10d %11d\n",innodb_lor,innodb_phr);\
}}'

pgsql和mysql性能比较

#!/bin/sh

cmd="psql<<EOF
select id,substring(md5(id::varchar),2,8) into test from generate_series (1,10000000) as t(id);
EOF"
echo "pgsql 生成 1000万条数据耗时"
time su - postgres -c "${cmd}"

cmd="psql<<EOF
copy test to '/tmp/test.csv' csv;
EOF"
echo "pgsql 导出 1000万条数据耗时"
time su - postgres -c "${cmd}"

cmd="psql<<EOF
copy test from '/tmp/test.csv' csv;
EOF"
echo "pgsql 导入 1000万条数据耗时"
time su - postgres -c "${cmd}"

cmd="time /usr/local/mysql/bin/mysql -uroot -pmysql<<EOF
use test;
create table test(id int,substring char(8));
load data infile '/tmp/test.csv' into table test fields terminated by ',';
EOF"
echo "mysql 导入 1000万条数据耗时"
eval "${cmd}"

cmd="time /usr/local/mysql/bin/mysql -uroot -pmysql<<EOF
use test;
select * from test into outfile '/tmp/mysql_test.csv' fields terminated by ',';
EOF"
echo "mysql 导出 1000万条数据耗时"
eval "${cmd}"