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]