Re: log-slow-queries

[Anirudh Sundar]

Hello Stephen,

Did u try this ??

mysql> show global variables like '%log_output%';
+---+---+
| Variable_name | Value |
+---+---+
| log_output| FILE  |
+---+---+

If only the log_output is FILE, then the slow queries will get logged in the
log.
mysql> set global log_output = FILE; [if you find log_output as TABLE or
NONE]

Let me know if you have any issues...

Cheers,
Anirudh Sundar


On Fri, May 7, 2010 at 10:34 PM, Stephen Sunderlin <
stephen.sunder...@verizon.net> wrote:

> Can't get slow querys to log.  Does this not work in myisam?
>
> *snip*
> [mysqld]
> log-slow-queries = /var/log/mysql/mysql-slow.log
> long_query_time = 1
> *snip*
>
> restarted mysqld  - no log.
>
> Created in file in /var/log/mysql/
>
> *snip*
> -rwxr--r-- 1 mysql mysql 0 May  7 10:33 mysql-slow.log
> *snip*
>
> still not writing to the file
>
> I've read
> http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html
>
> http://www.mydigitallife.info/2007/01/22/enable-logging-of-slow-queries-slow-query-log-in-mysql-database/
>
>
> looks pretty simple -  not sure what I'm missing.
>
> Thanks!
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=sundar.anir...@gmail.com
>
>

Re: Where to index - over 15m records and growing

[Anirudh Sundar]

Hello Chris,

Your Query Build


EXPLAIN SELECT COUNT(FlightRoutes.FlightID) AS Count, FlightRoutes.Dep AS
Dep, FlightRoutes.Des AS Des FROM FlightRoutes LEFT JOIN IVAOData ON
FlightRoutes.FlightID=IVAOData.FlightID WHERE IVAOData.TrackerTime >=
UNIX_TIMESTAMP('2010-04-01 00:00:00') AND IVAOData.TrackerTime <=
UNIX_TIMESTAMP('2010-04-30 23:59:50') GROUP BY FlightRoutes.Dep,
FlightRoutes.Des ORDER BY COUNT(FlightRoutes.FlightID) LIMIT 20;

My Query Build
--

EXPLAIN SELECT COUNT(FlightRoutes.FlightID) AS Count, FlightRoutes.Dep AS
Dep, FlightRoutes.Des AS Des FROM FlightRoutes LEFT JOIN IVAOData ON
FlightRoutes.FlightID=IVAOData.FlightID WHERE IVAOData.TrackerTime between
UNIX_TIMESTAMP('2010-04-01 00:00:00') AND UNIX_TIMESTAMP('2010-04-30
23:59:50') GROUP BY FlightRoutes.Dep, FlightRoutes.Des ORDER BY
COUNT(FlightRoutes.FlightID) LIMIT 20;

Suggestions :-

1. Do some research on Query caching because both the tables used are
MYISAM, Query Cache can be useful on MYISAM tables (Provided query build
values are static).
2. Run "Analyze" and "Optimize" commands on the IVAOData table. It will help
updating the index statictics and defragmenting the table (the table will
respond better to queries).

try these can get back with your observations.

Cheers,
Anirudh Sundar



On Fri, May 7, 2010 at 12:51 PM, Chris Knipe  wrote:

> My appologies for leaving that bit out...
>
> mysql> EXPLAIN SELECT COUNT(FlightRoutes.FlightID) AS Count,
> FlightRoutes.Dep AS Dep, FlightRoutes.Des AS Des FROM FlightRoutes LEFT JOIN
> IVAOData ON FlightRoutes.FlightID=IVAOData.FlightID WHERE
> IVAOData.TrackerTime >= UNIX_TIMESTAMP('2010-04-01 00:00:00') AND
> IVAOData.TrackerTime <= UNIX_TIMESTAMP('2010-04-30 23:59:50') GROUP BY
> FlightRoutes.Dep, FlightRoutes.Des ORDER BY COUNT(FlightRoutes.FlightID)
> LIMIT 20;
>
> ++-+--+---+---++-+---++-+
> | id | select_type | table| type  | possible_keys | key
> | key_len | ref   | rows   |
> Extra   |
>
> ++-+--+---+---++-+---++-+
> |  1 | SIMPLE  | FlightRoutes | index | PRIMARY   | ixAirports
> | 14  | NULL  | 106216 | Using temporary; Using
> filesort |
> |  1 | SIMPLE  | IVAOData | ref   | ixFlightID,ixTime | ixFlightID
> | 36  | tracker.FlightRoutes.FlightID | 73 | Using
> where |
>
> ++-+--+---+---++-+---++-+
> 2 rows in set (0.33 sec)
>
> Table / Index Sizes:
> r...@netsonic:/var/lib/mysql/tracker# ls -lah IVAOData.* FlightRoutes.*
> -rw-rw 1 mysql mysql 8.5K 2010-04-30 08:57 FlightRoutes.frm
> -rw-rw 1 mysql mysql 9.7M 2010-05-07 01:13 FlightRoutes.MYD
> -rw-rw 1 mysql mysql 6.1M 2010-05-07 01:39 FlightRoutes.MYI
> -rw-rw 1 mysql mysql  11K 2010-05-06 11:23 IVAOData.frm
> -rw-rw 1 mysql mysql 3.9G 2010-05-07 09:19 IVAOData.MYD
> -rw-rw 1 mysql mysql 1.4G 2010-05-07 09:19 IVAOData.MYI
>
> I expect the IVAOData table to roughly tripple in size.  Currently it holds
> 2 months worth of data, the ideal situation would be to keep 6 months worth
> of data in the table...
>
> RAM Size on the machine is 8GB...
>
> Regards,
> Chris.
>
>
>
>
> On Fri, May 7, 2010 at 9:17 AM, Anirudh Sundar 
> wrote:
>
>>
>> Hey Chris,
>>
>> Please send the explain plan for this query, the estimated table sizes (in
>> MB or GB) and the RAM capacity.
>>
>> These are also the requisites for helping optimizing your query if
>> required...
>>
>> Thanks.
>>
>> Anirudh Sundar
>>
>>
>> On Fri, May 7, 2010 at 12:14 PM, Chris Knipe wrote:
>>
>>> Hi All,
>>>
>>> I have a huge issue with a query - it copies the entire table to a tmp
>>> table
>>> when executing the query - and it's a big ass table Any help and/or
>>> pointers please?
>>>
>>>
>>> The query:
>>> SELECT COUNT(FlightRoutes.FlightID) AS Count, FlightRoutes.Dep AS Dep,
>>> FlightRoutes.Des AS Des FROM FlightRoutes LEFT JOIN IVAOData ON
>>> FlightRoutes.FlightID=IVAOData.FlightID WHERE IVAOData.TrackerTime >=
>>> UNIX_TIMESTAMP('2010-04-01 00:00:00') AND IVAOData.TrackerTime <=
>>> UNIX_TIMESTAMP('2010-04-30 23:59:50') GROUP BY FlightRoutes.Dep,
>>> FlightRoutes.Des ORDER BY COUNT(FlightRoutes.FlightID) LIMIT 20;
>>>
>>> FlightRoutes:
>>> mysql> DESCRIBE FlightRoutes;
>>> +--++--+-+-+---+
>>> | Field| Type   | Null | Key | Default | Extra |
>>> +--++--+-+-+---+
>>> | FlightID | char(36)   | NO   | PRI | NULL|   |
>>> | Dep  | varchar(5) | NO   | MUL | NULL|   

Re: log-slow-queries

[mos]

At 03:58 PM 5/7/2010, you wrote:

At 12:04 PM 5/7/2010, Stephen Sunderlin wrote:

Can't get slow querys to log.  Does this not work in myisam?


Sure it does. Have you tried:

slow_query_time = 1

Mike


Sorry, ignore that previous message. (Serves me right for trying to 
remember it from the top of my head.)


I'm using (Windows):

general_log=0
log-output=FILE
log_queries_not_using_indexes=1
long_query_time=3
slow_query_log=1
slow_query_log_file="U:/mysql5.5/data/SLOWLOG.TXT"

I assume you are outputting the slow query log to a text file and not to a 
table.


Mike



*snip*
[mysqld]
log-slow-queries = /var/log/mysql/mysql-slow.log
long_query_time = 1
*snip*

restarted mysqld  - no log.

Created in file in /var/log/mysql/

*snip*
-rwxr--r-- 1 mysql mysql 0 May  7 10:33 mysql-slow.log
*snip*

still not writing to the file

I've read
http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html
http://www.mydigitallife.info/2007/01/22/enable-logging-of-slow-queries-slow-query-log-in-mysql-database/


looks pretty simple -  not sure what I'm missing.

Thanks!



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: log-slow-queries

[mos]

At 12:04 PM 5/7/2010, Stephen Sunderlin wrote:

Can't get slow querys to log.  Does this not work in myisam?


Sure it does. Have you tried:

slow_query_time = 1

Mike



*snip*
[mysqld]
log-slow-queries = /var/log/mysql/mysql-slow.log
long_query_time = 1
*snip*

restarted mysqld  - no log.

Created in file in /var/log/mysql/

*snip*
-rwxr--r-- 1 mysql mysql 0 May  7 10:33 mysql-slow.log
*snip*

still not writing to the file

I've read
http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html
http://www.mydigitallife.info/2007/01/22/enable-logging-of-slow-queries-slow-query-log-in-mysql-database/


looks pretty simple -  not sure what I'm missing.

Thanks!



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

log-slow-queries

[Stephen Sunderlin]

Can't get slow querys to log.  Does this not work in myisam?

*snip*
[mysqld]
log-slow-queries = /var/log/mysql/mysql-slow.log
long_query_time = 1
*snip*

restarted mysqld  - no log.

Created in file in /var/log/mysql/

*snip*
-rwxr--r-- 1 mysql mysql 0 May  7 10:33 mysql-slow.log
*snip*

still not writing to the file

I've read
http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html
http://www.mydigitallife.info/2007/01/22/enable-logging-of-slow-queries-slow-query-log-in-mysql-database/


looks pretty simple -  not sure what I'm missing.

Thanks!



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Changing date result automatically

[Michael Dykman]

You build pretty much any format you desire out of those speifiers.  ie:

select date_format(now(),'%m/%c/%Y');

 - md

On Thu, May 6, 2010 at 5:00 PM, Weydson Lima  wrote:
> I was referring to the function:
>
> http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format
>
> ---
> Weydson Lima
> weys...@gmail.com
>
>
> On Thu, May 6, 2010 at 1:58 PM, Martin Gainty  wrote:
>
>>  http://lists.mysql.com/commits/60834
>>
>> date_format system variable is currently an unused system variable
>>
>> Martin Gainty
>> __
>> Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
>>
>> Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene
>> Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte
>> Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht
>> dient lediglich dem Austausch von Informationen und entfaltet keine
>> rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von
>> E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
>>
>> Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le 
>> destinataire prévu, nous te demandons avec bonté que pour satisfaire 
>> informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie 
>> de ceci est interdite. Ce message sert à l'information seulement et n'aura 
>> pas n'importe quel effet légalement obligatoire. Étant donné que les email 
>> peuvent facilement être sujets à la manipulation, nous ne pouvons accepter 
>> aucune responsabilité pour le contenu fourni.
>>
>>
>>
>>
>>
>>
>> > From: weys...@gmail.com
>> > Date: Thu, 6 May 2010 11:44:37 -0500
>> > Subject: Changing date result automatically
>> > To: mysql@lists.mysql.com
>>
>> >
>> > Hi,
>> >
>> > Is there any way to format all dates generated by a MySQL query as US
>> > format? What I want to do is having a generic way to use the DATE_FORMAT
>> > function on all my date fields, instead of having to enter them manually.
>> > Thank you
>> >
>> > ---
>> > Weydson Lima
>> > weys...@gmail.com
>> > http://bluetoothmono.weydson.com
>>
>> --
>> The New Busy is not the old busy. Search, chat and e-mail from your inbox. 
>> Get
>> started.
>>
>



-- 
 - michael dykman
 - mdyk...@gmail.com

 May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: New MySQL InStall

[Weydson Lima]

You can dowload the community server here:

http://dev.mysql.com/downloads/mysql/

---
Weydson Lima
weys...@gmail.com


On Thu, May 6, 2010 at 12:59 PM, Michael Abbott  wrote:

>
> Can anyone give me some info here  I want to install MySQL on a Vista
> Home Edition Laptop  Can anyone tell me what I should download from the
> site?  I want to run a java app.  What version is stable and does it
> matter the operating system?  Thank-YouMike
> _
> Win $10,000 from Hotmail! Enter Here.
> http://go.microsoft.com/?linkid=9729708
>

Re: Re: Changing date result automatically

[weyseal]

Thank you for your explanation. I was just curious if there was an "easy"  
way to format the output of the dates.. maybe by using a wildcard  
expression on the field names :)



Weydson Lima

On May 7, 2010 3:08am, Johan De Meersman  wrote:
Dates are not internally stored as a specific region format, they're  
stored as seconds since epoch - just a big number.


Output formatting is a presentation layer issue, which you can solve  
either in your SQL [select date_format(datefield, 'us format string')  
from table] or in your application.




Maybe MySQL looks at the system's locale for default formatting, or  
there's a config setting, but I've never looked at that - doing it  
explicitly protects you from annoying glitches when you move to another  
system that might be configured slightly differently.






On Thu, May 6, 2010 at 11:00 PM, Weydson Lima weys...@gmail.com> wrote:




I was referring to the function:







http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format







---




Weydson Lima




weys...@gmail.com











On Thu, May 6, 2010 at 1:58 PM, Martin Gainty mgai...@hotmail.com> wrote:







> http://lists.mysql.com/commits/60834




>




> date_format system variable is currently an unused system variable




>




> Martin Gainty




> __




> Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité




>




> Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene



> Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede  
unbefugte



> Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese  
Nachricht




> dient lediglich dem Austausch von Informationen und entfaltet keine




> rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von




> E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.




>



> Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas  
le destinataire prévu, nous te demandons avec bonté que pour satisfaire  
informez l'expéditeur. N'importe quelle diffusion non autorisée ou la  
copie de ceci est interdite. Ce message sert à l'information seulement et  
n'aura pas n'importe quel effet légalement obligatoire. Étant donné que  
les email peuvent facilement être sujets à la manipulation, nous ne  
pouvons accepter aucune responsabilité pour le contenu fourni.






>




>




>




>




>




>




> > From: weys...@gmail.com




> > Date: Thu, 6 May 2010 11:44:37 -0500




> > Subject: Changing date result automatically




> > To: mysql@lists.mysql.com





>




> >




> > Hi,




> >




> > Is there any way to format all dates generated by a MySQL query as US



> > format? What I want to do is having a generic way to use the  
DATE_FORMAT



> > function on all my date fields, instead of having to enter them  
manually.




> > Thank you




> >




> > ---




> > Weydson Lima




> > weys...@gmail.com




> > http://bluetoothmono.weydson.com




>






> --



> The New Busy is not the old busy. Search, chat and e-mail from your  
inbox. Get



>  
started.http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_3>






>







--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Where to index - over 15m records and growing

[Johnny Withers]

You could be running into this:

http://dev.mysql.com/doc/refman/5.0/en/how-to-avoid-table-scan.html



On Fri, May 7, 2010 at 10:05 AM, Peter Brawley
wrote:

> something tells me I need to rethink this yes.
>>
>
> If you were to add a computed column yearmonth, you could write WHERE
> yearmonth=201004.
>
> PB
>
> -
>
> Chris Knipe wrote:
>
>> On Fri, May 7, 2010 at 10:42 AM, Rob Wultsch  wrote:
>>
>>
>>
>>> Second thing:
>>> How selective is
>>> "WHERE IVAOData.TrackerTime >=UNIX_TIMESTAMP('2010-04-01 00:00:00')
>>>   AND IVAOData.TrackerTime <=UNIX_TIMESTAMP('2010-04-30 23:59:50') "
>>>
>>> Test by running
>>> SELECT COUNT(*)
>>> FROM  IVAOData
>>> WHERE IVAOData.TrackerTime >=UNIX_TIMESTAMP('2010-04-01 00:00:00')
>>>   AND IVAOData.TrackerTime <=UNIX_TIMESTAMP('2010-04-30 23:59:50')
>>>
>>> If this is a large proportion of the row count then you are probably
>>> in store for pain. It sounds like you are matching half the table. Big
>>> (intermediate) result sets often end in pain.
>>>
>>>
>>>
>>
>> At this stage, you are correct.  We have roughly 2 months worth of data in
>> the table and are selecting about half (one months worth), thus about 50%.
>> With 6 months worth of data in the table and selecting one months worth of
>> data, that's roughly 16% of the data - but it will still be a bulk large
>> result... Hmmm, something tells me I need to rethink this yes.
>>
>>
>>
>>
>>
>>> Third thing:
>>> My (rather sleepy) gut thinks your best bet is a a composite index on
>>> the table IVAOData on the columns TrackerTime and FlightID. This will
>>> make all access to the table in this query hit a covering index.
>>>
>>>
>>>
>>
>> Took over 12 hours to create the index on TrackerTime, and you're right -
>> I
>> should have seen and realised this.  I will drop the index on TrackerTime
>> and re-create it using both colums as I should have done in the first
>> place.
>>
>>
>>
>>
>>
>>> Fourth thing:
>>> What do you intend to ask the database with this query. This query
>>> smells overly broad
>>>
>>>
>>
>>
>> The idea is to get a count of the number of entries from Dep to Des during
>> the last month.  I.E.  How many records are there where Dep and Des are
>> the
>> same during the last month.  With some changes to the application that
>> captures the data in the first place, I should be able to drop the need
>> for
>> this query completely.  You have made me think a bit here and you're
>> right.
>> This is not the way to do it.
>>
>> I'll rethink this a bit more and come up with something better.
>>
>> PS - Started the query before my first email was even posted, it's still
>> running... 3948 Seconds the last time I checked...
>>
>>
>>
>>  
>>
>>
>> No virus found in this incoming message.
>> Checked by AVG - www.avg.com Version: 8.5.437 / Virus Database:
>> 271.1.1/2859 - Release Date: 05/07/10 06:26:00
>>
>>
>>
>


-- 
-
Johnny Withers
601.209.4985
joh...@pixelated.net

Re: Where to index - over 15m records and growing

[Peter Brawley]

something tells me I need to rethink this yes.


If you were to add a computed column yearmonth, you could write WHERE 
yearmonth=201004.

PB

-

Chris Knipe wrote:

On Fri, May 7, 2010 at 10:42 AM, Rob Wultsch  wrote:

  

Second thing:
How selective is
"WHERE IVAOData.TrackerTime >=UNIX_TIMESTAMP('2010-04-01 00:00:00')
   AND IVAOData.TrackerTime <=UNIX_TIMESTAMP('2010-04-30 23:59:50') "

Test by running
SELECT COUNT(*)
FROM  IVAOData
WHERE IVAOData.TrackerTime >=UNIX_TIMESTAMP('2010-04-01 00:00:00')
   AND IVAOData.TrackerTime <=UNIX_TIMESTAMP('2010-04-30 23:59:50')

If this is a large proportion of the row count then you are probably
in store for pain. It sounds like you are matching half the table. Big
(intermediate) result sets often end in pain.




At this stage, you are correct.  We have roughly 2 months worth of data in
the table and are selecting about half (one months worth), thus about 50%.
With 6 months worth of data in the table and selecting one months worth of
data, that's roughly 16% of the data - but it will still be a bulk large
result... Hmmm, something tells me I need to rethink this yes.



  

Third thing:
My (rather sleepy) gut thinks your best bet is a a composite index on
the table IVAOData on the columns TrackerTime and FlightID. This will
make all access to the table in this query hit a covering index.




Took over 12 hours to create the index on TrackerTime, and you're right - I
should have seen and realised this.  I will drop the index on TrackerTime
and re-create it using both colums as I should have done in the first place.



  

Fourth thing:
What do you intend to ask the database with this query. This query
smells overly broad




The idea is to get a count of the number of entries from Dep to Des during
the last month.  I.E.  How many records are there where Dep and Des are the
same during the last month.  With some changes to the application that
captures the data in the first place, I should be able to drop the need for
this query completely.  You have made me think a bit here and you're right.
This is not the way to do it.

I'll rethink this a bit more and come up with something better.

PS - Started the query before my first email was even posted, it's still
running... 3948 Seconds the last time I checked...



  




No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 8.5.437 / Virus Database: 271.1.1/2859 - Release Date: 05/07/10 06:26:00


  

Re: Queries not in Cache

[Johan De Meersman]

What queries, precisely, I can't tell you, but you can have a good idea
about how your cache performs using the stuff in "show global variables;"
and the online manuals about what it all means :)

Look at 'show global variables like %qcache%', for a start.

On Fri, May 7, 2010 at 2:22 PM, Darvin Denmian wrote:

> Can somebody help me with this?
>
> Thanks!
>
> On Thu, May 6, 2010 at 10:39 AM, Darvin Denmian
>  wrote:
> > Hello,
> >
> > I've activated the query_cache in Mysql with the variable
> > "query_cache_limit" value to 1 MB.
> > My question is:
> >
> > How to know what queries wasn't cached because they have exceeded the
> > value of "query_cache_limit"?
> >
> > **Sorry for my Brazilian Englihs :(
> >
> > Thanks!
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
>
>


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Queries not in Cache

[Darvin Denmian]

Can somebody help me with this?

Thanks!

On Thu, May 6, 2010 at 10:39 AM, Darvin Denmian
 wrote:
> Hello,
>
> I've activated the query_cache in Mysql with the variable
> "query_cache_limit" value to 1 MB.
> My question is:
>
> How to know what queries wasn't cached because they have exceeded the
> value of "query_cache_limit"?
>
> **Sorry for my Brazilian Englihs :(
>
> Thanks!
>

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Where to index - over 15m records and growing

[Chris Knipe]

On Fri, May 7, 2010 at 10:42 AM, Rob Wultsch  wrote:

> Second thing:
> How selective is
> "WHERE IVAOData.TrackerTime >=UNIX_TIMESTAMP('2010-04-01 00:00:00')
>AND IVAOData.TrackerTime <=UNIX_TIMESTAMP('2010-04-30 23:59:50') "
>
> Test by running
> SELECT COUNT(*)
> FROM  IVAOData
> WHERE IVAOData.TrackerTime >=UNIX_TIMESTAMP('2010-04-01 00:00:00')
>AND IVAOData.TrackerTime <=UNIX_TIMESTAMP('2010-04-30 23:59:50')
>
> If this is a large proportion of the row count then you are probably
> in store for pain. It sounds like you are matching half the table. Big
> (intermediate) result sets often end in pain.
>

At this stage, you are correct.  We have roughly 2 months worth of data in
the table and are selecting about half (one months worth), thus about 50%.
With 6 months worth of data in the table and selecting one months worth of
data, that's roughly 16% of the data - but it will still be a bulk large
result... Hmmm, something tells me I need to rethink this yes.



> Third thing:
> My (rather sleepy) gut thinks your best bet is a a composite index on
> the table IVAOData on the columns TrackerTime and FlightID. This will
> make all access to the table in this query hit a covering index.
>

Took over 12 hours to create the index on TrackerTime, and you're right - I
should have seen and realised this.  I will drop the index on TrackerTime
and re-create it using both colums as I should have done in the first place.



> Fourth thing:
> What do you intend to ask the database with this query. This query
> smells overly broad


The idea is to get a count of the number of entries from Dep to Des during
the last month.  I.E.  How many records are there where Dep and Des are the
same during the last month.  With some changes to the application that
captures the data in the first place, I should be able to drop the need for
this query completely.  You have made me think a bit here and you're right.
This is not the way to do it.

I'll rethink this a bit more and come up with something better.

PS - Started the query before my first email was even posted, it's still
running... 3948 Seconds the last time I checked...



-- 

Regards,
Chris Knipe

Re: Where to index - over 15m records and growing

[Rob Wultsch]

Added whitespace for readabilty:
SELECT
COUNT(FlightRoutes.FlightID) AS Count,
FlightRoutes.Dep AS Dep,
FlightRoutes.Des AS Des
FROM FlightRoutes
LEFT JOIN IVAOData ON FlightRoutes.FlightID=IVAOData.FlightID
WHERE IVAOData.TrackerTime >=UNIX_TIMESTAMP('2010-04-01 00:00:00')
AND IVAOData.TrackerTime <=UNIX_TIMESTAMP('2010-04-30 23:59:50')
GROUP BY FlightRoutes.Dep, FlightRoutes.Des
ORDER BY COUNT(FlightRoutes.FlightID)
LIMIT 20;

First thing that pops to mind: Do you *really* mean left join?

Second thing:
How selective is
"WHERE IVAOData.TrackerTime >=UNIX_TIMESTAMP('2010-04-01 00:00:00')
AND IVAOData.TrackerTime <=UNIX_TIMESTAMP('2010-04-30 23:59:50') "

Test by running
SELECT COUNT(*)
FROM  IVAOData
WHERE IVAOData.TrackerTime >=UNIX_TIMESTAMP('2010-04-01 00:00:00')
AND IVAOData.TrackerTime <=UNIX_TIMESTAMP('2010-04-30 23:59:50')

If this is a large proportion of the row count then you are probably
in store for pain. It sounds like you are matching half the table. Big
(intermediate) result sets often end in pain.

Third thing:
My (rather sleepy) gut thinks your best bet is a a composite index on
the table IVAOData on the columns TrackerTime and FlightID. This will
make all access to the table in this query hit a covering index.

Fourth thing:
What do you intend to ask the database with this query. This query
smells overly broad.

-- 
Rob Wultsch
wult...@gmail.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Changing date result automatically

[Johan De Meersman]

Dates are not internally stored as a specific region format, they're stored
as seconds since epoch - just a big number.

Output formatting is a presentation layer issue, which you can solve either
in your SQL [select date_format(datefield, 'us format string') from table]
or in your application.

Maybe MySQL looks at the system's locale for default formatting, or there's
a config setting, but I've never looked at that - doing it explicitly
protects you from annoying glitches when you move to another system that
might be configured slightly differently.


On Thu, May 6, 2010 at 11:00 PM, Weydson Lima  wrote:

> I was referring to the function:
>
>
> http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format
>
> ---
> Weydson Lima
> weys...@gmail.com
>
>
> On Thu, May 6, 2010 at 1:58 PM, Martin Gainty  wrote:
>
> >  http://lists.mysql.com/commits/60834
> >
> > date_format system variable is currently an unused system variable
> >
> > Martin Gainty
> > __
> > Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
> >
> > Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene
> > Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede
> unbefugte
> > Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht
> > dient lediglich dem Austausch von Informationen und entfaltet keine
> > rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von
> > E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
> >
> > Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas
> le destinataire prévu, nous te demandons avec bonté que pour satisfaire
> informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie
> de ceci est interdite. Ce message sert à l'information seulement et n'aura
> pas n'importe quel effet légalement obligatoire. Étant donné que les email
> peuvent facilement être sujets à la manipulation, nous ne pouvons accepter
> aucune responsabilité pour le contenu fourni.
> >
> >
> >
> >
> >
> >
> > > From: weys...@gmail.com
> > > Date: Thu, 6 May 2010 11:44:37 -0500
> > > Subject: Changing date result automatically
> > > To: mysql@lists.mysql.com
> >
> > >
> > > Hi,
> > >
> > > Is there any way to format all dates generated by a MySQL query as US
> > > format? What I want to do is having a generic way to use the
> DATE_FORMAT
> > > function on all my date fields, instead of having to enter them
> manually.
> > > Thank you
> > >
> > > ---
> > > Weydson Lima
> > > weys...@gmail.com
> > > http://bluetoothmono.weydson.com
> >
> > --
> > The New Busy is not the old busy. Search, chat and e-mail from your
> inbox. Get
> > started.<
> http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_3
> >
> >
>



-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Where to index - over 15m records and growing

[Chris Knipe]

My appologies for leaving that bit out...

mysql> EXPLAIN SELECT COUNT(FlightRoutes.FlightID) AS Count,
FlightRoutes.Dep AS Dep, FlightRoutes.Des AS Des FROM FlightRoutes LEFT JOIN
IVAOData ON FlightRoutes.FlightID=IVAOData.FlightID WHERE
IVAOData.TrackerTime >= UNIX_TIMESTAMP('2010-04-01 00:00:00') AND
IVAOData.TrackerTime <= UNIX_TIMESTAMP('2010-04-30 23:59:50') GROUP BY
FlightRoutes.Dep, FlightRoutes.Des ORDER BY COUNT(FlightRoutes.FlightID)
LIMIT 20;
++-+--+---+---++-+---++-+
| id | select_type | table| type  | possible_keys | key|
key_len | ref   | rows   |
Extra   |
++-+--+---+---++-+---++-+
|  1 | SIMPLE  | FlightRoutes | index | PRIMARY   | ixAirports |
14  | NULL  | 106216 | Using temporary; Using
filesort |
|  1 | SIMPLE  | IVAOData | ref   | ixFlightID,ixTime | ixFlightID |
36  | tracker.FlightRoutes.FlightID | 73 | Using
where |
++-+--+---+---++-+---++-+
2 rows in set (0.33 sec)

Table / Index Sizes:
r...@netsonic:/var/lib/mysql/tracker# ls -lah IVAOData.* FlightRoutes.*
-rw-rw 1 mysql mysql 8.5K 2010-04-30 08:57 FlightRoutes.frm
-rw-rw 1 mysql mysql 9.7M 2010-05-07 01:13 FlightRoutes.MYD
-rw-rw 1 mysql mysql 6.1M 2010-05-07 01:39 FlightRoutes.MYI
-rw-rw 1 mysql mysql  11K 2010-05-06 11:23 IVAOData.frm
-rw-rw 1 mysql mysql 3.9G 2010-05-07 09:19 IVAOData.MYD
-rw-rw 1 mysql mysql 1.4G 2010-05-07 09:19 IVAOData.MYI

I expect the IVAOData table to roughly tripple in size.  Currently it holds
2 months worth of data, the ideal situation would be to keep 6 months worth
of data in the table...

RAM Size on the machine is 8GB...

Regards,
Chris.



On Fri, May 7, 2010 at 9:17 AM, Anirudh Sundar wrote:

>
> Hey Chris,
>
> Please send the explain plan for this query, the estimated table sizes (in
> MB or GB) and the RAM capacity.
>
> These are also the requisites for helping optimizing your query if
> required...
>
> Thanks.
>
> Anirudh Sundar
>
>
> On Fri, May 7, 2010 at 12:14 PM, Chris Knipe  wrote:
>
>> Hi All,
>>
>> I have a huge issue with a query - it copies the entire table to a tmp
>> table
>> when executing the query - and it's a big ass table Any help and/or
>> pointers please?
>>
>>
>> The query:
>> SELECT COUNT(FlightRoutes.FlightID) AS Count, FlightRoutes.Dep AS Dep,
>> FlightRoutes.Des AS Des FROM FlightRoutes LEFT JOIN IVAOData ON
>> FlightRoutes.FlightID=IVAOData.FlightID WHERE IVAOData.TrackerTime >=
>> UNIX_TIMESTAMP('2010-04-01 00:00:00') AND IVAOData.TrackerTime <=
>> UNIX_TIMESTAMP('2010-04-30 23:59:50') GROUP BY FlightRoutes.Dep,
>> FlightRoutes.Des ORDER BY COUNT(FlightRoutes.FlightID) LIMIT 20;
>>
>> FlightRoutes:
>> mysql> DESCRIBE FlightRoutes;
>> +--++--+-+-+---+
>> | Field| Type   | Null | Key | Default | Extra |
>> +--++--+-+-+---+
>> | FlightID | char(36)   | NO   | PRI | NULL|   |
>> | Dep  | varchar(5) | NO   | MUL | NULL|   |
>> | Des  | varchar(5) | NO   | | NULL|   |
>> | Route| text   | NO   | | NULL|   |
>> +--++--+-+-+---+
>> 4 rows in set (0.00 sec)
>>
>> mysql> SHOW INDEX IN FlightRoutes;
>>
>> +--+++--+-+---+-+--++--++-+
>> | Table| Non_unique | Key_name   | Seq_in_index | Column_name |
>> Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment
>> |
>>
>> +--+++--+-+---+-+--++--++-+
>> | FlightRoutes |  0 | PRIMARY|1 | FlightID|
>> A |  106216 | NULL | NULL   |  | BTREE  |
>> |
>> | FlightRoutes |  1 | ixAirports |1 | Dep |
>> A |3124 | NULL | NULL   |  | BTREE  |
>> |
>> | FlightRoutes |  1 | ixAirports |2 | Des |
>> A |   26554 | NULL | NULL   |  | BTREE  |
>> |
>>
>> +--+++--+-+---+-+--++--++-+
>> 3 rows in set (0.00 sec)
>>
>> IVAOData:
>> mysql> DESCRIBE IVAOData;
>>
>> +---+--+--+-+-+

Re: Where to index - over 15m records and growing

[Anirudh Sundar]

Hey Chris,

Please send the explain plan for this query, the estimated table sizes (in
MB or GB) and the RAM capacity.

These are also the requisites for helping optimizing your query if
required...

Thanks.

Anirudh Sundar


On Fri, May 7, 2010 at 12:14 PM, Chris Knipe  wrote:

> Hi All,
>
> I have a huge issue with a query - it copies the entire table to a tmp
> table
> when executing the query - and it's a big ass table Any help and/or
> pointers please?
>
>
> The query:
> SELECT COUNT(FlightRoutes.FlightID) AS Count, FlightRoutes.Dep AS Dep,
> FlightRoutes.Des AS Des FROM FlightRoutes LEFT JOIN IVAOData ON
> FlightRoutes.FlightID=IVAOData.FlightID WHERE IVAOData.TrackerTime >=
> UNIX_TIMESTAMP('2010-04-01 00:00:00') AND IVAOData.TrackerTime <=
> UNIX_TIMESTAMP('2010-04-30 23:59:50') GROUP BY FlightRoutes.Dep,
> FlightRoutes.Des ORDER BY COUNT(FlightRoutes.FlightID) LIMIT 20;
>
> FlightRoutes:
> mysql> DESCRIBE FlightRoutes;
> +--++--+-+-+---+
> | Field| Type   | Null | Key | Default | Extra |
> +--++--+-+-+---+
> | FlightID | char(36)   | NO   | PRI | NULL|   |
> | Dep  | varchar(5) | NO   | MUL | NULL|   |
> | Des  | varchar(5) | NO   | | NULL|   |
> | Route| text   | NO   | | NULL|   |
> +--++--+-+-+---+
> 4 rows in set (0.00 sec)
>
> mysql> SHOW INDEX IN FlightRoutes;
>
> +--+++--+-+---+-+--++--++-+
> | Table| Non_unique | Key_name   | Seq_in_index | Column_name |
> Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
>
> +--+++--+-+---+-+--++--++-+
> | FlightRoutes |  0 | PRIMARY|1 | FlightID|
> A |  106216 | NULL | NULL   |  | BTREE  | |
> | FlightRoutes |  1 | ixAirports |1 | Dep |
> A |3124 | NULL | NULL   |  | BTREE  | |
> | FlightRoutes |  1 | ixAirports |2 | Des |
> A |   26554 | NULL | NULL   |  | BTREE  | |
>
> +--+++--+-+---+-+--++--++-+
> 3 rows in set (0.00 sec)
>
> IVAOData:
> mysql> DESCRIBE IVAOData;
>
> +---+--+--+-+-+---+
> | Field |
> Type | Null | Key |
> Default | Extra |
>
> +---+--+--+-+-+---+
> | EntryID   |
> char(36) | NO   | PRI |
> NULL|   |
> | FlightID  |
> char(36) | NO   | MUL |
> NULL|   |
> | isProcessed   |
> enum('0','1')| NO   | MUL |
> NULL|   |
> | TrackerTime   | int(10)
> unsigned | NO   | MUL | NULL
> |   |
> | CallSign  |
> varchar(10)  | NO   | |
> NULL|   |
> | VID   | mediumint(6)
> unsigned| NO   | MUL | NULL|
> |
> | RealName  |
> tinytext | NO   | |
> NULL|   |
> | ClientType|
> enum('ACT','PILOT','FOLME')  | NO   | |
> NULL|   |
> | Latitude  |
> float(8,5)   | NO   | |
> NULL|   |
> | Longitude |
> float(9,5)   | NO   | |
> NULL|   |
> | Altitude  | smallint(5)
> unsigned | NO   | | NULL|
> |
> | GroundSpeed   | smallint(5)
> unsigned | NO   | | NULL|
> |
> | PlannedAircraft   |
> varchar(30)  | NO   | |
> NULL|   |
> | PlannedTASCruise  |
> varchar(10)  | NO   | |
> NULL|   |
> | PlannedDepAirport |
> varchar(5)   | NO   | |
> NULL|   |
> | PlannedAltitude   |
> varchar(5)   | NO   | |
> NULL|   |
> | PlannedDestAirport|
> varchar(5)   |