{"id":11495,"date":"2019-03-14T09:27:34","date_gmt":"2019-03-14T09:27:34","guid":{"rendered":"http:\/\/www.appservgrid.com\/paw92\/?p=11495"},"modified":"2019-03-14T09:27:34","modified_gmt":"2019-03-14T09:27:34","slug":"4-useful-commandline-tools-to-monitor-mysql-performance-in-linux","status":"publish","type":"post","link":"https:\/\/www.appservgrid.com\/paw92\/index.php\/2019\/03\/14\/4-useful-commandline-tools-to-monitor-mysql-performance-in-linux\/","title":{"rendered":"4 Useful Commandline Tools to Monitor MySQL Performance in Linux"},"content":{"rendered":"

There are plenty of tools to monitor\u00a0MySQL<\/strong>\u00a0performance and troubleshoot a server, but they don\u2019t always perfect match for a\u00a0MySQL<\/strong>\u00a0developer or administrator\u2019s for their common needs, or may not work in some situations, such as remote or over the web monitoring.<\/p>\n

\n

\"MySQL<\/a><\/p>\n

MySQL Monitoring Tools<\/p>\n<\/div>\n

Luckily, there are variety of open source tools created by\u00a0MySQL<\/strong>\u00a0community to fill the gaps. On the other hand, it\u2019s very difficult to locate these tools via web searches, that\u2019s the reason we\u2019ve compiled\u00a04 command line tools<\/strong>to monitor\u00a0MySQL<\/strong>\u00a0database\u00a0uptime<\/strong>,\u00a0load<\/strong>\u00a0and\u00a0performance<\/strong>\u00a0in\u00a0Linux<\/strong>.<\/p>\n

Uptime<\/strong>\u00a0means how long the database has been running and up since its last shutdown or restart. Getting information about uptime is very crucial in many situations, as it helps system administrators to check the status of MySQL database about, how many queries per second that MySQL database serves, threads, slow queries and lots of interesting statistics.<\/p>\n

1. Mytop<\/h3>\n

Mytop<\/strong>\u00a0is one of my classic open source and free console-based (non-gui) monitoring tool for\u00a0MySQL<\/strong>\u00a0database was written by\u00a0Jereme Zawodny<\/strong>\u00a0using\u00a0Perl<\/strong>\u00a0language. Mytop runs in a terminal and displays statistics about threads, queries, slow queries, uptime, load, etc. in tabular format, much similar to the Linux\u00a0top program<\/a>. Which indirectly helps the administrators to optimize and improve performance of MySQl to handle large requests and decrease server load.<\/p>\n

\n

\"Install<\/a><\/p>\n

Mytop: Mysql Monitoring<\/p>\n

There are mytop packages available for various Linux distributions, such as\u00a0Ubuntu<\/strong>,\u00a0Fedora<\/strong>\u00a0and\u00a0CentOS<\/strong>. For more about installation instruction read:\u00a0How to Install Mytop (MySQL Monitoring) in Linux<\/a><\/p>\n

2. Mtop<\/h3>\n

mtop<\/strong>\u00a0(MySQL top) is a another similar open source, command line based real time MYSQL Server monitoring tool, was written in\u00a0Perl<\/strong>\u00a0language that display results in tabular format much like\u00a0mytop<\/strong>. mtop monitors MySQL queries which are taking the most amount of time to finish and kills those long running queries after certain specified time.<\/p>\n

In addition, it also enable us to identify performance related problems, configuration information, performance statistics and tuning related tips from the command line interface. The two tools are very similar, but\u00a0mtop<\/strong>\u00a0is not actively maintained and may not work on newly installed MySQL versions.<\/p>\n

\n

\"Install<\/a><\/p>\n

mtop Preview<\/p>\n<\/div>\n

For more about installation instruction read:\u00a0How to Install Mtop (MySQL Monitoring) in Linux<\/a><\/p>\n

3. Innotop<\/h3>\n

Innotop<\/strong>\u00a0is a real time advanced command line based investigation program to monitor local and remote MySQL servers running under\u00a0InnoDB<\/strong>\u00a0engine. Innotop includes many features and comes with different types of modes\/options, which helps us to monitor various aspects of MySQL performance to find out what\u2019s wrong going with MySQL server.<\/p>\n

\n

\"Install<\/a><\/p>\n

Innotop Preview<\/p>\n<\/div>\n

For more about installation instruction read:\u00a0How to Install Innotop (MySQL Monitoring) in Linux<\/a><\/p>\n

4. mysqladmin<\/h3>\n

mysqladmin<\/strong>\u00a0is a default command line MySQL client that comes pre-installed with MySQL package for performing administrative operations such as monitoring processes, checking server configuration, reloading privileges, current status, setting root password, changing root password, create\/drop databases, and much more.<\/p>\n

To check the mysql status as well as uptime run the following command from the terminal, and make sure you must have root permission to execute the command from the shell.<\/p>\n

[root@localhost ~]# mysqladmin -u root -p version\r\nEnter password:<\/pre>\n
Sample Output<\/h5>\n
mysqladmin  Ver 8.42 Distrib 5.1.61, for redhat-linux-gnu on i386\r\nCopyright (c) 2000, 2011, Oracle and\/or its affiliates. All rights reserved.\r\n\r\nOracle is a registered trademark of Oracle Corporation and\/or its\r\naffiliates. Other names may be trademarks of their respective\r\nowners.\r\n\r\nServer version\t\t5.1.61-log\r\nProtocol version\t10\r\nConnection\t\tLocalhost via UNIX socket\r\nUNIX socket\t\t\/var\/lib\/mysql\/mysql.sock\r\nUptime:\t\t\t20 days 54 min 30 sec\r\n\r\nThreads: 1  Questions: 149941143  Slow queries: 21  Opens: 752  Flush tables: 1  Open tables: 745  Queries per second avg: 86.607<\/pre>\n
For more about mysqladmin commands and examples, read:\u00a020 mysqladmin Commands for MySQL Administration in Linux<\/a><\/p>\n
Conclusion<\/h3>\n
If you\u2019re looking for a good monitor tool for your own work, I recommend\u00a0mytop<\/strong>\u00a0and\u00a0innotop<\/strong>. I used to depend on mytop for my daily monitoring purposes, but now I shifted to\u00a0innotop<\/strong>, because it displays much more statistics and information, including important transactions.<\/p>\n
Source<\/a><\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
There are plenty of tools to monitor\u00a0MySQL\u00a0performance and troubleshoot a server, but they don\u2019t always perfect match for a\u00a0MySQL\u00a0developer or administrator\u2019s for their common needs, or may not work in some situations, such as remote or over the web monitoring. MySQL Monitoring Tools Luckily, there are variety of open source tools created by\u00a0MySQL\u00a0community to fill … <\/p>\n
Continue reading “4 Useful Commandline Tools to Monitor MySQL Performance in Linux”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-11495","post","type-post","status-publish","format-standard","hentry","category-linux"],"_links":{"self":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts\/11495","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/comments?post=11495"}],"version-history":[{"count":1,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts\/11495\/revisions"}],"predecessor-version":[{"id":11496,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts\/11495\/revisions\/11496"}],"wp:attachment":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/media?parent=11495"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/categories?post=11495"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/tags?post=11495"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}