Weird performance problem.
Weird performance problem.
am 06.07.2006 14:23:29 von Michael Nielsen
I have two systems.
1. Linux Fedora 4, on a 500Mhz 256Mb Via Intel Pentium II clone.
Using gcc version 4.0.2 Mysql version 4.1.16 - default
installation.
2. Windows XP prof - HP laptop 1.73Ghz Intel processor 1Gb Memory
Using Visual Studio 2005, Mysql Version 5.0.22 - default development
installation.
I obtained an insert rate of approx 74.6 inserts/second on the windows
box (barely any cpu usage - seemed like it was iobound)
and a rate of 1428.57 inserts/second on the linux box, with a cpu usage
of 77%.
I'm very curious about what could be causing the difference in speed..
I am running the following code (Note minor changes needed to run on
linux).
//NOTE - careful it will delete a database called TestData if you want
to run it.
// mysqlbench.cpp : Defines the entry point for the console
application.
//
#include "stdafx.h"
#include
#include
#include
#include
#include
int isError(MYSQL *mysql_struct,int rc) {
printf("command returned (%s)\n",mysql_error(mysql_struct));
return rc;
}
int createDatabase(MYSQL *mysql_struct) {
int rc;
printf("dropping database\n");
rc = mysql_query(mysql_struct,"DROP DATABASE TestData");
if (isError(mysql_struct,rc)) {
printf("database not found\n");
}
printf("creating database\n");
rc = mysql_query(mysql_struct,"CREATE DATABASE TestData");
if (isError(mysql_struct,rc)) return -1;
printf("using database\n");
rc = mysql_query(mysql_struct,"USE TestData");
if (isError(mysql_struct,rc)) return -1;
printf("creating table element\n");
rc = mysql_query(mysql_struct,"CREATE TABLE Element ("
"ID INTEGER NOT NULL,"
"elementname VARCHAR(250) NOT NULL,"
"PRIMARY KEY (ID));");
if (isError(mysql_struct,rc)) return -1;
printf("creating table field\n");
rc = mysql_query(mysql_struct,"CREATE TABLE Field ("
"ID INTEGER NOT NULL, "
"element_ID INTEGER NOT NULL,"
"meta_ID INTEGER NOT NULL,"
"registered datetime NOT NULL,"
"unregistered datetime NULL,"
"PRIMARY KEY (ID));");
if (isError(mysql_struct, rc)) return -1;
printf("create table log\n");
rc = mysql_query(mysql_struct,"CREATE TABLE Log ("
"field_ID INTEGER NOT NULL,"
"timestamp datetime NOT NULL,"
"value varchar(250),"
"PRIMARY KEY (field_ID,timestamp));");
if (isError(mysql_struct,rc)) return -1;
printf("create table meta\n");
rc = mysql_query(mysql_struct,"CREATE TABLE meta_data ("
"ID INTEGER AUTO_INCREMENT NOT NULL,"
"name varchar(50) NOT NULL ,"
"logtype varchar(50) NOT NULL,"
"valueAsString varchar(250) NOT NULL,"
"PRIMARY KEY (ID,name));");
if (isError(mysql_struct,rc)) return -1;
return 0;
}
int _tmain(int argc, _TCHAR* argv[])
{
int delay=1000000;
if (argc > 1) {
sscanf_s((const char *)argv[1],"%d",&delay);
printf("%s %d\n",argv[0], delay);
}
MYSQL mysql_struct;
mysql_init(&mysql_struct);
MYSQL *ret = mysql_real_connect(&mysql_struct,"localhost",
"root","snip-justincase-snip","test", 0,NULL,CLIENT_MULTI_RESULTS);
if (!ret) {
printf("error db not open");
}
int rc = mysql_query(&mysql_struct,"show databases;");
if (isError(&mysql_struct,rc)) {
}
else{
MYSQL_RES *result;
result = mysql_store_result(&mysql_struct);
while (result) {
MYSQL_ROW row;
if (result) {
do
{
row =
mysql_fetch_row(result);
if (row && row[0]) {
printf("getting row
%s\n",row[0]);
}
} while (row);
}
result = mysql_store_result(&mysql_struct);
}
}
if (!createDatabase(&mysql_struct)) {
}
else {
printf("create database failed\n");
}
int i=0;
MYSQL_STMT *stmt = mysql_stmt_init(&mysql_struct);
MYSQL_BIND bind[4];
const char INSERT_SAMPLE[] = "INSERT INTO meta_data
(ID,name,logtype,valueAsString) VALUES(?,?,?,?);";
if (mysql_stmt_prepare(stmt, INSERT_SAMPLE, (unsigned
long)strlen(INSERT_SAMPLE))){
fprintf(stderr, " mysql_stmt_prepare(), INSERT
failed\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
Sleep(10000);
return 0;
}
fprintf(stdout, " prepare, INSERT successful\n");
int id=0;
const int namesize=50;
char *name[namesize];
const int typesize=50;
char *logtype[typesize];
const int valuesize=50;
char *valueAsString[valuesize];
time_t start; // C run-time time (defined in )
time_t stop;
time( &start ) ;
for (i = 0; i < 10000; i++) {
printf("doing %d\r",i);
memset((char *)bind, 0, sizeof(bind));
memset((char *)name,0,sizeof(name));
memset((char *)logtype,0,sizeof(logtype));
memset(valueAsString,0,sizeof(valueAsString));
sprintf_s((char*)name,sizeof(name),"name=%d",i);
sprintf_s((char*)logtype,sizeof(logtype),"logtype=%d",i);
sprintf_s((char*)valueAsString,sizeof(valueAsString),"value= %d",i);
bind[0].buffer_type= MYSQL_TYPE_LONG;
bind[0].buffer=&id;
bind[0].length=0;
bind[0].is_null= 0;
bind[1].buffer_type= MYSQL_TYPE_STRING;
bind[1].buffer=name;
bind[1].length=(unsigned long*)&namesize;
bind[1].is_null=0;
bind[2].buffer_type= MYSQL_TYPE_STRING;
bind[2].buffer=logtype;
bind[2].length=(unsigned long*)&typesize;
bind[2].is_null= 0;
bind[3].buffer_type= MYSQL_TYPE_STRING;
bind[3].buffer= valueAsString;
bind[3].length= (unsigned long*)&valuesize;
bind[3].is_null= 0;
// Bind the buffers
if (mysql_stmt_bind_param(stmt, bind)){
fprintf(stderr, " mysql_stmt_bind_param()
failed\n");
fprintf(stderr, " %s\n",
mysql_stmt_error(stmt));
break;
}
//Execute the INSERT statement - 1
if (mysql_stmt_execute(stmt)) {
fprintf(stderr, " mysql_stmt_execute(), 1
failed\n");
fprintf(stderr, " %s\n",
mysql_stmt_error(stmt));
break;
}
/* Get the total number of affected rows */
unsigned long affected_rows=(unsigned long)
mysql_stmt_affected_rows(stmt);
//fprintf(stdout, " total affected rows(insert 1):
%lu\n",
// (unsigned long) affected_rows);
if (affected_rows != 1) /* validate affected rows */
{
//fprintf(stderr, " invalid affected rows by
MySQL\n");
//break;
}
else {
}
//sleep(delay);
}
mysql_stmt_close(stmt);
time(&stop);
printf("time taken = %d\n", stop-start);
printf("statements/second = %f\n",10000.0/(float)(stop-start));
printf("finished\n");
Sleep(10000);
return 0;
}
Michael (Mike) Nielsen BE.
Cypresvej 6
7400 Herning
Denmark.
Phone: +4596604505
Mobile: +4523694901
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org
RE: Weird performance problem.
am 07.07.2006 08:54:12 von Leigh Sharpe
I would suggest you look at your disk speed. I have a similar issue with a=
Windows server running slower than I would expect, but I have come to th=
e conclusion that the bottleneck is not actually related to MySQL itself,=
but rather to poor disk transfer speeds. (I'm yet to establish whether i=
t's the windows drivers or the hardware itself, though.)=20
Regards,
Leigh
=20
Leigh Sharpe
Network Systems Engineer
Pacific Wireless
Ph +61 3 9584 8966
Mob 0408 009 502
email lsharpe@pacificwireless.com.au
web www.pacificwireless.com.au
-----Original Message-----
From: Michael Nielsen [mailto:Mni@kk-electronic.dk]=20
Sent: Thursday, July 06, 2006 10:23 PM
To: win32@lists.mysql.com
Subject: Weird performance problem.
I have two systems.
1. Linux Fedora 4, on a 500Mhz 256Mb Via Intel Pentium II clone.
Using gcc version 4.0.2 Mysql version 4.1.16 - default
installation.
2. Windows XP prof - HP laptop 1.73Ghz Intel processor 1Gb Memory
Using Visual Studio 2005, Mysql Version 5.0.22 - default development
installation.
I obtained an insert rate of approx 74.6 inserts/second on the windows
box (barely any cpu usage - seemed like it was iobound)
and a rate of 1428.57 inserts/second on the linux box, with a cpu usage
of 77%.=20
I'm very curious about what could be causing the difference in speed..
I am running the following code (Note minor changes needed to run on
linux).
//NOTE - careful it will delete a database called TestData if you want
to run it.
// mysqlbench.cpp : Defines the entry point for the console
application.
//
#include "stdafx.h"
#include
#include
#include
#include =20
#include
int isError(MYSQL *mysql_struct,int rc) {
printf("command returned (%s)\n",mysql_error(mysql_struct));
return rc;
}
int createDatabase(MYSQL *mysql_struct) {
int rc;
printf("dropping database\n");
rc =3D mysql_query(mysql_struct,"DROP DATABASE TestData");
if (isError(mysql_struct,rc)) {
printf("database not found\n");
}
printf("creating database\n");
rc =3D mysql_query(mysql_struct,"CREATE DATABASE TestData");
if (isError(mysql_struct,rc)) return -1;
printf("using database\n");
rc =3D mysql_query(mysql_struct,"USE TestData");
if (isError(mysql_struct,rc)) return -1;
printf("creating table element\n");
rc =3D mysql_query(mysql_struct,"CREATE TABLE Element ("
"ID INTEGER NOT NULL,"
"elementname VARCHAR(250) NOT NULL,"
"PRIMARY KEY (ID));");
if (isError(mysql_struct,rc)) return -1;
printf("creating table field\n");=20
rc =3D mysql_query(mysql_struct,"CREATE TABLE Field ("
"ID INTEGER NOT NULL, "
"element_ID INTEGER NOT NULL,"
"meta_ID INTEGER NOT NULL,"
"registered datetime NOT NULL,"
"unregistered datetime NULL,"
"PRIMARY KEY (ID));");
if (isError(mysql_struct, rc)) return -1;
printf("create table log\n");
rc =3D mysql_query(mysql_struct,"CREATE TABLE Log ("
"field_ID INTEGER NOT NULL,"
"timestamp datetime NOT NULL,"
"value varchar(250),"
"PRIMARY KEY (field_ID,timestamp));");
if (isError(mysql_struct,rc)) return -1;
printf("create table meta\n");
rc =3D mysql_query(mysql_struct,"CREATE TABLE meta_data ("
"ID INTEGER AUTO_INCREMENT NOT NULL,"
"name varchar(50) NOT NULL ,"
"logtype varchar(50) NOT NULL,"
"valueAsString varchar(250) NOT NULL,"
"PRIMARY KEY (ID,name));");
if (isError(mysql_struct,rc)) return -1;
return 0;
}
int _tmain(int argc, _TCHAR* argv[])
{
int delay=3D1000000;
if (argc > 1) {
sscanf_s((const char *)argv[1],"%d",&delay);
printf("%s %d\n",argv[0], delay);
}
MYSQL mysql_struct;
mysql_init(&mysql_struct);
MYSQL *ret =3D mysql_real_connect(&mysql_struct,"localhost",
"root","snip-justincase-snip","test", 0,NULL,CLIENT_MULTI_RESULTS);
if (!ret) {
printf("error db not open");
}
int rc =3D mysql_query(&mysql_struct,"show databases;");
if (isError(&mysql_struct,rc)) {
}
else{
MYSQL_RES *result;
result =3D mysql_store_result(&mysql_struct);
while (result) {
MYSQL_ROW row;
if (result) {=09
do
{ =09
row =3D
mysql_fetch_row(result);=09
if (row && row[0]) {
printf("getting row
%s\n",row[0]);
}
} while (row);
}
result =3D mysql_store_result(&mysql_struct);
}
}
if (!createDatabase(&mysql_struct)) {
}
else {
printf("create database failed\n");
}
int i=3D0;
MYSQL_STMT *stmt =3D mysql_stmt_init(&mysql_struct);
MYSQL_BIND bind[4];
const char INSERT_SAMPLE[] =3D "INSERT INTO meta_data
(ID,name,logtype,valueAsString) VALUES(?,?,?,?);";
if (mysql_stmt_prepare(stmt, INSERT_SAMPLE, (unsigned
long)strlen(INSERT_SAMPLE))){
fprintf(stderr, " mysql_stmt_prepare(), INSERT
failed\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
Sleep(10000);
return 0;
}
fprintf(stdout, " prepare, INSERT successful\n");
int id=3D0;
const int namesize=3D50;
char *name[namesize];
const int typesize=3D50;
char *logtype[typesize];
const int valuesize=3D50;
char *valueAsString[valuesize];
time_t start; // C run-time time (defined in )
time_t stop;
time( &start ) ;
for (i =3D 0; i < 10000; i++) {
printf("doing %d\r",i);
memset((char *)bind, 0, sizeof(bind));
memset((char *)name,0,sizeof(name));
memset((char *)logtype,0,sizeof(logtype));
memset(valueAsString,0,sizeof(valueAsString));
sprintf_s((char*)name,sizeof(name),"name=3D%d",i);
sprintf_s((char*)logtype,sizeof(logtype),"logtype=3D%d",i); =09
sprintf_s((char*)valueAsString,sizeof(valueAsString),"value= 3D%d",i);
bind[0].buffer_type=3D MYSQL_TYPE_LONG; =09
bind[0].buffer=3D&id;
bind[0].length=3D0;
bind[0].is_null=3D 0;
bind[1].buffer_type=3D MYSQL_TYPE_STRING; =09
bind[1].buffer=3Dname;
bind[1].length=3D(unsigned long*)&namesize;
bind[1].is_null=3D0;
bind[2].buffer_type=3D MYSQL_TYPE_STRING; =09
bind[2].buffer=3Dlogtype;
bind[2].length=3D(unsigned long*)&typesize;
bind[2].is_null=3D 0;
bind[3].buffer_type=3D MYSQL_TYPE_STRING; =09
bind[3].buffer=3D valueAsString;
bind[3].length=3D (unsigned long*)&valuesize;
bind[3].is_null=3D 0;
// Bind the buffers
if (mysql_stmt_bind_param(stmt, bind)){
fprintf(stderr, " mysql_stmt_bind_param()
failed\n");
fprintf(stderr, " %s\n",
mysql_stmt_error(stmt));
break;
}
//Execute the INSERT statement - 1
if (mysql_stmt_execute(stmt)) {
fprintf(stderr, " mysql_stmt_execute(), 1
failed\n");
fprintf(stderr, " %s\n",
mysql_stmt_error(stmt));
break;
}
=09
/* Get the total number of affected rows */
unsigned long affected_rows=3D(unsigned long)
mysql_stmt_affected_rows(stmt);
//fprintf(stdout, " total affected rows(insert 1):
%lu\n",
// (unsigned long) affected_rows);
if (affected_rows !=3D 1) /* validate affected rows */
{
//fprintf(stderr, " invalid affected rows by
MySQL\n");
//break;
}=09
else {
}
//sleep(delay);
}
mysql_stmt_close(stmt);
time(&stop);
printf("time taken =3D %d\n", stop-start);
printf("statements/second =3D %f\n",10000.0/(float)(stop-start));
printf("finished\n");
Sleep(10000);
return 0;
}
Michael (Mike) Nielsen BE.
Cypresvej 6
7400 Herning
Denmark.
Phone: +4596604505
Mobile: +4523694901
--=20
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dlsharpe@pacificwi=
reless.com.au
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org
Svar: RE: Weird performance problem.
am 07.07.2006 10:31:07 von Michael Nielsen
Hi,
Thanks for the info.
I suspect that you are correct, though it is a rather significant
problem, as the company that I am evaluating MySQL for, wishes to run it
on a windows platform.
regards
Mike.
Michael (Mike) Nielsen BE.
Cypresvej 6
7400 Herning
Denmark.
Phone: +4596604505
Mobile: +4523694901
>>> "Leigh Sharpe" 7/07/2006 8:54 am
>>>
I would suggest you look at your disk speed. I have a similar issue
with a Windows server running slower than I would expect, but I have
come to the conclusion that the bottleneck is not actually related to
MySQL itself, but rather to poor disk transfer speeds. (I'm yet to
establish whether it's the windows drivers or the hardware itself,
though.)
Regards,
Leigh
Leigh Sharpe
Network Systems Engineer
Pacific Wireless
Ph +61 3 9584 8966
Mob 0408 009 502
email lsharpe@pacificwireless.com.au
web www.pacificwireless.com.au
-----Original Message-----
From: Michael Nielsen [mailto:Mni@kk-electronic.dk]
Sent: Thursday, July 06, 2006 10:23 PM
To: win32@lists.mysql.com
Subject: Weird performance problem.
I have two systems.
1. Linux Fedora 4, on a 500Mhz 256Mb Via Intel Pentium II clone.
Using gcc version 4.0.2 Mysql version 4.1.16 - default
installation.
2. Windows XP prof - HP laptop 1.73Ghz Intel processor 1Gb Memory
Using Visual Studio 2005, Mysql Version 5.0.22 - default
development
installation.
I obtained an insert rate of approx 74.6 inserts/second on the
windows
box (barely any cpu usage - seemed like it was iobound)
and a rate of 1428.57 inserts/second on the linux box, with a cpu
usage
of 77%.
I'm very curious about what could be causing the difference in speed..
I am running the following code (Note minor changes needed to run on
linux).
//NOTE - careful it will delete a database called TestData if you want
to run it.
// mysqlbench.cpp : Defines the entry point for the console
application.
//
#include "stdafx.h"
#include
#include
#include
#include
#include
int isError(MYSQL *mysql_struct,int rc) {
printf("command returned (%s)\n",mysql_error(mysql_struct));
return rc;
}
int createDatabase(MYSQL *mysql_struct) {
int rc;
printf("dropping database\n");
rc = mysql_query(mysql_struct,"DROP DATABASE TestData");
if (isError(mysql_struct,rc)) {
printf("database not found\n");
}
printf("creating database\n");
rc = mysql_query(mysql_struct,"CREATE DATABASE TestData");
if (isError(mysql_struct,rc)) return -1;
printf("using database\n");
rc = mysql_query(mysql_struct,"USE TestData");
if (isError(mysql_struct,rc)) return -1;
printf("creating table element\n");
rc = mysql_query(mysql_struct,"CREATE TABLE Element ("
"ID INTEGER NOT NULL,"
"elementname VARCHAR(250) NOT NULL,"
"PRIMARY KEY (ID));");
if (isError(mysql_struct,rc)) return -1;
printf("creating table field\n");
rc = mysql_query(mysql_struct,"CREATE TABLE Field ("
"ID INTEGER NOT NULL, "
"element_ID INTEGER NOT NULL,"
"meta_ID INTEGER NOT NULL,"
"registered datetime NOT NULL,"
"unregistered datetime NULL,"
"PRIMARY KEY (ID));");
if (isError(mysql_struct, rc)) return -1;
printf("create table log\n");
rc = mysql_query(mysql_struct,"CREATE TABLE Log ("
"field_ID INTEGER NOT NULL,"
"timestamp datetime NOT NULL,"
"value varchar(250),"
"PRIMARY KEY (field_ID,timestamp));");
if (isError(mysql_struct,rc)) return -1;
printf("create table meta\n");
rc = mysql_query(mysql_struct,"CREATE TABLE meta_data ("
"ID INTEGER AUTO_INCREMENT NOT NULL,"
"name varchar(50) NOT NULL ,"
"logtype varchar(50) NOT NULL,"
"valueAsString varchar(250) NOT NULL,"
"PRIMARY KEY (ID,name));");
if (isError(mysql_struct,rc)) return -1;
return 0;
}
int _tmain(int argc, _TCHAR* argv[])
{
int delay=1000000;
if (argc > 1) {
sscanf_s((const char *)argv[1],"%d",&delay);
printf("%s %d\n",argv[0], delay);
}
MYSQL mysql_struct;
mysql_init(&mysql_struct);
MYSQL *ret = mysql_real_connect(&mysql_struct,"localhost",
"root","snip-justincase-snip","test", 0,NULL,CLIENT_MULTI_RESULTS);
if (!ret) {
printf("error db not open");
}
int rc = mysql_query(&mysql_struct,"show databases;");
if (isError(&mysql_struct,rc)) {
}
else{
MYSQL_RES *result;
result = mysql_store_result(&mysql_struct);
while (result) {
MYSQL_ROW row;
if (result) {
do
{
row =
mysql_fetch_row(result);
if (row && row[0]) {
printf("getting row
%s\n",row[0]);
}
} while (row);
}
result = mysql_store_result(&mysql_struct);
}
}
if (!createDatabase(&mysql_struct)) {
}
else {
printf("create database failed\n");
}
int i=0;
MYSQL_STMT *stmt = mysql_stmt_init(&mysql_struct);
MYSQL_BIND bind[4];
const char INSERT_SAMPLE[] = "INSERT INTO meta_data
(ID,name,logtype,valueAsString) VALUES(?,?,?,?);";
if (mysql_stmt_prepare(stmt, INSERT_SAMPLE, (unsigned
long)strlen(INSERT_SAMPLE))){
fprintf(stderr, " mysql_stmt_prepare(), INSERT
failed\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
Sleep(10000);
return 0;
}
fprintf(stdout, " prepare, INSERT successful\n");
int id=0;
const int namesize=50;
char *name[namesize];
const int typesize=50;
char *logtype[typesize];
const int valuesize=50;
char *valueAsString[valuesize];
time_t start; // C run-time time (defined in )
time_t stop;
time( &start ) ;
for (i = 0; i < 10000; i++) {
printf("doing %d\r",i);
memset((char *)bind, 0, sizeof(bind));
memset((char *)name,0,sizeof(name));
memset((char *)logtype,0,sizeof(logtype));
memset(valueAsString,0,sizeof(valueAsString));
sprintf_s((char*)name,sizeof(name),"name=%d",i);
sprintf_s((char*)logtype,sizeof(logtype),"logtype=%d",i);
sprintf_s((char*)valueAsString,sizeof(valueAsString),"value= %d",i);
bind[0].buffer_type= MYSQL_TYPE_LONG;
bind[0].buffer=&id;
bind[0].length=0;
bind[0].is_null= 0;
bind[1].buffer_type= MYSQL_TYPE_STRING;
bind[1].buffer=name;
bind[1].length=(unsigned long*)&namesize;
bind[1].is_null=0;
bind[2].buffer_type= MYSQL_TYPE_STRING;
bind[2].buffer=logtype;
bind[2].length=(unsigned long*)&typesize;
bind[2].is_null= 0;
bind[3].buffer_type= MYSQL_TYPE_STRING;
bind[3].buffer= valueAsString;
bind[3].length= (unsigned long*)&valuesize;
bind[3].is_null= 0;
// Bind the buffers
if (mysql_stmt_bind_param(stmt, bind)){
fprintf(stderr, " mysql_stmt_bind_param()
failed\n");
fprintf(stderr, " %s\n",
mysql_stmt_error(stmt));
break;
}
//Execute the INSERT statement - 1
if (mysql_stmt_execute(stmt)) {
fprintf(stderr, " mysql_stmt_execute(), 1
failed\n");
fprintf(stderr, " %s\n",
mysql_stmt_error(stmt));
break;
}
/* Get the total number of affected rows */
unsigned long affected_rows=(unsigned long)
mysql_stmt_affected_rows(stmt);
//fprintf(stdout, " total affected rows(insert 1):
%lu\n",
// (unsigned long) affected_rows);
if (affected_rows != 1) /* validate affected rows */
{
//fprintf(stderr, " invalid affected rows by
MySQL\n");
//break;
}
else {
}
//sleep(delay);
}
mysql_stmt_close(stmt);
time(&stop);
printf("time taken = %d\n", stop-start);
printf("statements/second = %f\n",10000.0/(float)(stop-start));
printf("finished\n");
Sleep(10000);
return 0;
}
Michael (Mike) Nielsen BE.
Cypresvej 6
7400 Herning
Denmark.
Phone: +4596604505
Mobile: +4523694901
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:
http://lists.mysql.com/win32?unsub=lsharpe@pacificwireless.c om.au
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:
http://lists.mysql.com/win32?unsub=mni@kk-electronic.dk
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org
RE: Svar: RE: Weird performance problem.
am 07.07.2006 10:53:33 von Appeltans David
If disc I/O is a problem/bottleneck, I remember an issue I had with a
customer which ran on windows2003 server.
On this platform there is a special parameter for optimizing disc writes
actions.
We increased performance with 30% by activating this check box. It is in
My computer/manage/device manager/policies by default "enable write
caching on the disk" is activated but ... "enable advanced performance"
is deactivated by default. By activating this latter, disc I/O was much
better.
Maybe usefull for your customer.
Kind regards,
David
==================== =====3D=
============
David Appeltans =20
EAI/B2B/EDI consultant =20
Axway Belgium NV/SA=20
Direct: +32.(0)2.641.96.55
Mobile: +32.(0)496.12.21.57 =20
Recep.: +32.(0)2.641.96.60 =20
Fax: +32.(0)2.641.96.48
Webdoc: http://support.axway.com =20
Mail support: support.be@axway.com=20
==================== =====3D=
============
-----Original Message-----
From: Michael Nielsen [mailto:Mni@kk-electronic.dk]=20
Sent: 07 July 2006 10:31
To: win32
Subject: Svar: RE: Weird performance problem.
Hi,=20
Thanks for the info.
I suspect that you are correct, though it is a rather significant
problem, as the company that I am evaluating MySQL for, wishes to run it
on a windows platform.
regards
Mike.
Michael (Mike) Nielsen BE.
Cypresvej 6
7400 Herning
Denmark.
Phone: +4596604505
Mobile: +4523694901
>>> "Leigh Sharpe" 7/07/2006 8:54 am
>>>
I would suggest you look at your disk speed. I have a similar issue with
a Windows server running slower than I would expect, but I have come to
the conclusion that the bottleneck is not actually related to MySQL
itself, but rather to poor disk transfer speeds. (I'm yet to establish
whether it's the windows drivers or the hardware itself,
though.)=20
Regards,
Leigh
=20
Leigh Sharpe
Network Systems Engineer
Pacific Wireless
Ph +61 3 9584 8966
Mob 0408 009 502
email lsharpe@pacificwireless.com.au
web www.pacificwireless.com.au=20
-----Original Message-----
From: Michael Nielsen [mailto:Mni@kk-electronic.dk]
Sent: Thursday, July 06, 2006 10:23 PM
To: win32@lists.mysql.com
Subject: Weird performance problem.
I have two systems.
1. Linux Fedora 4, on a 500Mhz 256Mb Via Intel Pentium II clone.
Using gcc version 4.0.2 Mysql version 4.1.16 - default installation.
2. Windows XP prof - HP laptop 1.73Ghz Intel processor 1Gb Memory
Using Visual Studio 2005, Mysql Version 5.0.22 - default development
installation.
I obtained an insert rate of approx 74.6 inserts/second on the windows
box (barely any cpu usage - seemed like it was iobound) and a rate of
1428.57 inserts/second on the linux box, with a cpu usage of 77%.=20
I'm very curious about what could be causing the difference in speed..
I am running the following code (Note minor changes needed to run on
linux).
//NOTE - careful it will delete a database called TestData if you want
to run it.
// mysqlbench.cpp : Defines the entry point for the console application.
//
#include "stdafx.h"
#include
#include
#include
#include
#include
int isError(MYSQL *mysql_struct,int rc) {
printf("command returned (%s)\n",mysql_error(mysql_struct));
return rc;
}
int createDatabase(MYSQL *mysql_struct) {
int rc;
printf("dropping database\n");
rc =3D mysql_query(mysql_struct,"DROP DATABASE TestData");
if (isError(mysql_struct,rc)) {
printf("database not found\n");
}
printf("creating database\n");
rc =3D mysql_query(mysql_struct,"CREATE DATABASE TestData");
if (isError(mysql_struct,rc)) return -1;
printf("using database\n");
rc =3D mysql_query(mysql_struct,"USE TestData");
if (isError(mysql_struct,rc)) return -1;
printf("creating table element\n");
rc =3D mysql_query(mysql_struct,"CREATE TABLE Element ("
"ID INTEGER NOT NULL,"
"elementname VARCHAR(250) NOT NULL,"
"PRIMARY KEY (ID));");
if (isError(mysql_struct,rc)) return -1;
printf("creating table field\n");=20
rc =3D mysql_query(mysql_struct,"CREATE TABLE Field ("
"ID INTEGER NOT NULL, "
"element_ID INTEGER NOT NULL,"
"meta_ID INTEGER NOT NULL,"
"registered datetime NOT NULL,"
"unregistered datetime NULL,"
"PRIMARY KEY (ID));");
if (isError(mysql_struct, rc)) return -1;
printf("create table log\n");
rc =3D mysql_query(mysql_struct,"CREATE TABLE Log ("
"field_ID INTEGER NOT NULL,"
"timestamp datetime NOT NULL,"
"value varchar(250),"
"PRIMARY KEY (field_ID,timestamp));");
if (isError(mysql_struct,rc)) return -1;
printf("create table meta\n");
rc =3D mysql_query(mysql_struct,"CREATE TABLE meta_data ("
"ID INTEGER AUTO_INCREMENT NOT NULL,"
"name varchar(50) NOT NULL ,"
"logtype varchar(50) NOT NULL,"
"valueAsString varchar(250) NOT NULL,"
"PRIMARY KEY (ID,name));");
if (isError(mysql_struct,rc)) return -1;
return 0;
}
int _tmain(int argc, _TCHAR* argv[])
{
int delay=3D1000000;
if (argc > 1) {
sscanf_s((const char *)argv[1],"%d",&delay);
printf("%s %d\n",argv[0], delay);
}
MYSQL mysql_struct;
mysql_init(&mysql_struct);
MYSQL *ret =3D mysql_real_connect(&mysql_struct,"localhost",
"root","snip-justincase-snip","test", 0,NULL,CLIENT_MULTI_RESULTS);
if (!ret) {
printf("error db not open");
}
int rc =3D mysql_query(&mysql_struct,"show databases;");
if (isError(&mysql_struct,rc)) {
}
else{
MYSQL_RES *result;
result =3D mysql_store_result(&mysql_struct);
while (result) {
MYSQL_ROW row;
if (result) {=09
do
{ =09
row =3D
mysql_fetch_row(result);=09
if (row && row[0]) {
printf("getting row
%s\n",row[0]);
}
} while (row);
}
result =3D mysql_store_result(&mysql_struct);
}
}
if (!createDatabase(&mysql_struct)) {
}
else {
printf("create database failed\n");
}
int i=3D0;
MYSQL_STMT *stmt =3D mysql_stmt_init(&mysql_struct);
MYSQL_BIND bind[4];
const char INSERT_SAMPLE[] =3D "INSERT INTO meta_data
(ID,name,logtype,valueAsString) VALUES(?,?,?,?);";
if (mysql_stmt_prepare(stmt, INSERT_SAMPLE, (unsigned
long)strlen(INSERT_SAMPLE))){
fprintf(stderr, " mysql_stmt_prepare(), INSERT
failed\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
Sleep(10000);
return 0;
}
fprintf(stdout, " prepare, INSERT successful\n");
int id=3D0;
const int namesize=3D50;
char *name[namesize];
const int typesize=3D50;
char *logtype[typesize];
const int valuesize=3D50;
char *valueAsString[valuesize];
time_t start; // C run-time time (defined in )
time_t stop;
time( &start ) ;
for (i =3D 0; i < 10000; i++) {
printf("doing %d\r",i);
memset((char *)bind, 0, sizeof(bind));
memset((char *)name,0,sizeof(name));
memset((char *)logtype,0,sizeof(logtype));
memset(valueAsString,0,sizeof(valueAsString));
sprintf_s((char*)name,sizeof(name),"name=3D%d",i);
=09
sprintf_s((char*)logtype,sizeof(logtype),"logtype=3D%d",i); =09
=09
sprintf_s((char*)valueAsString,sizeof(valueAsString),"value= 3D%d",i);
bind[0].buffer_type=3D MYSQL_TYPE_LONG; =09
bind[0].buffer=3D&id;
bind[0].length=3D0;
bind[0].is_null=3D 0;
bind[1].buffer_type=3D MYSQL_TYPE_STRING; =09
bind[1].buffer=3Dname;
bind[1].length=3D(unsigned long*)&namesize;
bind[1].is_null=3D0;
bind[2].buffer_type=3D MYSQL_TYPE_STRING; =09
bind[2].buffer=3Dlogtype;
bind[2].length=3D(unsigned long*)&typesize;
bind[2].is_null=3D 0;
bind[3].buffer_type=3D MYSQL_TYPE_STRING; =09
bind[3].buffer=3D valueAsString;
bind[3].length=3D (unsigned long*)&valuesize;
bind[3].is_null=3D 0;
// Bind the buffers
if (mysql_stmt_bind_param(stmt, bind)){
fprintf(stderr, " mysql_stmt_bind_param()
failed\n");
fprintf(stderr, " %s\n",
mysql_stmt_error(stmt));
break;
}
//Execute the INSERT statement - 1
if (mysql_stmt_execute(stmt)) {
fprintf(stderr, " mysql_stmt_execute(), 1
failed\n");
fprintf(stderr, " %s\n",
mysql_stmt_error(stmt));
break;
}
=09
/* Get the total number of affected rows */
unsigned long affected_rows=3D(unsigned long)
mysql_stmt_affected_rows(stmt);
//fprintf(stdout, " total affected rows(insert 1):
%lu\n",
// (unsigned long) affected_rows);
if (affected_rows !=3D 1) /* validate affected rows */
{
//fprintf(stderr, " invalid affected rows by
MySQL\n");
//break;
}=09
else {
}
//sleep(delay);
}
mysql_stmt_close(stmt);
time(&stop);
printf("time taken =3D %d\n", stop-start);
printf("statements/second =3D %f\n",10000.0/(float)(stop-start));
printf("finished\n");
Sleep(10000);
return 0;
}
Michael (Mike) Nielsen BE.
Cypresvej 6
7400 Herning
Denmark.
Phone: +4596604505
Mobile: +4523694901
--=20
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32=20
To unsubscribe: =20
http://lists.mysql.com/win32?unsub=3Dlsharpe@pacificwireless .com.au=20
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32=20
To unsubscribe: =20
http://lists.mysql.com/win32?unsub=3Dmni@kk-electronic.dk=20
--=20
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:
http://lists.mysql.com/win32?unsub=3Ddappeltans@axway.com
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org
RE: Svar: RE: Weird performance problem.
am 07.07.2006 13:23:33 von Michael Nielsen
Thank you for the feedback I will look into it.
I now have two machines that does the same, the second is a dual xeon
processor machine, with scsi disks - shouldn't have
I/O problems, but it still has the very same problem :-(..
regards
michael nielsen.
Michael (Mike) Nielsen BE.
Cypresvej 6
7400 Herning
Denmark.
Phone: +4596604505
Mobile: +4523694901
>>> "Appeltans David" 7/07/2006 10:53 am >>>
If disc I/O is a problem/bottleneck, I remember an issue I had with a
customer which ran on windows2003 server.
On this platform there is a special parameter for optimizing disc
writes
actions.
We increased performance with 30% by activating this check box. It is
in
My computer/manage/device manager/policies by default "enable write
caching on the disk" is activated but ... "enable advanced
performance"
is deactivated by default. By activating this latter, disc I/O was
much
better.
Maybe usefull for your customer.
Kind regards,
David
=====================================
David Appeltans
EAI/B2B/EDI consultant
Axway Belgium NV/SA
Direct: +32.(0)2.641.96.55
Mobile: +32.(0)496.12.21.57
Recep.: +32.(0)2.641.96.60
Fax: +32.(0)2.641.96.48
Webdoc: http://support.axway.com
Mail support: support.be@axway.com
=====================================
-----Original Message-----
From: Michael Nielsen [mailto:Mni@kk-electronic.dk]
Sent: 07 July 2006 10:31
To: win32
Subject: Svar: RE: Weird performance problem.
Hi,
Thanks for the info.
I suspect that you are correct, though it is a rather significant
problem, as the company that I am evaluating MySQL for, wishes to run
it
on a windows platform.
regards
Mike.
Michael (Mike) Nielsen BE.
Cypresvej 6
7400 Herning
Denmark.
Phone: +4596604505
Mobile: +4523694901
>>> "Leigh Sharpe" 7/07/2006 8:54 am
>>>
I would suggest you look at your disk speed. I have a similar issue
with
a Windows server running slower than I would expect, but I have come
to
the conclusion that the bottleneck is not actually related to MySQL
itself, but rather to poor disk transfer speeds. (I'm yet to establish
whether it's the windows drivers or the hardware itself,
though.)
Regards,
Leigh
Leigh Sharpe
Network Systems Engineer
Pacific Wireless
Ph +61 3 9584 8966
Mob 0408 009 502
email lsharpe@pacificwireless.com.au
web www.pacificwireless.com.au
-----Original Message-----
From: Michael Nielsen [mailto:Mni@kk-electronic.dk]
Sent: Thursday, July 06, 2006 10:23 PM
To: win32@lists.mysql.com
Subject: Weird performance problem.
I have two systems.
1. Linux Fedora 4, on a 500Mhz 256Mb Via Intel Pentium II clone.
Using gcc version 4.0.2 Mysql version 4.1.16 - default
installation.
2. Windows XP prof - HP laptop 1.73Ghz Intel processor 1Gb Memory
Using Visual Studio 2005, Mysql Version 5.0.22 - default
development
installation.
I obtained an insert rate of approx 74.6 inserts/second on the
windows
box (barely any cpu usage - seemed like it was iobound) and a rate of
1428.57 inserts/second on the linux box, with a cpu usage of 77%.
I'm very curious about what could be causing the difference in speed..
I am running the following code (Note minor changes needed to run on
linux).
//NOTE - careful it will delete a database called TestData if you want
to run it.
// mysqlbench.cpp : Defines the entry point for the console
application.
//
#include "stdafx.h"
#include
#include
#include
#include
#include
int isError(MYSQL *mysql_struct,int rc) {
printf("command returned (%s)\n",mysql_error(mysql_struct));
return rc;
}
int createDatabase(MYSQL *mysql_struct) {
int rc;
printf("dropping database\n");
rc = mysql_query(mysql_struct,"DROP DATABASE TestData");
if (isError(mysql_struct,rc)) {
printf("database not found\n");
}
printf("creating database\n");
rc = mysql_query(mysql_struct,"CREATE DATABASE TestData");
if (isError(mysql_struct,rc)) return -1;
printf("using database\n");
rc = mysql_query(mysql_struct,"USE TestData");
if (isError(mysql_struct,rc)) return -1;
printf("creating table element\n");
rc = mysql_query(mysql_struct,"CREATE TABLE Element ("
"ID INTEGER NOT NULL,"
"elementname VARCHAR(250) NOT NULL,"
"PRIMARY KEY (ID));");
if (isError(mysql_struct,rc)) return -1;
printf("creating table field\n");
rc = mysql_query(mysql_struct,"CREATE TABLE Field ("
"ID INTEGER NOT NULL, "
"element_ID INTEGER NOT NULL,"
"meta_ID INTEGER NOT NULL,"
"registered datetime NOT NULL,"
"unregistered datetime NULL,"
"PRIMARY KEY (ID));");
if (isError(mysql_struct, rc)) return -1;
printf("create table log\n");
rc = mysql_query(mysql_struct,"CREATE TABLE Log ("
"field_ID INTEGER NOT NULL,"
"timestamp datetime NOT NULL,"
"value varchar(250),"
"PRIMARY KEY (field_ID,timestamp));");
if (isError(mysql_struct,rc)) return -1;
printf("create table meta\n");
rc = mysql_query(mysql_struct,"CREATE TABLE meta_data ("
"ID INTEGER AUTO_INCREMENT NOT NULL,"
"name varchar(50) NOT NULL ,"
"logtype varchar(50) NOT NULL,"
"valueAsString varchar(250) NOT NULL,"
"PRIMARY KEY (ID,name));");
if (isError(mysql_struct,rc)) return -1;
return 0;
}
int _tmain(int argc, _TCHAR* argv[])
{
int delay=1000000;
if (argc > 1) {
sscanf_s((const char *)argv[1],"%d",&delay);
printf("%s %d\n",argv[0], delay);
}
MYSQL mysql_struct;
mysql_init(&mysql_struct);
MYSQL *ret = mysql_real_connect(&mysql_struct,"localhost",
"root","snip-justincase-snip","test", 0,NULL,CLIENT_MULTI_RESULTS);
if (!ret) {
printf("error db not open");
}
int rc = mysql_query(&mysql_struct,"show databases;");
if (isError(&mysql_struct,rc)) {
}
else{
MYSQL_RES *result;
result = mysql_store_result(&mysql_struct);
while (result) {
MYSQL_ROW row;
if (result) {
do
{
row =
mysql_fetch_row(result);
if (row && row[0]) {
printf("getting row
%s\n",row[0]);
}
} while (row);
}
result = mysql_store_result(&mysql_struct);
}
}
if (!createDatabase(&mysql_struct)) {
}
else {
printf("create database failed\n");
}
int i=0;
MYSQL_STMT *stmt = mysql_stmt_init(&mysql_struct);
MYSQL_BIND bind[4];
const char INSERT_SAMPLE[] = "INSERT INTO meta_data
(ID,name,logtype,valueAsString) VALUES(?,?,?,?);";
if (mysql_stmt_prepare(stmt, INSERT_SAMPLE, (unsigned
long)strlen(INSERT_SAMPLE))){
fprintf(stderr, " mysql_stmt_prepare(), INSERT
failed\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
Sleep(10000);
return 0;
}
fprintf(stdout, " prepare, INSERT successful\n");
int id=0;
const int namesize=50;
char *name[namesize];
const int typesize=50;
char *logtype[typesize];
const int valuesize=50;
char *valueAsString[valuesize];
time_t start; // C run-time time (defined in )
time_t stop;
time( &start ) ;
for (i = 0; i < 10000; i++) {
printf("doing %d\r",i);
memset((char *)bind, 0, sizeof(bind));
memset((char *)name,0,sizeof(name));
memset((char *)logtype,0,sizeof(logtype));
memset(valueAsString,0,sizeof(valueAsString));
sprintf_s((char*)name,sizeof(name),"name=%d",i);
sprintf_s((char*)logtype,sizeof(logtype),"logtype=%d",i);
sprintf_s((char*)valueAsString,sizeof(valueAsString),"value= %d",i);
bind[0].buffer_type= MYSQL_TYPE_LONG;
bind[0].buffer=&id;
bind[0].length=0;
bind[0].is_null= 0;
bind[1].buffer_type= MYSQL_TYPE_STRING;
bind[1].buffer=name;
bind[1].length=(unsigned long*)&namesize;
bind[1].is_null=0;
bind[2].buffer_type= MYSQL_TYPE_STRING;
bind[2].buffer=logtype;
bind[2].length=(unsigned long*)&typesize;
bind[2].is_null= 0;
bind[3].buffer_type= MYSQL_TYPE_STRING;
bind[3].buffer= valueAsString;
bind[3].length= (unsigned long*)&valuesize;
bind[3].is_null= 0;
// Bind the buffers
if (mysql_stmt_bind_param(stmt, bind)){
fprintf(stderr, " mysql_stmt_bind_param()
failed\n");
fprintf(stderr, " %s\n",
mysql_stmt_error(stmt));
break;
}
//Execute the INSERT statement - 1
if (mysql_stmt_execute(stmt)) {
fprintf(stderr, " mysql_stmt_execute(), 1
failed\n");
fprintf(stderr, " %s\n",
mysql_stmt_error(stmt));
break;
}
/* Get the total number of affected rows */
unsigned long affected_rows=(unsigned long)
mysql_stmt_affected_rows(stmt);
//fprintf(stdout, " total affected rows(insert 1):
%lu\n",
// (unsigned long) affected_rows);
if (affected_rows != 1) /* validate affected rows */
{
//fprintf(stderr, " invalid affected rows by
MySQL\n");
//break;
}
else {
}
//sleep(delay);
}
mysql_stmt_close(stmt);
time(&stop);
printf("time taken = %d\n", stop-start);
printf("statements/second = %f\n",10000.0/(float)(stop-start));
printf("finished\n");
Sleep(10000);
return 0;
}
Michael (Mike) Nielsen BE.
Cypresvej 6
7400 Herning
Denmark.
Phone: +4596604505
Mobile: +4523694901
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:
http://lists.mysql.com/win32?unsub=lsharpe@pacificwireless.c om.au
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:
http://lists.mysql.com/win32?unsub=mni@kk-electronic.dk
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:
http://lists.mysql.com/win32?unsub=dappeltans@axway.com
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:
http://lists.mysql.com/win32?unsub=mni@kk-electronic.dk
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org