Troubleshooting slow PostgreSQL queries
With modern distributed systems performance issues can surface in unexpected ways. When it comes to database servers like PostgreSQL issues can surface as slow pages, API calls timing out, jobs not completing, or users saying that the application is "slow". These issues usually occur at 4AM, so having a good debugging methodology (and lots of coffee) baked into your brain can help with speedy remediation. The hard part is that these problems often look the same from the outside: the site is slow and nobody knows why yet…
$ read more →Visualizing PostgreSQL index types
Over the past few months I've been doing diving deep into PostgreSQL. I've spent a good bit of my career supporting Oracle and MySQL, but over the past few years I've spent more time managing PostgreSQL. I'm super comfortable getting around a psql prompt, but wanted to really dive into the guts of PostgreSQL to take my knowledge to the next level. I started with the PostgreSQL internals documentation…
$ read more →Debugging OpenLDAP ACLs
OpenLDAP provides a super powerful ACL syntax which allows you to control access to every nook and cranny of your directory server. When I'm testing advanced ACL configurations I have found it incredibly useful to add the "ACL" log option to the loglevel directive: When this option is set slapd will show you how it applies the ACLs to a given LDAP operation: This is super handy and will save you tons of time and heartburn when crafting complex ACLs.
$ read more →Using MySQL query logs to debug authentication issues
I recently installed LogAnalyzer, and after the install completed I noticed that nothing was being displayed in the web interface. I figured I fat fingered something, but needed a way to verify this. Luckily for me I was using MySQL, so I enabled MySQL query logging and low and behold I proved my hypothesis: To fix this I logged into the database and changed the password for the rsyslog user: Everything began working once the application could authenticate, and I was able to start playing around with LogAnalyzer. Noting this here in case I fat finger another password in the future…
$ read more →Enabling MySQL query logging
I recently installed the LogAnalyzer graphical syslog analysis tool. After the install completed I went to the "Show Events" page and noticed that no data was being displayed. I wanted to see which queries were being sent by LogAnalyzer to my MySQL database instance, so I enabled query logging by adding the following two statements to the [mysqld] block in the /etc/my.cnf configuration file: The first line enables logging, and the second line tells MySQL were to write the logs. Once enabled you can see the queries executed against your server by paging out the contents of /var/log/query.log…
$ read more →