Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what is the use of mysqladmin in MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

If we do stress testing on the performance testing tools of MySQL, such as sysbench, we can see the performance indicator QPS,TPS that we are concerned about, and the performance changes during the stress testing process are clear at a glance.

In normal work, if you also want to see the value of this indicator, it is not appropriate to use sysbench. So we need to know how TPS,QPS is calculated and whether we can figure it out without tools like sysbench.

First of all, we can calculate the value of the performance index. We can get a real-time changing state through show status, in which the values are basically cumulative values. We can convert them by setting the frequency to get a near real-time status data.

At this time, mysqladmin will play, not only can you easily check the parameters, such as mysqladmin var, but also easily check the parameters. Mysqladmin shutdown to stop the library, mysqladmin flush-hosts,mysqladmin flush-privileges to refresh permissions, or use mysqladmin pro to check threads. If you need to see QPS, you need to use the results of show status.

For example, I get a status value at an interval of one second.

> show status like 'queries'

+-+ +

| | Variable_name | Value |

+-+ +

| | Queries | 9978587 | |

+-+ + 1 second interval to view

> show status like 'queries'

+-+ +

| | Variable_name | Value |

+-+ +

| | Queries | 9978588 | |

+-+-+ if we want to calculate this value, we can use mysqladmin extended-status to get it, but this is a cumulative value, and if we want to get a difference, there is still an option-r-I to assist, such as loop call-r, with an interval of 1 second-I

For example, if we look at an indicator innodb_pages_read, we can look at it in the following way.

# mysqladmin-r-I 1 extended-status | grep Handler_read_rnd_next

| | Handler_read_rnd_next | 6814 |

| | Handler_read_rnd_next | 399 |

| | Handler_read_rnd_next | 399 |

| | Handler_read_rnd_next | 399 |

If you look at TPS, the formula is actually very simple, as follows:

TPS = (Com_commit + Com_rollback) / Seconds

If you integrate all these indicator values, it will be much easier to view, such as the following way, you can view multiple indicator values.

Mysqladmin-r-I 1 extended-status\

| | grep "Questions\ | Queries\ | Innodb_rows\ | Com_select\ | Com_insert\ | Com_update\ | Com_delete"

But the effect still seems to be a step closer, I read this aspect of Daniel's script, it is still very interesting.

Mysqladmin-r-I 1 ext |\

Awk-F "|" {\

If ($2 ~ / Variable_name/) {\

Print "";\

}\

If ($2 ~ / Questions | Queries | Innodb_rows | Com_select | Com_insert | Com_update | Com_delete | Innodb_buffer_pool_read_requests/)\

Print $2 $3;\

}'

The result of the operation is as follows:

# sh aa.sh

Com_delete 6

Com_insert 4953706

Com_select 2672

Com_update 4

Innodb_buffer_pool_read_requests 67500881

Innodb_rows_deleted 10

Innodb_rows_inserted 8464857

Innodb_rows_read 46945213

Innodb_rows_updated 3

Queries 9978553

Questions 2454058

Com_delete 0

Com_insert 0

Com_select 0

Com_update 0

Innodb_buffer_pool_read_requests 0

Innodb_rows_deleted 0

Innodb_rows_inserted 0

Innodb_rows_read 0

Innodb_rows_updated 0

Queries 1

Questions 1

Then put two big, one is to use awk to deep customization, this effect is awesome.

Mysqladmin-r-I 1 ext |\

Awk-F "|"\

"BEGIN {count=0;}"\

'{if ($2 ~ / Variable_name/ & & + + count = = 1) {\

Print "- |-|-- MySQL Command Status-- |-Innodb row operation-|-- Buffer Pool Read--";\

Print "--Time--- |-- QPS--- | select insert update delete | read inserted updated deleted | logical physical";\

}\

Else if ($2 ~ / Queries/) {queries=$3;}\

Else if ($2 ~ / Com_select /) {com_select=$3;}\

Else if ($2 ~ / Com_insert /) {com_insert=$3;}\

Else if ($2 ~ / Com_update /) {com_update=$3;}\

Else if ($2 ~ / Com_delete /) {com_delete=$3;}\

Else if ($2 ~ / Innodb_rows_read/) {innodb_rows_read=$3;}\

Else if ($2 ~ / Innodb_rows_deleted/) {innodb_rows_deleted=$3;}\

Else if ($2 ~ / Innodb_rows_inserted/) {innodb_rows_inserted=$3;}\

Else if ($2 ~ / Innodb_rows_updated/) {innodb_rows_updated=$3;}\

Else if ($2 ~ / Innodb_buffer_pool_read_requests/) {innodb_lor=$3;}\

Else if ($2 ~ / Innodb_buffer_pool_reads/) {innodb_phr=$3;}\

Else if ($2 ~ / Uptime / & & count > = 2) {\

Printf ("% s |% 9d", strftime ("% H:%M:%S"), queries);\

Printf ("|% 6d% 6d% 6d% 6d", com_select,com_insert,com_update,com_delete);\

Printf ("% 6d% 8d% 7d% 7d", innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted);\

Printf ("| d\ n", innodb_lor,innodb_phr);\

}'

The script is long and the result is cool.

# sh aa.sh

-|-|-- MySQL Command Status-- |-Innodb row operation-|-- Buffer Pool Read--

-Time--- |-QPS--- | select insert update delete | read inserted updated deleted | logical physical

23:10:47 | 1993 | 64 120 | 12296 120 | 1755 0

23:10:48 | 12 9 | 44 0 20 | 11184 0 20 | 1582 0

23:10:49 | 89 | 33 0 10 | 11898 0 10 | 1667 0

And a simplified version.

Mysqladmin extended-status-i1 | awk 'BEGIN {local_switch=0;print "QPS Commit Rollback TPS Threads_con Threads_run\ nMurray -"}

$2 ~ / Queries$/ {QQ stories 4MB LQ investors LQ shares 4;}

$2 ~ / Com_commit$/ {centering 4 Mustang 4 Muhammad 4;}

$2 ~ / Com_rollback$/ {rpm, 4MB, lr, lrc, lr, r, r, o, r, o,

$2 ~ / Threads_connected$/ {tc=$4;}

$2 ~ / Threads_running$/ {tr=$4

If (local_switch==0)

{local_switch=1; count=0}

Else {

If (count > 10)

{count=0;print "- -\ nQPS Commit Rollback TPS Threads_con Threads_run\ nMurray -" }

Else {

Count+=1

Printf "%-6d%-8d%-7d%-8d%-10d% d\ n"

}

}

}'

# sh aa.sh

QPS Commit Rollback TPS Threads_con Threads_run

108 0 3 3 46 2

173 0 1 1 47 2

69 0 0 0 47 2

This is the end of the content of "what is the use of mysqladmin in MySQL". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.