Terrible performance on Win 2003 server with InnoDB

--------------090505050000050506060307
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

We have a MySQL v4.1.12a database installed on a Windows 2003 server and
it is incredibly slow - the application is effectively unusable.

The server has 2Gb of RAM and the database is pretty small (currently
around 10Mb) and all the tables are InnoDB as transactions are to be
used. The application (PHP based) runs very fast when the database
server is a Linux machine but the client is insisting on Windows. I'm
not much of a database guru so can anyone point me in the right
direction here? The relevant Innodb entries from the my.ini file are
shown below...

innodb_data_home_dir="D:/MySQL Datafiles/"
#skip-innodb
innodb_additional_mem_pool_size=22M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=11M
innodb_buffer_pool_size=1G
innodb_log_file_size=190M
innodb_thread_concurrency=8

TIA
rich


--------------090505050000050506060307--

RE: Terrible performance on Win 2003 server with InnoDB

I doubt it's MySQL but it might be. I would start looking at the OS
first. The biggest thing I can think of now is your Antivirus which
could be scanning everything MySQL is doing.

What else do you have on this server? How much memory is free (2g is
easy to use up with Microsoft Bloat like ISS and the like.) What is
your CPU usage (when and when not accessing the database)? Have you
checked for firewall settings, etc?

Finally, have you checked your permissions, etc on the database? I'd be
particularly interested in the limit settings like the number of
connections per second, etc.


-----Original Message-----
From: rich [mailto:rich@richgray.com]
Sent: Monday, August 15, 2005 12:04 PM
To: win32@lists.mysql.com
Subject: Terrible performance on Win 2003 server with InnoDB

We have a MySQL v4.1.12a database installed on a Windows 2003 server and

it is incredibly slow - the application is effectively unusable.

The server has 2Gb of RAM and the database is pretty small (currently
around 10Mb) and all the tables are InnoDB as transactions are to be
used. The application (PHP based) runs very fast when the database
server is a Linux machine but the client is insisting on Windows. I'm
not much of a database guru so can anyone point me in the right
direction here? The relevant Innodb entries from the my.ini file are
shown below...

innodb_data_home_dir="D:/MySQL Datafiles/"
#skip-innodb
innodb_additional_mem_pool_size=22M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=11M
innodb_buffer_pool_size=1G
innodb_log_file_size=190M
innodb_thread_concurrency=8

TIA
rich


--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.10.8/71 - Release Date: 8/12/2005


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.10.8/71 - Release Date: 8/12/2005



--
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: Terrible performance on Win 2003 server with InnoDB

Here's where I would start my troubleshooting...

How'd you get the DB from Linux to Windows? Are you sure your indices made
it? How'd the transactions get from Linux to Windows? Do you only observe
slowness with transactions? Have you checked the Windows drive for fragging?

-jgl

At 11:03 AM 8/15/2005, rich wrote:
>We have a MySQL v4.1.12a database installed on a Windows 2003 server and
>it is incredibly slow - the application is effectively unusable.
>
>The server has 2Gb of RAM and the database is pretty small (currently
>around 10Mb) and all the tables are InnoDB as transactions are to be used.
>The application (PHP based) runs very fast when the database server is a
>Linux machine but the client is insisting on Windows. I'm not much of a
>database guru so can anyone point me in the right direction here? The
>relevant Innodb entries from the my.ini file are shown below...
>
>innodb_data_home_dir="D:/MySQL Datafiles/"
>#skip-innodb
>innodb_additional_mem_pool_size=22M
>innodb_flush_log_at_trx_commit=1
>innodb_log_buffer_size=11M
>innodb_buffer_pool_size=1G
>innodb_log_file_size=190M
>innodb_thread_concurrency=8
>
>TIA
>rich
>

- Jonathan Lampe
- jonathan.lampe@standardnetworks.com

******************* PLEASE NOTE *******************

This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed.
If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited.


--
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: Terrible performance on Win 2003 server with InnoDB

If it is terrible performance re. clients establishing connections to the
server, it may be a DNS resolution issue. Try applying the

--skip-name-resolve

option to your MySql ini file and restart the MySql instance.

-----Original Message-----
From: rich@richgray.com [mailto:rich@richgray.com]
Sent: 15 August 2005 17:04
To: win32@lists.mysql.com
Subject: Terrible performance on Win 2003 server with InnoDB

We have a MySQL v4.1.12a database installed on a Windows 2003 server and
it is incredibly slow - the application is effectively unusable.

The server has 2Gb of RAM and the database is pretty small (currently
around 10Mb) and all the tables are InnoDB as transactions are to be used.
The application (PHP based) runs very fast when the database server is a
Linux machine but the client is insisting on Windows. I'm not much of a
database guru so can anyone point me in the right direction here? The
relevant Innodb entries from the my.ini file are shown below...

innodb_data_home_dir="D:/MySQL Datafiles/"
#skip-innodb
innodb_additional_mem_pool_size=22M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=11M
innodb_buffer_pool_size=1G
innodb_log_file_size=190M
innodb_thread_concurrency=8

TIA
rich


--
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: Terrible performance on Win 2003 server with InnoDB

--------------000700000705000402060805
Content-Type: text/plain; charset=windows-1250; format=flowed
Content-Transfer-Encoding: 7bit

Hi Jeremy
Thanks for your reply.
The server is a dedicated DB server and there is no A/V running as far
as I can tell however I will check with the sysadmin guy. The server is
not running IIS. Running Task Manager shows the page file running at a
steady 1.43Gb - showing the running processes has mysql-nt .exe at the
top of the list with 84,400K of memory usage. The CPUs 2 x Intel Xeon
3.4Ghz are basically idle around 2% usage I guess. The sysadmin has
verfied that the network link between the web server and this server is
OK they are on a LAN connection...
When you say permissions limit connections per second on the database? I
have checked the Windows permissions and they seem OK but where do I
find the connection limit on the database?
Thanks again
Rich

Jeremy wrote:

>I doubt it's MySQL but it might be. I would start looking at the OS
>first. The biggest thing I can think of now is your Antivirus which
>could be scanning everything MySQL is doing.
>
>What else do you have on this server? How much memory is free (2g is
>easy to use up with Microsoft Bloat like ISS and the like.) What is
>your CPU usage (when and when not accessing the database)? Have you
>checked for firewall settings, etc?
>
>Finally, have you checked your permissions, etc on the database? I'd be
>particularly interested in the limit settings like the number of
>connections per second, etc.
>
>
>


--------------000700000705000402060805--

Re: Terrible performance on Win 2003 server with InnoDB

I sincerely believe its an OS issue, if you tested it with a Linux
server and it worked fast, then when using Windows, it should perform
good as well, as Jonathan said, check if the conversion made it
between the OSs, rebuild all indexes (I usually do a complete check
and repair when experimenting slow responses in Windows MySQL server).

Check also if the Windows settings are not blocking MySQL in any way,
and try other servers (mysqld-opt, mysqld, mysqld-safe) to see if its
a version problem.

I have MySQL server 4.1.11 running at an Athlon XP 1.7 with 256 RAM
and a 20GB disk (not the best server in the world) and its running
smooth feeding many clients with thousands of daily connections (and
I'm using it right now too). So, IMHO its the config of your server
that is making it slow.

--=20
Daniel da Veiga
Computer Operator - RS - Brazil
-----BEGIN GEEK CODE BLOCK-----
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
------END GEEK CODE BLOCK------

--
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: Terrible performance on Win 2003 server with InnoDB

--------------020500020701060200050504
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Tim
Thanks - putting this into the my.ini did make an appreciable
improvement in speed.
Regards
Rich

Tim.Lloyd@biomni.com wrote:

>If it is terrible performance re. clients establishing connections to the
>server, it may be a DNS resolution issue. Try applying the
>
>--skip-name-resolve
>
>option to your MySql ini file and restart the MySql instance.
>
>-----Original Message-----
>From: rich@richgray.com [mailto:rich@richgray.com]
>Sent: 15 August 2005 17:04
>To: win32@lists.mysql.com
>Subject: Terrible performance on Win 2003 server with InnoDB
>
>We have a MySQL v4.1.12a database installed on a Windows 2003 server and
>it is incredibly slow - the application is effectively unusable.
>
>The server has 2Gb of RAM and the database is pretty small (currently
>around 10Mb) and all the tables are InnoDB as transactions are to be used.
>The application (PHP based) runs very fast when the database server is a
>Linux machine but the client is insisting on Windows. I'm not much of a
>database guru so can anyone point me in the right direction here? The
>relevant Innodb entries from the my.ini file are shown below...
>
>innodb_data_home_dir="D:/MySQL Datafiles/"
>#skip-innodb
>innodb_additional_mem_pool_size=22M
>innodb_flush_log_at_trx_commit=1
>innodb_log_buffer_size=11M
>innodb_buffer_pool_size=1G
>innodb_log_file_size=190M
>innodb_thread_concurrency=8
>
>TIA
>rich
>
>
>
>


--------------020500020701060200050504--

RE: Terrible performance on Win 2003 server with InnoDB

Rich,

If you need to have name resolution for your client connections, then I
would suggest getting to grips with DNS issues with your sysasdmin. If you
have MySql running on a multi-homed server (i.e. it has more than one IP
address), then you may have to look into binding the MySql server instance
to a specific IP address using the

--bind-address

option.

Thanks, Tim.

-----Original Message-----
From: rich@richgray.com [mailto:rich@richgray.com]
Sent: 15 August 2005 18:14
To: Tim Lloyd
Cc: win32@lists.mysql.com
Subject: Re: Terrible performance on Win 2003 server with InnoDB

Tim
Thanks - putting this into the my.ini did make an appreciable improvement
in speed.
Regards
Rich

Tim.Lloyd@biomni.com wrote:

>If it is terrible performance re. clients establishing connections to
>the server, it may be a DNS resolution issue. Try applying the
>
>--skip-name-resolve
>
>option to your MySql ini file and restart the MySql instance.
>
>-----Original Message-----
>From: rich@richgray.com [mailto:rich@richgray.com]
>Sent: 15 August 2005 17:04
>To: win32@lists.mysql.com
>Subject: Terrible performance on Win 2003 server with InnoDB
>
>We have a MySQL v4.1.12a database installed on a Windows 2003 server
>and it is incredibly slow - the application is effectively unusable.
>
>The server has 2Gb of RAM and the database is pretty small (currently
>around 10Mb) and all the tables are InnoDB as transactions are to be
used.
>The application (PHP based) runs very fast when the database server is
>a Linux machine but the client is insisting on Windows. I'm not much of
>a database guru so can anyone point me in the right direction here? The
>relevant Innodb entries from the my.ini file are shown below...
>
>innodb_data_home_dir="D:/MySQL Datafiles/"
>#skip-innodb
>innodb_additional_mem_pool_size=22M
>innodb_flush_log_at_trx_commit=1
>innodb_log_buffer_size=11M
>innodb_buffer_pool_size=1G
>innodb_log_file_size=190M
>innodb_thread_concurrency=8
>
>TIA
>rich
>
>
>
>


--
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