Hi,
Our RT3 system is a big and it crashes time to time. Today we found one
query in the slow queries log which killed the system:
SELECT main.* FROM Transactions main WHERE (main.Type = ‘Create’ OR
main.Type = ‘Comment’ OR main.Type = ‘Correspond’) ORDER BY main.Created
ASC, main.id ASC;
Is there a way to reduce a number of returning records on the application
level?
Thanks in advance.
Cheers,
Michael
Hi,
Our RT3 system is a big and it crashes time to time. Today we found one
query in the slow queries log which killed the system:
*Query_time: 97 Lock_time: 0 Rows_sent: 15906633 Rows_examined: 39069181
SELECT main.* FROM Transactions main WHERE (main.Type = ‘Create’ OR
main.Type = ‘Comment’ OR main.Type = ‘Correspond’) ORDER BY main.Created
ASC, main.id ASC;
*
Is there a way to reduce a number of returning records on the application
level?
That doesn’t looks like a standard RT query, but you haven’t provided
your version. You’ll probably want to turn on the StatementLog option
and figure out where that query is coming from so you can fix the
source.
-kevin
I use RT 3.8.2On Fri, Sep 16, 2011 at 6:02 PM, Kevin Falcone falcone@bestpractical.comwrote:
On Fri, Sep 16, 2011 at 05:08:24PM +0300, Michael Stepanov wrote:
Hi,
Our RT3 system is a big and it crashes time to time. Today we found one
query in the slow queries log which killed the system:
*Query_time: 97 Lock_time: 0 Rows_sent: 15906633 Rows_examined:
39069181
SELECT main.* FROM Transactions main WHERE (main.Type = ‘Create’ OR
main.Type = ‘Comment’ OR main.Type = ‘Correspond’) ORDER BY main.Created
ASC, main.id ASC;
*
Is there a way to reduce a number of returning records on the application
level?That doesn’t looks like a standard RT query, but you haven’t provided
your version. You’ll probably want to turn on the StatementLog option
and figure out where that query is coming from so you can fix the
source.-kevin
RT Training Sessions (http://bestpractical.com/services/training.html)
- Chicago, IL, USA — September 26 & 27, 2011
- San Francisco, CA, USA — October 18 & 19, 2011
- Washington DC, USA — October 31 & November 1, 2011
- Melbourne VIC, Australia — November 28 & 29, 2011
- Barcelona, Spain — November 28 & 29, 2011
Cheers,
Michael
I use RT 3.8.2
That still doesn’t look like a standard RT query.
You’ll need to move on to my other suggestion.
your version. You'll probably want to turn on the StatementLog option and figure out where that query is coming from so you can fix the source.
-kevin> On Fri, Sep 16, 2011 at 6:02 PM, Kevin Falcone <[1]falcone@bestpractical.com> wrote:
On Fri, Sep 16, 2011 at 05:08:24PM +0300, Michael Stepanov wrote: > Hi, > > Our RT3 system is a big and it crashes time to time. Today we found one > query in the slow queries log which killed the system: > * > # Query_time: 97 Lock_time: 0 Rows_sent: 15906633 Rows_examined: 39069181 > SELECT main.* FROM Transactions main WHERE (main.Type = 'Create' OR > main.Type = 'Comment' OR main.Type = 'Correspond') ORDER BY main.Created > ASC, [2]main.id ASC; > * > Is there a way to reduce a number of returning records on the application > level? That doesn't looks like a standard RT query, but you haven't provided your version. You'll probably want to turn on the StatementLog option and figure out where that query is coming from so you can fix the source.
From: “Kevin Falcone” falcone@bestpractical.com
That doesn’t looks like a standard RT query, but you haven’t provided
your version. You’ll probably want to turn on the StatementLog option
and figure out where that query is coming from so you can fix the
source.
Which makes you a good person for me to bounce a SQL idea off of:
For the purpose of tracing queries back into code, how about something like:
“add to the end of each query a clause like AND NOT Todays_Date = ‘1000-08-22’
(using the appropriate SQL syntax, which I forget)… using the date as an
‘address’ for the query in the code (program, module, query, or something
similar.”
The entire clause will be statically true, and get optimized off, but will
still show up in the slow-query log, and in mytop/mtop, etc… giving you
a handle to get back to the code.
Is this possible? Practical?
Cheers,
– jr ‘Ashworth’s Device’ a
Jay R. Ashworth Baylink jra@baylink.com
Designer The Things I Think RFC 2100
Ashworth & Associates http://baylink.pitas.com 2000 Land Rover DII
St Petersburg FL USA http://photo.imageinc.us +1 727 647 1274
----- Original Message -----
From: “Kevin Falcone” falcone@bestpractical.com
That doesn’t looks like a standard RT query, but you haven’t provided
your version. You’ll probably want to turn on the StatementLog option
and figure out where that query is coming from so you can fix the
source.Which makes you a good person for me to bounce a SQL idea off of:
For the purpose of tracing queries back into code, how about something like:
“add to the end of each query a clause like AND NOT Todays_Date = ‘1000-08-22’
(using the appropriate SQL syntax, which I forget)… using the date as an
‘address’ for the query in the code (program, module, query, or something
similar.”The entire clause will be statically true, and get optimized off, but will
still show up in the slow-query log, and in mytop/mtop, etc… giving you
a handle to get back to the code.Is this possible? Practical?
This destroys mysql’s query cache, which is a very nice performance win.
On RT4 you can enable the statement log and see every query run
on a page with pointers into the source that executed it.
-kevin
From: “Kevin Falcone” falcone@bestpractical.com
This destroys mysql’s query cache, which is a very nice performance
win.
Oh hell; of course.
On RT4 you can enable the statement log and see every query run
on a page with pointers into the source that executed it.
That doesn’t help, though, on a large realtime system, and especially
not on code that isn’t RT. 
Cheers,
– jra
Jay R. Ashworth Baylink jra@baylink.com
Designer The Things I Think RFC 2100
Ashworth & Associates http://baylink.pitas.com 2000 Land Rover DII
St Petersburg FL USA http://photo.imageinc.us +1 727 647 1274
----- Original Message -----
From: “Kevin Falcone” falcone@bestpractical.com
On RT4 you can enable the statement log and see every query run
on a page with pointers into the source that executed it.That doesn’t help, though, on a large realtime system, and especially
not on code that isn’t RT.
I actually have found it to be my favorite new RT4 feature, especially
for improving the performance of large systems.
-kevin
That doesn’t help, though, on a large realtime system, and
especially not on code that isn’t RT.I actually have found it to be my favorite new RT4 feature, especially
for improving the performance of large systems.
I’m sure it’s very nice (and since I’m probably about to install RT4, I
will care. But I’m trying to solve the more general problem; all the
world is not RT4.
Cheers,
– jra
Jay R. Ashworth Baylink jra@baylink.com
Designer The Things I Think RFC 2100
Ashworth & Associates http://baylink.pitas.com 2000 Land Rover DII
St Petersburg FL USA http://photo.imageinc.us +1 727 647 1274
From: “Kevin Falcone” falcone@bestpractical.com
This destroys mysql’s query cache, which is a very nice performance
win.
On some reflection, I have a question: which part of my suggestion destroys
the utility of the cache? That there’s another clause in the query? Or that
it involves “Today’s Date”?
Cause the former would only seem to apply between uses of the same exact
query in different places in the code… and the latter could be solved
by simply making the tagged-on clause be ‘Tag-string’ = ‘Tag-string’,
I think.
But I am only an egg in these things…
Cheers,
– jra
Jay R. Ashworth Baylink jra@baylink.com
Designer The Things I Think RFC 2100
Ashworth & Associates http://baylink.pitas.com 2000 Land Rover DII
St Petersburg FL USA http://photo.imageinc.us +1 727 647 1274