Forums

Wendell
Wendell
Offline
Resolved
0 votes
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
Friday, March 21 2014, 06:08 PM
Share this post:
Responses (15)
  • Accepted Answer

    Thursday, March 17 2016, 10:04 PM - #Permalink
    Resolved
    0 votes
    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.
    The reply is currently minimized Show
  • Accepted Answer

    Thursday, March 17 2016, 08:34 PM - #Permalink
    Resolved
    0 votes
    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.
    The reply is currently minimized Show
  • Accepted Answer

    Thursday, March 17 2016, 01:33 PM - #Permalink
    Resolved
    1 votes
    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
    The reply is currently minimized Show
  • Accepted Answer

    Wednesday, March 16 2016, 10:17 PM - #Permalink
    Resolved
    0 votes
    The app was withdrawn from the marketplace because there was no obvious fix. The recommended solution is to uninstall the app which won't really help you.
    The reply is currently minimized Show
  • Accepted Answer

    Wednesday, March 16 2016, 09:44 PM - #Permalink
    Resolved
    0 votes
    I've been having this problem for the last couple of years without a solution. My workaround is to set up a cron job to run
    system-database reset
    every night to clear the database, which improves my load averages dramatically. I would like a more permanent solution. Did anyone find one?
    The reply is currently minimized Show
  • Accepted Answer

    Saturday, August 09 2014, 08:36 PM - #Permalink
    Resolved
    0 votes
    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?
    The reply is currently minimized Show
  • Accepted Answer

    Wendell
    Wendell
    Offline
    Friday, April 04 2014, 12:20 AM - #Permalink
    Resolved
    0 votes
    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.
    The reply is currently minimized Show
  • Accepted Answer

    Tuesday, April 01 2014, 08:00 PM - #Permalink
    Resolved
    0 votes
    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.
    yum remove pmacct-0.14.3-2.v6.i686
    which also uninstalled "app-network-detail-report-core" with the problematic cron job "app-network-detail-report".
    Then did:
    killall networkdetail2db
    killall pmacctd

    Now back to normal (fingers crossed)
    The reply is currently minimized Show
  • Accepted Answer

    Friday, March 28 2014, 03:54 AM - #Permalink
    Resolved
    0 votes
    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
    The reply is currently minimized Show
  • Accepted Answer

    Thursday, March 27 2014, 02:14 PM - #Permalink
    Resolved
    0 votes
    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.
    The reply is currently minimized Show
  • Accepted Answer

    Thursday, March 27 2014, 02:29 AM - #Permalink
    Resolved
    0 votes
    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
    The reply is currently minimized Show
  • Accepted Answer

    Wednesday, March 26 2014, 05:44 PM - #Permalink
    Resolved
    0 votes
    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.
    The reply is currently minimized Show
  • Accepted Answer

    Wednesday, March 26 2014, 03:31 PM - #Permalink
    Resolved
    0 votes
    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...
    The reply is currently minimized Show
  • Accepted Answer

    Wednesday, March 26 2014, 02:19 AM - #Permalink
    Resolved
    0 votes
    Devs: Would appreciate a response on this one please.

    The database (system_mysqld) is now using 100% of one 3GHz core for the first 30 seconds of every minute and then pretty much idle for the remainder.

    Peter
    The reply is currently minimized Show
  • Accepted Answer

    Saturday, March 22 2014, 03:16 AM - #Permalink
    Resolved
    0 votes
    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:

    http://s7.postimg.org/qmn3jwe0b/image.jpg

    This started with 6.5.0, load over time was flat prior to this update. How best to diagnose?

    Peter
    The reply is currently minimized Show
Your Reply