1

we have a wordpress site (Centos6.5+apache2.2.15+mysql5.1.73+php5.3), which are server in vultr, I noticed there has a terrible mysql traffic.

Traffic Tip ø per hour
Received    67 MiB  88 MiB
Sent    4,634 MiB   6,106 MiB
Total   4,701 MiB   6,194 MiB

Total   ø per hour  ø per minute    ø per second
1,481 k 1.95 M  32.52 k 541.96


 Show Full Queries  ID  User    Host    Database    Command Time    Status  SQL query
Kill    311206  root    localhost   wp  Sleep   3   --- ---
Kill    311210  root    localhost   wp  Sleep   2   --- ---
Kill    311213  root    localhost   wp  Sleep   1   --- ---
Kill    311214  root    localhost   wp  Sleep   0   --- ---

sent data is 6GB per hour. I listed in show processlist; and view the slow query log, it seems no problem.

Here is my my.cnf

port=3306
socket=/var/lib/mysql/mysql.sock
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
skip-external-locking
bind-address=xx.xx.xx.xx
# I need some remote connection, but I have stopped them in crontab, then monitor again, in processlist and slow query log, all the queries have already come from localhost, but the sent traffic still looks terrible.
max_connections=2500
wait_timeout=30
back_log=256
key_buffer_size=256M
table_cache=2048
sort_buffer_size=128k
read_buffer_size=128k
net_buffer_length=128K
read_rnd_buffer_size=3M
myisam_sort_buffer_size=16M
thread_cache=64
query_cache_size=32M
bulk_insert_buffer_size=16M
ft_min_word_len=2

innodb_buffer_pool_size=256M
innodb_additional_mem_pool_size=8M
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=2
innodb_force_recovery=0
innodb_log_file_size=96M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

httpd.conf

Timeout 45
KeepAlive Off
MaxKeepAliveRequests 256
KeepAliveTimeout 3
<IfModule prefork.c>
StartServers 8
MinSpareServers 5
MaxSpareServers 20
ServerLimit 192
MaxClients 192
MaxRequestsPerChild 2500
</IfModule>

what caused such a huge mysql traffic, Is this normal?

1 Answer 1

1

I have three conjectures on this situation. One or more of them may apply...

CONJECTURE #1

There may be other operations that happen apart from fetching data back-and-forth across the network. Here are a couple of of my old posts where I actually discuss Bytes_received and Bytes_sent

In these posts, I talk about how MySQL Replication creates traffic in its framework. How ?

There are two threads involved for MySQL Replication

  • I/O Thread : TCP/IP Connection back to its Master read binlog events and record the binlog events in the Slave's Relay Logs.
  • SQL Thread : DB Connection that reads binlog events from the Relay Log and executes the SQL. If log-slave-updates and log-bin are enabled in the Slave, the binlog event whose SQL was executed in this thread is recorded in the Slave's binary logs.

If the WordPress DB Server is in Master/Slave Replication topology, note the effect

  • If DB Server is a Master, any Slave's I/O thread connected will make a Master's Bytes_sent increase.
  • If DB Server is a Slave, the I/O thread will increase Bytes_received reading binlog events from the Master and the SQL Thread will increase Bytes_received reading binlog events from the Relay Logs.

Based on the numbers you are showing, I would suspect the DB Server would be a Master and it replicates any log changes to one of more Slaves.

CONJECTURE #2

If the DB Server is not in MySQL Replication Topology at all, I would then suspect some exporting operations, such as mysqldump to another Server or even locally. Why ? A DB Conneciton is still require by mysqldump. Bytes being written to a dump have to be recorded in Bytes_sent.

CONJECTURE #3

If there are neither MySQL Replication nor regular mysqldump backups in place, then I can only see one thing running up Bytes_sent so high and leaving Bytes_received so low: DB Monitoring.

Someone asked this in ServerFault back in August 2011 : 1 billion mysql queries in 24 days? Can something be wrong?. In that post, I explained that running SHOW GLOBAL STATUS often can run up the status counts on Queries and Questions. Retrieving status values frequently can also contribute to increasing Bytes_sent.

UPDATE 2014-11-28 15:10 EST

If you control everything in Vultr and you know for sure that the connections are all from localhost, the problem may not be WordPress itself. It would have to be your either remote connections or just very frequent traffic spikes.

At this point, WordPress would be out of my wheelhouse. Try out the info in this post : How can I improve site/page performance of WordPress websites?

11
  • that is a detailed answer, +1, I will read and test the 3 CONJECTURE, btw, i have not set mysqldump. And If I added to a remote server, my cloud server bandwidth would be calculated by such a huge OUTBOUND GBs
    – Giberno
    Nov 28, 2014 at 19:17
  • SELECT * FROM information_schema.global_status WHERE variable_name IN ('Bytes_received','Bytes_sent'); BYTES_RECEIVED 97180261 BYTES_SENT 201803377228; SELECT * FROM information_schema.session_status WHERE variable_name IN ('Bytes_received','Bytes_sent'); BYTES_RECEIVED 665 BYTES_SENT 8214, then what should I to do? I just have little knowledge about mysql. can you help me more? I can provide you more data. thanks.
    – Giberno
    Nov 28, 2014 at 19:31
  • Your global status is showing your all traffic for the mysqld process. The session is just you. Look for all DB Connections whose processes do not originate with your application. Nov 28, 2014 at 19:36
  • how to find them? SHOW PROCESSLIST all the connection from localhost, and db is wp(wordpress), Command is sleep, Time 0-7
    – Giberno
    Nov 28, 2014 at 19:39
  • show binary logs; #1381 - You are not using binary logging Queries 1964113 Questions 1964113
    – Giberno
    Nov 28, 2014 at 19:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.