In MySQL 5.6, they introduced a different way for logging slow queries compared to previous versions. This article will show you how to quickly, and correctly enable the slow query log which is extremely useful for debugging application performance issues. Enable Slow Query Log in MySQL 5.6 Login to your MySQL host Open the my.cnf… Read more
Databases
Useful tips for PHP developers using MySQL
MySQL is a very common database used while developing web application. When developers are using PHP as their main programming language, they prefer MySQL as database. We are going to discuss some of uncommon but useful queries and precautions every developer should follow and will be taken care of. 1. Why not to use… Read more
Solution to SQL ERROR -532 THE RELATIONSHIP CONSTRAINT-NAME RESTRICTS THE DELETION OF ROW WITH RID X’RID-NUMBER’
This article will explain the cause and solution to DB2 SQL Error-532, which is also similar to MySQL : ERROR 1216: Cannot add or update a child row and SQL Server error: foreign key constraint failed Foreign Key Constraint Error Possible Manifestation The DELETE statement cannot be executed. The contents of the object table are… Read more
Create a compressed MySQL database dump/backup (Linux)
This may go without saying, but backing up your database regularly is important. Hard disks can fail at anytime causing you to lose all your data (We recommend using a RAID configuration when possible for data redundancy!) Often times, we may overlook the database and simply make backups of our /www or /public_html folder that… Read more
How to backup/dump a PostgreSQL database using pg_dump
This article will show you how to use the pg_dump utility that is built-in to PostgreSQL to backup or dump the database to a file. This is a utility I use all the time to quickly make periodic backups of my databases. This tool has the ability to create both compressed and uncompressed dumps of… Read more
pgAdmin – PostgreSQL GUI Management Tool
If you are looking for a GUI management tool for PostgreSQL databases, look no further. pgAdmin III is a freeware utility for *nix and Windows operating systems that allows you to design and manage your databases. Using this tool, you can create, edit, delete tables and data for your databases. It supports an enormous amount… Read more
How to find (log) slow queries in PostgreSQL 8.x, 9.x
One of the most important factors when troubleshooting the performance of an application or website, is the performance of the database. Improper indexing or inefficient SQL queries can kill the performance of your application rendering it useless. It is amazing what a simple tweak or change to a SQL statement can do. I have seen… Read more
How to install PostgreSQL 8.4 on CentOS 5.5
If you have tried installing PostgreSQL database on CentOS 5.5 using yum, you will notice that you an old, outdated version! There are several ways to get the 8.4.x version of Postgres installed on this OS, but here is the quick and easy method! Installing PostgreSQL 8.4.x on CentOS 5.5 Download the PostgreSQL 8.4 repository… Read more
PostgreSQL: How to reset the pg_stat statistics tables?
The Postgres pg_stat tables show a variety of statistical information regarding the database. In certain situations (such as after major updates to your application), you may want to clear out the gathered statistics and start from scratch. For instance, if you recently implemented numerous SQL query and indexing optimizations, and you want to see statistical… Read more
PostgreSQL: How to reload config settings without restarting database
If you are making modifications to the Postgres configuration file postgresql.conf (or similar), and you want to new settings to take effect without needing to restart the entire database, there are two ways to accomplish this. Option 1: From the command-line shell su - postgres /usr/bin/pg_ctl reload Option 2: Using SQL SELECT pg_reload_conf(); Using either… Read more
