Re: Bug 9064 Not Quite Fixed (3.1.10)?
Posted by: Mark Matthews
Date: July 26, 2005 11:20AM

Jason Winnebeck wrote:
> In the bug comment you mentioned memory leak
> issues. I can possibly think of a case where you
> want to store only part of a string, but with
> substring you will always keep the much larger
> string in memory, so if you want to force a
> compaction, you must use toCharArray. Is that
> what you are thinking about when you say memory
> leak? If not, I am interested to know as to avoid
> similar things in my applications.
>
> I only checked on this out of curiousity because
> of the time it takes to prepare. One 3rd party I
> used had a "bug" where a debugging string
> generation was left in but unused, and was in a
> tight loop. Removing the code increased
> performance by something like 90%, which
> represented something around 25-50% of app time (I
> know, an extreme case -- but as this was for an
> embedded Java system it was impressive). My
> motivation of examining the code was simply to see
> if the call actually reduced performance a bit or
> was more superficial in terms of actual gains.
>
> We prepare a whole load of statements at the start
> of our application, because we have a large number
> of variations of where clauses. One thing why
> this probably does not matter is because of the
> round-trip time to server to prepare the statement
> is too large compared to any time spent processing
> on the client (I assume preparing causes network
> communication?). The problem is amplified by the
> fact that we have multiple connections.
>
> This is no huge deal, but with the new MySQL it
> takes maybe 5 to 10 seconds for our app to start
> now, most of that time spent in serverPrepare().
> We didn't notice this on MySQL 4.0 and 3.0
> connector because everything was done client side.
> We realize now the problem is important because
> of the load on the server to hold so many
> prepares. We've had three ideas that we haven't
> gotten around to yet:
>
> * Combine multiple SQL into one SQL, and set where
> clauses that are unused to some "identity"
> condition that picks all rows, and put all
> possible wheres into a single statement.
> * Many statements are never used on a connection.
> We can do a lazy prepare of the statements.
> * Change our pooling from connection pooling to
> statement pooling. That is, we "check out"
> statemetns rather than connections, to reduce the
> amount of prepares.
>

Jason,

The code in PreparedStatement.ParseInfo isn't used when you server-side prepare, so you're running into network latency issues, not anything to do with how well the constructor to ParseInfo() is implemented.

Are you using a custom connection pool? Most "standard" ones will do statement pooling for you.

-Mark

Mark Matthews
Consulting Member Technical Staff - MySQL Enterprise Tools
Oracle
http://www.mysql.com/products/enterprise/monitor.html

Options: ReplyQuote


Subject
Written By
Posted
Re: Bug 9064 Not Quite Fixed (3.1.10)?
July 26, 2005 11:20AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.