0

1.for mysql slow query , we use pt-query-digest tools from Percona ,

we deploy a crontab in mysql db server,

the crontab use pt-query-digest tools to write the mysql slow query data to a manager db about every 15 minutes.

like this: $pt_query_digest --user=$db_user --password=$db_password --port=$db_port --review h=$db_host,D=$db_database,t=mysql_slow_query_review --history h=$db_host,D=$db_database,t=mysql_slow_query_review_history --no-report --limit=100% --filter=" \$event->{add_column} = length(\$event->{arg}) and \$event->{serverid}=$server_id " $slowquery_file

2.we find when the crontab job running . it will ues one cpu 100%,our db have 6 cpu, so when the total cpu will use 20%, when the crontab job running.

it will runnin about 6-8 minutes to complete ,

3.so my question is

why pt_query_digest cause so much cpu ,is there any method to avoid the cpu high issue .

mike_dba
  • 21
  • 2
  • run it with `nice -n 19` and then its priority will be so low that everything else gets priority. It consumes a lot of CPU because its pattern matching a lot of unindexed text and possibly not doing it in the most efficient way possible – danblack May 18 '20 at 03:53
  • yes, the server is ruuning in on virtual machine, we guess maybe the disk ip is bottlem , – mike_dba May 18 '20 at 07:06
  • nice also (de-)prioritizes IO traffic, however not the IO in the mysqld process's mysql table where the slow queries are stored. Is the table CSV or MyISAM? Maybe worth indexing the server ID on this table if possible. – danblack May 18 '20 at 07:49

0 Answers0