List:General Discussion« Previous MessageNext Message »
From:Larry Lowry Date:January 23 2005 3:25am
Subject:Re: Connection performance, suggestions?
View as plain text  
Actually I was just trying to see why the same code to mySql is
so much slower than SQL Server.  I tried the same code using the
data provider from CoreLabs and found the connections occur
4 times faster than the mySQL AB connector.  The slowness
just seems to be in the connector.

Now I know my method seems silly but to my boss it makes
sense.  We have web sites that make these connections.  They
are obviously stateless and each makes it's own connection.
Where it takes 22 seconds to connect to mySql 100 times I can
in the same loop execute a select statement and bring back a
700k binary field adding only abour .5 seconds to the whole
loop time.  Half a second to dig into a database with 400k
records and yank the record 100 times but 22seconds just to
open and close the conection.  Something just does not
seem right.

Thanks for your input.

Larry Lowry





----- Original Message ----- 
From: "Peter J Milanese" <PMilanese@stripped>
To: "Larry Lowry" <Larry.Lowry@stripped>
Sent: Saturday, January 22, 2005 10:22 AM
Subject: Re: Connection performance, suggestions?


You are not simulating. Your scripting produces 100 linear connections. You 
are attempting, by theory, to simulate simultaneous connections. Big 
difference.



-----------------
Sent from my NYPL BlackBerry Handheld.


----- Original Message -----
From: "Larry Lowry" [Larry.Lowry@stripped]
Sent: 01/22/2005 10:36 AM
To: "Peter J Milanese" <PMilanese@stripped>
Subject: Re: Connection performance, suggestions?


----- Original Message ----- 
From: "Peter J Milanese" <PMilanese@stripped>
To: "Larry Lowry" <Larry.Lowry@stripped>; "mysql" <mysql@stripped>
Sent: Friday, January 21, 2005 12:57 PM
Subject: Re: Connection performance, suggestions?


>A single transaction logs into the db several times?
Not really. I am simulating the connection process of a bunch of
browser based/web services transactions.
>
> I assume its a browser  based transaction, no?
Correct.
>
> Are you limiting connections (in my.conf)? Have you tuned the config, if
> yes how so?

Here is my My.INI.

[mysqld]
basedir=C:/MySql
datadir=D:/MySqlData
language=C:/MySql/share/english
port=3306
key_buffer_size=512M
table_cache=64
net_buffer_length=1M
max_allowed_packet=3M
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
read_buffer_size=2M
read_rnd_buffer_size=8M
skip-innodb

Larry


>
>
> ------Original Message------
> From: Larry Lowry
> To: mysql
> Sent: Jan 21, 2005 12:49 PM
> Subject: Connection performance, suggestions?
>
> Before I ask this question I must state I love mySql and want to use it
> more.
> But I work in a shop were we mostly use SQL Server 2000.  In trying to use
> mySql (4.12 and 4.19) we are seeing some performance issues.  I hope
> someone can help me with this.  I am in a Windows 2003 server environment
> and
> running mostly asp.net applications.
>
> I have narrowed the problem down to the speed at which the database
> connections
> open and close.   The following code opens a mySql database 100 times.
> This takes 21 to 23 seconds.  I know I should only open the connection 
> once
> but this represents the asp environment where we are using a cluster of 
> web
> servers.  MySQL is running on an Athlon 64 3500+ with 2 gigs of memory. It
> is the
> only process on the server.
>
> The SQL server code below does the same thing except to Sql Server 2000.
> This
> only takes .1 to .4 seconds.   Sql Server is running on a PIII at 1.2ghz
> with
> 1gb memory.  It's a crappy old box for testing.
>
> I am using the stock Framework 1.1 SqlClient and MySql.Data.MySqlClient
> version  1.0.3.31712.  All test boxes are on the same network switch.
>
> Can anyone help explain this difference?  Is it the database engines or 
> the
> data connectors? Any help would be appreciated.
>
> Thanks
>
> Larry Lowry
>
>
> 'MySql Code
> Dim sDBCS As String
> = "Server=DB;UserId=userid;Password=pass;Database=images"
> Dim i As Long
> Dim ti As Long = Microsoft.VisualBasic.Timer()
>
> Dim db As MySqlConnection
> db = New MySqlConnection(sDBCS)
> For i = 1 To 100
> db.Open()
> db.Close()
> Next
> db.Dispose()
> tbEnd.Text = Microsoft.VisualBasic.Timer() - ti
>
>
> 'SQL Server
> Dim sDBCS As String = "Data Source=db;User Id=userid;Password=pass;Initial
> Catalog=images"
> Dim i As Long
> Dim ti As Long = Microsoft.VisualBasic.Timer()
>
> Dim db As SqlConnection
> db = New SqlConnection(sDBCS)
> For i = 1 To 100
> db.Open()
> db.Close()
> Next
> db.Dispose()
> tbEnd.Text = Microsoft.VisualBasic.Timer() - ti
>
>
> -----------------
> Sent from my NYPL BlackBerry Handheld.
>
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
>


Thread
Connection performance, suggestions?Larry Lowry21 Jan
Re: Connection performance, suggestions?Peter J Milanese21 Jan
  • Re: Connection performance, suggestions?Larry Lowry21 Jan
Re: Connection performance, suggestions?Larry Lowry23 Jan
  • RE: Connection performance, suggestions?Donny Simonton23 Jan
Re: Connection performance, suggestions?Larry Lowry24 Jan
  • Re: Connection performance, suggestions?Dan Nelson24 Jan