Haproxy CSV stats

Haproxy exposes a very useful statistics page that can be accessed over a web browser or from the command line using a tool like curl.
Haproxy allows the stats to be exposed in a CSV format that is exceedingly useful if you are going to have a script around it.

To access the CSV stats from http interface use the below:

curl  -u statsuser:statspassword 'http://haproxyhost:8001/stats/?stats;csv;' 2>/dev/null |egrep -iv "up|^#|open" |cut -d"," -f1,2,18

Where port 8001 is the statistics port as defined in the haproxy config.
The config is simple, already shown in older posts, should be something like this:

listen stats *:8001
    stats enable
    stats uri /stats
    stats hide-version
    stats auth statsuser:statspassword


The above mini script will print out only the apps and backends that show as down.
working ones will not show up.
The CVS header is also stripped as the header always starts with a "#" this makes it possible to process only the data using a grep -v as above.

The CSV result contains a big amount of info that can be used for load management and automation.
The details of the haproxy CSV stats layout can be found at:
https://cbonte.github.io/haproxy-dconv/1.5/configuration.html#9  

Updating a field of a DB table using records from same table

The below query is to update a field that is set to null in a config table from records that contain values in the same table.

I came across this scenario while working on a puppet upgrade project where we are moving the file based Yaml configs to SQL DB.


below is the query:

update config_dev c

set groupname = l.groupname

from ( select distinct (groupname) , name from config_dev where groupname is not null ) l

where c.groupname is null

and c.name = l.name

The table contains some "groupname" fields that contains null, still those should contain same group values as others given that name field is the same.

the above query will do that for us.
Not that the above query will not run in MySQL !!

Thanks to my friend Mohamed Youssef who helped me with this query.

Setting up a new PostgreSQL on Redhat systems

Postgres setup turns out to be not as straight forward as I assumed.
When you setup the DB server using yum, the DB comes in a not initialized state, and thus it fails to start using standard service commands.

The below Fedora project wiki link entails a lot of details about this issue:
https://fedoraproject.org/wiki/PostgreSQL

An example on CentOS 7 looks like below.
Once you initialize the DB it starts up with no issues.

[root@vardamir ~]# systemctl start postgresql
Job for postgresql.service failed because the control process exited with error code. See "systemctl status postgresql.service" and "journalctl -xe" for details.
[root@vardamir ~]# journalctl -xe
-- Documentation: http://www.freedesktop.org/wiki/Software/systemd/multiseat
--
-- A new session with the ID 3 has been created for the user root.
--
-- The leading process of the session is 11388.
Sep 19 11:54:29 vardamir systemd[1]: Starting Session 3 of user root.
-- Subject: Unit session-3.scope has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit session-3.scope has begun starting up.
Sep 19 11:54:29 vardamir sshd[11388]: pam_unix(sshd:session): session opened for user root by (uid=0)
Sep 19 11:54:30 vardamir dbus[725]: [system] Activating service name='org.freedesktop.problems' (using servicehelper)
Sep 19 11:54:30 vardamir dbus-daemon[725]: dbus[725]: [system] Activating service name='org.freedesktop.problems' (using servicehelper)
Sep 19 11:54:30 vardamir dbus[725]: [system] Successfully activated service 'org.freedesktop.problems'
Sep 19 11:54:30 vardamir dbus-daemon[725]: dbus[725]: [system] Successfully activated service 'org.freedesktop.problems'
Sep 19 11:54:52 vardamir polkitd[1394]: Registered Authentication Agent for unix-process:11426:31997 (system bus name :1.28 [/usr/bin/pkttyagent --notify-fd 5 --fallback],
Sep 19 11:54:52 vardamir systemd[1]: Starting PostgreSQL database server...
-- Subject: Unit postgresql.service has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit postgresql.service has begun starting up.
Sep 19 11:54:52 vardamir postgresql-check-db-dir[11431]: "/var/lib/pgsql/data" is missing or empty.
Sep 19 11:54:52 vardamir postgresql-check-db-dir[11431]: Use "postgresql-setup initdb" to initialize the database cluster.
Sep 19 11:54:52 vardamir postgresql-check-db-dir[11431]: See /usr/share/doc/postgresql-9.2.15/README.rpm-dist for more information.
Sep 19 11:54:52 vardamir systemd[1]: postgresql.service: control process exited, code=exited status=1
Sep 19 11:54:52 vardamir systemd[1]: Failed to start PostgreSQL database server.
-- Subject: Unit postgresql.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit postgresql.service has failed.
--
-- The result is failed.
Sep 19 11:54:52 vardamir systemd[1]: Unit postgresql.service entered failed state.
Sep 19 11:54:52 vardamir systemd[1]: postgresql.service failed.
Sep 19 11:54:52 vardamir polkitd[1394]: Unregistered Authentication Agent for unix-process:11426:31997 (system bus name :1.28, object path /org/freedesktop/PolicyKit1/Authe
[root@vardamir ~]# postgresql-setup initdb
Initializing database ... OK

[root@vardamir ~]# systemctl status postgresql
● postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2016-09-19 11:58:57 EDT; 10s ago
  Process: 11542 ExecStart=/usr/bin/pg_ctl start -D ${PGDATA} -s -o -p ${PGPORT} -w -t 300 (code=exited, status=0/SUCCESS)
  Process: 11537 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 11546 (postgres)
   CGroup: /system.slice/postgresql.service
           ├─11546 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
           ├─11547 postgres: logger process  
           ├─11549 postgres: checkpointer process  
           ├─11550 postgres: writer process  
           ├─11551 postgres: wal writer process  
           ├─11552 postgres: autovacuum launcher process  
           └─11553 postgres: stats collector process  

Sep 19 11:58:56 vardamir systemd[1]: Starting PostgreSQL database server...
Sep 19 11:58:57 vardamir systemd[1]: Started PostgreSQL database server.
[root@vardamir ~]#

To allow the postgresql server (postmaster) to listen on all interfaces, we need to pieces of config changes, 1 in postgresql.conf and other in pg_hba.conf as below.
in postgresql.conf we need to set :

listen_addresses = '*' 

so that the postmaster server listens on all interfaces.
second we need to add a host entry in pg_hba.conf so that clients are allowed to connect.
this config will depend on network config of the installed DB, an example is shown below:

host    all             all             10.0.0.0/8              md5

md5 is used to allow encrypted password authentication.
Don't forget to allow the Linux firewall to open port 5432 so that external users can access the newly created DB server.

Its always better to create another user than the default postgres user and use that for the DB work remotely.

Apache / haproxy large Cookie size issues

Although HTTP protocol doesn't have a limit on the cookie size, which is in turn a part of the request/response headers, Apache does impose a limit to protect the webserver from denial of service attaches.

Apache controls this using the "LimitRequestFieldSize" directive which defaults to 8192 byte as a max for a header field.

If larger cookies are needed, we need to bump up this value to a bigger number.
eg:

LimitRequestFieldSize 18192

in order to test this, i set up a simple webserver config with the below config:

RequestHeader unset If-Modified-Since
RequestHeader unset If-None-Match

LimitRequestFieldSize 18192

Header set Set-Cookie "apachenode=node1; path=/;"
Header add Set-Cookie "env=test; path=/;"
Header add Set-Cookie "TimeS=NONE; path=/;"

The LimitRequestFieldSize only works on Requests not on the response, thus apache can set-Cookies with large values with no problems, its the job of the browser to validate this.

To be able to test this from Firefox, I used the Cookie Manager plugin https://addons.mozilla.org/en-US/firefox/addon/cookies-manager-plus/ to set a large cookie that is 24k in size with a random string generated from the site: http://textmechanic.com/text-tools/randomization-tools/random-string-generator/

I managed to prove the compiled limit in Centos 7 is indeed 8192 and expanded that to ~18k as seen above and it worked.

When apache fails to accept the request it responses with an http 400 error as below:

 

I tried to do the same test with commandline curl but seems curl truncates the large cookie when using the -b option and passing a file as below:

[root@feanor conf]# curl -b ./cookies.txt  http://localhost/new.html
this is a new page :)
[root@feanor conf]# ls -ltrh ./cookies.txt
-rw-r--r-- 1 root root 25K Sep 15 12:28 ./cookies.txt
[root@feanor conf]#

Although the cookie file contains a single cookie that is 24k in size, curl seems to have truncated it and the request went in. 

lowering the LimitRequestFieldSize to something like 4600 managed to have curl reproduce the same behaviors as the browsers:

[root@feanor conf]# curl -b ./cookies.txt  http://localhost/new.html
<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">
<html><head>
<title>400 Bad Request</title>
</head><body>
<h1>Bad Request</h1>
<p>Your browser sent a request that this server could not understand.<br />
Size of a request header field exceeds server limit.<br />
<pre>

</pre>
</p>
</body></html>
[root@feanor conf]#

Thus, curl could be tricking you if you are debugging an issue like this from the command line.

Also one more note about large Cookie sizes, if haproxy is used in the setup, eg: as a balancer in front of Apache or for SSL offloading, it could be needed to increase the tune.bufsize so that it can accept larger requests and larger Cookie sizes.

Haproxy checks the size as (tune.bufsize - tune.maxrewrite) where Maxrewrite is adjusted to half buffersize if that is larger than 1024.

Check the haproxy config at: http://www.haproxy.org/download/1.5/doc/configuration.txt.

Given the above info, tune.bufsize should be set to be double the Apache LimitRequestFieldSize.

Watch out for memory and ensure haproxy has enough resources to work with no issues.