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" <[email protected]> 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 [email protected] 
web www.pacificwireless.com.au 

-----Original Message-----
From: Michael Nielsen [mailto:[email protected]] 
Sent: Thursday, July 06, 2006 10:23 PM
To: [email protected] 
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 <windows.h>
#include <winsock.h>
#include <mysql.h>
#include <mysql_com.h> 
#include <time.h>


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.h>)
	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/[email protected] 



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32 
To unsubscribe:   
http://lists.mysql.com/[email protected] 



-- 
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:    http://lists.mysql.com/[email protected]