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

--
Eduardo Schoedler

Eduardo Schoedler