Hi Sacha,
Thx a lot for your reply
you wrote:
>in this case, I suspect, is that the disconnect happens in the
pre-data-sending
>stage, which is possibly quite lengthy.
Exactly ! It takes hours till these results are erased and i even don't know
where the period when they are erased come from.
I still hope this is set in a server-variable.
>I would also check to see if there might be some simple fixes
>, such as adding appropriate keys to the tables, that would optimize the
slow
>queries.
The problem is, that i can't prevent queries like
SELECT * FROM <TABLE> WHERE <COLUMN> LIKE 'a%'
where an index will not help on a Table > 100.000.000 records.
It's not that we have to answer hese queries (therefore the timeout), but
it's impossible to prevent dumm users.
>The solution I would propose is to maintain a separate connection on which
the KILL
>commands will be sent for the long queries.
I did this (by reading processlist and kiliing any process that
lasts longer then 20 seconds) and it works fine, but it is not such a
'proper' solution.
I still hope there is a way to get that by setting up the server.
Thx a lot again
Klaus
----Ursprüngliche Nachricht-----
Von: Sasha Pachev [mailto:[EMAIL PROTECTED]
Gesendet: Dienstag, 17. Februar 2004 14:19
An: Franz, Fa. PostDirekt MA
Cc: Stefan Hinz; [EMAIL PROTECTED]
Betreff: Re: AW: Pending results blocking mysql-server
Franz, Fa. PostDirekt MA wrote:
> Hi Stefan,
>
> thanks for your fast reply !
>
>
>>>Would it be possible to call mysql_free_result() from your
>>>application? You might be able to set a "timer" in your app, calling
>>>that function after, for example, 20 seconds.
>
>
> I am not the of developer this programm ...
> But i'm sure I can convince him if it helps :0)
> I read about mysql_free_result() already, but i thought it would
> clear the local buffer for an already fetched result.
> Are you sure it erases a pending result on the server?
mysql_free_result() frees only the client memory. However, a disconnect on
the
socket will tell the server to stop sending the data and clean up. The
problem
in this case, I suspect, is that the disconnect happens in the
pre-data-sending
stage, which is possibly quite lengthy. The solution I would propose is to
maintain a separate connection on which the KILL commands will be sent for
the
long queries. And, I would also check to see if there might be some simple
fixes
, such as adding appropriate keys to the tables, that would optimize the
slow
queries.
--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
