-----Original Message----- From: Michael Nielsen [mailto:Mn...@kk-electronic.dk] Sent: Thursday, July 06, 2006 10:23 PM To: win...@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 <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