How can I find out why there are so many entries in my system-mysqld database log?
Once reviewed, how can I empty the database log?
I removed network detail report and network report apps this morning, in case they were adding many entries. Currently showing over 3M entries and high system-mysql cpu. Perhaps some of my modules aren't set up correctly.
Here is the system-mysqld.log entries:
140321 13:40:13 mysqld_safe Starting system-mysqld daemon with databases from /var/lib/system-mysql
140321 13:40:13 InnoDB: Initializing buffer pool, size = 500.0M
140321 13:40:13 InnoDB: Completed initialization of buffer pool
140321 13:40:13 InnoDB: Started; log sequence number 0 3011806
140321 13:40:13 [Note] Event Scheduler: Loaded 0 events
140321 13:40:13 [Note] /usr/clearos/sandbox/usr/libexec/system-mysqld: ready for connections.
Thanks,
Wendell
Once reviewed, how can I empty the database log?
I removed network detail report and network report apps this morning, in case they were adding many entries. Currently showing over 3M entries and high system-mysql cpu. Perhaps some of my modules aren't set up correctly.
Here is the system-mysqld.log entries:
140321 13:40:13 mysqld_safe Starting system-mysqld daemon with databases from /var/lib/system-mysql
140321 13:40:13 InnoDB: Initializing buffer pool, size = 500.0M
140321 13:40:13 InnoDB: Completed initialization of buffer pool
140321 13:40:13 InnoDB: Started; log sequence number 0 3011806
140321 13:40:13 [Note] Event Scheduler: Loaded 0 events
140321 13:40:13 [Note] /usr/clearos/sandbox/usr/libexec/system-mysqld: ready for connections.
Thanks,
Wendell
In Database
Share this post:
Responses (15)
-
Accepted Answer
That will still leave the system-mysql database populated. It is possible to log into mysql (you have to do a config change that I'd need to research to get to the system-mysql database) and drop the relevant databases as well. There are a couple of very old threads which may contain instructions. You could try searching for something like "pmacctd" and you may find it - no guarantees. -
Accepted Answer
Ben Chambers wrote:
The sure-fire way to remove the network detail report is:
service pmacctd stop
service system-mysql stop
killall -9 pmacctd
yum remove pmacct app-network-detail-report app-network-detail-report-core
service system-mysql start
B
That worked. Load averages are looking exponentially better. -
Accepted Answer
-
Accepted Answer
-
Accepted Answer
-
Accepted Answer
Hi Wendell,
There is another thread running on what is really the same problem. With your sql knowledge, is there a simple query I can run to count the number of records by day? -
Accepted Answer
I got started with some helpful info by Tim Burgess in a post called "Network Report broken?", and will post the essentials where needed here. This isn't a final solution, but did get some relief as shown in the image 24 hours later.
# removed detail generating apps
> yum remove app-network-report
> yum remove app-network-detail-report
# get mysql password and log into mysql console
> cat /var/clearos/system_database/reports
> /usr/clearos/sandbox/usr/bin/mysql -u reports -p reports
# prompts for long password here
mysql> show tables;
mysql> select count(*) from network_detail; # will display number of records
mysql> select * from network_detail where stamp_inserted < '2014-03-24' Limit 1000; # view records if needed
mysql> delete from network_detail where stamp_inserted < '2014-03-24' ; # leave a few days records for graphing
mysql> \q
# wait 12-24 hours for results to show up in graphs. Sample graph attached.
# hope this helps someone out. I haven't tried Jos latest commands described in the previous post. -
Accepted Answer
The described problems started with the installation of "Network report" and "Network detail report" apps.
I ended up with deinstallation of those apps and then
system-database rebuild
but that didn't help.
With losing all my graphs, but no more 100% CPU usage of system-mysqld this helped:
system-database reset
EDIT: Oh no, the CPU load of system-mysqld is back again after four hours
EDIT again:
I had to remove some packages which were not removed when I uninstalled the above apps.
which also uninstalled "app-network-detail-report-core" with the problematic cron job "app-network-detail-report".yum remove pmacct-0.14.3-2.v6.i686
Then did:
killall networkdetail2db
killall pmacctd
Now back to normal (fingers crossed) -
Accepted Answer
For my ClearOS 6.5.0 system here are the current number of rows in the tables defined in reports schema:
proxy 2,003,429
network_detail 636,125
network 498,007
resource 146,548
network_detail_external 0
proxy_domains 0
My guess on why system_mysqld is sucking up CPU would be missing indexes or poorly constructed queries causing frequent full table scans on proxy or network_detail. Neither of those tables, the two largest, have any indexes:
mysql> SELECT DISTINCT
-> TABLE_NAME,
-> INDEX_NAME
-> FROM INFORMATION_SCHEMA.STATISTICS
-> WHERE TABLE_SCHEMA = 'reports';
+-------------------------+----------------+
| TABLE_NAME | INDEX_NAME |
+-------------------------+----------------+
| network | PRIMARY |
| network | iface |
| network | timestamp |
| network_detail_external | PRIMARY |
| network_detail_external | ip_src |
| network_detail_external | ip_dst |
| network_detail_external | ip |
| network_detail_external | hostname |
| network_detail_external | username |
| network_detail_external | device_type |
| network_detail_external | device_vendor |
| network_detail_external | stamp_inserted |
| proxy_domains | PRIMARY |
| proxy_domains | ip |
| proxy_domains | timestamp |
| proxy_domains | hostname |
| resource | PRIMARY |
+-------------------------+----------------+
17 rows in set (0.01 sec)
Regards,
Peter -
Accepted Answer
I filed a bug report a few days ago [img size=here]http://tracker.clearfoundation.com/view.php?id=1625[/img]...I'll try to get an update to the test repos for next week.
But...as Pete says in the tracker notes, this isn't the cause, but it should stop making the problem worse. The core problem is what's up with MySQL that is causing the problems in the first place. If anyone can focus on that who sees this issue and can help provide data, logs etc. to look at, it will help us get to the root cause.
B. -
Accepted Answer
My ClearOS server has only OpenVPN port open on the WAN side and I am the only VPN user with periodic brief sessions to access LAN side devices remotely. I have an FTP server that gets written to by devices on the LAN but that has all been in place for 2 years. The problem began with the 6.5.0 upgrade but at that time I may also have installed the Network Detail Report and Network Map modules which this thread indicates may be the problem:
http://www.clearfoundation.com/component/option,com_kunena/Itemid,232/catid,12/func,view/id,58760/
Peter -
Accepted Answer
The OP is misleading, the actual size of log files (on disk) can be found by running
[root@leonardo ~]# ls -lah /var/lib/system-mysql/ib_log*
-rw-rw---- 1 system-mysql system-mysql 125M Mar 26 16:25 /var/lib/system-mysql/ib_logfile0
-rw-rw---- 1 system-mysql system-mysql 125M Mar 26 04:18 /var/lib/system-mysql/ib_logfile1
The parameters are set in /usr/clearos/sandbox/etc/my.cnf, and tuned to permit Zarafa to run smoothly
It doesn't explain why system-mysql is eating up your CPU, which should not happen under normal conditions... sounds like one of the network / resource scripts are running away and not completing before the next one starts...but your top output does not show any other running script.
Peter, what services are you running? are any exposed to the web
Seth, you could try increasing the time interval from 5minutes in /etc/cron.d/app-network-report.cron ...perhaps your bus device errors are causing it to hang? you might find more clues in 'dmesg' output. -
Accepted Answer
I am having the exact same problem with system_mysqld running out of control. Very frustrating.
When I was still running Network Detail Report, I was also getting bus errors reported by the networkdetail2db process.
I submitted a support ticket and was told it was probably a hardware problem that was causing my issue. That doesn't seem so likely to me, given how many other people have posted about having the same issue with ClearOS 6.5.0. Unless we all happen to have bad hardware... -
Accepted Answer
-
Accepted Answer
I've been meaning to ask about this one myself. Mine is even worse at a half billion:
121121 11:26:16 InnoDB: Started; log sequence number 0 421085297
My system which is way overbuilt used to loaf but now, with no change in the demand placed on my gateway, mysqld is consistently the top process with nothing else even close:
ID Owner Running CPU Memory Size Command
2091 494 09:01:17 37.7 0.8 332806 system-mysqld
2359 root 00:03:47 0.2 0.1 15288 pmacctd
1747 ldap 00:01:57 0.1 0.3 205773 slapd
2222 root 00:02:06 0.1 0.2 43589 l7-filter
2860 495 00:01:37 0.1 0.7 168411 gconsole
1 root 00:00:00 0.0 0.0 5362 init
2 root 00:00:00 0.0 0.0 0 kthreadd
And my system is experiencing linear load growth over time:
This started with 6.5.0, load over time was flat prior to this update. How best to diagnose?
Peter
Please login to post a reply
You will need to be logged in to be able to post a reply. Login using the form on the right or register an account if you are new here.
Register Here »