我需要测试一下MySQL服务器在记录数达到1000w记录时的事务处理性能。我想保持30个线程对MySQL进行访问(利用LibMySQLd.dll)。每个线程每次访问时,查询一条记录。在查询前后统计时间差,作为一次访问的时间,并作为结果输出。在这里面有两个问题,我很纳闷:
1、如果在本地(MySQL服务器也在本地)运行测试进程,那么30个线程可以持续进行访问,并且都能访问成功。但是如果从局域网上的另一台机器上运行测试进程,则测试进行不久(大概能连接3700多次)之后,就会出现连接不上的错误。返回的错误码是2003。
2、不管是在本地还是在远端运行测试进程,返回的统计结果很难理解:统计结果基本上是两个值,大部分时间响应时间为0us(我的统计单位是微秒),还有一部分是10000us(本地)/16000(us),当然,偶尔会有例外的。奇怪的是,同样是用一个线程去 查询一条记录,时间差为什么这么大呢?而且性能呈现出严格的两极分化。我的主机配置:2.66G CPU、1G内存测试程序是用C写的。
代码如下:
#include <stdio.h>
#include <stdlib.h>
#include <stdarg.h>
#include "my_global.h"
#include "mysql.h"
MYSQL *mysql[200];
MYSQL_RES *results[200];
MYSQL_ROW record[200];
int iTestTimes[200];
int iNowConnCnt = 0,
iTotalConnCnt = 0,
iSerialNo = 0,
iConnFailCnt = 0,
iQryFailCnt = 0;
char acServerIP[20]={0},
acUser[32]={0},
acPwd[16]={0},
acDatabase[32] = {0};
HANDLE hMutexThread, hMutexResult, hSemArg; static char *server_options[] = { "mysql_test", "--defaults-file=my.cnf" };
int num_elements = sizeof(server_options)/ sizeof(char *);
static char *server_groups[] = { "libmysqld_server", "libmysqld_client" };
int QueryDataThread(int *piConnSerialNo);int main(int argc,char** argv)
{
int i = 0;
DWORD lpThreadId = 0;
HANDLE ThreadHdl[200] = {0};
SYSTEMTIME tStartSysTime;
FILETIME tStartFileTime;
FILETIME tLastConnFileTime;
FILETIME tNowFileTime;
SYSTEMTIME tNowSysTime; if (argc == 1)
{
strcpy( &acServerIP[0],“MyIP” );
strcpy( &acUser[0],"root" );
strcpy( &acPwd[0],"MyPwd“);
strcpy( &acDatabase[0],"MyDB" );
}
else if (argc != 5)
{
printf("Please start the program With parameters MySQL server IP,MySQL User,MySQL Pwd!\n");
return 1;
}
else
{
if(strlen(*(argv+1)) > 20)
{
printf("The length of IP should not long than 20!\n");
return 1;
}
if(strlen(*(argv+2)) > 32)
{
printf("The length of user name should not long than 32!\n");
return 1;
}
if(strlen(*(argv+3)) > 16)
{
printf("The length of password should not long than 16!\n");
return 1;
}
if(strlen(*(argv+4)) > 32)
{
printf("The length of database name should not long than 32!\n");
return 1;
}
strcpy( &acServerIP[0],*(argv+1) );
strcpy( &acUser[0],*(argv+2) );
strcpy( &acPwd[0],*(argv+3) );
strcpy( &acDatabase[0],*(argv+4) );
}
mysql_server_init(num_elements, server_options, server_groups);
GetLocalTime(&tStartSysTime);
SystemTimeToFileTime(&tStartSysTime,&tStartFileTime);
tLastConnFileTime = tStartFileTime;
printf("Test Start Time:%ul,%ul\n",tStartFileTime.dwHighDateTime,tStartFileTime.dwLowDateTime);
hSemArg = CreateSemaphore( NULL, 0, 1, TEXT("SemArg")); for(;;)
{
GetLocalTime(&tNowSysTime);
SystemTimeToFileTime(&tNowSysTime,&tNowFileTime);
/*if( tNowFileTime.dwLowDateTime > (tStartFileTime.dwLowDateTime + 40000000) ||
tNowFileTime.dwHighDateTime > tStartFileTime.dwHighDateTime)
{
printf("=============================================================================\n");
printf("Times try to connect:%d\n",iTotalConnCnt);
printf("Conn failed times:%d\n",iConnFailCnt);
printf("Qry failed times:%d\n",iQryFailCnt);
break;
}*/
if(iTestTimes[0]>=1000000)
{
break;
} for(iSerialNo=0;iSerialNo<1;iSerialNo++)
{
ThreadHdl[iSerialNo] = CreateThread(NULL,
0,
(LPTHREAD_START_ROUTINE)QueryDataThread,
&iSerialNo,
CREATE_SUSPENDED,
&lpThreadId);
if (ThreadHdl[iSerialNo] == NULL)
{
printf("create thread failed.\n");
}
else
{
SetThreadPriority(ThreadHdl[iSerialNo], THREAD_PRIORITY_IDLE);
ResumeThread(ThreadHdl[iSerialNo]);
iTotalConnCnt++;
/*printf("The thread %d has been created.\n",iTotalConnCnt);*/
}
WaitForSingleObject(hSemArg, INFINITE);
}
for ( i=0; i<=iSerialNo; i++ )
{
WaitForSingleObject(ThreadHdl[i], INFINITE);
CloseHandle( ThreadHdl[i] );
}
}
mysql_server_end();
return 0;
}
int QueryDataThread(int *piConnSerialNo)
{
FILETIME tBefQueryFileTime;
SYSTEMTIME tBefQuerySysTime;
FILETIME tAftQueryFileTime;
SYSTEMTIME tAftQuerySysTime;
char acSqlString[256] = {0};
int iSerialNo; iSerialNo = *piConnSerialNo;
ReleaseSemaphore(hSemArg, 1, NULL);
mysql[iSerialNo] = mysql_init(NULL);
mysql_options(mysql[iSerialNo] , MYSQL_READ_DEFAULT_GROUP, "libmysqld_client");
mysql_options(mysql[iSerialNo] , MYSQL_OPT_USE_EMBEDDED_CONNECTION, NULL);
if(!mysql_real_connect(mysql[iSerialNo], acServerIP,acUser,acPwd, acDatabase, 0,NULL,0))
{
printf("Connect to mysql server error at %d times!Failed to connect to database!The error no is:%d Error: %s\n",
iSerialNo,
mysql_errno( mysql[iSerialNo] ),
mysql_error( mysql[iSerialNo] )); iConnFailCnt++;
return 1;
}
else
{
iNowConnCnt++;
iTestTimes[iSerialNo]++;
} GetLocalTime(&tBefQuerySysTime);
SystemTimeToFileTime(&tBefQuerySysTime,&tBefQueryFileTime);
/*sprintf(acSqlString,"SELECT * FROM r_tsmap where neno > %d and neno<%d ",
10*(iSerialNo),
10*(iSerialNo)+1000);*/
sprintf(acSqlString,"SELECT * FROM r_tsmap where neno = %d",iTestTimes[iSerialNo]*30+iSerialNo);
//sprintf(acSqlString,"update r_tsmap set neno = 10000000+%d where neno = %d",iTestTimes[iSerialNo]*30+iSerialNo);
//sprintf(acSqlString,"update r_tsmap set neno = 10000000+%d where neno = %d",iTestTimes[iSerialNo]*30+iSerialNo);
if(0 != mysql_query(mysql[iSerialNo] ,acSqlString))
{
iQryFailCnt++;
iNowConnCnt--;
return 2;
}
GetLocalTime(&tAftQuerySysTime);
SystemTimeToFileTime(&tAftQuerySysTime,&tAftQueryFileTime);
results[iSerialNo] = mysql_store_result(mysql[iSerialNo]);
if(results[iSerialNo] != NULL)
{
printf("The %d Time access to mysql successfully,time interval is:%dus\n%s\n",
iSerialNo,
(tAftQueryFileTime.dwLowDateTime-tBefQueryFileTime.dwLowDateTime)/10,
acSqlString);
}
/*while((record[iSerialNo] = mysql_fetch_row(results[iSerialNo]))) {
printf("%d - %d \n", atoi(record[iSerialNo][0]), atoi(record[iSerialNo][1]) );
}*/
mysql_free_result(results[iSerialNo]);
mysql_thread_end();
mysql_close(mysql[iSerialNo]);
iNowConnCnt -= 1;
return 0;
}请各位大侠帮我分析下以上出现的问题,在下感激不尽。
1、如果在本地(MySQL服务器也在本地)运行测试进程,那么30个线程可以持续进行访问,并且都能访问成功。但是如果从局域网上的另一台机器上运行测试进程,则测试进行不久(大概能连接3700多次)之后,就会出现连接不上的错误。返回的错误码是2003。
2、不管是在本地还是在远端运行测试进程,返回的统计结果很难理解:统计结果基本上是两个值,大部分时间响应时间为0us(我的统计单位是微秒),还有一部分是10000us(本地)/16000(us),当然,偶尔会有例外的。奇怪的是,同样是用一个线程去 查询一条记录,时间差为什么这么大呢?而且性能呈现出严格的两极分化。我的主机配置:2.66G CPU、1G内存测试程序是用C写的。
代码如下:
#include <stdio.h>
#include <stdlib.h>
#include <stdarg.h>
#include "my_global.h"
#include "mysql.h"
MYSQL *mysql[200];
MYSQL_RES *results[200];
MYSQL_ROW record[200];
int iTestTimes[200];
int iNowConnCnt = 0,
iTotalConnCnt = 0,
iSerialNo = 0,
iConnFailCnt = 0,
iQryFailCnt = 0;
char acServerIP[20]={0},
acUser[32]={0},
acPwd[16]={0},
acDatabase[32] = {0};
HANDLE hMutexThread, hMutexResult, hSemArg; static char *server_options[] = { "mysql_test", "--defaults-file=my.cnf" };
int num_elements = sizeof(server_options)/ sizeof(char *);
static char *server_groups[] = { "libmysqld_server", "libmysqld_client" };
int QueryDataThread(int *piConnSerialNo);int main(int argc,char** argv)
{
int i = 0;
DWORD lpThreadId = 0;
HANDLE ThreadHdl[200] = {0};
SYSTEMTIME tStartSysTime;
FILETIME tStartFileTime;
FILETIME tLastConnFileTime;
FILETIME tNowFileTime;
SYSTEMTIME tNowSysTime; if (argc == 1)
{
strcpy( &acServerIP[0],“MyIP” );
strcpy( &acUser[0],"root" );
strcpy( &acPwd[0],"MyPwd“);
strcpy( &acDatabase[0],"MyDB" );
}
else if (argc != 5)
{
printf("Please start the program With parameters MySQL server IP,MySQL User,MySQL Pwd!\n");
return 1;
}
else
{
if(strlen(*(argv+1)) > 20)
{
printf("The length of IP should not long than 20!\n");
return 1;
}
if(strlen(*(argv+2)) > 32)
{
printf("The length of user name should not long than 32!\n");
return 1;
}
if(strlen(*(argv+3)) > 16)
{
printf("The length of password should not long than 16!\n");
return 1;
}
if(strlen(*(argv+4)) > 32)
{
printf("The length of database name should not long than 32!\n");
return 1;
}
strcpy( &acServerIP[0],*(argv+1) );
strcpy( &acUser[0],*(argv+2) );
strcpy( &acPwd[0],*(argv+3) );
strcpy( &acDatabase[0],*(argv+4) );
}
mysql_server_init(num_elements, server_options, server_groups);
GetLocalTime(&tStartSysTime);
SystemTimeToFileTime(&tStartSysTime,&tStartFileTime);
tLastConnFileTime = tStartFileTime;
printf("Test Start Time:%ul,%ul\n",tStartFileTime.dwHighDateTime,tStartFileTime.dwLowDateTime);
hSemArg = CreateSemaphore( NULL, 0, 1, TEXT("SemArg")); for(;;)
{
GetLocalTime(&tNowSysTime);
SystemTimeToFileTime(&tNowSysTime,&tNowFileTime);
/*if( tNowFileTime.dwLowDateTime > (tStartFileTime.dwLowDateTime + 40000000) ||
tNowFileTime.dwHighDateTime > tStartFileTime.dwHighDateTime)
{
printf("=============================================================================\n");
printf("Times try to connect:%d\n",iTotalConnCnt);
printf("Conn failed times:%d\n",iConnFailCnt);
printf("Qry failed times:%d\n",iQryFailCnt);
break;
}*/
if(iTestTimes[0]>=1000000)
{
break;
} for(iSerialNo=0;iSerialNo<1;iSerialNo++)
{
ThreadHdl[iSerialNo] = CreateThread(NULL,
0,
(LPTHREAD_START_ROUTINE)QueryDataThread,
&iSerialNo,
CREATE_SUSPENDED,
&lpThreadId);
if (ThreadHdl[iSerialNo] == NULL)
{
printf("create thread failed.\n");
}
else
{
SetThreadPriority(ThreadHdl[iSerialNo], THREAD_PRIORITY_IDLE);
ResumeThread(ThreadHdl[iSerialNo]);
iTotalConnCnt++;
/*printf("The thread %d has been created.\n",iTotalConnCnt);*/
}
WaitForSingleObject(hSemArg, INFINITE);
}
for ( i=0; i<=iSerialNo; i++ )
{
WaitForSingleObject(ThreadHdl[i], INFINITE);
CloseHandle( ThreadHdl[i] );
}
}
mysql_server_end();
return 0;
}
int QueryDataThread(int *piConnSerialNo)
{
FILETIME tBefQueryFileTime;
SYSTEMTIME tBefQuerySysTime;
FILETIME tAftQueryFileTime;
SYSTEMTIME tAftQuerySysTime;
char acSqlString[256] = {0};
int iSerialNo; iSerialNo = *piConnSerialNo;
ReleaseSemaphore(hSemArg, 1, NULL);
mysql[iSerialNo] = mysql_init(NULL);
mysql_options(mysql[iSerialNo] , MYSQL_READ_DEFAULT_GROUP, "libmysqld_client");
mysql_options(mysql[iSerialNo] , MYSQL_OPT_USE_EMBEDDED_CONNECTION, NULL);
if(!mysql_real_connect(mysql[iSerialNo], acServerIP,acUser,acPwd, acDatabase, 0,NULL,0))
{
printf("Connect to mysql server error at %d times!Failed to connect to database!The error no is:%d Error: %s\n",
iSerialNo,
mysql_errno( mysql[iSerialNo] ),
mysql_error( mysql[iSerialNo] )); iConnFailCnt++;
return 1;
}
else
{
iNowConnCnt++;
iTestTimes[iSerialNo]++;
} GetLocalTime(&tBefQuerySysTime);
SystemTimeToFileTime(&tBefQuerySysTime,&tBefQueryFileTime);
/*sprintf(acSqlString,"SELECT * FROM r_tsmap where neno > %d and neno<%d ",
10*(iSerialNo),
10*(iSerialNo)+1000);*/
sprintf(acSqlString,"SELECT * FROM r_tsmap where neno = %d",iTestTimes[iSerialNo]*30+iSerialNo);
//sprintf(acSqlString,"update r_tsmap set neno = 10000000+%d where neno = %d",iTestTimes[iSerialNo]*30+iSerialNo);
//sprintf(acSqlString,"update r_tsmap set neno = 10000000+%d where neno = %d",iTestTimes[iSerialNo]*30+iSerialNo);
if(0 != mysql_query(mysql[iSerialNo] ,acSqlString))
{
iQryFailCnt++;
iNowConnCnt--;
return 2;
}
GetLocalTime(&tAftQuerySysTime);
SystemTimeToFileTime(&tAftQuerySysTime,&tAftQueryFileTime);
results[iSerialNo] = mysql_store_result(mysql[iSerialNo]);
if(results[iSerialNo] != NULL)
{
printf("The %d Time access to mysql successfully,time interval is:%dus\n%s\n",
iSerialNo,
(tAftQueryFileTime.dwLowDateTime-tBefQueryFileTime.dwLowDateTime)/10,
acSqlString);
}
/*while((record[iSerialNo] = mysql_fetch_row(results[iSerialNo]))) {
printf("%d - %d \n", atoi(record[iSerialNo][0]), atoi(record[iSerialNo][1]) );
}*/
mysql_free_result(results[iSerialNo]);
mysql_thread_end();
mysql_close(mysql[iSerialNo]);
iNowConnCnt -= 1;
return 0;
}请各位大侠帮我分析下以上出现的问题,在下感激不尽。
VOID GetLocalTime(
LPSYSTEMTIME lpSystemTime // 系统时间结构体的地址
);
参数:指向用来接收本地当前日期和时间的系统时间结构体的指针。系统中使用的是ms级的,所以你会得出这样的结果