Verification of different mysql versions and storage engine selection _ MySQL

Verification of different mysql versions and storage engine selection bitsCN.com

Mysql has many versions and storage engines. to select the most suitable system for business use, some verification is required. This article describes the mysql verification process and ideas.

It mainly involves:

Mysql version

V Mariadb

V Tokudb

V Oracle

Specific storage engine

V Myisam

V Innodb

V TokuDB

V Maria

The following are specific ideas:

My. cnf configuration

Log-bin = mysql-bin disabled. do not write logs.

Skip-networking enabled

Installation and configuration

V mariadb5.5

V Oracle

V Tokudb

The installation and uninstallation scripts are available in the preceding directory.

The procedure is as follows:

1. stop the mysql service first.

A) service mysql stop/service mysqld stop

B) killall-9 mysql | killal-9 mysqld

C) do not set mysql environment variables in/etc/profile.

2. install the engine

A) the above directories contain installation scripts.

3. Inspection

A) enter the bin directory under the corresponding installation directory.

B)./mysql-uroot-p123456 check whether the installed version is correct.

C) show engines; show plugins; you can view the engine installation status.

4. run a unit test to verify the performance of each engine.

Unit Test [Gtest] basic insertion function

Including

V iterations

V storage package size: the data field size can be set

### Specific business table

Static void insertOneSession (int count, int size, bool canTruncate = true ){

### Item = createItem (size );

Cppdb: session;

Static const std: string SQL =

"Insert '###')/

VALUES (?, FROM_UNIXTIME (?), ?, ?, ?, ?, ?, /

INET_ATON (?), ?, ?, ?, ?, ?, /

?, ?, ?) ";

{

Try {

Session = cppdb: session (: common: base: BaseData: dbConnectString );

Cppdb: statement stmt;

If (canTruncate ){

Const static string ready = "TRUNCATE table ***";

Stmt = session. prepare (ready );

Stmt.exe c ();

}

Stmt = session. prepare (SQL );

For (int I = 0; I <count; I ++ ){

Stmt. reset ();

Stmt. bind (###);

...

Stmt.exe c ();

}

} Catch (std: exception const & e ){

LOG (ERROR) <"saveDB:" <e. what ();

}

/// Close the link

If (session. is_open ())

Session. close ();

}

{

// Calculate the tablespace

Session =

Cppdb: session (

"Mysql: user = root; password = 123456; database = mysql; set_charset_name = utf8; @ pool_size = 1 ");

Cppdb: statement stmt =

Session. create_statement (

"Select table_name, engine, ROUND (data_length/1024,2) size, table_rows from information_schema.tables where table_schema = '###' and table_name = 'Traffic '");

Cppdb: result r = stmt. query ();

While (r. next ()){

String table_name, engine;

Long size, table_rows;

R. fetch (table_name );

R. fetch (engine );

R. fetch (size );

R. fetch (table_rows );

LOG (INFO) <"TableInfo:" <table_name <"" <engine <"<size <" "<

}

If (session. is_open ())

Session. close ();

}

}

Isam storage test

Class benchMyisamTest: public testing: Test {

Public:

Static void SetUpTestCase (){

// Create the corresponding table structure

Std: string mysql = "/usr/local/mysql/bin/mysql -- default-character-set = utf8-uroot-p123456-D mysql-e/" source myisam. SQL /"";

System (mysql. c_str ());

}

Static void TearDownTestCase (){

}

};

TEST_F (benchMyisamTest, 1w100 ){

InsertOneSession (10000,100 );

}

TEST_F (benchMyisamTest, 1w1000 ){

InsertOneSession (10000,100 0 );

}

....

Multi-thread storage test

# Include

// Processing of multiple worker threads

Int thread_Num, thread_Size;

Void worker (){

InsertOneSession (thread_Num, thread_Size );

}

Void workerThread (int ts, int count, int size ){

// Required in multi-threaded mode; otherwise, mysql client library cannot be connected. error 111

Mysql_library_init (0, NULL, NULL );

Thread_Num = count;

Thread_Size = size;

Boost: thread_group threads;

For (int I = 0; I <ts; ++ I ){

Threads. create_thread (& worker );

}

Threads. join_all ();

LOG (INFO) <"done ";

// This Error seems to be libmysqlclient compatibility Error in my_thread_global_end (): 4 threads didn't exit

Mysql_library_end ();

}

TEST_F (benchMyisamTest, thread_1w100 ){

WorkerThread (2, 10000,100 );

}

TEST_F (benchMyisamTest, thread_30w ){

WorkerThread (3, 100000,100 0 );

}

..

Test other engines

Similar to how, you can write your own test engine

Result

The following is only the result of my virtual machine platform, which does not represent universality.

Storage Engine	Advantages	Disadvantages
MyISAM	Fast v insertion Indexes can be used for v queries.	V table crash
ARCHIVE	V is faster than myisam.	V no index V cannot be updated or deleted
InnoDB	V supports transactions	Slow v
TokuDB	The write performance of v is not measured.	V
Maria	V and Myisam are similar V security for crashes	V

Http://pan.baidu.com/s/1sj0cI8t is specific to some of the installation and configuration of different database scripts

BitsCN.com