Mysql-show Processlist writing to net

MySQL prompt writing to net resolution

A database has recently been found to have a CPU usage comparison. More than 200%.

First look at the database slow log, set slow log 5 seconds, basically no day, no more than 5 seconds statement.

Show Processlist;

After discovering a few select have several table query takes 1-2 seconds,

Explain select * from XXX, found no index, full table scan, the previous data amount of less that will soon come out, the current query total data are within 20,000, now the data is large, the other tables are 1000w level, previously only optimized the index of large tables, so the problem is highlighted. After the index was built, the CPU showed a drop, as far as I know, the MSYQL CPU occupies a high, 80% of the situation is not using the index.

At the second show Processlist

Multiple statement states are found to be writing to net

Through the analysis of the basic is a SELECT statement, are querying the data too database return network write-back does not win,

View Show global variables like "Global Max_allowed_packet" only 1 m

By increasing the cache

Set global Max_allowed_packet =134217728

Problem solved! The reason is that multiple select return data over the cache exceeds the default setting

Mysql-show Processlist writing to net