0 5 9 2 9 5 . c o m/女生怎么看懂足球比分188 比分？
点击联系发帖人
时间：2017-05-02 13:22
温馨提示！由于新浪微博认证机制调整，您的新浪微博帐号绑定已过期，请重新绑定！&&|&&
PostgreSQL 高校实验室,企业,社区大联盟才是未来的方向.
LOFTER精选
网易考拉推荐
用微信&&“扫一扫”
将文章分享到朋友圈。
用易信&&“扫一扫”
将文章分享到朋友圈。
文件系统为XFS，优化手段如下：本文在同样的硬件测试环境下对比PostgreSQL 9.5.0。我们看看1月7号发布的PostgreSQL 新版本性能咋样？benchmarkSQL 配置请参考：内核配置/etc/grub.confnuma=offelevator=deadline编译器版本gcc version 4.4.6
(Red Hat 4.4.6-3) (GCC)&PostgreSQL编译项./configure --prefix=/u02/digoal/soft_bak/pgsql9.5 --with-blocksize=8 --with-pgport=1921 --with-perl --with-python --with-tcl --with-openssl --with-pam --with-ldap --with-libxml --with-libxslt --enable-thread-safetygmake world -j32gmake install-world -j32配置postgres环境变量$ vi env_pg.sh&export PS1="$USER@`/bin/hostname -s`-& "export PGPORT=1921export PGDATA=/data01/pgdata/pg_rootexport LANG=en_US.utf8export PGHOME=/u02/digoal/soft_bak/pgsql9.5export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATHexport DATE=`date +"%Y%m%d%H%M"`export PATH=$PGHOME/bin:$PATH:.export MANPATH=$PGHOME/share/man:$MANPATHexport PGHOST=$PGDATAexport PGDATABASE=postgresexport PGUSER=postgresalias rm='rm -i'alias ll='ls -lh'unalias vi配置postgresql.conf$ vi $PGDATA/postgresql.confport = 1921 & & & & & & & & & & & & & & # (change requires restart)max_connections = 300 & & & & & & & & & # (change requires restart)unix_socket_directories = '.' & # comma-separated list of directoriesshared_buffers = 32GB & & & & & & & & & # min 128kBhuge_pages = try & & & & & & & & & & & # on, off, or trymaintenance_work_mem = 2GB & & & & & & &# min 1MBdynamic_shared_memory_type = posix & & &# the default is the first optionbgwriter_delay = 10ms & & & & & & & & & # 10-10000ms between roundswal_level = minimal &# minimal, archive, hot_standby, or logicalsynchronous_commit = off & & & & & & & &# sfull_page_writes = off & & & & & & & & &# recover from partial page writeswal_buffers = 16MB & & & & & & & & & & & # min 32kB, -1 sets based on shared_bufferswal_writer_delay = 10ms & & & & # 1-10000 millisecondsmax_wal_size = 32GBeffective_cache_size = 240GBlog_destination = 'csvlog' & & & & & & &# Valid values are combinations oflogging_collector = on & & & & &# Enable capturing of stderr and csvloglog_truncate_on_rotation = on & & & & & # If on, an existing log file with the下载最新java版本对应的postgresql jdbc jarwget https://jdbc.postgresql.org/download/postgresql-9.4.1207.jre7.jarmv postgresql-9.4.1207.jre7.jar benchmarksql-4.1.0/lib/配置benchmarksql，使用新的postgresql java驱动$ vi runBenchmark.sh&java -cp .:../lib/postgresql-9.4.1207.jre7.jar:../lib/log4j-1.2.17.jar:../lib/apache-log4j-extras-1.1.jar:../dist/BenchmarkSQL-4.1.jar -Dprop=$1 jTPCC$ vi runLoader.shjava -cp .:../lib/postgresql-9.4.1207.jre7.jar:../dist/BenchmarkSQL-4.1.jar -Dprop=$1 LoadData $2 $3 $4 $5$ vi runSQL.sh&myCP="../lib/postgresql-9.4.1207.jre7.jar"myCP="$myCP:../dist/BenchmarkSQL-4.1.jar"myOPTS="-Dprop=$1"myOPTS="$myOPTS -DcommandFile=$2"java -cp .:$myCP $myOPTS ExecJDBC修改log4j，减少日志打印量。priority改成info，只输出最终结果，不输出产生订单的日志。$ vi&log4j.xml&?xml version="1.0" encoding="UTF-8" ?&&!DOCTYPE log4j:configuration SYSTEM "log4j.dtd"&&log4j:configuration xmlns:log4j='http://jakarta.apache.org/log4j/'&&appender name="console" class="org.apache.log4j.ConsoleAppender"&¶m name="Threshold" value="info"/&&layout class="org.apache.log4j.PatternLayout"&¶m name="ConversionPattern" value="%d %5p - %m%n"/&&/layout&&/appender&&appender name="R" class="org.apache.log4j.rolling.RollingFileAppender"&¶m name="Append" value="True" /&&rollingPolicy class="org.apache.log4j.rolling.TimeBasedRollingPolicy"&¶m name="FileNamePattern" value="log/archive/benchmarksql.%d{yyyyMMddHHmm}.log"/&¶m name="ActiveFileName" value="log/benchmarksql.log"/&&/rollingPolicy&&triggeringPolicy class="org.apache.log4j.rolling.SizeBasedTriggeringPolicy"&¶m name="MaxFileSize" value="1"/&&/triggeringPolicy&&layout class="org.apache.log4j.PatternLayout"&¶m name="ConversionPattern" value="%5p\t[%d{yyyy-MM-dd HH:mm:ss.SSS}]\t%t \t%m%n"/&&/layout&&filter class="org.apache.log4j.filter.StringMatchFilter"&¶m name="StringToMatch" value ="\n" /&¶m name="AcceptOnMatch" value="false" /&&/filter&&/appender&&appender name="E" class="org.apache.log4j.rolling.RollingFileAppender"&¶m name="Append" value="True" /&¶m name="Threshold" value="warn"/&&rollingPolicy class="org.apache.log4j.rolling.TimeBasedRollingPolicy"&¶m name="FileNamePattern" value="log/BenchmarkSQLError.%d.log"/&¶m name="ActiveFileName" value="log/BenchmarkSQLError.log"/&&/rollingPolicy&&layout class="org.apache.log4j.PatternLayout"&¶m name="ConversionPattern" value="%5p\t[%d{yyyy-MM-dd HH:mm:ss.SSS}]\t%t \t%m%n"/&&/layout&&/appender&&root&&priority value="info"/&&appender-ref ref="R"/&&appender-ref ref="E"/&&/root&&/log4j:configuration&编辑连接配置和压测配置，与测试Oracle 12c 的保持一致。1000 个仓库，约5亿数据量。$ vi props.pg&driver=org.postgresql.Driverconn=jdbc:postgresql://localhost:1921/postgresuser=postgrespassword=123warehouses=1000terminals=96//To run specified transactions per terminal- runMins must equal zerorunTxnsPerTerminal=0//To run for specified minutes- runTxnsPerTerminal must equal zerorunMins=1//Number of total transactions per minutelimitTxnsPerMin=0//The following five values must add up to 100//The default percentages of 45, 43, 4, 4 & 4 match the TPC-C specnewOrderWeight=40paymentWeight=36orderStatusWeight=8deliveryWeight=8stockLevelWeight=8配置postgres用户默认搜索路径$ psqlpsql (9.5.0)Type "help" for help.postgres=# alter role postgres set search_path='benchmarksql','public';创建用于存放生成CSV的目录$ mkdir /u02/digoal/soft_bak/benchcsv修改sqlTableCopies，指定目录$ vi sqlTableCopies© benchmarksql.warehouse& (w_id, w_ytd, w_tax, w_name, w_street_1, w_street_2, w_city, w_state, w_zip) && from '/u02/digoal/soft_bak/benchcsv/warehouse.csv' WITH CSV;copy benchmarksql.item& (i_id, i_name, i_price, i_data, i_im_id)&& from '/u02/digoal/soft_bak/benchcsv/item.csv' WITH CSV;copy benchmarksql.stock& (s_i_id, s_w_id, s_quantity, s_ytd, s_order_cnt, s_remote_cnt, s_data,& &s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05,& &s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10)& from '/u02/digoal/soft_bak/benchcsv/stock.csv' WITH CSV;copy benchmarksql.district& (d_id, d_w_id, d_ytd, d_tax, d_next_o_id, d_name, d_street_1,& &d_street_2, d_city, d_state, d_zip)&& from '/u02/digoal/soft_bak/benchcsv/district.csv' WITH CSV;copy benchmarksql.customer& (c_id, c_d_id, c_w_id, c_discount, c_credit, c_last, c_first, c_credit_lim,&& &c_balance, c_ytd_payment, c_payment_cnt, c_delivery_cnt, c_street_1,&& &c_street_2, c_city, c_state, c_zip, c_phone, c_since, c_middle, c_data)&& from '/u02/digoal/soft_bak/benchcsv/customer.csv' WITH CSV;copy benchmarksql.history& (hist_id, h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id, h_date, h_amount, h_data)&& from '/u02/digoal/soft_bak/benchcsv/cust-hist.csv' WITH CSV;copy benchmarksql.oorder& (o_id, o_w_id, o_d_id, o_c_id, o_carrier_id, o_ol_cnt, o_all_local, o_entry_d)&& from '/u02/digoal/soft_bak/benchcsv/order.csv' WITH CSV;copy benchmarksql.order_line& (ol_w_id, ol_d_id, ol_o_id, ol_number, ol_i_id, ol_delivery_d,&& &ol_amount, ol_supply_w_id, ol_quantity, ol_dist_info)&& from '/u02/digoal/soft_bak/benchcsv/order-line.csv' WITH CSV;copy benchmarksql.new_order& (no_w_id, no_d_id, no_o_id) && from '/u02/digoal/soft_bak/benchcsv/new-order.csv' WITH CSV;建立表结构$ cd benchmarksql-4.1.0/run$ ./runSQL.sh props.pg sqlTableCreates生成CSV$ ./runLoader.sh props.pg numWarehouses 1000 fileLocation /u02/digoal/soft_bak/benchcsv/&1000个仓库的数据量：total 69G-rw-r--r-- 1 digoal users 2.0G Jan &9 15:53 cust-hist.csv-rw-r--r-- 1 digoal users &16G Jan &9 15:53 customer.csv-rw-r--r-- 1 digoal users 898K Jan &9 15:12 district.csv-rw-r--r-- 1 digoal users 7.0M Jan &9 14:22 item.csv-rw-r--r-- 1 digoal users &95M Jan &9 16:14 new-order.csv-rw-r--r-- 1 digoal users 1.3G Jan &9 16:14 order.csv-rw-r--r-- 1 digoal users &22G Jan &9 16:14 order-line.csv-rw-r--r-- 1 digoal users &28G Jan &9 15:12 stock.csv-rw-r--r-- 1 digoal users &84K Jan &9 14:22 warehouse.csv导入数据库$ ./runSQL.sh props.pg sqlTableCopies创建约束和索引$ ./runSQL.sh props.pg sqlIndexCreates&备份$ pg_dump -f /u02/digoal/soft_bak/benchmarksql.dmp -F c -n benchmarksql postgres压测：nohup ./runBenchmark.sh props.pg &/dev/null 2&./errrun.log &测试结果：&INFO & [ 22:03:39.961] & & & Thread-7 & & & &Term-00,&&INFO & [ 22:03:39.963] & & & Thread-7 & & & &Term-00,&&INFO & [ 22:03:39.963] & & & Thread-7 & & & &Term-00, Measured tpmC (NewOrders) = &INFO & [ 22:03:39.963] & & & Thread-7 & & & &Term-00, Measured tpmTOTAL = &INFO & [ 22:03:39.964] & & & Thread-7 & & & &Term-00, Session Start & & =
21:53:39&INFO & [ 22:03:39.964] & & & Thread-7 & & & &Term-00, Session End & & & =
22:03:39&INFO & [ 22:03:39.964] & & & Thread-7 & & & &Term-00, Transaction Count = 2563088主机信息，截取压测第9分钟的数据。TOPtop - 22:02:09 up 3 days, 12:55, &3 users, &load average: 19.23, 15.97, 8.37Tasks: 619 total, &10 running, 609 sleeping, & 0 stopped, & 0 zombieCpu(s): 35.0%us, &9.4%sy, &0.0%ni, 52.6%id, &0.1%wa, &0.0%hi, &2.9%si, &0.0%stMem: &k total, k used, k free, & &36672k buffersSwap: k total, & & & &0k used, k free, k cachediostat -xavg-cpu: &%user & %nice %system %iowait &%steal & %idle& & & & & 35.07 & &0.00 & 12.30 & &0.12 & &0.00 & 52.51Device: & & & & rrqm/s & wrqm/s & & r/s & & w/s & rsec/s & wsec/s avgrq-sz avgqu-sz & await &svctm &%utildfa & & & & & & & 0.00 & & 0.00 & 57.40 &743.40 & 918.40 11849.00 & &15.94 & & 0.02 & &0.03 & 0.03 & 2.08dfb & & & & & & & 0.00 & & 0.00 & 57.20 &740.40 & 915.20 11829.00 & &15.98 & & 0.02 & &0.03 & 0.03 & 2.04dfc & & & & & & & 0.00 & & 0.00 & 58.40 &730.80 & 934.40 11675.80 & &15.98 & & 0.03 & &0.03 & 0.03 & 2.52dm-0 & & & & & & &0.00 & & 0.00 &173.00 68.00 35331.40 & &15.97 & & 0.08 & &0.03 & 0.03 & 7.02Oracle有AWR报告，PostgreSQL可以使用oprofile跟踪统计：参考找到需要优化的代码就靠它了。CPU: Intel Ivy Bridge microarchitecture, speed 2600 MHz (estimated)Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (No unit mask) count 100000vma & & &samples &% & & & &app name & & & & & & & & symbol name007a & &5.1565 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres hash_search_with_hash_value004a92f0 574315 & &3.9419 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres _bt_compare006a4bd0 514473 & &3.5312 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres LWLockAcquire962 & &3.5071 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres SearchCatCache007bc3a0 484601 & &3.3262 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres AllocSetAlloc 442341 & &3.0361 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres GetSnapshotData134 & &2.4170 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres heap_hot_search_buffer005b8f70 279718 & &1.9199 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres ExecInitExpr 249377 & &1.7117 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres PinBuffer006a & &1.1584 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres LWLockRelease007ac620 161861 & &1.1110 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres pg_encoding_mbcliplen007a & &1.1057 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres FunctionCall2Coll004aaa80 153079 & &1.0507 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres _bt_checkkeys007a & &1.0095 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres fmgr_info_cxt_security0049bce0 136680 & &0.9381 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres heap_page_prune_opt 130807 & &0.8978 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres hash_any006b2e50 130564 & &0.8962 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres PostgresMain776 & &0.8358 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres slot_deform_tuple......阶段1（测试程序和数据库在同一主机）PostgreSQL 9.5.0 对比 Oracle 12c TPC-C tpm对比如下：测试数据仅供参考。使用benchmarksql测试，系统还有大量空闲CPU，IO资源，所以性能应该不止于此。预计PostgreSQL可到50W tpm。有兴趣的童鞋可以使用load runner或者sysbench或其他工具再测试一下。------------------------------------------------------------------------------------------------------------特别感谢给Oracle优化支招的Oracle圈子的兄弟姐妹们。优化中，期待Oracle更好的表现。AWR报告截图见：------------------------------------------------------------------------------------------------------------阶段2对比，benchmarksql放到另一台主机，主机间万兆网同一交换机下互联。参考------------------------------------------------------------------------------------------------------------为了突破测试程序的极限，开4个schema，每个schema负责1000个仓库，数据量总共20亿左右，数据量400GB。每个测试程序对付一个schema。终端数保持一致，每个测试程序开24个终端，一共96个终端。测试数据量postgres=# \l+& & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &List of databases& &Name & &| &Owner & | Encoding | Collate | Ctype | & Access privileges & | &Size & | Tablespace | & & & & & & & &Description & & & & & & & &&-----------+----------+----------+---------+-------+-----------------------+---------+------------+--------------------------------------------&test01 & &| test01 & | UTF8 & & | C & & & | C & & | & & & & & & & & & & & | 100 GB &| pg_default |&&test02 & &| test02 & | UTF8 & & | C & & & | C & & | & & & & & & & & & & & | 100 GB &| pg_default |&&test03 & &| test03 & | UTF8 & & | C & & & | C & & | & & & & & & & & & & & | 100 GB &| pg_default |&&test04 & &| test04 & | UTF8 & & | C & & & | C & & | & & & & & & & & & & & | 100 GB &| pg_default |&benchmarksql软件目录$ lldrwxr-xr-x 7 digoal users 4.0K Jan 10 14:41 benchmarksql-4.1.0_pg01drwxr-xr-x 7 digoal users 4.0K Jan 10 14:41 benchmarksql-4.1.0_pg02drwxr-xr-x 7 digoal users 4.0K Jan 10 14:41 benchmarksql-4.1.0_pg03drwxr-xr-x 7 digoal users 4.0K Jan 10 14:41 benchmarksql-4.1.0_pg04测试cd benchmarksql-4.1.0_pg01/runnohup ./runBenchmark.sh props.pg &/dev/null 2&./errrun.log &cd ../../benchmarksql-4.1.0_pg02/runnohup ./runBenchmark.sh props.pg &/dev/null 2&./errrun.log &cd ../../benchmarksql-4.1.0_pg03/runnohup ./runBenchmark.sh props.pg &/dev/null 2&./errrun.log &cd ../../benchmarksql-4.1.0_pg04/runnohup ./runBenchmark.sh props.pg &/dev/null 2&./errrun.log &cd ../..测试结果$ cat benchmarksql-4.1.0_pg01/run/log/benchmarksql.log&&INFO & [ 17:54:04.925] & & & Thread-22 & & & Term-00, Measured tpmC (NewOrders) = 45416.28&INFO & [ 17:54:04.925] & & & Thread-22 & & & Term-00, Measured tpmTOTAL = &INFO & [ 17:54:04.925] & & & Thread-22 & & & Term-00, Session Start & & =
17:44:04&INFO & [ 17:54:04.925] & & & Thread-22 & & & Term-00, Session End & & & =
17:54:04&INFO & [ 17:54:04.925] & & & Thread-22 & & & Term-00, Transaction Count = 1134913$ cat benchmarksql-4.1.0_pg02/run/log/benchmarksql.log&&INFO & [ 17:54:04.943] & & & Thread-12 & & & Term-00, Measured tpmC (NewOrders) = 45292.48&INFO & [ 17:54:04.943] & & & Thread-12 & & & Term-00, Measured tpmTOTAL = &INFO & [ 17:54:04.943] & & & Thread-12 & & & Term-00, Session Start & & =
17:44:04&INFO & [ 17:54:04.944] & & & Thread-12 & & & Term-00, Session End & & & =
17:54:04&INFO & [ 17:54:04.944] & & & Thread-12 & & & Term-00, Transaction Count = 1132770$ cat benchmarksql-4.1.0_pg03/run/log/benchmarksql.log&&INFO & [ 17:54:04.955] & & & Thread-12 & & & Term-00, Measured tpmC (NewOrders) = 45336.15&INFO & [ 17:54:04.955] & & & Thread-12 & & & Term-00, Measured tpmTOTAL = &INFO & [ 17:54:04.956] & & & Thread-12 & & & Term-00, Session Start & & =
17:44:04&INFO & [ 17:54:04.956] & & & Thread-12 & & & Term-00, Session End & & & =
17:54:04&INFO & [ 17:54:04.956] & & & Thread-12 & & & Term-00, Transaction Count = 1132537$ cat benchmarksql-4.1.0_pg04/run/log/benchmarksql.log&&INFO & [ 17:54:04.986] & & & Thread-23 & & & Term-00, Measured tpmC (NewOrders) = 45231.67&INFO & [ 17:54:04.987] & & & Thread-23 & & & Term-00, Measured tpmTOTAL = &INFO & [ 17:54:04.987] & & & Thread-23 & & & Term-00, Session Start & & =
17:44:04&INFO & [ 17:54:04.987] & & & Thread-23 & & & Term-00, Session End & & & =
17:54:04&INFO & [ 17:54:04.987] & & & Thread-23 & & & Term-00, Transaction Count = 1130640TPM ：& +& +& +& = &第9分钟操作系统统计信息TOPtop - 17:38:27 up 4 days, &8:32, &4 users, &load average: 78.54, 68.64, 37.22Tasks: 658 total, &34 running, 624 sleeping, & 0 stopped, & 0 zombieCpu(s): 70.2%us, 15.7%sy, &0.0%ni, &5.5%id, &1.5%wa, &0.0%hi, &7.1%si, &0.0%stMem: &k total, k used, k free, & &59652k buffersSwap: k total, & & & &0k used, k free, k cachediostat -xavg-cpu: &%user & %nice %system %iowait &%steal & %idle& & & & & 71.39 & &0.00 & 22.47 & &1.26 & &0.00 & &4.88Device: & & & & rrqm/s & wrqm/s & & r/s & & w/s & rsec/s & wsec/s avgrq-sz avgqu-sz & await &svctm &%utildfa & & & & & & & 0.00 & & 0.00 8.67 050.67 & &15.99 & & 5.85 & &0.55 & 0.06 &68.17dfb & & & & & & & 0.00 & & 0.00 8.67 173.33 & &15.99 & & 5.98 & &0.56 & 0.06 &67.87dfc & & & & & & & 0.00 & & 0.00 4.33 504.00 & &15.99 & & 5.63 & &0.52 & 0.07 &71.60dm-0 & & & & & & &0.00 & & 0.00 70.33
& &15.99 & &17.60 & &0.55 & 0.03 &92.10测试过程oprofile报告#/home/digoal/oprof/bin/opreport -l -f -w -x -t 0.5Using /soft/digoal/soft_bak/oprof_test/oprofile_data/samples/ for samples directory.WARNING! Some of the events were throttled. Throttling occurs whenthe initial sample rate is too high, causing an excessive number ofinterrupts. &Decrease the sampling frequency. Check the directory/soft/digoal/soft_bak/oprof_test/oprofile_data/samples/current/stats/throttledfor the throttled event names.CPU: Intel Ivy Bridge microarchitecture, speed 2600 MHz (estimated)Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (No unit mask) count 100000vma & & &samples &% & & & &app name & & & & & & & & symbol name007a & 5.2511 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres hash_search_with_hash_value004a92f0 1895924 & 3.7816 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres _bt_compare 1844371 & 3.6787 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres GetSnapshotData5031 & 3.5404 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres SearchCatCache006a4bd0 1725350 & 3.4413 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres LWLockAcquire007bc3a0 1565190 & 3.1219 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres AllocSetAlloc6694 & 2.8058 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres heap_hot_search_buffer005b8f70 965646 & &1.9261 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres ExecInitExpr 767078 & &1.5300 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres PinBuffer004aaa80 617741 & &1.2321 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres _bt_checkkeys007a & &1.1729 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres FunctionCall2Coll006a & &1.1486 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres LWLockRelease007ac620 485162 & &0.9677 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres pg_encoding_mbcliplen007a & &0.9396 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres fmgr_info_cxt_security548 & &0.8807 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres slot_deform_tuple 425867 & &0.8494 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres hash_any006b2e50 404548 & &0.8069 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres PostgresMain007bd0f0 396510 & &0.7909 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres palloc0049bce0 394201 & &0.7863 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres heap_page_prune_opt007bce00 353243 & &0.7046 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres pfree896 & &0.6700 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres heap_page_prune145 & &0.6246 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres heap_getsysattr006b14a0 311776 & &0.6219 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres exec_bind_message007cb070 292106 & &0.5826 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres HeapTupleSatisfiesMVCC007bd210 275282 & &0.5491 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres MemoryContextAllocZeroAligned005b & &0.5449 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres ExecProject0495 & &0.5315 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres heap_update007bca10 265556 & &0.5297 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres AllocSetFree第二阶段Oracle 12.1.0.2.0和PostgreSQL 9.5.0 TPM 对比------------------------------------------------------------------------------------------------------------阶段3,开启PostgreSQL&预读, -- (n-1), n是条带数, 所以本例case effective_io_concurrency = 2大页支持, &开到168G; &/etc/sysctl.confvm.nr_hugepages = 102352sysctl -p/etc/security/limits.conf* & soft & memlock & &* & hard & memlock & &memlock & &大于 &nr_hugepages& &大于 &shared_buffers&分组提交,&commit_delay = 10,&commit_siblings = 16：shared_buffers = 164GB & & & & & & & & & # min 128kBhuge_pages = on & & & & & & & & & & & # on, off, or trymaintenance_work_mem = 2GB & & & & & & &# min 1MBwal_buffers = 16MB & & & & & & & & & & & # min 32kB, -1 sets based on shared_bufferswal_writer_delay = 10ms & & & & # 1-10000 millisecondscommit_delay = 10 & & & & & & & & & & & # range 0-100000, in microsecondscommit_siblings = 16 & & & & & & & & & &# range 1-1000checkpoint_timeout = 35min & & & & & & &# range 30s-1hmax_wal_size = 320GBcheckpoint_completion_target = 0.8 & & # checkpoint target duration, 0.0 - 1.0effective_cache_size = 240GBlog_destination = 'csvlog' & & & & & & &# Valid values are combinations oflogging_collector = on & & & & &# Enable capturing of stderr and csvloglog_truncate_on_rotation = on & & & & & # If on, an existing log file with the测试结果：$tail -n 5 benchmarksql-4.1.0_pg01/run/log/benchmarksql.log&&INFO & [ 13:33:55.917] & & & Thread-14 & & & Term-00, Measured tpmC (NewOrders) = 48151.07&INFO & [ 13:33:55.917] & & & Thread-14 & & & Term-00, Measured tpmTOTAL = &INFO & [ 13:33:55.917] & & & Thread-14 & & & Term-00, Session Start & & =
13:23:55&INFO & [ 13:33:55.917] & & & Thread-14 & & & Term-00, Session End & & & =
13:33:55&INFO & [ 13:33:55.917] & & & Thread-14 & & & Term-00, Transaction Count = 1202222$tail -n 5 benchmarksql-4.1.0_pg02/run/log/benchmarksql.log&&INFO & [ 13:33:55.971] & & & Thread-16 & & & Term-00, Measured tpmC (NewOrders) = 48505.54&INFO & [ 13:33:55.971] & & & Thread-16 & & & Term-00, Measured tpmTOTAL = &INFO & [ 13:33:55.971] & & & Thread-16 & & & Term-00, Session Start & & =
13:23:55&INFO & [ 13:33:55.972] & & & Thread-16 & & & Term-00, Session End & & & =
13:33:55&INFO & [ 13:33:55.972] & & & Thread-16 & & & Term-00, Transaction Count = 1211858$tail -n 5 benchmarksql-4.1.0_pg03/run/log/benchmarksql.log&&INFO & [ 13:33:55.985] & & & Thread-4 & & & &Term-00, Measured tpmC (NewOrders) = 48119.61&INFO & [ 13:33:55.985] & & & Thread-4 & & & &Term-00, Measured tpmTOTAL = &INFO & [ 13:33:55.985] & & & Thread-4 & & & &Term-00, Session Start & & =
13:23:55&INFO & [ 13:33:55.985] & & & Thread-4 & & & &Term-00, Session End & & & =
13:33:55&INFO & [ 13:33:55.985] & & & Thread-4 & & & &Term-00, Transaction Count = 1205271$tail -n 5 benchmarksql-4.1.0_pg04/run/log/benchmarksql.log&&INFO & [ 13:33:55.958] & & & Thread-21 & & & Term-00, Measured tpmC (NewOrders) = 48087.55&INFO & [ 13:33:55.958] & & & Thread-21 & & & Term-00, Measured tpmTOTAL = &INFO & [ 13:33:55.958] & & & Thread-21 & & & Term-00, Session Start & & =
13:23:55&INFO & [ 13:33:55.958] & & & Thread-21 & & & Term-00, Session End & & & =
13:33:55&INFO & [ 13:33:55.958] & & & Thread-21 & & & Term-00, Transaction Count = 1204638TPM: +& +& +& =&------------------------------------------------------------------------------------------------------------阶段4，优化分组提交的时延，最小结束点并发事务数量，work_mem等。操作系统优化，优化老化脏页刷新间隔vm.dirty_writeback_centisecs=10优化老化脏页阈值vm.dirty_expire_centisecs=6000优化用户进程刷脏页阈值vm.dirty_ratio=80优化内核进程刷脏页阈值vm.dirty_background_bytes=优化终端数，每个benchmarksql 20个终端，一共80个终端。psql\c test01 test01explain analyze select * explain analyze select *\c test02 test02explain analyze select * explain analyze select *\c test03 test03explain analyze select * explain analyze select *\c test04 test04explain analyze select * explain analyze select *listen_addresses = '0.0.0.0'
# what IP address(es)port = 1921
# (change requires restart)max_connections = 300
# (change requires restart)unix_socket_directories = '.'
# comma-separated list of directoriesshared_buffers = 164GB
# min 128kBhuge_pages = on
# on, off, or trywork_mem = 256MB
# min 64kBmaintenance_work_mem = 2GB
# min 1MBautovacuum_work_mem = 2GB
# min 1MB, or -1 to use maintenance_work_memdynamic_shared_memory_type = mmap
# the default is the first optionvacuum_cost_delay = 10
# 0-100 millisecondsvacuum_cost_limit = 10000
# 1-10000 creditsbgwriter_delay = 10ms
# 10-10000ms between roundsbgwriter_lru_maxpages = 1000
# 0-1000 max buffers written/roundbgwriter_lru_multiplier = 10.0
# 0-10.0 multipler on buffers scanned/roundeffective_io_concurrency = 2
# 1-1000; 0 disables prefetchingwal_level = minimal
# minimal, archive, hot_standby, or logicalsynchronous_commit = off
# sfull_page_writes = off
# recover from partial page writeswal_buffers = 1GB
# min 32kB, -1 sets based on shared_bufferswal_writer_delay = 10ms
# 1-10000 millisecondscommit_delay = 10
# range 0-100000, in microsecondscommit_siblings = 6
# range 1-1000checkpoint_timeout = 55min
# range 30s-1hmax_wal_size = 320GBcheckpoint_completion_target = 0.99
# checkpoint target duration, 0.0 - 1.0random_page_cost = 1.0
# same scale as aboveeffective_cache_size = 240GBlog_destination = 'csvlog'
# Valid values are combinations oflogging_collector = on
# Enable capturing of stderr and csvloglog_truncate_on_rotation = on
# If on, an existing log file with thelog_timezone = 'PRC'update_process_title = offtrack_activities = offautovacuum = on
# Enable autovacuum subprocess?
'on'pg_ctl restart测试结果$ tail -n 5 benchmarksql-4.1.0_pg01/run/log/benchmarksql.log
[ 11:55:09.461]
Term-00, Measured tpmC (NewOrders) = 57995.55 INFO
[ 11:55:09.461]
Term-00, Measured tpmTOTAL =
[ 11:55:09.461]
Term-00, Session Start
11:45:09 INFO
[ 11:55:09.461]
Term-00, Session End
11:55:09 INFO
[ 11:55:09.462]
Term-00, Transaction Count = 1449796$ tail -n 5 benchmarksql-4.1.0_pg02/run/log/benchmarksql.log
[ 11:55:09.499]
Term-00, Measured tpmC (NewOrders) = 58013.75 INFO
[ 11:55:09.499]
Term-00, Measured tpmTOTAL =
[ 11:55:09.499]
Term-00, Session Start
11:45:09 INFO
[ 11:55:09.500]
Term-00, Session End
11:55:09 INFO
[ 11:55:09.500]
Term-00, Transaction Count = 1450110$ tail -n 5 benchmarksql-4.1.0_pg03/run/log/benchmarksql.log
[ 11:55:09.541]
Term-00, Measured tpmC (NewOrders) = 57322.05 INFO
[ 11:55:09.541]
Term-00, Measured tpmTOTAL =
[ 11:55:09.542]
Term-00, Session Start
11:45:09 INFO
[ 11:55:09.542]
Term-00, Session End
11:55:09 INFO
[ 11:55:09.542]
Term-00, Transaction Count = 1432298$ tail -n 5 benchmarksql-4.1.0_pg04/run/log/benchmarksql.log
[ 11:55:09.574]
Term-00, Measured tpmC (NewOrders) = 57863.92 INFO
[ 11:55:09.574]
Term-00, Measured tpmTOTAL =
[ 11:55:09.575]
Term-00, Session Start
11:45:09 INFO
[ 11:55:09.575]
Term-00, Session End
11:55:09 INFO
[ 11:55:09.575]
Term-00, Transaction Count = 1445978TPM：&+&&+&&+&&=&------------------------------------------------------------------------------------------------------------阶段5，编译器版本INTEL编译器CLANG编译器$ export LD_LIBRARY_PATH=/u02/digoal/gcc4.9.3/lib:/u02/digoal/cloog/lib:/u02/digoal/gmp/lib:/u02/digoal/isl/lib:/u02/digoal/mpc/lib:/u02/digoal/mpfr/lib:$LD_LIBRARY_PATH$ export PATH=/u02/digoal/gcc4.9.3/bin:$PATH$ CFLAGS="-O3 -march=native -flto" CC=/u02/digoal/gcc4.9.3/bin/gcc ./configure --prefix=/u02/digoal/soft_bak/pgsql9.5 --with-blocksize=8 --with-pgport=1921 --with-perl --with-python --with-tcl --with-openssl --with-pam --with-ldap --with-libxml --with-libxslt --enable-thread-safety --with-wal-segsize=64$ make world -j 32$ make install-world -j 32$ pg_config&测试结果digoal tail -n 5 benchmarksql-4.1.0_pg01/run/log/benchmarksql.log&&INFO & [ 02:00:49.699] & & & Thread-15 & & & Term-00, Measured tpmC (NewOrders) = 59092.33&INFO & [ 02:00:49.699] & & & Thread-15 & & & Term-00, Measured tpmTOTAL = &INFO & [ 02:00:49.699] & & & Thread-15 & & & Term-00, Session Start & & =
01:50:49&INFO & [ 02:00:49.699] & & & Thread-15 & & & Term-00, Session End & & & =
02:00:49&INFO & [ 02:00:49.699] & & & Thread-15 & & & Term-00, Transaction Count = 1478385digoal tail -n 5 benchmarksql-4.1.0_pg02/run/log/benchmarksql.log&&INFO & [ 02:00:49.704] & & & Thread-0 & & & &Term-00, Measured tpmC (NewOrders) = 60051.49&INFO & [ 02:00:49.704] & & & Thread-0 & & & &Term-00, Measured tpmTOTAL = &INFO & [ 02:00:49.704] & & & Thread-0 & & & &Term-00, Session Start & & =
01:50:49&INFO & [ 02:00:49.704] & & & Thread-0 & & & &Term-00, Session End & & & =
02:00:49&INFO & [ 02:00:49.704] & & & Thread-0 & & & &Term-00, Transaction Count = 1502367digoal tail -n 5 benchmarksql-4.1.0_pg03/run/log/benchmarksql.log&&INFO & [ 02:00:49.693] & & & Thread-16 & & & Term-00, Measured tpmC (NewOrders) = 60273.99&INFO & [ 02:00:49.694] & & & Thread-16 & & & Term-00, Measured tpmTOTAL = &INFO & [ 02:00:49.694] & & & Thread-16 & & & Term-00, Session Start & & =
01:50:49&INFO & [ 02:00:49.694] & & & Thread-16 & & & Term-00, Session End & & & =
02:00:49&INFO & [ 02:00:49.694] & & & Thread-16 & & & Term-00, Transaction Count = 1506066digoal tail -n 5 benchmarksql-4.1.0_pg04/run/log/benchmarksql.log&&INFO & [ 02:00:49.715] & & & Thread-18 & & & Term-00, Measured tpmC (NewOrders) = 60180.69&INFO & [ 02:00:49.715] & & & Thread-18 & & & Term-00, Measured tpmTOTAL = &INFO & [ 02:00:49.716] & & & Thread-18 & & & Term-00, Session Start & & =
01:50:49&INFO & [ 02:00:49.716] & & & Thread-18 & & & Term-00, Session End & & & =
02:00:49&INFO & [ 02:00:49.716] & & & Thread-18 & & & Term-00, Transaction Count = 1505962TPM------------------------------------------------------------------------------------------------------------CLANG编译CC=/u02/digoal/llvm/bin/clang CFLAGS="-O2 -fstrict-enums" ./configure --prefix=/u02/digoal/soft_bak/pgsql9.5 &--with-pgport=1921 --with-perl --with-python --with-tcl --with-openssl --with-pam --with-ldap --with-libxml --with-libxslt --enable-thread-safetymake world -j 32make install-world -j 32压测结果$ tail -n 5 benchmarksql-4.1.0_pg01/run/log/benchmarksql.log&&INFO & [ 07:21:58.070] & & & Thread-12 & & & Term-00, Measured tpmC (NewOrders) = 60519.19&INFO & [ 07:21:58.070] & & & Thread-12 & & & Term-00, Measured tpmTOTAL = &INFO & [ 07:21:58.070] & & & Thread-12 & & & Term-00, Session Start & & =
07:11:58&INFO & [ 07:21:58.071] & & & Thread-12 & & & Term-00, Session End & & & =
07:21:58&INFO & [ 07:21:58.071] & & & Thread-12 & & & Term-00, Transaction Count = 1512377$ tail -n 5 benchmarksql-4.1.0_pg02/run/log/benchmarksql.log&&INFO & [ 07:21:58.180] & & & Thread-15 & & & Term-00, Measured tpmC (NewOrders) = 60924.87&INFO & [ 07:21:58.180] & & & Thread-15 & & & Term-00, Measured tpmTOTAL = &INFO & [ 07:21:58.180] & & & Thread-15 & & & Term-00, Session Start & & =
07:11:58&INFO & [ 07:21:58.180] & & & Thread-15 & & & Term-00, Session End & & & =
07:21:58&INFO & [ 07:21:58.180] & & & Thread-15 & & & Term-00, Transaction Count = 1521312$ tail -n 5 benchmarksql-4.1.0_pg03/run/log/benchmarksql.log&&INFO & [ 07:21:58.198] & & & Thread-0 & & & &Term-00, Measured tpmC (NewOrders) = 60481.19&INFO & [ 07:21:58.198] & & & Thread-0 & & & &Term-00, Measured tpmTOTAL = &INFO & [ 07:21:58.199] & & & Thread-0 & & & &Term-00, Session Start & & =
07:11:58&INFO & [ 07:21:58.199] & & & Thread-0 & & & &Term-00, Session End & & & =
07:21:58&INFO & [ 07:21:58.199] & & & Thread-0 & & & &Term-00, Transaction Count = 1512968$ tail -n 5 benchmarksql-4.1.0_pg04/run/log/benchmarksql.log&&INFO & [ 07:21:58.200] & & & Thread-5 & & & &Term-00, Measured tpmC (NewOrders) = 60715.57&INFO & [ 07:21:58.200] & & & Thread-5 & & & &Term-00, Measured tpmTOTAL = &INFO & [ 07:21:58.200] & & & Thread-5 & & & &Term-00, Session Start & & =
07:11:58&INFO & [ 07:21:58.200] & & & Thread-5 & & & &Term-00, Session End & & & =
07:21:58&INFO & [ 07:21:58.200] & & & Thread-5 & & & &Term-00, Transaction Count = 1518149TPM:当前perf top& & & & & & &samples &pcnt function & & & & & & & & & & & & &DSO& & & & & & &_______ _____ _________________________________ __________________________________________& & & & & & .2% hash_search_with_hash_value & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & .8% _bt_compare & & & & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & .6% AllocSetAlloc & & & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & .1% LWLockAcquire & & & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & .1% memcpy & & & & & & & & & & & & & &/lib64/libc-2.12.so & & & & & & & & & & &&& & & & & & &.8% SearchCatCache & & & & & & & & & &/u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.7% heap_hot_search_buffer & & & & & &/u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.6% GetSnapshotData & & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.4% ExecInitExpr & & & & & & & & & & &/u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.3% fmgr_info_cxt_security & & & & & &/u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.0% PostgresMain & & & & & & & & & & &/u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.9% LWLockRelease & & & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.8% PinBuffer & & & & & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.8% _int_malloc & & & & & & & & & & & /lib64/libc-2.12.so & & & & & & & & & & &&& & & & & & &.8% StrategyGetBuffer & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.8% slot_deform_tuple & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.7% FunctionCall2Coll & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.7% __GI_vfprintf & & & & & & & & & & /lib64/libc-2.12.so & & & & & & & & & & &&& & & & & & &.7% __strncpy_ssse3 & & & & & & & & & /lib64/libc-2.12.so & & & & & & & & & & &&& & & & & & &.7% aliflash_reconfig_task & & & & & &[aliflash] & & & & & & & & & & & & & & & && & & & & & &.6% _bt_checkkeys & & & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.6% __memset_sse2 & & & & & & & & & & /lib64/libc-2.12.so & & & & & & & & & & &&& & & & & & &.6% palloc & & & & & & & & & & & & & &/u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.5% __strlen_sse42 & & & & & & & & & &/lib64/libc-2.12.so & & & & & & & & & & &&& & & & & & &.5% _int_free & & & & & & & & & & & & /lib64/libc-2.12.so & & & & & & & & & & &&& & & & & & &.5% heap_page_prune & & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.5% hash_any & & & & & & & & & & & & &/u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.5% heap_page_prune_opt & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.5% __schedule & & & & & & & & & & & &[kernel.kallsyms] & & & & & & & & & & & &&& & & & & & &.4% MemoryContextAllocZeroAligned & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.4% pfree & & & & & & & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.4% heap_update & & & & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.4% update_blocked_averages & & & & & [kernel.kallsyms] & & & & & & & & & & & &&& & & & & & &.4% __switch_to & & & & & & & & & & & [kernel.kallsyms] & & & & & & & & & & & &&& & & & & & &.4% heap_getsysattr & & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.4% TupleDescInitEntry & & & & & & & &/u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.4% irq_entries_start & & & & & & & & [kernel.kallsyms] & & & & & & & & & & & &&& & & & & & &.4% AllocSetFree & & & & & & & & & & &/u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.4% _wordcopy_bwd_aligned & & & & & & /lib64/libc-2.12.so & & & & & & & & & & &&& & & & & & &.4% _raw_spin_lock & & & & & & & & & &[kernel.kallsyms] & & & & & & & & & & & &&& & & & & & &.4% check_stack_depth & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.3% _bt_binsrch & & & & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.3% ReadBuffer_common & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.3% expression_tree_walker & & & & & &/u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.3% __GI___libc_malloc & & & & & & & &/lib64/libc-2.12.so & & & & & & & & & & &&& & & & & & &.3% __memcmp_sse4_1 & & & & & & & & & /lib64/libc-2.12.so & & & & & & & & & & &&& & & & & & &.3% LockAcquireExtended & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres------------------------------------------------------------------------------------------------------------极限性能参数vm.swappiness = 0kernel.shmmax=net.core.rmem_max = 4194304net.core.wmem_max = 4194304net.core.rmem_default = 262144net.core.wmem_default = 262144net.ipv4.ip_local_port_range = kernel.sem = 00 vm.dirty_background_bytes = vm.dirty_ratio = 80vm.nr_hugepages = 102352kernel /vmlinuz-3.18.24 numa=off elevator=deadline intel_idle.max_cstate=0 scsi_mod.scan=syncblockdev --setra 16384 /dev/dfablockdev --setra 16384 /dev/dfbblockdev --setra 16384 /dev/dfcblockdev --setra 16384 /dev/dm-0关闭analyze, 关闭某些日志表的autovacuum.max_connections = 300 & & & & & & & & & # (change requires restart)unix_socket_directories = '.' & # comma-separated list of directoriesshared_buffers = 194GB & & & & & & & & & # min 128kBhuge_pages = on & & & & & & & & & & & # on, off, or trywork_mem = 256MB & & & & & & & & & & & & # min 64kBmaintenance_work_mem = 2GB & & & & & & &# min 1MBautovacuum_work_mem = 2GB & & & & & & & # min 1MB, or -1 to use maintenance_work_memdynamic_shared_memory_type = mmap & & &# the default is the first optionvacuum_cost_delay = 0 & & & & & & & & &# 0-100 millisecondsbgwriter_delay = 10ms & & & & & & & & & # 10-10000ms between roundsbgwriter_lru_maxpages = 1000 & & & & & &# 0-1000 max buffers written/roundbgwriter_lru_multiplier = 10.0 & & & & &# 0-10.0 multipler on buffers scanned/roundeffective_io_concurrency = 2 & & & & & # 1-1000; 0 disables prefetchingwal_level = minimal &# minimal, archive, hot_standby, or logicalsynchronous_commit = off & & & & & & & &# swal_sync_method = open_sync & & & & & & & &# the default is the first optionfull_page_writes = off & & & & & & & & &# recover from partial page writeswal_buffers = 1GB & & & & & & & & & & & # min 32kB, -1 sets based on shared_bufferswal_writer_delay = 10ms & & & & # 1-10000 millisecondscommit_delay = 20 & & & & & & & & & & & # range 0-100000, in microsecondscommit_siblings = 9 & & & & & & & & & &# range 1-1000checkpoint_timeout = 55min & & & & & & &# range 30s-1hmax_wal_size = 320GBcheckpoint_completion_target = 0.99 & & # checkpoint target duration, 0.0 - 1.0random_page_cost = 1.0 & & & & & & & & # same scale as aboveeffective_cache_size = 240GBlog_destination = 'csvlog' & & & & & & &# Valid values are combinations oflogging_collector = on & & & & &# Enable capturing of stderr and csvloglog_truncate_on_rotation = on & & & & & # If on, an existing log file with theupdate_process_title = offtrack_activities = offautovacuum = on & & & & & & & & & & & &# Enable autovacuum subprocess? &'on'autovacuum_max_workers = 4 & & & & & & # max number of autovacuum subprocesses &autovacuum_naptime = 6s & & & & & & &# time between autovacuum runsautovacuum_vacuum_cost_delay = 0 & &# default vacuum cost delay for[其他优化手段]1. PostgreSQL jdbc有一些参数可以优化，本文还未处理。例如防止类型转换，QUERY plan CACHE size。2. PostgreSQL 代码层也有优化的空间，例如分区表的代码，快照的优化。[特别声明]1. 本文纯属技术交流，测试数据不具备任何指导意义。[参考]1.&
阅读(4418)|
用微信&&“扫一扫”
将文章分享到朋友圈。
用易信&&“扫一扫”
将文章分享到朋友圈。
历史上的今天
在LOFTER的更多文章
loftPermalink:'',
id:'fks_',
blogTitle:'BenchmarkSQL 测试PostgreSQL 9.5.0 TPC-C 性能',
blogAbstract:'Oracle 12c TPC-C 测试请参考：
{if x.moveFrom=='wap'}
{elseif x.moveFrom=='iphone'}
{elseif x.moveFrom=='android'}
{elseif x.moveFrom=='mobile'}
${a.selfIntro|escape}{if great260}${suplement}{/if}
{list a as x}
推荐过这篇日志的人：
{list a as x}
{if !!b&&b.length>0}
他们还推荐了：
{list b as y}
转载记录：
{list d as x}
{list a as x}
{list a as x}
{list a as x}
{list a as x}
{if x_index>4}{break}{/if}
${fn2(x.publishTime,'yyyy-MM-dd HH:mm:ss')}
{list a as x}
{if !!(blogDetail.preBlogPermalink)}
{if !!(blogDetail.nextBlogPermalink)}
{list a as x}
{if defined('newslist')&&newslist.length>0}
{list newslist as x}
{if x_index>7}{break}{/if}
{list a as x}
{var first_option =}
{list x.voteDetailList as voteToOption}
{if voteToOption==1}
{if first_option==false},{/if}&&“${b[voteToOption_index]}”&&
{if (x.role!="-1") },“我是${c[x.role]}”&&{/if}
&&&&&&&&${fn1(x.voteTime)}
{if x.userName==''}{/if}
网易公司版权所有&&
{list x.l as y}
{if defined('wl')}
{list wl as x}{/list}}
PostgreSQL 高校实验室,企业,社区大联盟才是未来的方向.
LOFTER精选
网易考拉推荐
用微信&&“扫一扫”
将文章分享到朋友圈。
用易信&&“扫一扫”
将文章分享到朋友圈。
文件系统为XFS，优化手段如下：本文在同样的硬件测试环境下对比PostgreSQL 9.5.0。我们看看1月7号发布的PostgreSQL 新版本性能咋样？benchmarkSQL 配置请参考：内核配置/etc/grub.confnuma=offelevator=deadline编译器版本gcc version 4.4.6
(Red Hat 4.4.6-3) (GCC)&PostgreSQL编译项./configure --prefix=/u02/digoal/soft_bak/pgsql9.5 --with-blocksize=8 --with-pgport=1921 --with-perl --with-python --with-tcl --with-openssl --with-pam --with-ldap --with-libxml --with-libxslt --enable-thread-safetygmake world -j32gmake install-world -j32配置postgres环境变量$ vi env_pg.sh&export PS1="$USER@`/bin/hostname -s`-& "export PGPORT=1921export PGDATA=/data01/pgdata/pg_rootexport LANG=en_US.utf8export PGHOME=/u02/digoal/soft_bak/pgsql9.5export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATHexport DATE=`date +"%Y%m%d%H%M"`export PATH=$PGHOME/bin:$PATH:.export MANPATH=$PGHOME/share/man:$MANPATHexport PGHOST=$PGDATAexport PGDATABASE=postgresexport PGUSER=postgresalias rm='rm -i'alias ll='ls -lh'unalias vi配置postgresql.conf$ vi $PGDATA/postgresql.confport = 1921 & & & & & & & & & & & & & & # (change requires restart)max_connections = 300 & & & & & & & & & # (change requires restart)unix_socket_directories = '.' & # comma-separated list of directoriesshared_buffers = 32GB & & & & & & & & & # min 128kBhuge_pages = try & & & & & & & & & & & # on, off, or trymaintenance_work_mem = 2GB & & & & & & &# min 1MBdynamic_shared_memory_type = posix & & &# the default is the first optionbgwriter_delay = 10ms & & & & & & & & & # 10-10000ms between roundswal_level = minimal &# minimal, archive, hot_standby, or logicalsynchronous_commit = off & & & & & & & &# sfull_page_writes = off & & & & & & & & &# recover from partial page writeswal_buffers = 16MB & & & & & & & & & & & # min 32kB, -1 sets based on shared_bufferswal_writer_delay = 10ms & & & & # 1-10000 millisecondsmax_wal_size = 32GBeffective_cache_size = 240GBlog_destination = 'csvlog' & & & & & & &# Valid values are combinations oflogging_collector = on & & & & &# Enable capturing of stderr and csvloglog_truncate_on_rotation = on & & & & & # If on, an existing log file with the下载最新java版本对应的postgresql jdbc jarwget https://jdbc.postgresql.org/download/postgresql-9.4.1207.jre7.jarmv postgresql-9.4.1207.jre7.jar benchmarksql-4.1.0/lib/配置benchmarksql，使用新的postgresql java驱动$ vi runBenchmark.sh&java -cp .:../lib/postgresql-9.4.1207.jre7.jar:../lib/log4j-1.2.17.jar:../lib/apache-log4j-extras-1.1.jar:../dist/BenchmarkSQL-4.1.jar -Dprop=$1 jTPCC$ vi runLoader.shjava -cp .:../lib/postgresql-9.4.1207.jre7.jar:../dist/BenchmarkSQL-4.1.jar -Dprop=$1 LoadData $2 $3 $4 $5$ vi runSQL.sh&myCP="../lib/postgresql-9.4.1207.jre7.jar"myCP="$myCP:../dist/BenchmarkSQL-4.1.jar"myOPTS="-Dprop=$1"myOPTS="$myOPTS -DcommandFile=$2"java -cp .:$myCP $myOPTS ExecJDBC修改log4j，减少日志打印量。priority改成info，只输出最终结果，不输出产生订单的日志。$ vi&log4j.xml&?xml version="1.0" encoding="UTF-8" ?&&!DOCTYPE log4j:configuration SYSTEM "log4j.dtd"&&log4j:configuration xmlns:log4j='http://jakarta.apache.org/log4j/'&&appender name="console" class="org.apache.log4j.ConsoleAppender"&¶m name="Threshold" value="info"/&&layout class="org.apache.log4j.PatternLayout"&¶m name="ConversionPattern" value="%d %5p - %m%n"/&&/layout&&/appender&&appender name="R" class="org.apache.log4j.rolling.RollingFileAppender"&¶m name="Append" value="True" /&&rollingPolicy class="org.apache.log4j.rolling.TimeBasedRollingPolicy"&¶m name="FileNamePattern" value="log/archive/benchmarksql.%d{yyyyMMddHHmm}.log"/&¶m name="ActiveFileName" value="log/benchmarksql.log"/&&/rollingPolicy&&triggeringPolicy class="org.apache.log4j.rolling.SizeBasedTriggeringPolicy"&¶m name="MaxFileSize" value="1"/&&/triggeringPolicy&&layout class="org.apache.log4j.PatternLayout"&¶m name="ConversionPattern" value="%5p\t[%d{yyyy-MM-dd HH:mm:ss.SSS}]\t%t \t%m%n"/&&/layout&&filter class="org.apache.log4j.filter.StringMatchFilter"&¶m name="StringToMatch" value ="\n" /&¶m name="AcceptOnMatch" value="false" /&&/filter&&/appender&&appender name="E" class="org.apache.log4j.rolling.RollingFileAppender"&¶m name="Append" value="True" /&¶m name="Threshold" value="warn"/&&rollingPolicy class="org.apache.log4j.rolling.TimeBasedRollingPolicy"&¶m name="FileNamePattern" value="log/BenchmarkSQLError.%d.log"/&¶m name="ActiveFileName" value="log/BenchmarkSQLError.log"/&&/rollingPolicy&&layout class="org.apache.log4j.PatternLayout"&¶m name="ConversionPattern" value="%5p\t[%d{yyyy-MM-dd HH:mm:ss.SSS}]\t%t \t%m%n"/&&/layout&&/appender&&root&&priority value="info"/&&appender-ref ref="R"/&&appender-ref ref="E"/&&/root&&/log4j:configuration&编辑连接配置和压测配置，与测试Oracle 12c 的保持一致。1000 个仓库，约5亿数据量。$ vi props.pg&driver=org.postgresql.Driverconn=jdbc:postgresql://localhost:1921/postgresuser=postgrespassword=123warehouses=1000terminals=96//To run specified transactions per terminal- runMins must equal zerorunTxnsPerTerminal=0//To run for specified minutes- runTxnsPerTerminal must equal zerorunMins=1//Number of total transactions per minutelimitTxnsPerMin=0//The following five values must add up to 100//The default percentages of 45, 43, 4, 4 & 4 match the TPC-C specnewOrderWeight=40paymentWeight=36orderStatusWeight=8deliveryWeight=8stockLevelWeight=8配置postgres用户默认搜索路径$ psqlpsql (9.5.0)Type "help" for help.postgres=# alter role postgres set search_path='benchmarksql','public';创建用于存放生成CSV的目录$ mkdir /u02/digoal/soft_bak/benchcsv修改sqlTableCopies，指定目录$ vi sqlTableCopies© benchmarksql.warehouse& (w_id, w_ytd, w_tax, w_name, w_street_1, w_street_2, w_city, w_state, w_zip) && from '/u02/digoal/soft_bak/benchcsv/warehouse.csv' WITH CSV;copy benchmarksql.item& (i_id, i_name, i_price, i_data, i_im_id)&& from '/u02/digoal/soft_bak/benchcsv/item.csv' WITH CSV;copy benchmarksql.stock& (s_i_id, s_w_id, s_quantity, s_ytd, s_order_cnt, s_remote_cnt, s_data,& &s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05,& &s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10)& from '/u02/digoal/soft_bak/benchcsv/stock.csv' WITH CSV;copy benchmarksql.district& (d_id, d_w_id, d_ytd, d_tax, d_next_o_id, d_name, d_street_1,& &d_street_2, d_city, d_state, d_zip)&& from '/u02/digoal/soft_bak/benchcsv/district.csv' WITH CSV;copy benchmarksql.customer& (c_id, c_d_id, c_w_id, c_discount, c_credit, c_last, c_first, c_credit_lim,&& &c_balance, c_ytd_payment, c_payment_cnt, c_delivery_cnt, c_street_1,&& &c_street_2, c_city, c_state, c_zip, c_phone, c_since, c_middle, c_data)&& from '/u02/digoal/soft_bak/benchcsv/customer.csv' WITH CSV;copy benchmarksql.history& (hist_id, h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id, h_date, h_amount, h_data)&& from '/u02/digoal/soft_bak/benchcsv/cust-hist.csv' WITH CSV;copy benchmarksql.oorder& (o_id, o_w_id, o_d_id, o_c_id, o_carrier_id, o_ol_cnt, o_all_local, o_entry_d)&& from '/u02/digoal/soft_bak/benchcsv/order.csv' WITH CSV;copy benchmarksql.order_line& (ol_w_id, ol_d_id, ol_o_id, ol_number, ol_i_id, ol_delivery_d,&& &ol_amount, ol_supply_w_id, ol_quantity, ol_dist_info)&& from '/u02/digoal/soft_bak/benchcsv/order-line.csv' WITH CSV;copy benchmarksql.new_order& (no_w_id, no_d_id, no_o_id) && from '/u02/digoal/soft_bak/benchcsv/new-order.csv' WITH CSV;建立表结构$ cd benchmarksql-4.1.0/run$ ./runSQL.sh props.pg sqlTableCreates生成CSV$ ./runLoader.sh props.pg numWarehouses 1000 fileLocation /u02/digoal/soft_bak/benchcsv/&1000个仓库的数据量：total 69G-rw-r--r-- 1 digoal users 2.0G Jan &9 15:53 cust-hist.csv-rw-r--r-- 1 digoal users &16G Jan &9 15:53 customer.csv-rw-r--r-- 1 digoal users 898K Jan &9 15:12 district.csv-rw-r--r-- 1 digoal users 7.0M Jan &9 14:22 item.csv-rw-r--r-- 1 digoal users &95M Jan &9 16:14 new-order.csv-rw-r--r-- 1 digoal users 1.3G Jan &9 16:14 order.csv-rw-r--r-- 1 digoal users &22G Jan &9 16:14 order-line.csv-rw-r--r-- 1 digoal users &28G Jan &9 15:12 stock.csv-rw-r--r-- 1 digoal users &84K Jan &9 14:22 warehouse.csv导入数据库$ ./runSQL.sh props.pg sqlTableCopies创建约束和索引$ ./runSQL.sh props.pg sqlIndexCreates&备份$ pg_dump -f /u02/digoal/soft_bak/benchmarksql.dmp -F c -n benchmarksql postgres压测：nohup ./runBenchmark.sh props.pg &/dev/null 2&./errrun.log &测试结果：&INFO & [ 22:03:39.961] & & & Thread-7 & & & &Term-00,&&INFO & [ 22:03:39.963] & & & Thread-7 & & & &Term-00,&&INFO & [ 22:03:39.963] & & & Thread-7 & & & &Term-00, Measured tpmC (NewOrders) = &INFO & [ 22:03:39.963] & & & Thread-7 & & & &Term-00, Measured tpmTOTAL = &INFO & [ 22:03:39.964] & & & Thread-7 & & & &Term-00, Session Start & & =
21:53:39&INFO & [ 22:03:39.964] & & & Thread-7 & & & &Term-00, Session End & & & =
22:03:39&INFO & [ 22:03:39.964] & & & Thread-7 & & & &Term-00, Transaction Count = 2563088主机信息，截取压测第9分钟的数据。TOPtop - 22:02:09 up 3 days, 12:55, &3 users, &load average: 19.23, 15.97, 8.37Tasks: 619 total, &10 running, 609 sleeping, & 0 stopped, & 0 zombieCpu(s): 35.0%us, &9.4%sy, &0.0%ni, 52.6%id, &0.1%wa, &0.0%hi, &2.9%si, &0.0%stMem: &k total, k used, k free, & &36672k buffersSwap: k total, & & & &0k used, k free, k cachediostat -xavg-cpu: &%user & %nice %system %iowait &%steal & %idle& & & & & 35.07 & &0.00 & 12.30 & &0.12 & &0.00 & 52.51Device: & & & & rrqm/s & wrqm/s & & r/s & & w/s & rsec/s & wsec/s avgrq-sz avgqu-sz & await &svctm &%utildfa & & & & & & & 0.00 & & 0.00 & 57.40 &743.40 & 918.40 11849.00 & &15.94 & & 0.02 & &0.03 & 0.03 & 2.08dfb & & & & & & & 0.00 & & 0.00 & 57.20 &740.40 & 915.20 11829.00 & &15.98 & & 0.02 & &0.03 & 0.03 & 2.04dfc & & & & & & & 0.00 & & 0.00 & 58.40 &730.80 & 934.40 11675.80 & &15.98 & & 0.03 & &0.03 & 0.03 & 2.52dm-0 & & & & & & &0.00 & & 0.00 &173.00 68.00 35331.40 & &15.97 & & 0.08 & &0.03 & 0.03 & 7.02Oracle有AWR报告，PostgreSQL可以使用oprofile跟踪统计：参考找到需要优化的代码就靠它了。CPU: Intel Ivy Bridge microarchitecture, speed 2600 MHz (estimated)Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (No unit mask) count 100000vma & & &samples &% & & & &app name & & & & & & & & symbol name007a & &5.1565 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres hash_search_with_hash_value004a92f0 574315 & &3.9419 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres _bt_compare006a4bd0 514473 & &3.5312 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres LWLockAcquire962 & &3.5071 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres SearchCatCache007bc3a0 484601 & &3.3262 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres AllocSetAlloc 442341 & &3.0361 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres GetSnapshotData134 & &2.4170 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres heap_hot_search_buffer005b8f70 279718 & &1.9199 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres ExecInitExpr 249377 & &1.7117 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres PinBuffer006a & &1.1584 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres LWLockRelease007ac620 161861 & &1.1110 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres pg_encoding_mbcliplen007a & &1.1057 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres FunctionCall2Coll004aaa80 153079 & &1.0507 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres _bt_checkkeys007a & &1.0095 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres fmgr_info_cxt_security0049bce0 136680 & &0.9381 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres heap_page_prune_opt 130807 & &0.8978 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres hash_any006b2e50 130564 & &0.8962 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres PostgresMain776 & &0.8358 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres slot_deform_tuple......阶段1（测试程序和数据库在同一主机）PostgreSQL 9.5.0 对比 Oracle 12c TPC-C tpm对比如下：测试数据仅供参考。使用benchmarksql测试，系统还有大量空闲CPU，IO资源，所以性能应该不止于此。预计PostgreSQL可到50W tpm。有兴趣的童鞋可以使用load runner或者sysbench或其他工具再测试一下。------------------------------------------------------------------------------------------------------------特别感谢给Oracle优化支招的Oracle圈子的兄弟姐妹们。优化中，期待Oracle更好的表现。AWR报告截图见：------------------------------------------------------------------------------------------------------------阶段2对比，benchmarksql放到另一台主机，主机间万兆网同一交换机下互联。参考------------------------------------------------------------------------------------------------------------为了突破测试程序的极限，开4个schema，每个schema负责1000个仓库，数据量总共20亿左右，数据量400GB。每个测试程序对付一个schema。终端数保持一致，每个测试程序开24个终端，一共96个终端。测试数据量postgres=# \l+& & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &List of databases& &Name & &| &Owner & | Encoding | Collate | Ctype | & Access privileges & | &Size & | Tablespace | & & & & & & & &Description & & & & & & & &&-----------+----------+----------+---------+-------+-----------------------+---------+------------+--------------------------------------------&test01 & &| test01 & | UTF8 & & | C & & & | C & & | & & & & & & & & & & & | 100 GB &| pg_default |&&test02 & &| test02 & | UTF8 & & | C & & & | C & & | & & & & & & & & & & & | 100 GB &| pg_default |&&test03 & &| test03 & | UTF8 & & | C & & & | C & & | & & & & & & & & & & & | 100 GB &| pg_default |&&test04 & &| test04 & | UTF8 & & | C & & & | C & & | & & & & & & & & & & & | 100 GB &| pg_default |&benchmarksql软件目录$ lldrwxr-xr-x 7 digoal users 4.0K Jan 10 14:41 benchmarksql-4.1.0_pg01drwxr-xr-x 7 digoal users 4.0K Jan 10 14:41 benchmarksql-4.1.0_pg02drwxr-xr-x 7 digoal users 4.0K Jan 10 14:41 benchmarksql-4.1.0_pg03drwxr-xr-x 7 digoal users 4.0K Jan 10 14:41 benchmarksql-4.1.0_pg04测试cd benchmarksql-4.1.0_pg01/runnohup ./runBenchmark.sh props.pg &/dev/null 2&./errrun.log &cd ../../benchmarksql-4.1.0_pg02/runnohup ./runBenchmark.sh props.pg &/dev/null 2&./errrun.log &cd ../../benchmarksql-4.1.0_pg03/runnohup ./runBenchmark.sh props.pg &/dev/null 2&./errrun.log &cd ../../benchmarksql-4.1.0_pg04/runnohup ./runBenchmark.sh props.pg &/dev/null 2&./errrun.log &cd ../..测试结果$ cat benchmarksql-4.1.0_pg01/run/log/benchmarksql.log&&INFO & [ 17:54:04.925] & & & Thread-22 & & & Term-00, Measured tpmC (NewOrders) = 45416.28&INFO & [ 17:54:04.925] & & & Thread-22 & & & Term-00, Measured tpmTOTAL = &INFO & [ 17:54:04.925] & & & Thread-22 & & & Term-00, Session Start & & =
17:44:04&INFO & [ 17:54:04.925] & & & Thread-22 & & & Term-00, Session End & & & =
17:54:04&INFO & [ 17:54:04.925] & & & Thread-22 & & & Term-00, Transaction Count = 1134913$ cat benchmarksql-4.1.0_pg02/run/log/benchmarksql.log&&INFO & [ 17:54:04.943] & & & Thread-12 & & & Term-00, Measured tpmC (NewOrders) = 45292.48&INFO & [ 17:54:04.943] & & & Thread-12 & & & Term-00, Measured tpmTOTAL = &INFO & [ 17:54:04.943] & & & Thread-12 & & & Term-00, Session Start & & =
17:44:04&INFO & [ 17:54:04.944] & & & Thread-12 & & & Term-00, Session End & & & =
17:54:04&INFO & [ 17:54:04.944] & & & Thread-12 & & & Term-00, Transaction Count = 1132770$ cat benchmarksql-4.1.0_pg03/run/log/benchmarksql.log&&INFO & [ 17:54:04.955] & & & Thread-12 & & & Term-00, Measured tpmC (NewOrders) = 45336.15&INFO & [ 17:54:04.955] & & & Thread-12 & & & Term-00, Measured tpmTOTAL = &INFO & [ 17:54:04.956] & & & Thread-12 & & & Term-00, Session Start & & =
17:44:04&INFO & [ 17:54:04.956] & & & Thread-12 & & & Term-00, Session End & & & =
17:54:04&INFO & [ 17:54:04.956] & & & Thread-12 & & & Term-00, Transaction Count = 1132537$ cat benchmarksql-4.1.0_pg04/run/log/benchmarksql.log&&INFO & [ 17:54:04.986] & & & Thread-23 & & & Term-00, Measured tpmC (NewOrders) = 45231.67&INFO & [ 17:54:04.987] & & & Thread-23 & & & Term-00, Measured tpmTOTAL = &INFO & [ 17:54:04.987] & & & Thread-23 & & & Term-00, Session Start & & =
17:44:04&INFO & [ 17:54:04.987] & & & Thread-23 & & & Term-00, Session End & & & =
17:54:04&INFO & [ 17:54:04.987] & & & Thread-23 & & & Term-00, Transaction Count = 1130640TPM ：& +& +& +& = &第9分钟操作系统统计信息TOPtop - 17:38:27 up 4 days, &8:32, &4 users, &load average: 78.54, 68.64, 37.22Tasks: 658 total, &34 running, 624 sleeping, & 0 stopped, & 0 zombieCpu(s): 70.2%us, 15.7%sy, &0.0%ni, &5.5%id, &1.5%wa, &0.0%hi, &7.1%si, &0.0%stMem: &k total, k used, k free, & &59652k buffersSwap: k total, & & & &0k used, k free, k cachediostat -xavg-cpu: &%user & %nice %system %iowait &%steal & %idle& & & & & 71.39 & &0.00 & 22.47 & &1.26 & &0.00 & &4.88Device: & & & & rrqm/s & wrqm/s & & r/s & & w/s & rsec/s & wsec/s avgrq-sz avgqu-sz & await &svctm &%utildfa & & & & & & & 0.00 & & 0.00 8.67 050.67 & &15.99 & & 5.85 & &0.55 & 0.06 &68.17dfb & & & & & & & 0.00 & & 0.00 8.67 173.33 & &15.99 & & 5.98 & &0.56 & 0.06 &67.87dfc & & & & & & & 0.00 & & 0.00 4.33 504.00 & &15.99 & & 5.63 & &0.52 & 0.07 &71.60dm-0 & & & & & & &0.00 & & 0.00 70.33
& &15.99 & &17.60 & &0.55 & 0.03 &92.10测试过程oprofile报告#/home/digoal/oprof/bin/opreport -l -f -w -x -t 0.5Using /soft/digoal/soft_bak/oprof_test/oprofile_data/samples/ for samples directory.WARNING! Some of the events were throttled. Throttling occurs whenthe initial sample rate is too high, causing an excessive number ofinterrupts. &Decrease the sampling frequency. Check the directory/soft/digoal/soft_bak/oprof_test/oprofile_data/samples/current/stats/throttledfor the throttled event names.CPU: Intel Ivy Bridge microarchitecture, speed 2600 MHz (estimated)Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (No unit mask) count 100000vma & & &samples &% & & & &app name & & & & & & & & symbol name007a & 5.2511 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres hash_search_with_hash_value004a92f0 1895924 & 3.7816 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres _bt_compare 1844371 & 3.6787 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres GetSnapshotData5031 & 3.5404 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres SearchCatCache006a4bd0 1725350 & 3.4413 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres LWLockAcquire007bc3a0 1565190 & 3.1219 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres AllocSetAlloc6694 & 2.8058 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres heap_hot_search_buffer005b8f70 965646 & &1.9261 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres ExecInitExpr 767078 & &1.5300 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres PinBuffer004aaa80 617741 & &1.2321 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres _bt_checkkeys007a & &1.1729 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres FunctionCall2Coll006a & &1.1486 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres LWLockRelease007ac620 485162 & &0.9677 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres pg_encoding_mbcliplen007a & &0.9396 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres fmgr_info_cxt_security548 & &0.8807 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres slot_deform_tuple 425867 & &0.8494 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres hash_any006b2e50 404548 & &0.8069 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres PostgresMain007bd0f0 396510 & &0.7909 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres palloc0049bce0 394201 & &0.7863 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres heap_page_prune_opt007bce00 353243 & &0.7046 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres pfree896 & &0.6700 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres heap_page_prune145 & &0.6246 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres heap_getsysattr006b14a0 311776 & &0.6219 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres exec_bind_message007cb070 292106 & &0.5826 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres HeapTupleSatisfiesMVCC007bd210 275282 & &0.5491 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres MemoryContextAllocZeroAligned005b & &0.5449 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres ExecProject0495 & &0.5315 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres heap_update007bca10 265556 & &0.5297 &/soft/digoal/soft_bak/pgsql9.5/bin/postgres AllocSetFree第二阶段Oracle 12.1.0.2.0和PostgreSQL 9.5.0 TPM 对比------------------------------------------------------------------------------------------------------------阶段3,开启PostgreSQL&预读, -- (n-1), n是条带数, 所以本例case effective_io_concurrency = 2大页支持, &开到168G; &/etc/sysctl.confvm.nr_hugepages = 102352sysctl -p/etc/security/limits.conf* & soft & memlock & &* & hard & memlock & &memlock & &大于 &nr_hugepages& &大于 &shared_buffers&分组提交,&commit_delay = 10,&commit_siblings = 16：shared_buffers = 164GB & & & & & & & & & # min 128kBhuge_pages = on & & & & & & & & & & & # on, off, or trymaintenance_work_mem = 2GB & & & & & & &# min 1MBwal_buffers = 16MB & & & & & & & & & & & # min 32kB, -1 sets based on shared_bufferswal_writer_delay = 10ms & & & & # 1-10000 millisecondscommit_delay = 10 & & & & & & & & & & & # range 0-100000, in microsecondscommit_siblings = 16 & & & & & & & & & &# range 1-1000checkpoint_timeout = 35min & & & & & & &# range 30s-1hmax_wal_size = 320GBcheckpoint_completion_target = 0.8 & & # checkpoint target duration, 0.0 - 1.0effective_cache_size = 240GBlog_destination = 'csvlog' & & & & & & &# Valid values are combinations oflogging_collector = on & & & & &# Enable capturing of stderr and csvloglog_truncate_on_rotation = on & & & & & # If on, an existing log file with the测试结果：$tail -n 5 benchmarksql-4.1.0_pg01/run/log/benchmarksql.log&&INFO & [ 13:33:55.917] & & & Thread-14 & & & Term-00, Measured tpmC (NewOrders) = 48151.07&INFO & [ 13:33:55.917] & & & Thread-14 & & & Term-00, Measured tpmTOTAL = &INFO & [ 13:33:55.917] & & & Thread-14 & & & Term-00, Session Start & & =
13:23:55&INFO & [ 13:33:55.917] & & & Thread-14 & & & Term-00, Session End & & & =
13:33:55&INFO & [ 13:33:55.917] & & & Thread-14 & & & Term-00, Transaction Count = 1202222$tail -n 5 benchmarksql-4.1.0_pg02/run/log/benchmarksql.log&&INFO & [ 13:33:55.971] & & & Thread-16 & & & Term-00, Measured tpmC (NewOrders) = 48505.54&INFO & [ 13:33:55.971] & & & Thread-16 & & & Term-00, Measured tpmTOTAL = &INFO & [ 13:33:55.971] & & & Thread-16 & & & Term-00, Session Start & & =
13:23:55&INFO & [ 13:33:55.972] & & & Thread-16 & & & Term-00, Session End & & & =
13:33:55&INFO & [ 13:33:55.972] & & & Thread-16 & & & Term-00, Transaction Count = 1211858$tail -n 5 benchmarksql-4.1.0_pg03/run/log/benchmarksql.log&&INFO & [ 13:33:55.985] & & & Thread-4 & & & &Term-00, Measured tpmC (NewOrders) = 48119.61&INFO & [ 13:33:55.985] & & & Thread-4 & & & &Term-00, Measured tpmTOTAL = &INFO & [ 13:33:55.985] & & & Thread-4 & & & &Term-00, Session Start & & =
13:23:55&INFO & [ 13:33:55.985] & & & Thread-4 & & & &Term-00, Session End & & & =
13:33:55&INFO & [ 13:33:55.985] & & & Thread-4 & & & &Term-00, Transaction Count = 1205271$tail -n 5 benchmarksql-4.1.0_pg04/run/log/benchmarksql.log&&INFO & [ 13:33:55.958] & & & Thread-21 & & & Term-00, Measured tpmC (NewOrders) = 48087.55&INFO & [ 13:33:55.958] & & & Thread-21 & & & Term-00, Measured tpmTOTAL = &INFO & [ 13:33:55.958] & & & Thread-21 & & & Term-00, Session Start & & =
13:23:55&INFO & [ 13:33:55.958] & & & Thread-21 & & & Term-00, Session End & & & =
13:33:55&INFO & [ 13:33:55.958] & & & Thread-21 & & & Term-00, Transaction Count = 1204638TPM: +& +& +& =&------------------------------------------------------------------------------------------------------------阶段4，优化分组提交的时延，最小结束点并发事务数量，work_mem等。操作系统优化，优化老化脏页刷新间隔vm.dirty_writeback_centisecs=10优化老化脏页阈值vm.dirty_expire_centisecs=6000优化用户进程刷脏页阈值vm.dirty_ratio=80优化内核进程刷脏页阈值vm.dirty_background_bytes=优化终端数，每个benchmarksql 20个终端，一共80个终端。psql\c test01 test01explain analyze select * explain analyze select *\c test02 test02explain analyze select * explain analyze select *\c test03 test03explain analyze select * explain analyze select *\c test04 test04explain analyze select * explain analyze select *listen_addresses = '0.0.0.0'
# what IP address(es)port = 1921
# (change requires restart)max_connections = 300
# (change requires restart)unix_socket_directories = '.'
# comma-separated list of directoriesshared_buffers = 164GB
# min 128kBhuge_pages = on
# on, off, or trywork_mem = 256MB
# min 64kBmaintenance_work_mem = 2GB
# min 1MBautovacuum_work_mem = 2GB
# min 1MB, or -1 to use maintenance_work_memdynamic_shared_memory_type = mmap
# the default is the first optionvacuum_cost_delay = 10
# 0-100 millisecondsvacuum_cost_limit = 10000
# 1-10000 creditsbgwriter_delay = 10ms
# 10-10000ms between roundsbgwriter_lru_maxpages = 1000
# 0-1000 max buffers written/roundbgwriter_lru_multiplier = 10.0
# 0-10.0 multipler on buffers scanned/roundeffective_io_concurrency = 2
# 1-1000; 0 disables prefetchingwal_level = minimal
# minimal, archive, hot_standby, or logicalsynchronous_commit = off
# sfull_page_writes = off
# recover from partial page writeswal_buffers = 1GB
# min 32kB, -1 sets based on shared_bufferswal_writer_delay = 10ms
# 1-10000 millisecondscommit_delay = 10
# range 0-100000, in microsecondscommit_siblings = 6
# range 1-1000checkpoint_timeout = 55min
# range 30s-1hmax_wal_size = 320GBcheckpoint_completion_target = 0.99
# checkpoint target duration, 0.0 - 1.0random_page_cost = 1.0
# same scale as aboveeffective_cache_size = 240GBlog_destination = 'csvlog'
# Valid values are combinations oflogging_collector = on
# Enable capturing of stderr and csvloglog_truncate_on_rotation = on
# If on, an existing log file with thelog_timezone = 'PRC'update_process_title = offtrack_activities = offautovacuum = on
# Enable autovacuum subprocess?
'on'pg_ctl restart测试结果$ tail -n 5 benchmarksql-4.1.0_pg01/run/log/benchmarksql.log
[ 11:55:09.461]
Term-00, Measured tpmC (NewOrders) = 57995.55 INFO
[ 11:55:09.461]
Term-00, Measured tpmTOTAL =
[ 11:55:09.461]
Term-00, Session Start
11:45:09 INFO
[ 11:55:09.461]
Term-00, Session End
11:55:09 INFO
[ 11:55:09.462]
Term-00, Transaction Count = 1449796$ tail -n 5 benchmarksql-4.1.0_pg02/run/log/benchmarksql.log
[ 11:55:09.499]
Term-00, Measured tpmC (NewOrders) = 58013.75 INFO
[ 11:55:09.499]
Term-00, Measured tpmTOTAL =
[ 11:55:09.499]
Term-00, Session Start
11:45:09 INFO
[ 11:55:09.500]
Term-00, Session End
11:55:09 INFO
[ 11:55:09.500]
Term-00, Transaction Count = 1450110$ tail -n 5 benchmarksql-4.1.0_pg03/run/log/benchmarksql.log
[ 11:55:09.541]
Term-00, Measured tpmC (NewOrders) = 57322.05 INFO
[ 11:55:09.541]
Term-00, Measured tpmTOTAL =
[ 11:55:09.542]
Term-00, Session Start
11:45:09 INFO
[ 11:55:09.542]
Term-00, Session End
11:55:09 INFO
[ 11:55:09.542]
Term-00, Transaction Count = 1432298$ tail -n 5 benchmarksql-4.1.0_pg04/run/log/benchmarksql.log
[ 11:55:09.574]
Term-00, Measured tpmC (NewOrders) = 57863.92 INFO
[ 11:55:09.574]
Term-00, Measured tpmTOTAL =
[ 11:55:09.575]
Term-00, Session Start
11:45:09 INFO
[ 11:55:09.575]
Term-00, Session End
11:55:09 INFO
[ 11:55:09.575]
Term-00, Transaction Count = 1445978TPM：&+&&+&&+&&=&------------------------------------------------------------------------------------------------------------阶段5，编译器版本INTEL编译器CLANG编译器$ export LD_LIBRARY_PATH=/u02/digoal/gcc4.9.3/lib:/u02/digoal/cloog/lib:/u02/digoal/gmp/lib:/u02/digoal/isl/lib:/u02/digoal/mpc/lib:/u02/digoal/mpfr/lib:$LD_LIBRARY_PATH$ export PATH=/u02/digoal/gcc4.9.3/bin:$PATH$ CFLAGS="-O3 -march=native -flto" CC=/u02/digoal/gcc4.9.3/bin/gcc ./configure --prefix=/u02/digoal/soft_bak/pgsql9.5 --with-blocksize=8 --with-pgport=1921 --with-perl --with-python --with-tcl --with-openssl --with-pam --with-ldap --with-libxml --with-libxslt --enable-thread-safety --with-wal-segsize=64$ make world -j 32$ make install-world -j 32$ pg_config&测试结果digoal tail -n 5 benchmarksql-4.1.0_pg01/run/log/benchmarksql.log&&INFO & [ 02:00:49.699] & & & Thread-15 & & & Term-00, Measured tpmC (NewOrders) = 59092.33&INFO & [ 02:00:49.699] & & & Thread-15 & & & Term-00, Measured tpmTOTAL = &INFO & [ 02:00:49.699] & & & Thread-15 & & & Term-00, Session Start & & =
01:50:49&INFO & [ 02:00:49.699] & & & Thread-15 & & & Term-00, Session End & & & =
02:00:49&INFO & [ 02:00:49.699] & & & Thread-15 & & & Term-00, Transaction Count = 1478385digoal tail -n 5 benchmarksql-4.1.0_pg02/run/log/benchmarksql.log&&INFO & [ 02:00:49.704] & & & Thread-0 & & & &Term-00, Measured tpmC (NewOrders) = 60051.49&INFO & [ 02:00:49.704] & & & Thread-0 & & & &Term-00, Measured tpmTOTAL = &INFO & [ 02:00:49.704] & & & Thread-0 & & & &Term-00, Session Start & & =
01:50:49&INFO & [ 02:00:49.704] & & & Thread-0 & & & &Term-00, Session End & & & =
02:00:49&INFO & [ 02:00:49.704] & & & Thread-0 & & & &Term-00, Transaction Count = 1502367digoal tail -n 5 benchmarksql-4.1.0_pg03/run/log/benchmarksql.log&&INFO & [ 02:00:49.693] & & & Thread-16 & & & Term-00, Measured tpmC (NewOrders) = 60273.99&INFO & [ 02:00:49.694] & & & Thread-16 & & & Term-00, Measured tpmTOTAL = &INFO & [ 02:00:49.694] & & & Thread-16 & & & Term-00, Session Start & & =
01:50:49&INFO & [ 02:00:49.694] & & & Thread-16 & & & Term-00, Session End & & & =
02:00:49&INFO & [ 02:00:49.694] & & & Thread-16 & & & Term-00, Transaction Count = 1506066digoal tail -n 5 benchmarksql-4.1.0_pg04/run/log/benchmarksql.log&&INFO & [ 02:00:49.715] & & & Thread-18 & & & Term-00, Measured tpmC (NewOrders) = 60180.69&INFO & [ 02:00:49.715] & & & Thread-18 & & & Term-00, Measured tpmTOTAL = &INFO & [ 02:00:49.716] & & & Thread-18 & & & Term-00, Session Start & & =
01:50:49&INFO & [ 02:00:49.716] & & & Thread-18 & & & Term-00, Session End & & & =
02:00:49&INFO & [ 02:00:49.716] & & & Thread-18 & & & Term-00, Transaction Count = 1505962TPM------------------------------------------------------------------------------------------------------------CLANG编译CC=/u02/digoal/llvm/bin/clang CFLAGS="-O2 -fstrict-enums" ./configure --prefix=/u02/digoal/soft_bak/pgsql9.5 &--with-pgport=1921 --with-perl --with-python --with-tcl --with-openssl --with-pam --with-ldap --with-libxml --with-libxslt --enable-thread-safetymake world -j 32make install-world -j 32压测结果$ tail -n 5 benchmarksql-4.1.0_pg01/run/log/benchmarksql.log&&INFO & [ 07:21:58.070] & & & Thread-12 & & & Term-00, Measured tpmC (NewOrders) = 60519.19&INFO & [ 07:21:58.070] & & & Thread-12 & & & Term-00, Measured tpmTOTAL = &INFO & [ 07:21:58.070] & & & Thread-12 & & & Term-00, Session Start & & =
07:11:58&INFO & [ 07:21:58.071] & & & Thread-12 & & & Term-00, Session End & & & =
07:21:58&INFO & [ 07:21:58.071] & & & Thread-12 & & & Term-00, Transaction Count = 1512377$ tail -n 5 benchmarksql-4.1.0_pg02/run/log/benchmarksql.log&&INFO & [ 07:21:58.180] & & & Thread-15 & & & Term-00, Measured tpmC (NewOrders) = 60924.87&INFO & [ 07:21:58.180] & & & Thread-15 & & & Term-00, Measured tpmTOTAL = &INFO & [ 07:21:58.180] & & & Thread-15 & & & Term-00, Session Start & & =
07:11:58&INFO & [ 07:21:58.180] & & & Thread-15 & & & Term-00, Session End & & & =
07:21:58&INFO & [ 07:21:58.180] & & & Thread-15 & & & Term-00, Transaction Count = 1521312$ tail -n 5 benchmarksql-4.1.0_pg03/run/log/benchmarksql.log&&INFO & [ 07:21:58.198] & & & Thread-0 & & & &Term-00, Measured tpmC (NewOrders) = 60481.19&INFO & [ 07:21:58.198] & & & Thread-0 & & & &Term-00, Measured tpmTOTAL = &INFO & [ 07:21:58.199] & & & Thread-0 & & & &Term-00, Session Start & & =
07:11:58&INFO & [ 07:21:58.199] & & & Thread-0 & & & &Term-00, Session End & & & =
07:21:58&INFO & [ 07:21:58.199] & & & Thread-0 & & & &Term-00, Transaction Count = 1512968$ tail -n 5 benchmarksql-4.1.0_pg04/run/log/benchmarksql.log&&INFO & [ 07:21:58.200] & & & Thread-5 & & & &Term-00, Measured tpmC (NewOrders) = 60715.57&INFO & [ 07:21:58.200] & & & Thread-5 & & & &Term-00, Measured tpmTOTAL = &INFO & [ 07:21:58.200] & & & Thread-5 & & & &Term-00, Session Start & & =
07:11:58&INFO & [ 07:21:58.200] & & & Thread-5 & & & &Term-00, Session End & & & =
07:21:58&INFO & [ 07:21:58.200] & & & Thread-5 & & & &Term-00, Transaction Count = 1518149TPM:当前perf top& & & & & & &samples &pcnt function & & & & & & & & & & & & &DSO& & & & & & &_______ _____ _________________________________ __________________________________________& & & & & & .2% hash_search_with_hash_value & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & .8% _bt_compare & & & & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & .6% AllocSetAlloc & & & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & .1% LWLockAcquire & & & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & .1% memcpy & & & & & & & & & & & & & &/lib64/libc-2.12.so & & & & & & & & & & &&& & & & & & &.8% SearchCatCache & & & & & & & & & &/u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.7% heap_hot_search_buffer & & & & & &/u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.6% GetSnapshotData & & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.4% ExecInitExpr & & & & & & & & & & &/u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.3% fmgr_info_cxt_security & & & & & &/u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.0% PostgresMain & & & & & & & & & & &/u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.9% LWLockRelease & & & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.8% PinBuffer & & & & & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.8% _int_malloc & & & & & & & & & & & /lib64/libc-2.12.so & & & & & & & & & & &&& & & & & & &.8% StrategyGetBuffer & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.8% slot_deform_tuple & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.7% FunctionCall2Coll & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.7% __GI_vfprintf & & & & & & & & & & /lib64/libc-2.12.so & & & & & & & & & & &&& & & & & & &.7% __strncpy_ssse3 & & & & & & & & & /lib64/libc-2.12.so & & & & & & & & & & &&& & & & & & &.7% aliflash_reconfig_task & & & & & &[aliflash] & & & & & & & & & & & & & & & && & & & & & &.6% _bt_checkkeys & & & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.6% __memset_sse2 & & & & & & & & & & /lib64/libc-2.12.so & & & & & & & & & & &&& & & & & & &.6% palloc & & & & & & & & & & & & & &/u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.5% __strlen_sse42 & & & & & & & & & &/lib64/libc-2.12.so & & & & & & & & & & &&& & & & & & &.5% _int_free & & & & & & & & & & & & /lib64/libc-2.12.so & & & & & & & & & & &&& & & & & & &.5% heap_page_prune & & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.5% hash_any & & & & & & & & & & & & &/u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.5% heap_page_prune_opt & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.5% __schedule & & & & & & & & & & & &[kernel.kallsyms] & & & & & & & & & & & &&& & & & & & &.4% MemoryContextAllocZeroAligned & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.4% pfree & & & & & & & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.4% heap_update & & & & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.4% update_blocked_averages & & & & & [kernel.kallsyms] & & & & & & & & & & & &&& & & & & & &.4% __switch_to & & & & & & & & & & & [kernel.kallsyms] & & & & & & & & & & & &&& & & & & & &.4% heap_getsysattr & & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.4% TupleDescInitEntry & & & & & & & &/u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.4% irq_entries_start & & & & & & & & [kernel.kallsyms] & & & & & & & & & & & &&& & & & & & &.4% AllocSetFree & & & & & & & & & & &/u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.4% _wordcopy_bwd_aligned & & & & & & /lib64/libc-2.12.so & & & & & & & & & & &&& & & & & & &.4% _raw_spin_lock & & & & & & & & & &[kernel.kallsyms] & & & & & & & & & & & &&& & & & & & &.4% check_stack_depth & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.3% _bt_binsrch & & & & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.3% ReadBuffer_common & & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.3% expression_tree_walker & & & & & &/u02/digoal/soft_bak/pgsql9.5/bin/postgres& & & & & & &.3% __GI___libc_malloc & & & & & & & &/lib64/libc-2.12.so & & & & & & & & & & &&& & & & & & &.3% __memcmp_sse4_1 & & & & & & & & & /lib64/libc-2.12.so & & & & & & & & & & &&& & & & & & &.3% LockAcquireExtended & & & & & & & /u02/digoal/soft_bak/pgsql9.5/bin/postgres------------------------------------------------------------------------------------------------------------极限性能参数vm.swappiness = 0kernel.shmmax=net.core.rmem_max = 4194304net.core.wmem_max = 4194304net.core.rmem_default = 262144net.core.wmem_default = 262144net.ipv4.ip_local_port_range = kernel.sem = 00 vm.dirty_background_bytes = vm.dirty_ratio = 80vm.nr_hugepages = 102352kernel /vmlinuz-3.18.24 numa=off elevator=deadline intel_idle.max_cstate=0 scsi_mod.scan=syncblockdev --setra 16384 /dev/dfablockdev --setra 16384 /dev/dfbblockdev --setra 16384 /dev/dfcblockdev --setra 16384 /dev/dm-0关闭analyze, 关闭某些日志表的autovacuum.max_connections = 300 & & & & & & & & & # (change requires restart)unix_socket_directories = '.' & # comma-separated list of directoriesshared_buffers = 194GB & & & & & & & & & # min 128kBhuge_pages = on & & & & & & & & & & & # on, off, or trywork_mem = 256MB & & & & & & & & & & & & # min 64kBmaintenance_work_mem = 2GB & & & & & & &# min 1MBautovacuum_work_mem = 2GB & & & & & & & # min 1MB, or -1 to use maintenance_work_memdynamic_shared_memory_type = mmap & & &# the default is the first optionvacuum_cost_delay = 0 & & & & & & & & &# 0-100 millisecondsbgwriter_delay = 10ms & & & & & & & & & # 10-10000ms between roundsbgwriter_lru_maxpages = 1000 & & & & & &# 0-1000 max buffers written/roundbgwriter_lru_multiplier = 10.0 & & & & &# 0-10.0 multipler on buffers scanned/roundeffective_io_concurrency = 2 & & & & & # 1-1000; 0 disables prefetchingwal_level = minimal &# minimal, archive, hot_standby, or logicalsynchronous_commit = off & & & & & & & &# swal_sync_method = open_sync & & & & & & & &# the default is the first optionfull_page_writes = off & & & & & & & & &# recover from partial page writeswal_buffers = 1GB & & & & & & & & & & & # min 32kB, -1 sets based on shared_bufferswal_writer_delay = 10ms & & & & # 1-10000 millisecondscommit_delay = 20 & & & & & & & & & & & # range 0-100000, in microsecondscommit_siblings = 9 & & & & & & & & & &# range 1-1000checkpoint_timeout = 55min & & & & & & &# range 30s-1hmax_wal_size = 320GBcheckpoint_completion_target = 0.99 & & # checkpoint target duration, 0.0 - 1.0random_page_cost = 1.0 & & & & & & & & # same scale as aboveeffective_cache_size = 240GBlog_destination = 'csvlog' & & & & & & &# Valid values are combinations oflogging_collector = on & & & & &# Enable capturing of stderr and csvloglog_truncate_on_rotation = on & & & & & # If on, an existing log file with theupdate_process_title = offtrack_activities = offautovacuum = on & & & & & & & & & & & &# Enable autovacuum subprocess? &'on'autovacuum_max_workers = 4 & & & & & & # max number of autovacuum subprocesses &autovacuum_naptime = 6s & & & & & & &# time between autovacuum runsautovacuum_vacuum_cost_delay = 0 & &# default vacuum cost delay for[其他优化手段]1. PostgreSQL jdbc有一些参数可以优化，本文还未处理。例如防止类型转换，QUERY plan CACHE size。2. PostgreSQL 代码层也有优化的空间，例如分区表的代码，快照的优化。[特别声明]1. 本文纯属技术交流，测试数据不具备任何指导意义。[参考]1.&
阅读(4418)|
用微信&&“扫一扫”
将文章分享到朋友圈。
用易信&&“扫一扫”
将文章分享到朋友圈。
历史上的今天
在LOFTER的更多文章
loftPermalink:'',
id:'fks_',
blogTitle:'BenchmarkSQL 测试PostgreSQL 9.5.0 TPC-C 性能',
blogAbstract:'Oracle 12c TPC-C 测试请参考：
{if x.moveFrom=='wap'}
{elseif x.moveFrom=='iphone'}
{elseif x.moveFrom=='android'}
{elseif x.moveFrom=='mobile'}
${a.selfIntro|escape}{if great260}${suplement}{/if}
{list a as x}
推荐过这篇日志的人：
{list a as x}
{if !!b&&b.length>0}
他们还推荐了：
{list b as y}
转载记录：
{list d as x}
{list a as x}
{list a as x}
{list a as x}
{list a as x}
{if x_index>4}{break}{/if}
${fn2(x.publishTime,'yyyy-MM-dd HH:mm:ss')}
{list a as x}
{if !!(blogDetail.preBlogPermalink)}
{if !!(blogDetail.nextBlogPermalink)}
{list a as x}
{if defined('newslist')&&newslist.length>0}
{list newslist as x}
{if x_index>7}{break}{/if}
{list a as x}
{var first_option =}
{list x.voteDetailList as voteToOption}
{if voteToOption==1}
{if first_option==false},{/if}&&“${b[voteToOption_index]}”&&
{if (x.role!="-1") },“我是${c[x.role]}”&&{/if}
&&&&&&&&${fn1(x.voteTime)}
{if x.userName==''}{/if}
网易公司版权所有&&
{list x.l as y}
{if defined('wl')}
{list wl as x}{/list}}
我要回帖
更多推荐
- ·老人弥留之际遭后人故意伤害老人如何定罪可范法
- ·属蛇2024年最旺的微信头像免费，快来领取属于你的生肖2024年最旺的微信头像免费🐍
- ·菠萝啤是什么做的可不可以和鸡精一起做菜吃？
- ·严清和包头市音乐家协会什么级别名誉主席严清和
- ·请问这是哪部电视怎么搜索想看的电视剧剧里面的？
- ·按照史记世家本纪列传的本纪，世家，列传对球员进行排名，该怎么排
- ·天天跑步，今天左腿膝盖疼痛，是不是该停止，修养一下？
- ·入手捷安特山地车atx850ATX700，大家都用什么样的山地车
- ·右手小臂酸痛，握拳时右手无名指握拳僵硬不紧是怎么回事
- ·印象流，为什么科比不喜欢喊挡拆
- ·市区深圳哪里学游泳好好
- ·为什么其他球员都显老，就加内特显示器看着模糊还是那
- ·腓骨外侧的肌肉叫什么肌肉肿胀，三个多月不疼不痒，怎么回事
- ·泰拳练习注意事项有哪些
- ·新体育中心篮球场馆篮球场现在还可以进去打球吗 收
- ·手腕疼，滑旱冰鞋技巧摔得好多天了怎么办
- ·0 5 7 9 5 7 .b b a a 8 c o mm/广东卫视《足球 比分火》什么时候播出 足球 比分火主持人是谁
- ·男篮周琦是哪里人自称是同性恋是真的吗
- ·来说说我刚看nba时候和现今nba的不同
- ·如果雷霆三少如果不分开四少再聚首，能夺冠军吗
- ·0 5 9 2 9 5 . c o m/女生怎么看懂足球比分188 比分？
- ·跳绳突然头疼是不是减轻头痛？
- ·求图片中nike鞋子型号型号？谢谢
- ·奥运会英文介绍游艇这个项目用英文介绍
- ·在SD球员里，谁是最好的球队领袖
- ·大家觉不觉得KIMI和曼联的ALAN长得很相
- ·如何选购滑雪板选购
- ·0 5 9 3 0 6 .172a a c o mm/关于足球 比分的歌曲
- ·13岁男孩练跆拳道散打空手道哪个厉害.空手道.散打，哪个好
- ·有摇单羽毛球单打双打界限场的师傅吗
- ·最强大脑七季时间表四季国际赛有魔方比赛吗
- ·1.72要怎样练怎么才能灌篮篮
- ·最喜欢的电脑上怎么看屏线是多少P的P t体育运动是什么，情况统计图，喜欢打篮球的
- ·大家心中的亚洲欧洲足球五大联赛赛 都是哪些
- ·fgo里什么是fgo红卡蓝卡绿卡区别队，蓝卡队，绿卡队？怎么分辨从者是什么卡？
- ·体育项目大全水有关
