Index analyser

--_000_0AC31FD51798B348BFB7EFD2BDEFE96E1554558784EXVMBX02012 ex_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Is there still no such thing anywhere for Mysql as an index analyser?
Many others have such a thing that will sit and monitor db activity over a =
poeriod of time and suggest the exact indexes on each table based on what i=
t has seen to improve performance....
Anyone got that for MySQL?


--_000_0AC31FD51798B348BFB7EFD2BDEFE96E1554558784EXVMBX02012 ex_--

Re: Index analyser

On Tue, February 23, 2010 1:28 pm, Cantwell, Bryan wrote:
> Is there still no such thing anywhere for Mysql as an index analyser?
> Many others have such a thing that will sit and monitor db activity over a
> poeriod of time and suggest the exact indexes on each table based on what
> it has seen to improve performance.... Anyone got that for MySQL?
>


I know of no daemon/service-style analyzer, but are you aware of the
ANALYZE/OPTIMIZE commands?

http://dev.mysql.com/doc/refman/5.0/en/table-maintenance-sql .html

You could set up a scheduled job to run an ANALYZE during periods of low
activity, for example Saturday nights 11pm.

To examine performance of an individual SELECT query, there is the EXPLAIN
command.

http://dev.mysql.com/doc/refman/5.0/en/explain.html

Also the optimization section may be of use:

http://dev.mysql.com/doc/refman/5.0/en/optimization.html

(you can replace 5.0 with 5.1, 4.1, etc. depending on your version).

- steve edberg


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Index analyser

At 03:28 PM 2/23/2010, you wrote:
>Is there still no such thing anywhere for Mysql as an index analyser?
>Many others have such a thing that will sit and monitor db activity over a
>poeriod of time and suggest the exact indexes on each table based on what
>it has seen to improve performance....
>Anyone got that for MySQL?

You can look at www.maatkit.org/doc/mk-query-digest.html


DESCRIPTION

This tool was formerly known as mk-log-parser.

mk-query-digest is a framework for doing things with events from a query
source such as the slow query log or PROCESSLIST. By default it acts as a
very sophisticated log analysis tool. You can group and sort queries in
many different ways simultaneously and find the most expensive queries, or
create a timeline of queries in the log, for example. It can also do a
"query review," which means to save a sample of each type of query into a
MySQL table so you can easily see whether you've reviewed and analyzed a
query before. The benefit of this is that you can keep track of changes to
your server's queries and avoid repeated work. You can also save other
information with the queries, such as comments, issue numbers in your
ticketing system, and so on.


Mike


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Index analyser

Ya, that one is helpful... just trying to land on a solution like I've seen=
in other DB's that have index-advisor that listens and creates what it thi=
nks is the perfect indexes ... but thx...

________________________________________
From: mos [mos99@fastmail.fm]
Sent: Tuesday, February 23, 2010 4:33 PM
To: mysql@lists.mysql.com
Subject: Re: Index analyser

At 03:28 PM 2/23/2010, you wrote:
>Is there still no such thing anywhere for Mysql as an index analyser?
>Many others have such a thing that will sit and monitor db activity over a
>poeriod of time and suggest the exact indexes on each table based on what
>it has seen to improve performance....
>Anyone got that for MySQL?

You can look at www.maatkit.org/doc/mk-query-digest.html


DESCRIPTION

This tool was formerly known as mk-log-parser.

mk-query-digest is a framework for doing things with events from a query
source such as the slow query log or PROCESSLIST. By default it acts as a
very sophisticated log analysis tool. You can group and sort queries in
many different ways simultaneously and find the most expensive queries, or
create a timeline of queries in the log, for example. It can also do a
"query review," which means to save a sample of each type of query into a
MySQL table so you can easily see whether you've reviewed and analyzed a
query before. The benefit of this is that you can keep track of changes to
your server's queries and avoid repeated work. You can also save other
information with the queries, such as comments, issue numbers in your
ticketing system, and so on.


Mike


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dbcantwell@firescope=
..com=

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Index analyser

--MCBoundary=_110022417441701002
Content-Type: text/plain; charset=WINDOWS-1252
Content-Transfer-Encoding: quoted-printable

There's also the Query Analyser
http://www.mysql.com/products/enterprise/query.html which is part of
MySQL Enterprise - I've never used it and it is very expensive but I
believe it will advise on optimal indicies.

Cheers,

Andrew

-----Original Message-----
From: Cantwell, Bryan [mailto:bcantwell@firescope.com]=20
Sent: 23 February 2010 23:09
To: mysql@lists.mysql.com
Subject: RE: Index analyser


Ya, that one is helpful... just trying to land on a solution like I've
seen in other DB's that have index-advisor that listens and creates what
it thinks is the perfect indexes ... but thx...

________________________________________
From: mos [mos99@fastmail.fm]
Sent: Tuesday, February 23, 2010 4:33 PM
To: mysql@lists.mysql.com
Subject: Re: Index analyser

At 03:28 PM 2/23/2010, you wrote:
>Is there still no such thing anywhere for Mysql as an index analyser?
>Many others have such a thing that will sit and monitor db activity
over a
>poeriod of time and suggest the exact indexes on each table based on
what
>it has seen to improve performance....
>Anyone got that for MySQL?

You can look at www.maatkit.org/doc/mk-query-digest.html


DESCRIPTION

This tool was formerly known as mk-log-parser.

mk-query-digest is a framework for doing things with events from a query
source such as the slow query log or PROCESSLIST. By default it acts as
a
very sophisticated log analysis tool. You can group and sort queries in
many different ways simultaneously and find the most expensive queries,
or
create a timeline of queries in the log, for example. It can also do a
"query review," which means to save a sample of each type of query into
a
MySQL table so you can easily see whether you've reviewed and analyzed a
query before. The benefit of this is that you can keep track of changes
to
your server's queries and avoid repeated work. You can also save other
information with the queries, such as comments, issue numbers in your
ticketing system, and so on.


Mike


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=3Dbcantwell@firescope.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=3Dandrew.braithwaite@love film.com

------------------------------------------------------------ ---------------=
------------------------------------------------------------ --
LOVEFiLM UK Limited is a company registered in England and Wales.=20
Registered Number: 06528297.=20
Registered Office: No.9, 6 Portal Way, London W3 6RU, United Kingdom.

This e-mail is confidential to the ordinary user of the e-mail address to w=
hich it was addressed. If you have received it in error,=20
please delete it from your system and notify the sender immediately.

This email message has been delivered safely and archived online by Mimecas=
t.
For more information please visit http://www.mimecast.co.uk=20
------------------------------------------------------------ ---------------=
------------------------------------------------------------ --
--MCBoundary=_110022417441701002--

RE: Index analyser

--MCBoundary=_110022417525304302
Content-Type: text/plain; charset=WINDOWS-1252
Content-Transfer-Encoding: quoted-printable

You can use this to get rid of unused indicies too.

http://www.mysqlperformanceblog.com/2009/01/15/dropping-unus ed-indexes/

Requires the percona extensions to be loaded.

Cheers,

Andrew


-----Original Message-----
From: Andrew Braithwaite [mailto:andrew.braithwaite@lovefilm.com]=20
Sent: 24 February 2010 17:44
To: Cantwell, Bryan; mysql@lists.mysql.com
Subject: RE: Index analyser

There's also the Query Analyser
http://www.mysql.com/products/enterprise/query.html which is part of
MySQL Enterprise - I've never used it and it is very expensive but I
believe it will advise on optimal indicies.

Cheers,

Andrew

-----Original Message-----
From: Cantwell, Bryan [mailto:bcantwell@firescope.com]=20
Sent: 23 February 2010 23:09
To: mysql@lists.mysql.com
Subject: RE: Index analyser


Ya, that one is helpful... just trying to land on a solution like I've
seen in other DB's that have index-advisor that listens and creates what
it thinks is the perfect indexes ... but thx...

________________________________________
From: mos [mos99@fastmail.fm]
Sent: Tuesday, February 23, 2010 4:33 PM
To: mysql@lists.mysql.com
Subject: Re: Index analyser

At 03:28 PM 2/23/2010, you wrote:
>Is there still no such thing anywhere for Mysql as an index analyser?
>Many others have such a thing that will sit and monitor db activity
over a
>poeriod of time and suggest the exact indexes on each table based on
what
>it has seen to improve performance....
>Anyone got that for MySQL?

You can look at www.maatkit.org/doc/mk-query-digest.html


DESCRIPTION

This tool was formerly known as mk-log-parser.

mk-query-digest is a framework for doing things with events from a query
source such as the slow query log or PROCESSLIST. By default it acts as
a
very sophisticated log analysis tool. You can group and sort queries in
many different ways simultaneously and find the most expensive queries,
or
create a timeline of queries in the log, for example. It can also do a
"query review," which means to save a sample of each type of query into
a
MySQL table so you can easily see whether you've reviewed and analyzed a
query before. The benefit of this is that you can keep track of changes
to
your server's queries and avoid repeated work. You can also save other
information with the queries, such as comments, issue numbers in your
ticketing system, and so on.


Mike


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=3Dbcantwell@firescope.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=3Dandrew.braithwaite@love film.com

------------------------------------------------------------ ------------
------------------------------------------------------------ -----
LOVEFiLM UK Limited is a company registered in England and Wales.=20
Registered Number: 06528297.=20
Registered Office: No.9, 6 Portal Way, London W3 6RU, United Kingdom.

This e-mail is confidential to the ordinary user of the e-mail address
to which it was addressed. If you have received it in error,=20
please delete it from your system and notify the sender immediately.

This email message has been delivered safely and archived online by
Mimecast.
For more information please visit http://www.mimecast.co.uk=20
------------------------------------------------------------ ------------
------------------------------------------------------------ -----
--MCBoundary=_110022417525304302--

Re: Index analyser

Bryan,

On Tue, Feb 23, 2010 at 6:09 PM, Cantwell, Bryan
wrote:
>
> Ya, that one is helpful... just trying to land on a solution like I've seen in other DB's that have index-advisor that listens and creates what it thinks is the perfect indexes ... but thx...

I know exactly what you are thinking of. I used to live and breathe
SQL Server. Nothing similar exists for MySQL to the best of my
(reasonably extensive) knowledge. But it's a great idea for a future
Maatkit tool, or a plug-in for mk-query-digest.

- Baron

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org