Are there any performance limits for tholds ?

[alkman82]

Hi everyone in this excellent forum!

Maybe my question is a bit silly because I know it is basically depending on the hardware. Let's describe my problem:

I have a pretty strong hardware config for the cacti server (16 cores with 32GB RAM) with
SYSTEM STATS: Time:96.4769 Method:spine Processes:10 Threads:20 Hosts:3789 HostsPerProcess:379 DataSources:195636 RRDsProcessed:0

SYSTEM THOLD STATS: Time:58.0283 Tholds:25730 TotalHosts:3788 DownHosts:146 NewDownHosts:0

Cacti Version: 0.8.8a, Plugin Architecture Version: 3.1, Thold 0.5, Boost 5.1

Despite all the above, the last month I had two server crashes (reboot from console was the only solution). After all everything was up and running except the thold_data table that was corrupted and I had to restore all database from backup in order to fix this.

Does anyone have any suggestions if there is some kind of "limit" in the database or if I need to fine-tune something in order to prevent the system from future crashes? I also have the need to increase the number of thold checks over 30000 but can't do it for now as you can see.

Thank you !

[cigamit]

There are lot of performance tweaks you could do. The first thing I would do is ensure that my poller_output table is a MEMORY table.

For such a large system, did you optimize your MySQL server at all? Is MySQL running on its own box?

[alkman82]

Thank you for your answer cigamit! Yes I did some tuning to the sql server in the past but I am not an expert on this. As I can see in the mysqltuner output now I have some more thinks to do now again.

The mysql server runs on the same system. I can't say that the system performance is low in order to separate it. The poller_output table is not a memory table but all job I think is done now by boost plugin with it's own memory tables. Is that right?

Do you think it's time to think the multi poller solution? (I don't know how reliable is the Plugin "Multipollerserver").

Here is my mysqltuner.pl output:

Code: Select all

>>  MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: *****
Please enter your MySQL administrative password: 

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

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 458M (Tables: 228)
[--] Data in InnoDB tables: 2M (Tables: 55)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 985M (Tables: 4)
[!!] Total fragmented tables: 92

-------- Performance Metrics -------------------------------------------------
[--] Up for: 8d 4h 3m 24s (1B q [2K qps], 9M conn, TX: 1010B, RX: 320B)
[--] Reads / Writes: 75% / 25%
[--] Total buffers: 608.0M global + 40.6M per thread (700 max threads)
[!!] Maximum possible memory usage: 28.4G (90% of installed RAM)
[OK] Slow queries: 0% (573/1B)
[OK] Highest usage of available connections: 52% (364/700)
[OK] Key buffer size / total MyISAM indexes: 384.0M/308.7M
[OK] Key buffer hit rate: 100.0% (39B cached / 1M reads)
[OK] Query cache efficiency: 76.1% (1B cached / 1B selects)
[!!] Query cache prunes per day: 24307735
[OK] Sorts requiring temporary tables: 0% (344 temp sorts / 75M sorts)
[!!] Joins performed without indexes: 115500
[OK] Temporary tables created on disk: 0% (130K on disk / 19M total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (400 open / 214K opened)
[OK] Open file limit used: 0% (524/65K)
[OK] Table locks acquired immediately: 97% (559M immediate / 575M locks)
[OK] InnoDB data size / buffer pool: 2.7M/16.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 64M)
    join_buffer_size (> 32.0M, or always use indexes with joins)
    thread_cache_size (start at 4)
    table_cache (> 400)

[Howie]

Code: Select all

[!!] Joins performed without indexes: 115500

is not really something you want to see in a production db. That would be something for the developers.

"reboot from console was the only solution" sounds more like resource problems (like excessive swapping or similar) though. Do you keep Cacti graphs of your Cacti server to check those kinds of things? (You can also use Cacti to monitor the mysql statistics in real time). At least then you'd have a history leading up to the next crash if there is one.

[alkman82]

I have all the following graphs and some other for disk space usage but nothing seems to be abnormal the last month leading to a "crash".

Cacti Poller Statistics - Boost Memory
Cacti Poller Statistics - Boost Runtime
Cacti Poller Statistics - Boost Updates
Cacti Poller Statistics - DS/RRD
Cacti Poller Statistics - Export Duration
Cacti Poller Statistics - Hosts
Cacti Poller Statistics - Items
Cacti Poller Statistics - Processes
Cacti Poller Statistics - Recache
Linux - Memory Usage
ucd/net - CPU Usage
ucd/net - Load Average
ucd/net - Memory Usage
Unix - Load Average
Unix - Logged in Users
Unix - Processes

I am now about to add some more graphs for mysql stats and I/O and I' ll come back If I have something new.