Hi,
I would like to start with that the recent changes look really good on the billing pages. Excellent job, I like it.
Over the last few weeks minor changes have been done to these pages and I was wondering if anything was also done to the file: /opt/observium/poll-billing.php or the tables used for it.
That process currently takes about 40 minutes on our environment, while I hoped that upgrading to an Equillogic with 16 SAS drives with RAID10 for more IOPS would fix the issue, while it did improve a bit, the result is the similar. Could it be that there are indices missing? Or is there something else which could be done that might improve this?
I can create some indices myself to see if things improve in a shadow environment, but I'm not sure if that would break things with future SVN update's if done in production ?
Regards, Erik Bais
From the mysql-slow-query log:
# User@Host: observium[observium] @ localhost [] # Query_time: 0.002977 Lock_time: 0.000189 Rows_sent: 2 Rows_examined: 278 SET timestamp=1343723771; SELECT * FROM `bill_ports` as P, `ports` as I, `devices` as D WHERE P.bill_id='68' AND I.port_id = P.port_id AND D.device_id = I.device_id; # Time: 120731 10:36:27 # User@Host: observium[observium] @ localhost [] # Query_time: 0.006157 Lock_time: 0.000172 Rows_sent: 1 Rows_examined: 276 SET timestamp=1343723787; SELECT * FROM `bill_ports` as P, `ports` as I, `devices` as D WHERE P.bill_id='170' AND I.port_id = P.port_id AND D.device_id = I.device_id; # User@Host: observium[observium] @ localhost [] # Query_time: 0.003924 Lock_time: 0.000129 Rows_sent: 1 Rows_examined: 276 SET timestamp=1343723787; SELECT * FROM `bill_ports` as P, `ports` as I, `devices` as D WHERE P.bill_id='156' AND I.port_id = P.port_id AND D.device_id = I.device_id; # User@Host: observium[observium] @ localhost [] # Query_time: 0.002853 Lock_time: 0.000198 Rows_sent: 2 Rows_examined: 278 SET timestamp=1343723787; SELECT * FROM `bill_ports` as P, `ports` as I, `devices` as D WHERE P.bill_id='140' AND I.port_id = P.port_id AND D.device_id = I.device_id; # User@Host: observium[observium] @ localhost [] # Query_time: 0.001970 Lock_time: 0.000166 Rows_sent: 1 Rows_examined: 276 SET timestamp=1343723787; SELECT * FROM `bill_ports` as P, `ports` as I, `devices` as D WHERE P.bill_id='116' AND I.port_id = P.port_id AND D.device_id = I.device_id;
Top output:
Tasks: 189 total, 1 running, 188 sleeping, 0 stopped, 0 zombie Cpu0 : 73.6%us, 16.2%sy, 0.0%ni, 9.1%id, 1.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu1 : 74.2%us, 16.2%sy, 0.0%ni, 9.6%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu2 : 75.6%us, 14.0%sy, 0.0%ni, 10.0%id, 0.3%wa, 0.0%hi, 0.0%si, 0.0%st Cpu3 : 72.8%us, 16.4%sy, 0.0%ni, 10.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 12307228k total, 1980332k used, 10326896k free, 141572k buffers Swap: 7340028k total, 0k used, 7340028k free, 1098304k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 17981 mysql 20 0 4058m 208m 7840 S 329 1.7 284:02.92 mysqld 25265 root 20 0 192m 16m 6608 S 1 0.1 0:10.13 php 13586 root 20 0 192m 16m 6608 S 1 0.1 0:00.69 php 16609 root 20 0 192m 16m 6608 S 1 0.1 0:05.56 php 11807 root 20 0 192m 16m 6608 S 1 0.1 0:06.75 php 26474 root 20 0 192m 16m 6608 S 1 0.1 0:03.88 php
Processes running after 1.5 hours:
root 2272 2259 0 09:50 ? 00:00:00 /bin/sh -c /opt/observium/poll-billing.php >> /dev/null 2>&1 root 2279 2272 0 09:50 ? 00:00:08 php /opt/observium/poll-billing.php root 3757 3747 0 10:25 ? 00:00:00 /bin/sh -c /opt/observium/poll-billing.php >> /dev/null 2>&1 root 3760 3757 0 10:25 ? 00:00:02 php /opt/observium/poll-billing.php root 6983 6972 0 09:55 ? 00:00:00 /bin/sh -c /opt/observium/poll-billing.php >> /dev/null 2>&1 root 6991 6983 0 09:55 ? 00:00:07 php /opt/observium/poll-billing.php root 8755 8743 0 10:30 ? 00:00:00 /bin/sh -c /opt/observium/poll-billing.php >> /dev/null 2>&1 root 8759 8755 0 10:30 ? 00:00:01 php /opt/observium/poll-billing.php root 11514 11501 0 09:25 ? 00:00:00 /bin/sh -c /opt/observium/poll-billing.php >> /dev/null 2>&1 root 11522 11514 0 09:25 ? 00:00:13 php /opt/observium/poll-billing.php root 11803 11793 0 10:00 ? 00:00:00 /bin/sh -c /opt/observium/poll-billing.php >> /dev/null 2>&1 root 11807 11803 0 10:00 ? 00:00:06 php /opt/observium/poll-billing.php root 13578 13566 0 10:35 ? 00:00:00 /bin/sh -c /opt/observium/poll-billing.php >> /dev/null 2>&1 root 13586 13578 0 10:35 ? 00:00:00 php /opt/observium/poll-billing.php root 15995 15983 0 09:30 ? 00:00:00 /bin/sh -c /opt/observium/poll-billing.php >> /dev/null 2>&1 root 15998 15995 0 09:30 ? 00:00:11 php /opt/observium/poll-billing.php root 16605 16595 0 10:05 ? 00:00:00 /bin/sh -c /opt/observium/poll-billing.php >> /dev/null 2>&1 root 16609 16605 0 10:05 ? 00:00:05 php /opt/observium/poll-billing.php root 20583 20573 0 09:35 ? 00:00:00 /bin/sh -c /opt/observium/poll-billing.php >> /dev/null 2>&1 root 20586 20583 0 09:35 ? 00:00:11 php /opt/observium/poll-billing.php root 21514 21504 0 10:10 ? 00:00:00 /bin/sh -c /opt/observium/poll-billing.php >> /dev/null 2>&1 root 21519 21514 0 10:10 ? 00:00:04 php /opt/observium/poll-billing.php root 25260 25252 0 09:40 ? 00:00:00 /bin/sh -c /opt/observium/poll-billing.php >> /dev/null 2>&1 root 25265 25260 0 09:40 ? 00:00:10 php /opt/observium/poll-billing.php root 26469 26457 0 10:15 ? 00:00:00 /bin/sh -c /opt/observium/poll-billing.php >> /dev/null 2>&1 root 26474 26469 0 10:15 ? 00:00:03 php /opt/observium/poll-billing.php root 29987 29976 0 09:45 ? 00:00:00 /bin/sh -c /opt/observium/poll-billing.php >> /dev/null 2>&1 root 29990 29987 0 09:45 ? 00:00:09 php /opt/observium/poll-billing.php root 31342 31332 0 10:20 ? 00:00:00 /bin/sh -c /opt/observium/poll-billing.php >> /dev/null 2>&1 root 31347 31342 0 10:20 ? 00:00:02 php /opt/observium/poll-billing.php
root@portal:/var/log/mysql# ps -ef | grep poll-billing | wc -l 30