Novembro, 16 2006
Quinta-feira

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

16 Novembro 2006 - 23:51   3 Comentário(s)

O livro de reclamações
Na semana passada estreei o livro de reclamações numa agência de uma seguradora. Confesso que não me passo com facilidade, mas esta bateu tudo. Foram dias para marcar uma simples peritagem de uma quebra de vidros na marca... até o agente do seguro comeram. Enfim, uma manhã inteira e umas dezenas de Kms para resolver tudo, e agora o original da reclamação vai para O Instituto de Seguros de Portugal e a cópia vai para a companhia, juntamente com o cancelamento de três apólices com a justificação e o nome do funcionário por baixo.
O mais certo é não lhe acontecer nada, mas a acontecer, seja o que for I couldn't care less.

Curiosamente, puxar da "bomba atómica" e preencher-la foi relativamente simples. Pensei que era mais complicado... fiquei fã, de repente abate-se uma sensação de calmaria aparente nas imediações. :-D

16 Novembro 2006 - 00:48   5 Comentário(s)