RE: Svar: RE: Weird performance problem.

"Michael Nielsen" <[email protected]>
Newsgroups gmane.comp.db.mysql.windows
Message-ID <[email protected]> 
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" <[email protected]> 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: [email protected] 
=====================================



-----Original Message-----
From: Michael Nielsen [mailto:[email protected]] 
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" <[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] 


--
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]
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.