This post is based on a true story from one of our team members.

Here at EverSQL, we have high appreciation for the MySQL database.
Said that, every product has its pros and cons and even some bugs here and there.

So let’s dive into how we used some “bad practices” to overcome a potential bug in MySQL’s optimizer.
TL;DR: This case study will describe a scenario where the MySQL optimizer will not choose to use an existing and allegedly good index to optimize a search query, which eventually caused a response time of 80 seconds of a simple page in a web application.

The Problem

The scenario from a business perspective – the development team was trying to implement a web page that presents data about the user actions in the last year.
The scenario from a technical database perspective – The team was trying to join two (same issue will occur with more than two) tables and use a range …

Do not use the old mysql api

There are several ways to connect to a MySQL database in PHP. The most common ones are the MySQL API, the MySQLi API and the PDO API (PHP Data Objects). The last two support more features than the old mysql API and are more secure. If you’re using the old “mysql_” functions, you should stop and learn the new PDO API. Those old mysql functions are deprecated and are no longer supported in PHP 7.x.

Bad practice:

<?php  
$con = mysql_connect("localhost", "root", "mypass") or  
    die("Could not connect: " . mysql_error());  
mysql_select_db("tutorials");  
$result = mysql_query("select * from tutorials");  
echo "<h2>Here is a list of the topics:</h2>";  
while ($row = mysql_fetch_array($result)) {  
    echo $row['name']."<br />";  
}  
mysql_close($con);  
?>  

Better practice:

 …

We all do need sometimes to generate raw valid dummy data for our use cases and applications as we start them. Obviously, one can write their own scripts to generate random data, but it is much better to have data, to which human beings can associate with like names, addresses instead of having them filled with random "lorem ipsum" string data :)

While searching for such a tool, I found a site which does exactly this: http://www.generatedata.com/

Documentation: http://benkeen.github.io/generatedata/

This can also be downloaded and installed locally. It supports three types of installations:
- A single, anonymous user account
- A single user account, requires login
- Multiple accounts

Below is the set of wide varied data types it supports for …

New MariaDB/MySQL storage engines such as MyRocks and TokuDB have renewed interest in using engines other than InnoDB. This is great, but also presents new challenges. In this article, I will describe work that I am currently finishing, and which addresses one such challenge.

For example, the left bar in the figure shows what happens to MyRocks replication performance when used with a default install where the replication state table uses InnoDB. The middle bar shows the performance improvement from my patch.

Current MariaDB and MySQL replication uses tables to transactionally record the replication state (eg mysql.gtid_slave_pos). When non-InnoDB storage engines are introduced the question becomes: What engine should be used for the replication table? Any choice will penalise other engines heavily by injecting a cross-engine transaction with every replicated change. Unless all tables can be migrated to the …

New MariaDB/MySQL storage engines such as MyRocks and TokuDB have renewed interest in using engines other than InnoDB. This is great, but also presents new challenges. In this article, I will describe work that I am currently finishing, and which addresses one such challenge.

For example, the left bar in the figure shows what happens to MyRocks replication performance when used with a default install where the replication state table uses InnoDB. The middle bar shows the performance improvement from my patch.

Current MariaDB and MySQL replication uses tables to transactionally record the replication state (eg mysql.gtid_slave_pos). When non-InnoDB storage engines are introduced the question becomes: What engine should be used for the replication table? Any choice will penalise other engines heavily by injecting a cross-engine transaction with every replicated change. Unless all tables can be migrated to the …

I’ve been a fan of GNU Parallel for a while but until recently have only used it occasionally. That’s a shame, because it’s often the simplest solution for quickly solving embarrassingly parallel problems.

My recent usage of it has centered around database export/import operations where I have a file that contains a list of primary keys and need to fetch the matching rows from some number of tables and do something with the data. The database servers are sufficiently powerful that I can run N copies of my script to get the job done far faster (where N is value like 10 or 20).

A typical usage might look like this:

cat ids.txt | parallel -j24 --max-lines=1000 --pipe "bin/munge-data.pl  --db live >> {#}.out

However, I recently found myself scratching my head because parallel was only running 3 jobs rather than the 24 I had specified. …

Since Ruby on Rails 4.2 has just been released, perhaps now is a good time to review creating a shiny new Rails project. It’s not often I get to create a new project from scratch, but it’s Christmas and I’ve got a bit of downtime — and an itch I’d like to scratch! So, let’s get started.

I’m aiming to build a wee project that keeps track of OmniFocus perspectives. I’ve noticed that people are sharing their perspectives as screenshots and descriptions, in tweets and blog posts. Wouldn’t it be awesome if there was a one-stop-shop for everybody’s perspectives?

A couple of early decisions in terms of the basic starting point:

• Chances are I’ll deploy the app onto Heroku, so it’s a no-brainer to start out by using PostgreSQL in development. (Even …

In C (and C++) you can specify that a variable should take a specific number of bits of storage by doing “uint32_t foo:4;” rather than just “uint32_t foo”. In this example, the former uses 4 bits while the latter uses 32bits. This can be useful to pack many bit fields together.

Or, that’s what they’d like you to think.

In reality, the C spec allows the compiler to do just about anything it wants with these bitfields – which usually means it’s something you didn’t expect.

For a start, in a struct -e.g. “struct foo { uint32_t foo:4; uint32_t blah; uint32_t blergh:20; }” the compiler could go and combine foo and blergh into a single uint32_t and place it somewhere… or it could not. In this case, sizeof(struct foo) isn’t defined and may vary based on compiler, platform, compiler version, phases of the moon or if you’ve washed your hands recently.

Where …

While playing with MySQL 5.7.5 on POWER8, I came across a rather interesting bug (74775 – and this is not the only one… I think I have a decent amount of auditing and patching to do now) which made me want to write a bit on memory barriers and the volatile keyword.

Memory barriers are hard.

Like, super hard. It’s the kind of thing that makes you curse hardware designers, probably because they’re not magically solving all your problems for you. Basically, as you get more CPU cores and each of them have caches, it gets more expensive to keep everything in sync. It’s quite obvious that with *ahem* an eventually consistent model, you could save a bunch of time and effort at the expense of shifting some complexity into software.

Those in the MySQL world should recognize this – we’ve been dealing with asynchronous replication for well over a decade …

After a long time, I finally had some time to write again, and this time I intend to keep a periodicity. The reason for my absence? Well now I have Dom Without further ADO, let’s get to the point, what… Continue Reading →

Continue reading What is Iptables, what it’s for, and how to use?

Related posts:

1. O que é Iptables, para que serve, e como usar?
2. Scheduling commands with at linux
3. Using ssh without password