Okay, i enabled mysql slow query (1 second :D) logging, but there is no slow query when using quick search. I gave mysqlcheck with –optimize a try and this seems to work. Results are shown in 2-4 seconds now.
To answer your question about the two tables; mysql> select count(*) FROM `accesspoints-state`; +----------+ | count(*) | +----------+ | 57797349 | +----------+ 1 row in set (12.70 sec) mysql> select count(*) FROM notifications_queue; +----------+ | count(*) | +----------+ | 9120 | +----------+ 1 row in set (0.01 sec) Looks like notifications_queue has many old notifications – can i just empty this table? I looked after some entries – they are email notifications with ALERT_URLs that do not exist. There is just a message displayed: „Unfortunately, this alert entry id does not seem to exist in the database!“ So i think they dont need to be in the database anymore. Sorry i cant grant access without having legal hassle.. this is bad for me, but i think the response times are ok for now. But thanks for your help so far. Kind regards Florian Von: Adam Armstrong via observium <[email protected]> Gesendet: Dienstag, 26. November 2024 18:31 An: Meyer, Florian via observium <[email protected]> Cc: Adam Armstrong <[email protected]> Betreff: [Observium] Re: Quick Search Slow - how to troubleshoot? EXTERNER ABSENDER: Klicken Sie nur dann auf Links und Anhänge, wenn Sie dem Absender der Nachricht vertrauen. Diese Nachricht stammt von außerhalb der UMR! We don't really have any profiling on the search, but what it does is run a bunch of queries on all of the tables, which can be quite slow on some very large installs. You /might/ want to export and recreate the database to free up the disk space from the eventlog and syslog tables, but if you aren't short of space you can leave it. It's odd that the accesspoints-state and notifications_queue tables are so large. How many rows do they have? I'm not sure if the AP table is used in the search query, if it's very large it might be the cause. If that's not the cause we might be able to figure out which specific queries are slow. Are you able to provide ssh/http access to the system so I can figure out which bits are slow and see if I can make them faster? (the alternative is a dump of the database, but that's usually difficult for the same reason!) adam. Meyer, Florian via observium wrote on 26/11/2024 17:02: Hi Adam, thanks for you reply. You are right 11 million entries look quite much. I had the housekeeping setting for syslog to keep 30 days and eventlogt o keep 365 days. I reduced both now to 7 days. Housekeeping script crunched like half an hour on those entries. I reduced syslog to 2.5 million and eventlog to 232k. +-------------------------------+-----------+ | Table | Size (MB) | +-------------------------------+-----------+ | eventlog | 5469 | | syslog | 3275 | | accesspoints-state | 2861 | | notifications_queue | 902 | | alert_log | 464 | | vlans_fdb | 248 | | bill_data | 131 | But the problem still exist. The response times of the quick search is not faster. :( Any other advise? Kind regards, Florian Von: Adam Armstrong via observium <[email protected]><mailto:[email protected]> Gesendet: Montag, 25. November 2024 17:09 An: Observium <[email protected]><mailto:[email protected]> Cc: Adam Armstrong <[email protected]><mailto:[email protected]> Betreff: [Observium] Re: Quick Search Slow - how to troubleshoot? EXTERNER ABSENDER: Klicken Sie nur dann auf Links und Anhänge, wenn Sie dem Absender der Nachricht vertrauen. Diese Nachricht stammt von außerhalb der UMR! First truncate the syslog and eventlog tables, 11 million entries is unreasonable. Make sure you have housekeeping configured to keep the size of those tables down, MySQL is really unsuitable for large log storage. You need to truncate the tables first though, because housekeeping will take forever on those large tables. After those tables are at a manageable size you can see if it’s any faster. Adam. Sent from my iPhone On 25 Nov 2024, at 14:49, Meyer, Florian via observium <[email protected]<mailto:[email protected]>> wrote: Hi all, i have a problem with the quick search in my installation of observium professional. The results are displayed with a big delay. It varies from 3 to 10 seconds. Everything else is very fast (in my opinion): <image001.png> Sometimes only a part of the string i entered is searched. My MariaDB is on another virtual server. The problem started when i upgraded the OS from 10 to 12 a month ago. Does anybody have an idea how i can troubleshoot this issue? Version Information Observium 24.10.13681 (20th October 2024) OS Linux 5.15.0-126-generic [amd64] (Ubuntu 22.04) Apache 2.4.52 (Ubuntu) PHP 8.1.2-1ubuntu2.19 (OPcache: ENABLED) (Memory: 1GB) Python 3.10.12 MariaDB 10.11.6-MariaDB-0+deb12u1 (extension: mysqli 8.1.2-1ubuntu2.19) SNMP NET-SNMP 5.9.1 RRDtool 1.7.2 (rrdcached 1.7.2: unix:/var/run/rrdcached.sock) Fping 5.1 (IPv4 and IPv6) Fetch cURL 7.81.0 (OpenSSL/3.0.2, LibZ 1.2.11, LibIDN 2.3.2) Browser & Timezone Information User-Agent Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36 Browser Chrome 130.0 (Windows) Screen Resolution 3840x1600 Timezone +01:00 (System), +01:00 (PHP), +01:00 (DB), +01:00 (User) Statistics DB size 15.5GB RRD size 116GB Devices 779 Ports 41280 IPv4 Addresses 3271 IPv4 Networks 3497 IPv6 Addresses 287 IPv6 Networks 21 Services Applications 5 Processors 1330 Memory pools 1055 Storage Entries 858 Disk I/O Entries 1996 HR-MIB Entries 875 Entity-MIB Entries 52783 Syslog Entries 11299175 Eventlog Entries 11656552 Sensors 135228 Printer Supplies 0 Netscaler VServers 641 Netscaler Services 503 Virtual Machines 0 IP SLAs Kind regards, Florian _______________________________________________ observium mailing list -- [email protected]<mailto:[email protected]> To unsubscribe send an email to [email protected]<mailto:[email protected]> _______________________________________________ observium mailing list -- [email protected]<mailto:[email protected]> To unsubscribe send an email to [email protected]<mailto:[email protected]> -- Sent from Postbox<https://www.postbox-inc.com/?utm_source=email&utm_medium=siglink&utm_campaign=reach>
mysqlcheck -uroot --optimize --all-databases mysql.column_stats OK mysql.columns_priv OK mysql.db OK mysql.event OK mysql.func OK mysql.global_priv OK mysql.gtid_slave_pos note : Table does not support optimize, doing recreate + analyze instead status : OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.index_stats OK mysql.innodb_index_stats note : Table does not support optimize, doing recreate + analyze instead status : OK mysql.innodb_table_stats note : Table does not support optimize, doing recreate + analyze instead status : OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.roles_mapping OK mysql.servers OK mysql.table_stats OK mysql.tables_priv OK mysql.time_zone Table is already up to date mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.transaction_registry note : Table does not support optimize, doing recreate + analyze instead status : OK observium.accesspoints note : Table does not support optimize, doing recreate + analyze instead status : OK observium.accesspoints-state note : Table does not support optimize, doing recreate + analyze instead status : OK observium.alert_assoc note : Table does not support optimize, doing recreate + analyze instead status : OK observium.alert_contacts note : Table does not support optimize, doing recreate + analyze instead status : OK observium.alert_contacts_assoc note : Table does not support optimize, doing recreate + analyze instead status : OK observium.alert_log note : Table does not support optimize, doing recreate + analyze instead status : OK observium.alert_table note : Table does not support optimize, doing recreate + analyze instead status : OK observium.alert_tests note : Table does not support optimize, doing recreate + analyze instead status : OK observium.alerts_maint note : Table does not support optimize, doing recreate + analyze instead status : OK observium.alerts_maint_assoc note : Table does not support optimize, doing recreate + analyze instead status : OK observium.applications note : Table does not support optimize, doing recreate + analyze instead status : OK observium.authlog note : Table does not support optimize, doing recreate + analyze instead status : OK observium.autodiscovery note : Table does not support optimize, doing recreate + analyze instead status : OK observium.bgpPeers note : Table does not support optimize, doing recreate + analyze instead status : OK observium.bgpPeers_cbgp note : Table does not support optimize, doing recreate + analyze instead status : OK observium.bill_data note : Table does not support optimize, doing recreate + analyze instead status : OK observium.bill_entities note : Table does not support optimize, doing recreate + analyze instead status : OK observium.bill_history note : Table does not support optimize, doing recreate + analyze instead status : OK observium.bills note : Table does not support optimize, doing recreate + analyze instead status : OK observium.cef_prefix note : Table does not support optimize, doing recreate + analyze instead status : OK observium.cef_switching note : Table does not support optimize, doing recreate + analyze instead status : OK observium.config note : Table does not support optimize, doing recreate + analyze instead status : OK observium.counters note : Table does not support optimize, doing recreate + analyze instead status : OK observium.customers note : Table does not support optimize, doing recreate + analyze instead status : OK observium.dash_widgets note : Table does not support optimize, doing recreate + analyze instead status : OK observium.dashboards note : Table does not support optimize, doing recreate + analyze instead status : OK observium.device_graphs note : Table does not support optimize, doing recreate + analyze instead status : OK observium.devices note : Table does not support optimize, doing recreate + analyze instead status : OK observium.devices_locations note : Table does not support optimize, doing recreate + analyze instead status : OK observium.devices_mibs note : Table does not support optimize, doing recreate + analyze instead status : OK observium.eigrp_ases note : Table does not support optimize, doing recreate + analyze instead status : OK observium.eigrp_peers note : Table does not support optimize, doing recreate + analyze instead status : OK observium.eigrp_ports note : Table does not support optimize, doing recreate + analyze instead status : OK observium.eigrp_vpns note : Table does not support optimize, doing recreate + analyze instead status : OK observium.entPhysical note : Table does not support optimize, doing recreate + analyze instead status : OK observium.entPhysical-state note : Table does not support optimize, doing recreate + analyze instead status : OK observium.entity_attribs note : Table does not support optimize, doing recreate + analyze instead status : OK observium.entity_permissions note : Table does not support optimize, doing recreate + analyze instead status : OK observium.eventlog note : Table does not support optimize, doing recreate + analyze instead status : OK observium.group_table note : Table does not support optimize, doing recreate + analyze instead status : OK observium.groups note : Table does not support optimize, doing recreate + analyze instead status : OK observium.groups_assoc note : Table does not support optimize, doing recreate + analyze instead status : OK observium.hrDevice note : Table does not support optimize, doing recreate + analyze instead status : OK observium.ip_mac note : Table does not support optimize, doing recreate + analyze instead status : OK observium.ipsec_tunnels note : Table does not support optimize, doing recreate + analyze instead status : OK observium.ipv4_addresses note : Table does not support optimize, doing recreate + analyze instead status : OK observium.ipv4_networks note : Table does not support optimize, doing recreate + analyze instead status : OK observium.ipv6_addresses note : Table does not support optimize, doing recreate + analyze instead status : OK observium.ipv6_networks note : Table does not support optimize, doing recreate + analyze instead status : OK observium.isis_instances note : Table does not support optimize, doing recreate + analyze instead status : OK observium.juniAtmVp note : Table does not support optimize, doing recreate + analyze instead status : OK observium.lb_pool_members note : Table does not support optimize, doing recreate + analyze instead status : OK observium.lb_pools note : Table does not support optimize, doing recreate + analyze instead status : OK observium.lb_snatpools note : Table does not support optimize, doing recreate + analyze instead status : OK observium.lb_virtuals note : Table does not support optimize, doing recreate + analyze instead status : OK observium.loadbalancer_rservers note : Table does not support optimize, doing recreate + analyze instead status : OK observium.loadbalancer_vservers note : Table does not support optimize, doing recreate + analyze instead status : OK observium.lsp note : Table does not support optimize, doing recreate + analyze instead status : OK observium.mac_accounting note : Table does not support optimize, doing recreate + analyze instead status : OK observium.mempools note : Table does not support optimize, doing recreate + analyze instead status : OK observium.munin_plugins note : Table does not support optimize, doing recreate + analyze instead status : OK observium.munin_plugins_ds note : Table does not support optimize, doing recreate + analyze instead status : OK observium.neighbours note : Table does not support optimize, doing recreate + analyze instead status : OK observium.netmaps note : Table does not support optimize, doing recreate + analyze instead status : OK observium.netscaler_servicegroupmembers note : Table does not support optimize, doing recreate + analyze instead status : OK observium.netscaler_services note : Table does not support optimize, doing recreate + analyze instead status : OK observium.netscaler_services_vservers note : Table does not support optimize, doing recreate + analyze instead status : OK observium.netscaler_vservers note : Table does not support optimize, doing recreate + analyze instead status : OK observium.notifications_queue note : Table does not support optimize, doing recreate + analyze instead status : OK observium.observium_actions note : Table does not support optimize, doing recreate + analyze instead status : OK observium.observium_attribs note : Table does not support optimize, doing recreate + analyze instead status : OK observium.observium_processes note : Table does not support optimize, doing recreate + analyze instead status : OK observium.oids note : Table does not support optimize, doing recreate + analyze instead status : OK observium.oids_entries note : Table does not support optimize, doing recreate + analyze instead status : OK observium.ospf_areas note : Table does not support optimize, doing recreate + analyze instead status : OK observium.ospf_instances note : Table does not support optimize, doing recreate + analyze instead status : OK observium.ospf_nbrs note : Table does not support optimize, doing recreate + analyze instead status : OK observium.ospf_ports note : Table does not support optimize, doing recreate + analyze instead status : OK observium.p2p_radios note : Table does not support optimize, doing recreate + analyze instead status : OK observium.packages note : Table does not support optimize, doing recreate + analyze instead status : OK observium.pollers note : Table does not support optimize, doing recreate + analyze instead status : OK observium.ports note : Table does not support optimize, doing recreate + analyze instead status : OK observium.ports_adsl note : Table does not support optimize, doing recreate + analyze instead status : OK observium.ports_bayStackStats note : Table does not support optimize, doing recreate + analyze instead status : OK observium.ports_cbqos note : Table does not support optimize, doing recreate + analyze instead status : OK observium.ports_hp-dot1x note : Table does not support optimize, doing recreate + analyze instead status : OK observium.ports_hp_user_auth note : Table does not support optimize, doing recreate + analyze instead status : OK observium.ports_stack note : Table does not support optimize, doing recreate + analyze instead status : OK observium.ports_vlans note : Table does not support optimize, doing recreate + analyze instead status : OK observium.printersupplies note : Table does not support optimize, doing recreate + analyze instead status : OK observium.probes note : Table does not support optimize, doing recreate + analyze instead status : OK observium.processors note : Table does not support optimize, doing recreate + analyze instead status : OK observium.pseudowires note : Table does not support optimize, doing recreate + analyze instead status : OK observium.roles note : Table does not support optimize, doing recreate + analyze instead status : OK observium.roles_entity_permissions note : Table does not support optimize, doing recreate + analyze instead status : OK observium.roles_permissions note : Table does not support optimize, doing recreate + analyze instead status : OK observium.roles_users note : Table does not support optimize, doing recreate + analyze instead status : OK observium.sensors note : Table does not support optimize, doing recreate + analyze instead status : OK observium.services note : Table does not support optimize, doing recreate + analyze instead status : OK observium.slas note : Table does not support optimize, doing recreate + analyze instead status : OK observium.snmp_errors note : Table does not support optimize, doing recreate + analyze instead status : OK observium.status note : Table does not support optimize, doing recreate + analyze instead status : OK observium.storage note : Table does not support optimize, doing recreate + analyze instead status : OK observium.syslog note : Table does not support optimize, doing recreate + analyze instead status : OK observium.syslog_alerts note : Table does not support optimize, doing recreate + analyze instead status : OK observium.syslog_rules note : Table does not support optimize, doing recreate + analyze instead status : OK observium.syslog_rules_assoc note : Table does not support optimize, doing recreate + analyze instead status : OK observium.ucd_diskio note : Table does not support optimize, doing recreate + analyze instead status : OK observium.users note : Table does not support optimize, doing recreate + analyze instead status : OK observium.users_ckeys note : Table does not support optimize, doing recreate + analyze instead status : OK observium.users_prefs note : Table does not support optimize, doing recreate + analyze instead status : OK observium.vlans note : Table does not support optimize, doing recreate + analyze instead status : OK observium.vlans_fdb note : Table does not support optimize, doing recreate + analyze instead status : OK observium.vlans_fdb_old note : Table does not support optimize, doing recreate + analyze instead status : OK observium.vminfo note : Table does not support optimize, doing recreate + analyze instead status : OK observium.vrfs note : Table does not support optimize, doing recreate + analyze instead status : OK observium.weathermaps note : Table does not support optimize, doing recreate + analyze instead status : OK observium.wifi_accesspoints note : Table does not support optimize, doing recreate + analyze instead status : OK observium.wifi_aps note : Table does not support optimize, doing recreate + analyze instead status : OK observium.wifi_aps_members note : Table does not support optimize, doing recreate + analyze instead status : OK observium.wifi_radios note : Table does not support optimize, doing recreate + analyze instead status : OK observium.wifi_sessions note : Table does not support optimize, doing recreate + analyze instead status : OK observium.wifi_stations note : Table does not support optimize, doing recreate + analyze instead status : OK observium.wifi_wlans note : Table does not support optimize, doing recreate + analyze instead status : OK observium.winservices note : Table does not support optimize, doing recreate + analyze instead status : OK sys.sys_config OK
_______________________________________________ observium mailing list -- [email protected] To unsubscribe send an email to [email protected]
