Improving your database backup policy
In this brief article I will reflect on a strategy which I normally adopt in an enterprise which requires a solid database backup policy in which fre…
Showing posts with the label databasesShow all
MySQL with HAProxy for Failover and Load Balancing
As discussed in a previous blog post about different types of MySQL HA architectures , using HAProxy for failing over and load balancing over cluster…
My 5 minute Python data generator
I needed to do some database benchmarks on a 10GB dataset. The problem: I did not have the dataset in hand, and I needed to give an estimate to a cus…
MySQL Enable Slow Query Logging
In MySQL, slow queries can be logged in the 'slow query log'. To enable this feature you need to set the following variable: log_slow_queri…
Connect Perl to MySQL
If you are a MySQL DBA, one of the most useful things you need to know is Perl. This will make you able to create custom tools such as for example be…
Find which table has a particular column in Oracle
Sometimes we need to find out which tables contain a particular column. In Oracle this can be simply done by querying the DBA_TAB_COLUMNS system tabl…
Moving Master or Slave nodes in MySQL
In this post I want to discuss with you a concept which simplifies failing-over or migration of a master or slave node in a MySQL replication setup w…
How Tables are arranged in JOINS
I often get asked how does an optimizer (being in Oracle or MySQL) decide which would be the outer table in a join. Consider the following three tabl…
Efficient Random Rows in MySQL
MySQL's official documentation dictates the use of the ORDER BY RAND() (coupled with LIMIT if you want) to select one or more random rows from a …
Choosing the ideal InnoDB log file size
I refer to an incredible post by Baron Schwartz where he wittingly explains a technique to choose the best InnoDB log file size. The idea is so mon…
Oracle measure Transactions per Second (TPS)
In a previous post I described how I was tasked to produce a number of KPIs and queried Oracle's AWR to get such data. I was also asked to get th…
Oracle measure number of Active Sessions
I was recently asked to produce some KPIs for our three node Oracle cluster database. One of the KPIs was to display a graph trend for total number o…
Delete Duplicates fast in MySQL
In an earlier article I explained how to delete duplicate rows in Oracle using the rowid pseudocolumn. In MySQL there is no pseudocolumn, and in ord…
How Covering Indexes work in MySQL
I believe that MySQL has many cool and advanced index types which provide the best performance strategies (if used properly) compared to other RDBMS&…
Monitor Postgresql Load Activity
The best and easiest way to monitor what a Postgres server is doing is by using the pg_top tool. Usage is as follows: $pg_top -d postgres
Oracle export with query
If you need to produce an Oracle export with a filter/query, you may encounter syntax errors because you need to escape certain characters. This is t…
Formatting Dates in SQL Server
In SQL Server the date formatting is a bit peculiar to say the least...I used the following query to be able to go 30 days back from current date: …
List user or table privileges in Oracle
This morning I was asked by the auditors to give them a list of privileges which have been granted to users USER_A and USER_B. You can do this by usi…
Output table contents in PL/SQL stored procedure
With Oracle stored procedures the database developer can display output to the SQL client. This is very handy when you want to use a stored procedure…
MySQL Index Prefix for better performance
In MySQL you can optimize a query running against a table by choosing the correct index. Furthermore MySQL allows you to index strings as well (text,…
