MySQL concurrency test

Do some testing for the company's business for the previous section.

Let's do some stress tests.

Server configuration:

Operating system: CentOS 5.6-64

Cpu:8 Nuclear

Memory: 8G

Hard drive: SAS

File system: Linux

mysql:5.6.

Network card: 100M

Network environment: Good

Database tables:

C_account_customer, which is used to query and update certain fields based on ID

CREATE TABLE ' C_account_customer ' (
' id ' int (ten) unsigned not NULL auto_increment COMMENT ' table primary key ',
' customer_id ' int (ten) unsigned not NULL COMMENT ' customer number ',
' item_id ' int (ten) unsigned not NULL COMMENT ' account number ',
' ref_id ' int (ten) unsigned not NULL DEFAULT ' 0 ' COMMENT ' Business identification number ',
' Debit_balance ' decimal (14,2) DEFAULT NULL COMMENT ' Debit balance ',
' Credit_balance ' decimal (14,2) DEFAULT NULL COMMENT ' Credit Balance ',
' Last_time ' timestamp not NULL the DEFAULT current_timestamp on update current_timestamp COMMENT ' Last update Time ',
PRIMARY KEY (' id '),
UNIQUE KEY ' customer_id ' (' customer_id ', ' item_id ', ' ref_id ')
) Engine=innodb auto_increment=2 DEFAULT Charset=utf8;

C_entry_company used to insert debit and credit account information

CREATE TABLE ' C_entry_company ' (
' id ' int (ten) unsigned not NULL auto_increment COMMENT ' table primary key ',
' entity_id ' int (ten) unsigned not NULL COMMENT ' body number ',
' item_id ' int (ten) unsigned not NULL COMMENT ' account number ',
' ref_id ' int (one) not NULL DEFAULT ' 0 ' COMMENT ' Business identification number ',
' Direction ' tinyint (3) unsigned not NULL COMMENT ' bookkeeping direction ',
' Amount ' decimal (10,0) not NULL COMMENT ' bookkeeping amount ',
' Operation ' Int (one) DEFAULT NULL COMMENT ' Operation class model ',
' operation_id ' int (one) DEFAULT NULL COMMENT ' Operation serial number ',
' Note ' varchar ($) DEFAULT NULL COMMENT ' business notes ',
' Create_time ' timestamp not NULL the DEFAULT current_timestamp on UPDATE current_timestamp COMMENT ' data creation time ',
PRIMARY KEY (' id ')
) Engine=innodb DEFAULT Charset=utf8;

TestValue table, which is used to record the values of the fields queried in the transaction for the result analysis:

CREATE TABLE ' TestValue ' (
' id ' int (one) not NULL auto_increment,
' Val ' decimal (14,2) DEFAULT NULL,
PRIMARY KEY (' id ')
) Engine=innodb DEFAULT Charset=utf8;

Transaction operations for the above three data tables:

CREATE definer= ' root ' @ ' localhost ' PROCEDURE ' Test ' (Out DEBITB decimal (14,2))
BEGIN

START TRANSACTION;

Select @db: =debit_balance from C_account_customer where id=1 for update;
Set [email protected];
Insert into Abacus.testvalue (val) values (@db);
Insert into Abacus.c_entry_customer (customer_id,item_id,ref_id,direction,amount,operation,operation_id,note) Values (1,1,1,1,1,1,1,1);
Insert into Abacus.c_entry_customer (customer_id,item_id,ref_id,direction,amount,operation,operation_id,note) Values (1,2,1,1,1,1,1,1);
Update Abacus.c_account_customer Set [email protected]+1 where id=1;
Commit

END

C # test Code

public static void Testcomplicating ()
{
Console.WriteLine ("Start:" +datetime.now);
BOOL B = true;
int num = 0;
DateTime start = DateTime.Now;
while (b)
{
num++;
Complicating ();

if (DateTime.Now.AddMinutes ( -30) >start)
{
b = false;
}
}
Console.WriteLine ("End:" + datetime.now+ "; Number of Cycles:" +num);
}

public static void Complicating ()
{
for (int i = 0; i <; i++)
{
Thread thread = new Thread (new Parameterizedthreadstart (execution));
Thread. Start (i);

}
}

public static void Execution (object i)
{
Mysqlparameter DEBITB = new Mysqlparameter ("? Debitb", Mysqldbtype.decimal);
DEBITB. Direction = ParameterDirection.Output;

idataparameter[] Parameters = new Idataparameter[] {
Debitb
};
Mysqlhelper. Runprocedure ("Test", Parameters, "test");

Task.Factory.StartNew (() =>logs.jobstatus.info ("Pre-Update value:" +DEBITB. Value+ "; When the front-thread ID:" + Thread.CurrentThread.ManagedThreadId + "; Loop:" + (int) i));

}

First set of test results

Mysql Server Maximum Connections: 1024

. NET client Connection pool Maximum connections: 500

Does not simulate time-consuming operations

Insert a piece of data before testing: INSERT into Abacus.c_account_customer (customer_id,item_id,ref_id,debit_balance,credit_balance) VALUES ( 1,1,1,10000,10000);

1th Time: 100 Concurrent, complete 3 seconds, no data loss, no data error.
2nd time: 500 concurrent, complete 10 seconds, no data loss, no data error.
3rd time: 5 concurrent, while loop lasts 1 minutes, complete 1 minutes, transaction Count 171540, no data loss, no data error.
4th time: 10 concurrent, while loop lasts 10 minutes, complete 10 minutes, transaction 1,885,640 times, no data lost (3,771,280 data in C_entry_company), no data error (debit_balance=1895640.00 = 1885640+10000).
5th time: 10 concurrent, while loop lasts 30 minutes, complete 30 minutes, number of cycles (694861), transaction 6,948,610 times, no data lost (13,897,220 data in C_entry_company), no data error (Debit_ balance=6958610.00 =6948610+10000).

Legend:

In

Figure is the 4th time the MySQL server is tested.

Figure 3 The number of transactions performed per second by the MySQL server for the 4th test.

Figure 4 The number of transactions performed per minute by the MySQL server for the 5th test.

Note: Welcome to put forward more excellent and reasonable plan.

MySQL concurrency test