Hi,

I notice with ASL 3.0.8 (specifically) Web GUI that the mysqld process runs at fully 99-100% of CPU while the web page is open.
When I close ASL the mysqld process drops down to 5-12%

This happens on all our VPS machines

Any advice or maybe something to check?

Ryan

That sounds like you may have a performance issue with mysql. I worked on a case today where the customer had mysql configured to disable all its caches (it was only uses 32M of RAM too!), and they restricted the amount of connections to mysql, so mysql was hammered to death anytime anything tried to load a bunch of rows.

I dont know if thats the case for you, but whats the output of mysqltuner on your system?

Some output on one server:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.58-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 3817)
[--] Data in InnoDB tables: 18M (Tables: 354)
[--] Data in MEMORY tables: 0B (Tables: 3)
[!!] Total fragmented tables: 566

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 20h 58m 31s (3M q [22.531 qps], 464K conn, TX: 4B, RX: 274M)
[--] Reads / Writes: 59% / 41%
[--] Total buffers: 218.0M global + 4.2M per thread (500 max threads)
[OK] Maximum possible memory usage: 2.3G (59% of installed RAM)
[OK] Slow queries: 0% (19/3M)
[OK] Highest usage of available connections: 7% (36/500)
[OK] Key buffer size / total MyISAM indexes: 128.0M/679.6M
[OK] Key buffer hit rate: 99.5% (7M cached / 37K reads)
[OK] Query cache efficiency: 68.8% (1M cached / 1M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 23K sorts)
[!!] Joins performed without indexes: 2320
[OK] Temporary tables created on disk: 10% (20K on disk / 195K total)
[OK] Thread cache hit rate: 99% (36 created / 464K connections)
[!!] Table cache hit rate: 1% (1K open / 64K opened)
[OK] Open file limit used: 24% (2K/8K)
[OK] Table locks acquired immediately: 99% (689K immediate / 690K locks)
[!!] InnoDB data size / buffer pool: 18.3M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
join_buffer_size (> 1.0M, or always use indexes with joins)
table_cache (> 1024)
innodb_buffer_pool_size (>= 18M)

When I run mysqlcheck on all databases it flies through the databases and tables but it gets stuck on tortix and the related tortix tables for a few minutes.

This clearly seems to be very large tables or there is some thing slowing tortix down.

It sounds like you may have a lot of events in the database. How big is your tortix database?

If your system is having issues storing lots of event records, take a look at this article:

https://www.atomicorp.com/wiki/index.ph ... _Questions

I'm also seeing mysqld hitting 100% CPU when opening the ASL web GUI now. The alert table contains 1157939 rows.

That would certainly qualify as "A Lot"

I checked for the oldest timestamp, but there's really only 7 days worth of alerts. Maybe ASL could do some limiting/paging on the queries for the web UI?