MySQL monitoring tools In the last couple of weeks I've been feedling with MySQL, you know, that "not so serious database" as some people like to call it. ;-) I'm glad I did because I was not expecting that some days after, the server would be so hammered by a new feature in the portuguese version of Firefox 2. That particular site saw a 400% increment on it's daily none unique visits. Although that particular recuring request is cached, it can stress the database, and I'm not even mentioning some other couple of sites on the same server that are also growing it's daily visitors and use the same MySQL server, and share the same hard drive.
Anyway, I wanted to tweak MySQL because some weeks ago it was acting up on me, clogging all Apache processes, making them wait for it to respond. So i searched for some tools so i could monitor it, see what it was doing, when and why.
mytop - a life saver
I found about mytop some years ago but never got any use out of it because small sites don't even tickle a MySQL server and fast queries don't even showed up... till now.
I've been using mytop for the last couple of weeks and it's a life saver. Basicaly mytop is a console-based (non-gui) tool for monitoring the threads and overall performance of a MySQL just like top, the unix command line. It helped me understand exactly why MySQL was choking. mytop never got updated to handle MySQL 5, so if you are wondering why the command statistics aren't working, search the source code for she SHOW STATUS line and change it to this
Show me the graphs
Then I wanted some graphs. Sure, there's the awesome Cacti project with some nice monitoring templates (scrool down), but I wanted something simpler than Cacti (I've used it before).
For starters, there's a point and shoot graphing script here called mrtg-mysql-load. Brainless to install and obviousely it requires MRTG, wich probably everyone has allready installed. The output one gets is something like this...
Searching around for some more detailed graphs I found MySQL Activity Report. The MySQL Activity Report package is a tool to help MySQL database administrators to collect several database parameters and variables. These collected values can be used for server monitoring or performance tuning purposes.
The instalation is trickier than mrtg-mysql-load, it envolves a daemon, but the end result is far better. A demo can be seen here.
Basicaly, it gives you a connections graph, a questions (select,insert,update...) graphs and some others, but best of all, if you have the server running for some time it also gives you some server tunning tips like... The table cache hit rate is 98 %, while 99 % of your table cache is in use.
You should raize your table_cache to improve your server's performance.
For example, the connections graph looks like this...
On a related note I found two other tools, one for Apache called ApacheTop (guess what) and another called htop (top on steroids).
What do you use ? Wanna share ? :-)
ps: Looking at the graphs, it seems like MySQL went balistic around 13:00
Belo post, de certeza que foi um life saver para muita gente amante do MySQL, para mim foi!
Muito obrigado, se houve alguma tool que me venha à ideia mais para a tarde (acabei de acordar) virei aqui postar.
Cumprimentos. por David Negreira a 17 Novembro 2006 - 08:04
My 2c
O mysqldumpslow usado em conjunto com o explain parece-me ser a solução (em ambiente SAP a solução para performance também passa por sqltrace mais explain dos queries críticos). Sobre este tema, achei esta apresentação bem interessante:
Also, download mysql own tools and check the MySQL Administrator. It's the best way to test new ideas for graphs, and when you are happy with them, move them to MRTG.
Tem 32 anos, é natural de Vila Nova de Famalicão mas mora no Porto desde que veio para a universidade... bem, morou. Agora já casado, está pela Maia. Anda pela internet há já uns 12 anos tendo trabalhado em vários projectos como foram o caso do Mail.pt ou no Sapo. Nos tempos livres vai tendo tempo para fazer umas brincadeiras, entre as quais o ITJobs o Lusocast o Hispanocast ou o Destakes
De resto já bloga há uns 7 anos apesar de ter perdido parte da "vida" numa mudança de hosting provider. Algumas restias ainda por aí andam... ah, e o email de contacto está no footer. Btw... job offers are welcome!
@celso for sure... hope they release an API for that :> 08, Sep - 02:09 god, google now has it's own freaking satellite 08, Sep - 01:09 The story behind Google Chrome - http://tinyurl.com/6bxgm7 08, Sep - 01:09 @celso wondering why the blog post still didn't "get in" 08, Sep - 01:09 wikisym starts at 8:30... give me a break :-( 08, Sep - 00:09 @suskind ou isso :) 07, Sep - 23:09 @ndantas isso é tão web 1.0... agora há galerias com thumbs ;) 07, Sep - 23:09 there's that linus/bill gates hollywood movie playing at that "mov" channel 07, Sep - 22:09
não chateiem com os erros de Português... "the bad spelling is part of the charm". ;-)
escusado será dizer que as opiniões aqui expressas são minhas e só minhas, e não de outros ou da empresa onde trabalho
qualquer outro bitaite... ramblings at karlus.net
não, não é powered by MovableType
ou Wordpress, é powered by código meu em PHP
O conteúdo deste site não poderá ser utilizado sem a sua autorização expressa.
As imagens nele contidas são propriedade dos respectivos autores.
16 Novembro 2006 - 23:51
