Hello.
On Thu 2003-02-27 at 14:52:56 -0800, [EMAIL PROTECTED] wrote:
[...]
> Anyways, I'm running into a little bit of a performance issue as the old
> database report writer had direct access to the database through a c library
> (no sql interface). On some reports there can be as many as 100,000 select
> statements. With mysql this is ending up with a performance penalty of
> about 3x the existing time that the current report writer takes.
>
> Running Intel's vtune I can see that the select statements (through
> mysql_query)are taking up around 90% of the run time.
[...]
> Anyways, I'm not sure if there is any kind of change I can make to reduce
> this sql statement penalty and was hoping someone here could possibly help
> reduce it.
First, let me clarify, that the perceived speed loss is less with the
SQL statements per se, but with: build query -> send -> task switching
to server process -> read query -> parse -> optimizer -> read data and
build result -> send result -> task switching to client process ->
read result. That's of course not complete, but you get the idea. (You
can avoid some of the latency by running queries in parallel.)
IMHO, there is not much you can do about it. You switched from a
specialized database interface to one that is intended for general,
rational storage. A general approach is always slower than a
specialized one, if both are of the same quality.
There are some things you can try to get more speed, but when have
implemented so much of them that you are at the old speed, you will
have a similar specialized solution as you had before. If you say you
are fine with the specialized solution, I wonder why you changed to an
RDBMS to begin with (you could have taken, e.g. BDB). If you are not,
I fear you have to live with some speed loss.
That said, I suggest you take a look at (and benchmark for your
application):
- UNIX sockets, make sure to use them if you can,
- your queries: can you combine some of the 100.000 statements?
(oh, see you answered that below),
- Using MySQL 4.0 to take advantage of the new query cache
(you have to enable it explicitly),
- threading (client-side), maybe you can run stuff in parallel,
- the HANDLER commands which bypass some abstraction layers and
- libmysqld, the embedded library, which bypasses the connection
overhead.
I am sure there is more, but that is what came to mind currently.
[...]
> I have a feeling it's the overhead with every query that's really
> the problem here and that there really is no fix.
That's right.
> I also can't really combine the sql statements and save the data for
> later due to the unique format of the reports. But perhaps there
> are some optimizations I can make to help.
HTH,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
