O_DSYNC Flush Method for MySQL on Google Cloud

On my previous article, I tested ZFS with Google Compute engine using persistent disks and local SSDs. ZFS shows better performance with the help of a fast dedicated SLOG using the local SSD and NVMe driver – though the cloud, especially Google Cloud may not be the best platform for these tests. Fast forward to …

Continue reading

Testing ZFS vs ext4 with sysbench (Google Cloud)

I’ve had a chance to revisit ZFS lately and decided to take some more notes. One highlight of this test is revalidating a quick performance test against ext4. This test is ran on Google Compute Engine n1-highmem-8 (8 vCPU, 52GB RAM) with 2x375G local SSD attached via NVMe and 4x500G standard persistent disks using the …

Continue reading

Does Python MySQLdb Make You Wait?

Recently while writing a data loading application for a customer, we’ve come into a problematic situation with the Python MySQLdb module that can be installed with base RHEL repository or DVD. As a little background, this application uses an HA architecture where a Virtual IP can be assigned to different servers during a failover scenario. …

Continue reading

Sandboxed MySQL Utilities – HowTo

Often I would need to work on customer servers where MySQL Utilities would be a really good fit for the tools I need. However, I would not want to mess around with the customer servers just to have it running so I would have the tools built and sandboxed on its own directory where I …

Continue reading

RDS Migration from 5.5 to 5.6 with mysqldump

Amazon recently announced support for 5.6, unfortunately, direct upgrade from lower versions is not yet supported. On a recent migration work – running mysqldump flat out would’ve meant 6+hrs of downtime. How did we cut it off to 1h45m? Simple, run dump per table and pipe it directly to the new 5.6 instance in parallel …

Continue reading

Limit The Size of Your Core Files on Linux

So we all know that when troubleshooting MySQL crashes or any other processes in that regard, we simply enable core files to be dumped when the appropriate signal it triggered. To get the best results, we’d set the core file size limit everywhere to unlimited and be done with it, but what if you want …

Continue reading

Default RDS Account Privileges

I was searching looking for the PRIVILEGES that comes with the primary MySQL account on RDS, but it looks like this is not documented anywhere nor blogged about yet. So for the sake of other users, here it is: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY …

Continue reading

Optimizing MIN and MAX MySQL Functions

MySQL can optimize aggregate functions like MIN and MAX as long as the columns specified are indexed. This means that, in the case of MIN and MAX, the optimizer should be able to identify the highest and lowest values of an indexed column from the B-Tree index. Say I have a table like below: CREATE …

Continue reading

MySQL Backups, The Tools So Far

Backups is one of the most important part of any MySQL deployment, and nowadays, there’s a number of tools to choose from depending on how your organization implements them. The purpose of this post is to enumerate the main tools and some helpers that makes backing up and testing/restoring your backups more convenient. By all …

Continue reading

Check (Rough) Progress of Your CSV Import to MySQL

If you are importing large CSV or SQL dumps to MySQL, chances are you were looking for ways to see how far the import has gone. If you know how many rows there are from the file being imported, you can do a SELECT COUNT(*) but that would take sometime for the query to finish …

Continue reading

Optimizing OR’ed WHERE Clauses Between JOIN’ed Tables

OR conditions are normally difficult to optimize when used on different columns, a pain when the columns are of range conditions and worst when done between 2 or more different tables. Look at my example below, the original query  is trying to find rows which conditions are based on columns from the JOIN’ed tables. mysql …

Continue reading