I'll make them official shortly :)
adam.
On 2015-01-12 12:15, Mathieu POUSSIN wrote:
Can we, if there are no issues with those indexes push them into the Observium repository to make them “official” ?
If no, is there a risk of breaking futures update if we push those indexes ? (about the sql files ran when an update is run)
DE : observium [mailto:observium-bounces@observium.org] DE LA PART DE Eduardo Schoedler ENVOYÉ : vendredi 9 janvier 2015 19:43 À : Observium Network Observation System OBJET : Re: [Observium] Speed up graph rendering
Sorry, my previous sql is incorrect.
ALTER TABLE devices_attribs ADD KEY `idx0_devices_attribs` (`device_id`,`attrib_type`(50)) USING BTREE;
More indexes:
ALTER TABLE applications ADD KEY `idx0_applications` (`app_type`(32)) USING BTREE;
ALTER TABLE bgpPeers ADD KEY `idx0_bgpPeers` (`device_id`,`bgpPeerAdminStatus`(15),`bgpPeerState`(15)) USING BTREE;
ALTER TABLE ip_mac ADD KEY `idx0_ip_mac` (`ip_address`(15)) USING BTREE;
ALTER TABLE ipv4_addresses ADD KEY `idx0_ipv4_addresses` (`port_id`,`ipv4_network_id`(10)) USING BTREE;
ALTER TABLE ipv4_addresses ADD KEY KEY `idx1_ipv4_addresses` (`ipv4_address`(15)) USING BTREE;
ALTER TABLE ipv6_addresses ADD KEY `idx0_ipv6_addresses` (`ipv6_address`(64)) USING BTREE;
ALTER TABLE ospf_instances ADD KEY `idx0_ospf_instances` (`ospfAdminStat`) USING BTREE;
ALTER TABLE ports ADD KEY `idx0_ports` (`ifOperStatus`(4),`ifAdminStatus`(5),`ignore`,`deleted`,`ifLastChange`) USING BTREE;
ALTER TABLE ports ADD KEY `idx1_ports` (`device_id`,`port_id`) USING BTREE;
ALTER TABLE ports ADD KEY `idx2_ports` (`device_id`,`deleted`,`port_descr_type`(100),`ifAlias`(100)) USING BTREE;
ALTER TABLE ports-state ADD KEY `idx0_ports-state` (`port_id`,`ifInErrors_delta`,`ifOutErrors_delta`) USING HASH;
ALTER TABLE pseudowires ADD KEY `idx0_pseudowires` (`port_id`) USING BTREE;
ALTER TABLE syslog ADD KEY `idx0_syslog` (`timestamp`,`device_id`) USING BTREE;
ALTER TABLE syslog ADD KEY `idx1_syslog` (`device_id`,`program`) USING BTREE;
ALTER TABLE syslog ADD KEY `idx2_syslog` (`device_id`,`seq`) USING BTREE;
ALTER TABLE users_ckeys ADD KEY `idx0_users_ckeys` (`user_uniq`,`user_ckey`) USING BTREE;
For memory tables, HASH is better.
All indexes was created monitoring slow queries.
I run this queries with EXPLAIN to view execution plan.
Hope that helps.
2015-01-09 16:26 GMT-02:00 Eduardo Schoedler listas@esds.com.br:
I did:
ALTER TABLE ADD KEY `idx0_devices_attribs` (`device_id`,`attrib_type`(50)) USING BTREE;
2015-01-09 15:03 GMT-02:00 Alastair Chamorro Alastair.Chamorro@voip-unlimited.net:
alter table devices_attribs add key device_id(device_id); alter table alert_table add key dev_id(device_id);
I believe these are the two new indexes he added.
Kind Regards
Alastair
FROM: observium [mailto:observium-bounces@observium.org] ON BEHALF OF Adam Armstrong SENT: 09 January 2015 17:00 TO: Observium Network Observation System
SUBJECT: Re: [Observium] Speed up graph rendering
What indexes did he create?
adam.
------ Original Message ------
From: "Alastair Chamorro" Alastair.Chamorro@voip-unlimited.net
To: "Observium Network Observation System" observium@observium.org
Sent: 1/9/2015 9:00:09 AM
Subject: Re: [Observium] Speed up graph rendering
Hi Louis,
We had a similar issue where observium was falling over due to the amount of hosts we are monitoring and so it was alarming on everything all the time as the polls werent dealt with quick enough.
Our DBA moved the mySQL and RRD files to different volumes to spread the load on the drives. He also sorted an indexing issue on the mySQL side.
How many spindles do you have servicing the array? And what caching policy are you using on the RAID controller?
Alastair
FROM: observium [mailto:observium-bounces@observium.org] ON BEHALF OF Louis Bailleul SENT: 09 January 2015 14:46 TO: observium@observium.org SUBJECT: [Observium] Speed up graph rendering
Hi,
I am currently using Observium on 149 switches with a total of 10173 ports. I can't say that anything is wrong with it, but the web interface seems a bit slow especially when you want to display pages with a lot of graphs.
Observium is currently running in a VM with 4 xeon E5-2650 cores and 3Gb of ram. It's virtual disks are on a local raid5 composed of 10K SAS disks.
Observium report that it's VM use on average 25% of its CPUs with spikes at 40% when the web interface is heavily used. The disk I/O doesn't go past 150 ops OUT and rare spikes at 50 IN.
Anyone could advise on what kind of hardware or software tweaking could help speed up the thing ?
Are they special requirements/tunnings for the rrd storage or the mysql database ?
Best regards, Louis
The information contained in this email and any attachments is confidential and may be subject to copyright or other intellectual property protection. If you are not the intended recipient, you are not authorized to use or disclose this information, and we request that you notify us by reply mail or telephone and delete the original message from your mail system.
The information contained in this email and any attachments is confidential and may be subject to copyright or other intellectual property protection. If you are not the intended recipient, you are not authorized to use or disclose this information, and we request that you notify us by reply mail or telephone and delete the original message from your mail system.
observium mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium [1]
--
Eduardo Schoedler
--
Eduardo Schoedler
Links:
[1] http://postman.memetic.org/cgi-bin/mailman/listinfo/observium
observium mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium