How do I setup HAProxy traffic logging?

[bgood-clip]

How do I initialize the database schema, so the HAProxy traffic tab will work?

I have found the HAProxy monitoring very useful thus far and would like to see what the traffic tab does, however, I have not been able to figure out properly configure the database from a schema standpoint.

[NickCraver]

The traffic tab is highly specific to Stack Exchange - we have syslog forwarding to an inserter service on a SQL box that's doing locally batched bulk inserted into a schema with daily tables, these then do rollups we look at from the traffic tab perspective (it's just way too much data otherwise - 100m+ rows a day). Releasing that whole toolchain isn't a priority since I imagine the overlap of people who use HAProxy and SQL Server, and want to eat a license and a lot of storage on logs is pretty small.

However, we are also working on storing logs in logstash backed by elasticsearch - that we can simply document as the toolchain is already out there. If/when we move to that internally for traffic rollups, Opserver will have the code for it.

[jdaigle]

@NickCraver any change you could post the raw schema somewhere (i.e. a gist or whatever)? We're curious about exactly what data you persist, and when/how it gets aggregated.

We're not interested in the mechanics of handling/parsing/etc. the log messages, or the code to write to the database; that stuff is pretty straightforward. It's the relational schema we're looking for.

[NickCraver]

@jdaigle There were a lot of changes in motion and I never got back to this - the main table Opserver pulls from is just the aggregate and not the per-hit daily tables. It shifted a bit but here's the current form:

CREATE TABLE [dbo].[Log_Summary_Daily](
    [CreationDate] [date] NOT NULL,
    [Host] [varchar](50) NOT NULL,
    [Server] [varchar](20) NOT NULL,
    [ResponseCode] [smallint] NOT NULL,
    [RouteName] [varchar](70) NULL,
    [Hits] [int] NULL,
    [Tq] [bigint] NULL,
    [Tw] [bigint] NULL,
    [Tc] [bigint] NULL,
    [Tr] [bigint] NULL,
    [Tt] [bigint] NULL,
    [Bytes] [bigint] NULL,
    [IsPageView] [bit] NOT NULL,
    [SqlCount] [bigint] NULL,
    [SqlDurationMs] [bigint] NULL,
    [AspNetDurationMs] [bigint] NULL,
    [RedisCount] [bigint] NULL,
    [RedisDurationMs] [bigint] NULL,
    [HttpCount] [bigint] NULL,
    [HttpDurationMs] [bigint] NULL,
    [TagEngineCount] [bigint] NULL,
    [TagEngineDurationMs] [bigint] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Log_Summary_Daily] ADD  CONSTRAINT [DF_Log_Summary_Daily_IsPageView]  DEFAULT ((0)) FOR [IsPageView]

Now, that being the case, it's still not tenable for use at our scale. We outgrew it. I've removed the traffic tab in Opserver for several reasons, but the main driver was the old layout didn't really work anymore and it was the last remnants relying on the Microsoft charting library. I plan for it to return with 2 changes: a new more usable layout and all SVG graphics (everything in overhaul is SVG now).

I'll update this issue when I get time to bring it back, likely after a master merge takes place.

[brunocatteau]

Can you post a screenshot of the traffic interface?

Thanks