Olá a todos. Nesta quinta e última serie de posts relacionados a performance, falarei um pouco sobre algumas ferramentas e analises a serem feitas para diagnosticar problemas a nível de sistema operacional.

Creio que todo o DBA já deva ter passado por uma situação parecida com essa: O ambiente de produção está incrivelmente lento, ninguém consegue fazer nenhuma operação, por mais simples que seja no servidor… logo os usuários e a gerência põem a culpe, sem nenhuma analise ou algo parecido, no banco de dados e por consequência nos DBAs. Depois de uma analise rápida em conjunto do os administradores de sistema, descobre-se que alguém estava compactanco um arquivo de 100GBs, consuminto todo o poder de processamento do servidor. Eu já passei por isso, e garanto que boa parte dos DBAs também já.

Todas as ferramentas listadas abaixo foram testadas nas distros derivadas no Red Hat, como Oracle Linux e CentOS. Mencionarei também algumas ferramentas de analise para ambientes Solaris.

 

   1) COMANDOS ÚTEIS

   1.1) ANALISANDO CONSUMO DE CPU

Dentre as ferramentas mais comuns de mensuração de uso de CPU, estão:

  • top
  • vmstat
  • htop
  • prstat (Solaris)

A minha favorita para realizar analises iniciais é o “top” por já sr um padrão em todas as distros derivadas do Red Hat. Também já tive a oportunidade, e fortemente recomendo, a instalação da ferramenta htop, que mostra de uma maneira bem mais amigável o consumo de CPU, detalhando os principais processos consumidores. Exemplos de uso do top:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IyMjIGF0dWFsaXphIGFzIGluZm9ybWHDp8O1ZXMgYSBjYWRhIDIgc2VndW5kb3MKdG9wIC1kIDIKCiMjIyB1c2FuZG8gbyB0b3AgZW0gYmF0Y2ggbW9kZQojIyMgZXNzZSBtb2RvIHBlcm1pdGUgc2FsdmFyIGEgc2FpZGEgZGUgdW1hIGl0ZXJhw6fDo28gZG8gdG9wIGVtIHVtIHR4dCwgcGFyYQojIyMgZnV0dXJvcyB0cmF0YW1lbnRvcyBlIGRhZG9zIGhpc3TDs3JpY29zLCBwb3IgZXhlbXBsbwp0b3AgLWIgLW4xID4gdG9wLnR4dA==” hover_enabled=”0″]IyMjIGF0dWFsaXphIGFzIGluZm9ybWHDp8O1ZXMgYSBjYWRhIDIgc2VndW5kb3MKdG9wIC1kIDIKCiMjIyB1c2FuZG8gbyB0b3AgZW0gYmF0Y2ggbW9kZQojIyMgZXNzZSBtb2RvIHBlcm1pdGUgc2FsdmFyIGEgc2FpZGEgZGUgdW1hIGl0ZXJhw6fDo28gZG8gdG9wIGVtIHVtIHR4dCwgcGFyYQojIyMgZnV0dXJvcyB0cmF0YW1lbnRvcyBlIGRhZG9zIGhpc3TDs3JpY29zLCBwb3IgZXhlbXBsbwp0b3AgLWIgLW4xID4gdG9wLnR4dA==[/et_pb_dmb_code_snippet]

Também pode ser útil usar a ferramenta mpstat (requer o pacote sysstat). Ela pode mostrar o consumo geral por processador. Exemplos:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IyMjIGNvbnN1bW8gZ2VyYWwsIGRlIHRvZG9zIG9zIHByb2Nlc3NhZG9yZXMKbXBzdGF0CgojIyMgY29uc3VtbyBkZXRhbGhhZG8sIHBvciBwcm9jZXNzYWRvcgptcHN0YXQgLVAgQUxM” hover_enabled=”0″]IyMjIGNvbnN1bW8gZ2VyYWwsIGRlIHRvZG9zIG9zIHByb2Nlc3NhZG9yZXMKbXBzdGF0CgojIyMgY29uc3VtbyBkZXRhbGhhZG8sIHBvciBwcm9jZXNzYWRvcgptcHN0YXQgLVAgQUxM[/et_pb_dmb_code_snippet]

Por fim, a nível de Linux, pode-se usar o “pidstat” para obter detalhes de consumo de CPU, por processo

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IyMjIGV4aWJpbmRvIGluZm9ybWHDp8O1ZXMgYSBjYWRhIDIgc2VndW5kb3MKcGlkc3RhdCAtdCAy” hover_enabled=”0″]IyMjIGV4aWJpbmRvIGluZm9ybWHDp8O1ZXMgYSBjYWRhIDIgc2VndW5kb3MKcGlkc3RhdCAtdCAy[/et_pb_dmb_code_snippet]

A nível de Solaris, pode-se usar a ferramenta prstat, que equivale ao top do Linux. Exemplos

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IyMjIGluZm9ybWHDp8O1ZXMgZ2VyYWlzCnByc3RhdA==” hover_enabled=”0″]IyMjIGluZm9ybWHDp8O1ZXMgZ2VyYWlzCnByc3RhdA==[/et_pb_dmb_code_snippet]

1.2) ANALISANDO PROCESSOS CONSUMIDORES

Para analisar o consumo dos processos, pode-se usar a ferramenta “ps”, bem como o “top”, acima mencionado. Ex:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IyMjIGxpc3RhbmRvIG9zIDE1IHByaW5jaXBhaXMgcHJvY2Vzc29zIGNvbnN1bWlkb3JlcyBkZSBjcHUKZWNobyAiQ1BVKCUpIHwgUElEIHwgVVNFUiB8IFRFUk1JTkFMIHwgQVJHUyIgOyBlY2hvICItLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tIiA7IHBzIC1lIC1vIHBjcHUscGlkLHVzZXIsdHR5LGFyZ3MgfCBzb3J0IC1uIC1rIDEgLXIgfCBncmVwIC12ICVDUFUgfCBoZWFkIC0xNQoKIyMjIGxpc3RhbmRvIG9zIDE1IHByaW5jaXBhaXMgcHJvY2Vzc29zIGNvbnN1bWlkb3JlcyBkZSBtZW3Ds3JpYQplY2hvICJNRU0oJSkgfCBQSUQgfCBVU0VSIHwgVEVSTUlOQUwgfCBBUkdTIiA7IGVjaG8gIi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0iIDsgcHMgLWUgLW8gcG1lbSxwaWQsdXNlcix0dHksYXJncyB8IHNvcnQgLW4gLWsgMSAtciB8IGdyZXAgLXYgJUNQVSB8IGhlYWQgLTE=” hover_enabled=”0″]IyMjIGxpc3RhbmRvIG9zIDE1IHByaW5jaXBhaXMgcHJvY2Vzc29zIGNvbnN1bWlkb3JlcyBkZSBjcHUKZWNobyAiQ1BVKCUpIHwgUElEIHwgVVNFUiB8IFRFUk1JTkFMIHwgQVJHUyIgOyBlY2hvICItLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tIiA7IHBzIC1lIC1vIHBjcHUscGlkLHVzZXIsdHR5LGFyZ3MgfCBzb3J0IC1uIC1rIDEgLXIgfCBncmVwIC12ICVDUFUgfCBoZWFkIC0xNQoKIyMjIGxpc3RhbmRvIG9zIDE1IHByaW5jaXBhaXMgcHJvY2Vzc29zIGNvbnN1bWlkb3JlcyBkZSBtZW3Ds3JpYQplY2hvICJNRU0oJSkgfCBQSUQgfCBVU0VSIHwgVEVSTUlOQUwgfCBBUkdTIiA7IGVjaG8gIi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0iIDsgcHMgLWUgLW8gcG1lbSxwaWQsdXNlcix0dHksYXJncyB8IHNvcnQgLW4gLWsgMSAtciB8IGdyZXAgLXYgJUNQVSB8IGhlYWQgLTE=[/et_pb_dmb_code_snippet]

Em se tratando de Solaris, é possível usar o prstat com a opção -a. Ex:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IyMjIGluZm9ybWHDp8O1ZXMgc29icmUgb3MgcHJvY2Vzc29zIGUgdXN1w6FyaW9zCnByc3RhdCAtYQ==” hover_enabled=”0″]IyMjIGluZm9ybWHDp8O1ZXMgc29icmUgb3MgcHJvY2Vzc29zIGUgdXN1w6FyaW9zCnByc3RhdCAtYQ==[/et_pb_dmb_code_snippet]

1.3) ANALISANDO O CONSUMO DE MEMÓRIA

Para analisar o consumo de memória do ambiente, podemos usar as ferramentas “vmstat” (tanto para Linux quanto para Solaris) e o “free” (Linux). Ex:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IyMjIGxpc3RhbmRvIGluZm9ybWHDp8O1ZXMgYSBjYWRhIHNlZ3VuZG8sIGVtIE1CcwojIyMgYXRlbnRhciBwYXJhIGFzIGNvbHVuYXMgImZyZWUiLCAic2kiIGUgInNvIgojIyMgICAgLSBmcmVlIGluZGljYSBhIHF1YW50aWRhZGUgZGUgbWVtw7NyaWEgZsOtc2ljYSBsaXZyZSBubyBhbWJpZW50ZQojIyMgICAgLSBzaSBlIHNvIGNvbnTDqW0gaW5mb3JtYcOnw7VlcyByZWxhY2lvbmFkYXMgYW8gc3dhcAp2bXN0YXQgLVMgTSAxCgojIyMgbGlzdGFuZG8gaW5mb3JtYcOnw7VlcyBzb2JyZSBhIG1lbcOzcmlhLCBkZSB1bWEgZm9ybWEgbWFpcyBhbWlnw6F2ZWwgc28gcXVlIG8gdm1zdGF0CmZyZWUgLW0KCiMjIyBvIHNhciDDqSBpbnRlcmVzc2FudGUgcG9pcyBlbGUgbW9zdHJhIG8gJSBkZSBtZW3Ds3JpYSBlbSB1c28KIyMjIGV4ZWN1dGEgYSBjYWRhIDMgc2VndW5kb3MKc2FyIC1yIDM=” hover_enabled=”0″]IyMjIGxpc3RhbmRvIGluZm9ybWHDp8O1ZXMgYSBjYWRhIHNlZ3VuZG8sIGVtIE1CcwojIyMgYXRlbnRhciBwYXJhIGFzIGNvbHVuYXMgImZyZWUiLCAic2kiIGUgInNvIgojIyMgICAgLSBmcmVlIGluZGljYSBhIHF1YW50aWRhZGUgZGUgbWVtw7NyaWEgZsOtc2ljYSBsaXZyZSBubyBhbWJpZW50ZQojIyMgICAgLSBzaSBlIHNvIGNvbnTDqW0gaW5mb3JtYcOnw7VlcyByZWxhY2lvbmFkYXMgYW8gc3dhcAp2bXN0YXQgLVMgTSAxCgojIyMgbGlzdGFuZG8gaW5mb3JtYcOnw7VlcyBzb2JyZSBhIG1lbcOzcmlhLCBkZSB1bWEgZm9ybWEgbWFpcyBhbWlnw6F2ZWwgc28gcXVlIG8gdm1zdGF0CmZyZWUgLW0KCiMjIyBvIHNhciDDqSBpbnRlcmVzc2FudGUgcG9pcyBlbGUgbW9zdHJhIG8gJSBkZSBtZW3Ds3JpYSBlbSB1c28KIyMjIGV4ZWN1dGEgYSBjYWRhIDMgc2VndW5kb3MKc2FyIC1yIDM=[/et_pb_dmb_code_snippet]

1.4) ANALISANDO O CONSUMO DE I/O

Podemos usar as ferramentas “sar” e “iostat” (ambas existem para Linux e Solaris) para monitorar a atividade de I/O no ambiente. Ex:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IyMjIGV4aWJpbmRvIGluZm9ybWHDp8O1ZXMsIHBvciBkZXZpY2UsIGEgY2FkYSAyIHNlZ3VuZG9zCmlvc3RhdCAtZCAyCgojIyMgZXhpYmUgZXN0YXRpc3RpY2FzIGRlIEkvTyBhIGNhc2EgMyBzZWd1bmRvcywgYWdydXBhbmRvIHRvZG9zIG9zIGRldmljZXMKc2FyIC1iIDMKCiMjIyBleGliZSBpbmZvcm1hw6fDtWVzIHBvciBkZXZpY2UsIGEgY2FzYSAyIHNlZ3VuZG9zCnNhciAtZCAy” hover_enabled=”0″]IyMjIGV4aWJpbmRvIGluZm9ybWHDp8O1ZXMsIHBvciBkZXZpY2UsIGEgY2FkYSAyIHNlZ3VuZG9zCmlvc3RhdCAtZCAyCgojIyMgZXhpYmUgZXN0YXRpc3RpY2FzIGRlIEkvTyBhIGNhc2EgMyBzZWd1bmRvcywgYWdydXBhbmRvIHRvZG9zIG9zIGRldmljZXMKc2FyIC1iIDMKCiMjIyBleGliZSBpbmZvcm1hw6fDtWVzIHBvciBkZXZpY2UsIGEgY2FzYSAyIHNlZ3VuZG9zCnNhciAtZCAy[/et_pb_dmb_code_snippet]

OBS1: tps indica o número de transferencias por segundo de um dispositivo físico, onde uma transferencia é uma requisição de I/O a um dispositivo físico

OBS2: rtps e wtps indicam o número de requisições lidas (r) e escritas(w) em um dispositivo físico

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IyMjIGlvc3RhdCwgZW0gYW1iaWVudGVzIFNvbGFyaXMgMTEKIyMjIGV4ZWN1dGEgYSBjYWRhIDMgc2VndW5kb3MsIGV4aWJpbmRvIGluZm9ybWHDp8O1ZXMgc29icmUgdG9kb3Mgb3MgZGV2aWNlcwojIyMgQXRlbnRhciBwYXJhIGFzIGNvbHVuYXMgIiViIiAocGVyY2VudCBvZiB0aW1lIHRoZSBkaXNrIGlzIGJ1c3kpIGUgInN2Y190IiAodGhlIGF2ZXJhZ2Ugc2VydmljZSB0aW1lLCBpbiBtaWxsaXNlY29uZHMsIG9mIHRoZSBkaXNrKQojIyMgQ29tIHJlbGHDp8OjbyBhbyAic3ZjX3QiIGNhc28gZWxlIGFwcmVzZW50ZSB0ZW1wb3MgYWNpbWEgZGUgMzAgbXMsIGlzc28gcG9kZSBpbmRpY2FyIHVtIHByb2JsZW1hLgppb3N0YXQgLWN4IDMKCg==” hover_enabled=”0″]IyMjIGlvc3RhdCwgZW0gYW1iaWVudGVzIFNvbGFyaXMgMTEKIyMjIGV4ZWN1dGEgYSBjYWRhIDMgc2VndW5kb3MsIGV4aWJpbmRvIGluZm9ybWHDp8O1ZXMgc29icmUgdG9kb3Mgb3MgZGV2aWNlcwojIyMgQXRlbnRhciBwYXJhIGFzIGNvbHVuYXMgIiViIiAocGVyY2VudCBvZiB0aW1lIHRoZSBkaXNrIGlzIGJ1c3kpIGUgInN2Y190IiAodGhlIGF2ZXJhZ2Ugc2VydmljZSB0aW1lLCBpbiBtaWxsaXNlY29uZHMsIG9mIHRoZSBkaXNrKQojIyMgQ29tIHJlbGHDp8OjbyBhbyAic3ZjX3QiIGNhc28gZWxlIGFwcmVzZW50ZSB0ZW1wb3MgYWNpbWEgZGUgMzAgbXMsIGlzc28gcG9kZSBpbmRpY2FyIHVtIHByb2JsZW1hLgppb3N0YXQgLWN4IDMKCg==[/et_pb_dmb_code_snippet]

1.5) ANALISANDO O CONSUMO DE REDE

Para analisar o tráfego de rede, podemos usar o “nestat”. Ex:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IyMjIGF0ZW50YXIgcGFyYSBhIGNvbHVuYSAiU2VuZC1RIi4gQ2FzbyBlbGEgcG9zc3VhIHVtIHZhbG9yIGluY29tdW1lbnRlIGFsZ28sCiMjIyBpc3NvIHBvZGUgaW5kaWNhciB1bWEgc29icmVjYXJnYSBuYSByZWRlCm5ldHN0YXQgLXB0YwoKIyMjIGEgbsOtdmVsIGRlIFNvbGFyaXMgMTEKIyMjIGF0ZW50YXIgcGFyYSBhIGNvbHVuYSAiY29sbGlucyIgKFdoZW4gY29sbGlzaW9ucyBhcmUgZ3JlYXRlciB0aGFuIDUgcGVyY2VudCBvZiB0aGUgcGFja2V0cyBzZW50LAojIyMgeW91IGFyZSBzdGFydGluZyB0byBleHBlcmllbmNlIG5ldHdvcmsgc2F0dXJhdGlvbi4gVGhhdCBpcyBjb2xscyAvIHBhY2tldHMgPjUlKQpuZXRzdGF0IC1p” hover_enabled=”0″]IyMjIGF0ZW50YXIgcGFyYSBhIGNvbHVuYSAiU2VuZC1RIi4gQ2FzbyBlbGEgcG9zc3VhIHVtIHZhbG9yIGluY29tdW1lbnRlIGFsZ28sCiMjIyBpc3NvIHBvZGUgaW5kaWNhciB1bWEgc29icmVjYXJnYSBuYSByZWRlCm5ldHN0YXQgLXB0YwoKIyMjIGEgbsOtdmVsIGRlIFNvbGFyaXMgMTEKIyMjIGF0ZW50YXIgcGFyYSBhIGNvbHVuYSAiY29sbGlucyIgKFdoZW4gY29sbGlzaW9ucyBhcmUgZ3JlYXRlciB0aGFuIDUgcGVyY2VudCBvZiB0aGUgcGFja2V0cyBzZW50LAojIyMgeW91IGFyZSBzdGFydGluZyB0byBleHBlcmllbmNlIG5ldHdvcmsgc2F0dXJhdGlvbi4gVGhhdCBpcyBjb2xscyAvIHBhY2tldHMgPjUlKQpuZXRzdGF0IC1p[/et_pb_dmb_code_snippet]

É isso ai pessoal, com esse post eu encerro a série relacionada a Performance Tuning. Espero que os leitores tenham gostado do que foi exposto e como foi exposto. Quaisquer críticas e sujestões são mais do que bem vindas.

Até a próxima série de posts.

Keep Tuning.