1 简介 sysbench是一款开源的多线程性能测试工具,可以执行CPU/内存/线程/IO/数据库等方面的性能测试。数据库目前支持MySQL/Oracle/PostgreSQL/Drizzle。 它主要包括以下几种方式的测试: 1.cpu性能 2.磁盘io性能 3.调度程序性能 4.内存分配及传输速度 5.POSIX线程性能 6.数据库性能(OLTP基准测试) 本节主要演示使用sysbench对postgresql数据库进行oltp测试,关于sysbench的详细讲解请参考我前面的文章,【http://blog.csdn.net/jesseyoung/article/details/38418883】,这里不再赘述。 2 测试软硬件环境 操作系统:CentOS release 6.5 (Final) linux内核:2.6.32-431.el6.x86_64 数据库版本:PostgreSQL 9.3.5 SYSBENCH版本:0.4 CPU:Intel(R) Core(TM) i3 CPU M 380 @ 2.53GHz 4核 硬盘:128GB 内存:2GB 3 oltp测试相关参数
Installation
./configure --prefix=/usr/local/sysbench/ --with-pgsql--with-pgsql-includes=/usr/local/pgsql/include/--with-pgsql-libs=/usr/local/pgsql/lib/ --without-mysql
[plain]view plaincopy
[root@localhostdata]#sysbench--test=oltphelp
sysbench0.4.12:multi-threadedsystemevaluationbenchmark
oltpoptions:
--oltp-test-mode=STRINGtesttypetouse{simple,complex,nontrx,sp}[complex]
--oltp-reconnect-mode=STRINGreconnectmode{session,transaction,query,random}[session]
--oltp-sp-name=STRINGnameofstoreproceduretocallinSPtestmode[]
--oltp-read-only=[on|off]generateonly'read'queries(donotmodifydatabase)[off]
--oltp-skip-trx=[on|off]skipBEGIN/COMMITstatements[off]
--oltp-range-size=Nrangesizeforrangequeries[100]
--oltp-point-selects=Nnumberofpointselects[10]
--oltp-simple-ranges=Nnumberofsimpleranges[1]
--oltp-sum-ranges=Nnumberofsumranges[1]
--oltp-order-ranges=Nnumberoforderedranges[1]
--oltp-distinct-ranges=Nnumberofdistinctranges[1]
--oltp-index-updates=Nnumberofindexupdate[1]
--oltp-non-index-updates=Nnumberofnon-indexupdates[1]
--oltp-nontrx-mode=STRINGmodefornon-transactionaltest{select,update_key,update_nokey,insert,delete}[select]
--oltp-auto-inc=[on|off]whetherAUTO_INCREMENT(orequivalent)shouldbeusedonidcolumn[on]
--oltp-connect-delay=Ntimeinmicrosecondstosleepafterconnectiontodatabase[10000]
--oltp-user-delay-min=Nminimumtimeinmicrosecondstosleepaftereachrequest[0]
--oltp-user-delay-max=Nmaximumtimeinmicrosecondstosleepaftereachrequest[0]
--oltp-table-name=STRINGnameoftesttable[sbtest]
--oltp-table-size=Nnumberofrecordsintesttable[10000]
--oltp-dist-type=STRINGrandomnumbersdistribution{uniform,gaussian,special}[special]
--oltp-dist-iter=Nnumberofiterationsusedfornumbersgeneration[12]
--oltp-dist-pct=Npercentageofvaluestobetreatedas'special'(forspecialdistribution)[1]
--oltp-dist-res=Npercentageof'special'valuestouse(forspecialdistribution)[75]
GeneraldatabaSEOptions:
--db-driver=STRINGspecifiesdatabasedrivertouse('help'togetlistofavailabledrivers)
--db-ps-mode=STRINGpreparedstatementsusagemode{auto,disable}[auto]
Compiled-indatabasedrivers:
mysql-MySQLdriver
pgsql-PostgreSQLdriver
mysqloptions:
--mysql-host=[LIST,...]MySQLserverhost[localhost]
--mysql-port=NMySQLserverport[3306]
--mysql-socket=STRINGMySQLsocket
--mysql-user=STRINGMySQLuser[sbtest]
--mysql-password=STRINGMySQLpassword[]
--mysql-db=STRINGMySQLdatabasename[sbtest]
--mysql-table-engine=STRINGstorageenginetouseforthetesttable{myisam,innodb,bdb,heap,ndbcluster,federated}[innodb]
--mysql-engine-trx=STRINGwhetherstorageengineusedistransactionalornot{yes,no,auto}[auto]
--mysql-ssl=[on|off]useSSLconnections,ifavailableintheclientlibrary[off]
--myisam-max-rows=Nmax-rowsparameterforMyISAMtables[1000000]
--mysql-create-options=STRINGadditionaloptionspassedtoCREATETABLE[]
pgsqloptions:
--pgsql-host=STRINGPostgreSQLserverhost[localhost]
--pgsql-port=NPostgreSQLserverport[5432]
--pgsql-user=STRINGPostgreSQLuser[sbtest]
--pgsql-password=STRINGPostgreSQLpassword[]
--pgsql-db=STRINGPostgreSQLdatabasename[sbtest]
4 性能测试
4.1 准备测试数据
[plain]copy
[root@localhostdata]#sysbench--test=oltp--oltp-table-size=100000--pgsql-host=127.0.0.1--pgsql-port=5432--pgsql-user=postgres--pgsql-password=postgres--pgsql-db=postgres--db-driver=pgsql--max-requests=1000000--oltp-test-mode=simple--oltp-reconnect-mode=session--num-threads=10prepare
4.2 开始测试
[plain]copy
[root@localhostdata]#sysbench--test=oltp--oltp-table-size=100000--pgsql-host=127.0.0.1--pgsql-port=5432--pgsql-user=postgres--pgsql-password=postgres--pgsql-db=postgres--db-driver=pgsql--max-requests=1000000--oltp-test-mode=simple--oltp-reconnect-mode=session--num-threads=10run
PostgreSQL tps : 1001978(注:因在虚拟机上操作,仅为演示,不做性能对比参考)
4.3 清理测试数据
[plain]copy
[root@localhostdata]#sysbench--test=oltp--oltp-table-size=100000--pgsql-host=127.0.0.1--pgsql-port=5432--pgsql-user=postgres--pgsql-password=postgres--pgsql-db=postgres--db-driver=pgsql--max-requests=1000000--oltp-test-mode=simple--oltp-reconnect-mode=session--num-threads=10cleanup
-----------------------------------------------------------------------------
1. 查看数据库大小。
select pg_size_pretty(pg_database_size('db_name'));
通过 pgAdmin 我们可以看到,在 PostgreSQL(pg_catalog) 下有如下两个视图:
pg_stat_user_tables,pg_stat_user_indexes。可以通过它们来查看表及索引的大小。
2. 查看所有索引的大小。
select indexrelname,
pg_size_pretty(pg_relation_size(indexrelname))
from pg_stat_user_indexes
where schemaname = 'public'
order by pg_relation_size(indexrelname) desc;
3. 查所有表的大小。
select relname,
pg_size_pretty(pg_relation_size(relname))
from pg_stat_user_tables
where schemaname = 'public'
order by pg_relation_size(relname) desc;
select relname,pg_size_pretty(pg_relation_size(relid))
from pg_stat_user_tables
where schemaname = 'public'
order by pg_relation_size(relid) desc;
4. 查看单个表的大小。
select pg_size_pretty(pg_relation_size('table_name'));
5.查看Postgresql连接数
SELECT count(*) FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
6. 查看postgreSQL进程
SELECT pg_stat_get_backend_pid(s.backendid) AS procpid,pg_stat_get_backend_activity(s.backendid) AS current_query FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
杀掉某个进程为: select pg_cancel_backend('2056')
7.postgresql随机查询获取数据
比如是表test,则随机查询语句为: select * from test order by random() limit 1;
如果是随机获取5条记录,则 select * from test order by random() limit 5;
8.优化可见性判断
如果是刚插完数据(sysbench prepare),马上就做读的测试,通常会看到有比较大的写,原因是PostgreSQL为了在读数据时优化可见性判断,你可以对表做一次全表扫描 explain analyze select * from sbtest1;执行checkpoint;命令,然后再测试,写应该就能消失了。可见性判断可以见我的blog: http://osdbablog.sinaapp.com/94.html (编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|