test

PCIE performance test — LSI vs FusionIO vs VIRI

April 2, 2013 Architect, hardware 2 comments

Testing three PCIE cards’s performance for all of scenes using fio

Reference:fio parameter setting

MySQL key partition and MongoDB TEST

March 8, 2013 mongodb, MYSQL, NoSQL, performance No comments

对于业务的激活码需求做了一次关于mysql,mongodb的比对.mysql分为normal,key partition 数量分别是1亿和10亿数据,mysql采用直接访问PK键,partition key为PK,mysql table size 为90G,mongodb table size为157G。

[liuyang@yhdem ~]$ cat /proc/cpuinfo  |grep processor |wc -l
24

[liuyang@yhdem ~]$ cat /etc/issue
Oracle Linux Server release 5.8
Kernel \r on an \m

mysql evn:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.25a   | 
+-----------+
1 row in set (0.00 sec)
      
      log_bin[OFF] innodb_flush_log_at_trx_commit [2]  query_cache_type[OFF]
      max_connect_errors[10] max_connections[214] max_user_connections[0] 
      sync_binlog[0] table_definition_cache[400] 
      table_open_cache[400] thread_cache_size[8]  open_files_limit[30000]
      innodb_adaptive_flushing[ON] innodb_adaptive_hash_index[ON] innodb_buffer_pool_size[30.234375G] 
      innodb_file_per_table[ON] innodb_flush_log_at_trx_commit[2] innodb_flush_method[] 
      innodb_io_capacity[200] innodb_lock_wait_timeout[100] innodb_log_buffer_size[128M] 
      innodb_log_file_size[200M] innodb_log_files_in_group[2] innodb_max_dirty_pages_pct[75] 
      innodb_open_files[1600] innodb_read_io_threads[4] innodb_thread_concurrency[0] 
      innodb_write_io_threads[4]

以下图片均为QPS统计,TPS测试暂时没有做

no partition table with one billion rows –> small random select by pk

mysql_test_1

xDiskName Busy  Read WriteKB|0          |25         |50          |75	   100|                                                                        
xsda        1%    2.0   35.9|>                                                |                                                                      
xsda1       0%    0.0    0.0|>                                                |                                                                      
xsda2       0%    0.0    0.0|>                                                |                                                                      
xsda3       0%    0.0    0.0|>                                                |                                                                      
xsda4       0%    0.0    0.0|>disk busy not available                         |                                                                      
xsda5       0%    0.0    0.0|>                                                |                                                                      
xsda6       1%    2.0   35.9|>                                                |                                                                      
xsdb        0%    0.0   55.9|>                                                |                                                                      
xsdb1       0%    0.0   55.9|>                                                |                                                                      
xTotals Read-MB/s=0.0      Writes-MB/s=0.2      Transfers/sec=18.0 

partition table with one billion rows –> small random select by pk

mysql_test_2

xDiskName Busy  Read WriteKB|0          |25         |50          |75	   100|                                                                       
xsda        0%    0.0    8.0|>                                                |                                                                     
xsda1       0%    0.0    0.0|>                                                |                                                                     
xsda2       0%    0.0    8.0|>                                                |                                                                     
xsda3       0%    0.0    0.0|>                                                |                                                                     
xsda4       0%    0.0    0.0|>disk busy not available                         |                                                                     
xsda5       0%    0.0    0.0|>                                                |                                                                     
xsda6       0%    0.0    0.0|>                                                |                                                                     
xsdb        0%    0.0  201.5|                         >                       |                                                                     
xsdb1       0%    0.0  201.5|W                        >                       |                                                                     
xTotals Read-MB/s=0.0      Writes-MB/s=0.4      Transfers/sec=46.9             

no partition table with one billion rows –> full random select by pk

mysql_test_3

xDiskName Busy  Read WriteMB|0          |25         |50          |75	   100|                                                                        
xsda        0%    0.0    0.0| >                                               |                                                                      
xsda1       0%    0.0    0.0|>                                                |                                                                      
xsda2       0%    0.0    0.0|>                                                |                                                                      
xsda3       0%    0.0    0.0|>                                                |                                                                      
xsda4       0%    0.0    0.0|>disk busy not available                         |                                                                      
xsda5       0%    0.0    0.0|>                                                |                                                                      
xsda6       0%    0.0    0.0| >                                               |                                                                      
xsdb      100%   86.8    0.2|RRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRR>                                                                      
xsdb1     100%   86.8    0.2|RRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRR>                                                                      
xTotals Read-MB/s=173.6    Writes-MB/s=0.4      Transfers/sec=6448.1    

partition table with one billion rows –> full random select by pk

mysql_test_4

xDiskName Busy  Read WriteMB|0          |25         |50          |75	   100|                                                                        
xsda        0%    0.0    0.0| >                                               |                                                                      
xsda1       0%    0.0    0.0|>                                                |                                                                      
xsda2       0%    0.0    0.0| >                                               |                                                                      
xsda3       0%    0.0    0.0|>                                                |                                                                      
xsda4       0%    0.0    0.0|>disk busy not available                         |                                                                      
xsda5       0%    0.0    0.0|>                                                |                                                                      
xsda6       0%    0.0    0.0| >                                               |                                                                      
xsdb      100%   89.6    0.2|RRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRR>                                                                      
xsdb1     100%   89.6    0.2|                                                 >                                                                      
xTotals Read-MB/s=179.2    Writes-MB/s=0.3      Transfers/sec=6539.3        

no partition table with 100 million rows –> full random select by pk

mysql_test_5

下面基于mongodb的TEST.同样为10亿的表,157G.

[root@db-13 tmp]# mongo
MongoDB shell version: 2.0.8
connecting to: test
> db.foo.totalSize();
157875838416
> db.foo.find().count();
1000000000

——

第一次 使用128G 满额内存 16thread,10亿random query:

[root@db-13 tmp]# mongo test ./mongodb_benchmark_query.js 
MongoDB shell version: 2.0.8
connecting to: test
threads: 16      queries/sec: 126151.69666666667

第二次 使用128G 内存 24 thread,10亿中的前1亿数据random query:

[root@db-13 tmp]# mongo test ./mongodb_benchmark_query.js 
MongoDB shell version: 2.0.8
connecting to: test
threads: 24      queries/sec: 166527.42333333334

第三次 使用mysql用户启动mongo 限制mysql用户的mem为24G 24 thread , 10亿中的前1亿数据random query :

[mysql@db-13 ~]$ ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 1052672
max locked memory       (kbytes, -l) 26055452
max memory size         (kbytes, -m) 26055452
open files                      (-n) 131072
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) unlimited
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

[mysql@db-13 tmp]$ mongo test ./mongodb_benchmark_query.js 
MongoDB shell version: 2.0.8
connecting to: test
threads: 24	 queries/sec: 161358.03333333333

第四次 使用mysql用户启动mongo 限制mysql用户的mem为24G 24 thread , 10亿random query :

[mysql@db-13 tmp]$ mongo test ./mongodb_benchmark_query.js 
MongoDB shell version: 2.0.8
connecting to: test
threads: 24	 queries/sec: 2549.2 ----------------------> 这里出现了物理IO读写

—提供查询脚本

ops = [{op: "findOne", ns: "test.foo", query: {_id : { "#RAND_INT" : [ 1 , 100000000 ] } }}]       
x=24
 {
    res = benchRun( {
       parallel : x ,
        seconds : 60 ,
        ops : ops
    } );
    print( "threads: " + x + "\t queries/sec: " + res.query );
}

10亿 normal table 对于1亿 normal table 在内存基于PK的访问没有衰减,10亿的partition table 对于 10亿的 normal table 在内存中衰减了2/3,10亿的partition table对于10亿的 normal table 在full table out of memory 的情况下 性能有所提升 (另外注意激活码基本只会被访问1次)

对于mongodb来说,这种业务需求完全可以搞定,在内存充足的情况下QPS达到了16W+/s,但是在内存不足的情况下,暴跌至2549.

Oracle Bind Graduation 测试

November 28, 2012 Internals, oracle No comments

最近team 讨论 Bind Graduation 比较激烈,详细可以见上一篇文章,基于Bind Graduation 做了以下测试:

主要目的:

测试基于OCI JDBC 等接口的 Bind Graduation行为.针对目前Bind Graduation的行为,以及11.2.0.3出现的purge问题,由于bind graduation导致的child cursor过多问题,暂时没有好的solution(_cursor_obsolete_threshold ?).
建议对问题语句涉及到的表做水平拆分。

测试版本11.2.0.3

[oracle@testdb ~]$ ora si 

SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 28 16:39:30 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 
SQL> show user
USER is "SYS"
SQL> alter system flush shared_pool;

System altered.

1. sqlplus OCI

SQL> VARIABLE n NUMBER
VARIABLE v VARCHAR2(32)

EXECUTE :n := 1; :v := 'Helicon';

INSERT INTO t (n, v) VALUES (:n, :v);SQL> SQL> SQL> 
PL/SQL procedure successfully completed.

SQL> SQL> 

1 row created.

SQL> commit;

Commit complete.

SQL>  SELECT sql_id, child_number, executions
FROM v$sql
WHERE sql_text = 'INSERT INTO t (n, v) VALUES (:n, :v)';   2    3  

SQL_ID	      CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
6cvmu7dwnvxwj		 0	    1


SQL> VARIABLE v VARCHAR2(33)

EXECUTE :n := 4; :v := 'Terminus';

INSERT INTO t (n, v) VALUES (:n, :v);SQL> SQL> 
PL/SQL procedure successfully completed.

SQL> SQL> 

1 row created.

SQL> commit;

Commit complete.


SQL> VARIABLE v VARCHAR2(129)

EXECUTE :n := 4; :v := 'Terminus';

INSERT INTO t (n, v) VALUES (:n, :v);SQL> SQL> 
PL/SQL procedure successfully completed.

SQL> SQL> 

1 row created.

SQL> commit;

Commit complete.

SQL> /

SQL_ID	      CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
6cvmu7dwnvxwj		 0	    2
6cvmu7dwnvxwj		 1	    1                  -----------------129 产生第一个child cursor

SQL> SELECT s.child_number, m.position, m.max_length, 
       decode(m.datatype,1,'VARCHAR2',2,'NUMBER',m.datatype) aS datatype
FROM v$sql s, v$sql_bind_metadata m
WHERE s.sql_id = '&sql_id'
AND s.child_address = m.address
ORDER BY 1, 2;  2    3    4    5    6  
Enter value for sql_id: 6cvmu7dwnvxwj
old   4: WHERE s.sql_id = '&sql_id'
new   4: WHERE s.sql_id = '6cvmu7dwnvxwj'

CHILD_NUMBER   POSITION MAX_LENGTH DATATYPE
------------ ---------- ---------- ----------------------------------------
	   0	      1 	22 NUMBER
	   0	      2        128 VARCHAR2
	   1	      1 	22 NUMBER
	   1	      2       2000 VARCHAR2     
	   
丢失了 32这个区间

2 OCI pl/sqldeveloper 操作

SQL> VARIABLE n NUMBER
SQL> VARIABLE v VARCHAR2(32)
SQL> EXECUTE :n := 1; :v := 'Helicon';
 
PL/SQL procedure successfully completed
n
---------
1
v
---------
Helicon
SQL> INSERT INTO t (n, v) VALUES (:n, :v);
 
1 row inserted
n
---------
1
v
---------
Helicon
 
SQL> commit;
 
Commit complete
 
SQL>  SELECT sql_id,child_number, executions
FROM v$sql
WHERE sql_text = ' INSERT INTO t (n, v) VALUES (:n, :v) ';  2    3  

SQL_ID	      CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
fp1vwg5jfpk4b		 0	    1


SQL> VARIABLE v VARCHAR2(33)
SQL> EXECUTE :n := 4; :v := 'Terminus';
 
PL/SQL procedure successfully completed
n
---------
4
v
---------
Terminus
SQL> INSERT INTO t (n, v) VALUES (:n, :v);
 
1 row inserted
n
---------
4
v
---------
Terminus
 
SQL> commit;
 
Commit complete


SQL> /

SQL_ID	      CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
fp1vwg5jfpk4b		 0	    2

SQL> VARIABLE v VARCHAR2(129)
SQL> EXECUTE :n := 4; :v := 'Terminus';
 
PL/SQL procedure successfully completed
n
---------
4
v
---------
Terminus
SQL> INSERT INTO t (n, v) VALUES (:n, :v);
 
1 row inserted
n
---------
4
v
---------
Terminus
 
SQL> 
SQL> commit;
 
Commit complete


SQL> /

SQL_ID	      CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
fp1vwg5jfpk4b		 0	    3                  -------------------没有产生child cursor


SQL> SELECT s.child_number, m.position, m.max_length, 
       decode(m.datatype,1,'VARCHAR2',2,'NUMBER',m.datatype) aS datatype
FROM v$sql s, v$sql_bind_metadata m
WHERE s.sql_id = '&sql_id'
AND s.child_address = m.address
ORDER BY 1, 2;    2    3    4    5    6  
Enter value for sql_id: fp1vwg5jfpk4b
old   4: WHERE s.sql_id = '&sql_id'
new   4: WHERE s.sql_id = 'fp1vwg5jfpk4b'

CHILD_NUMBER   POSITION MAX_LENGTH DATATYPE
------------ ---------- ---------- ----------------------------------------
	   0	      1 	22 NUMBER
	   0	      2       4000 VARCHAR2

默认产生了4000的max值区间。

这个测试不具备任何意义,经过确认pl/sql developer经过了封装,导致oracle 端默认为4000的max区间。

3. OCI JAVA -(模拟真实环境)

代码如下:

        oracle_conn = DriverManager.getConnection("jdbc:oracle:oci:@xxx", "xxx", "xxx");
             
            oracle_stmt = oracle_conn.prepareStatement("insert into t values(?, ?)"); 
     
         oracle_stmt.setInt(1, 1);  
         oracle_stmt.setString(2, "Helicon"); 
         oracle_stmt.execute();
        
         oracle_stmt.setInt(1, 2);  
         oracle_stmt.setString(2, "Helicon33333333333333333333333333333"); 
         oracle_stmt.execute();
        
         oracle_stmt.setInt(1, 3);  
         oracle_stmt.setString(2, "Helicon3333333333333333333333333333323sssssssssssssssssssssssssssssssssssssssssssssssssssssssddddddddddddddddddddddddddddddddddssssssssssssssssssssssss"); 
         oracle_stmt.execute();
SQL> SELECT s.child_number, m.position, m.max_length,
  2         decode(m.datatype,1,'VARCHAR2',2,'NUMBER',m.datatype) aS datatype
  3  FROM v$sql s, v$sql_bind_metadata m
  4  WHERE s.sql_id = 'dw481sdb5fkkt'
  5  AND s.child_address = m.address
  6  ORDER BY 1, 2;
 
CHILD_NUMBER   POSITION MAX_LENGTH DATATYPE
------------ ---------- ---------- ----------------------------------------
           0          1         22 NUMBER
           0          2         32 VARCHAR2                  
           1          1         22 NUMBER
           1          2       2000 VARCHAR2                
 

    	SQL_TEXT 	SQL_ID 	EXECUTIONS 	LOADS 	FIRST_LOAD_TIME
 	insert into t values(:1, :2) 	dw481sdb5fkkt 	1 	1 	2012-11-28/20:30:05
	insert into t values(:1, :2) 	dw481sdb5fkkt 	2 	1 	2012-11-28/20:30:05

产生了32,2000的区间, 但是缺少了128的区间。

4 JDBC JAVA (目前使用的场景)

代码如下:

package com;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.driver.*;


public class test {  
  
    public static void main(String[] args) {  
        Connection oracle_conn = null;  
        PreparedStatement oracle_stmt = null;  
        ResultSet oracle_rs = null;  

                  
        try {  
            Class.forName("oracle.jdbc.driver.OracleDriver");  
            oracle.jdbc.driver.OracleDriver a;
            
            oracle_conn = DriverManager.getConnection("jdbc:oracle:thin:@xx.xx.xx.xx:xx:yy", "xx", "xx");  
              
            oracle_stmt = oracle_conn.prepareStatement("insert into t values(?, ?)");  
      
	        oracle_stmt.setInt(1, 1);   
	        oracle_stmt.setString(2, "Helicon");  
	        oracle_stmt.execute();
	        
	        oracle_stmt.setInt(1, 2);   
	        oracle_stmt.setString(2, "Helicon33333333333333333333333333333");  
	        oracle_stmt.execute();
	        
	        oracle_stmt.setInt(1, 3);   
	        oracle_stmt.setString(2, "Helicon3333333333333333333333333333323sssssssssssssssssssssssssssssssssssssssssssssssssssssssddddddddddddddddddddddddddddddddddssssssssssssssssssssssss");  
	        oracle_stmt.execute();		        

        } catch (ClassNotFoundException e) {  
            e.printStackTrace();  
        } catch (SQLException e) {  
            e.printStackTrace();  
        } finally {  
            try {  
                if(oracle_rs != null) {  
                    oracle_rs.close();  
                    oracle_rs = null;  
                }  
                  
                if(oracle_stmt != null) {  
                    oracle_stmt.close();  
                    oracle_stmt = null;  
                }  
                  
                if(oracle_conn != null) {  
                    oracle_conn.close();  
                    oracle_conn = null;  
                }    
            } catch (SQLException e) {  
                e.printStackTrace();  
            }  
        }  
    }  
  
}  
SQL> SELECT s.child_number, m.position, m.max_length,
  2         decode(m.datatype,1,'VARCHAR2',2,'NUMBER',m.datatype) aS datatype
  3  FROM v$sql s, v$sql_bind_metadata m
  4  WHERE s.sql_id = 'fw60v89km14c9'
  5  AND s.child_address = m.address
  6  ORDER BY 1, 2;
  
  


CHILD_NUMBER   POSITION MAX_LENGTH DATATYPE
------------ ---------- ---------- ----------------------------------------
           0          1         22 NUMBER
           0          2         32 VARCHAR2                  
           1          1         22 NUMBER
           1          2        128 VARCHAR2  
           2          1         22 NUMBER
           2          2        2000 VARCHAR2
           
           
    	SQL_TEXT 	SQL_ID 	EXECUTIONS 	LOADS 	FIRST_LOAD_TIME
 	insert into t values(:1, :2) 	fw60v89km14c9 	1 	1 	2012-11-28/16:16:46
	insert into t values(:1, :2) 	fw60v89km14c9 	2 	1 	2012-11-28/16:16:46
	insert into t values(:1, :2) 	fw60v89km14c9 	3 	1 	2012-11-28/16:16:46

JDBC 行为正常 32 128 2000的区间符合默认行为。

总结:

1. pl/sql developer 测试不具备任何价值。(各位同学也不要基于这个去测试了)

2. OCI sqlplus 缺少32区间, JAVA缺少 128区间 这个问题比较疑惑。

3. JDBC 目前正常

Bind Graduation oracle的本意是为了更详细的区分cursor,多次peeking 达到最佳的执行计划。但是对于一些设计很烂的表,将会出现child cursor暴增的可能

Reference:Oracle Call Interface