There is not magic solution to count table contents especially if you have a client that has a table with 40+ million records and you need to do the filtering using the 'OR' SQL statement.

Original query was the following:

SELECT COUNT(*) from table1 WHERE field1 IN ('val1','val2') OR field2 IN ('val3','val4');

First benchmark before doing optimization showed me results after 4 minutes.

My

I did a presentation on OQGraph for the +Southern California Linux Expo on Sunday and some of the audience seemed really interested. Demonstrated finding the shortest path in a graph of millions of edges, with a deliberately crippled config running in a VirtualBox environment.

(edit, updated URLs on slides)

In this post I will describe a non-trivial way to authenticate users in Percona Sever for MySQL. Percona Server comes with PAM authentication plugin, which allows you to do a lot of cool things, such as: OS authentication, LDAP authentication, even RSA Secure Server authentication (which is useful if you are required a PCI-compliance), and use Google Authenticator, which is the topic of this post.

You can use a smartphone for two-factor authentication with Percona Server for MySQL

With two-factor authentication a user is required to enter not only password, but to have an additional security token, which in the case with Google Authenticator can be your cell-phone (clients are available for Android, iPhone, Windows Mobile or BlackBerry with …

Most of the time, when people say “scalability” they mean any of dozens of things. Most of the time, when I say it I mean exactly one precisely defined thing. However, I don’t claim that’s the only correct use of “scalability.” There is another, in particular, that I think is very important to understand: the inherent limitations of the system. This second one doesn’t have a single mathematical definition, but it’s vital nonetheless.

I’ll frame the discussion by asking this: how scalable is your database?

Using the two definitions I like to use the most, I answer the question in this way.

1. Scalability in terms of the Universal Scalability Law is the degree to which you can add more workers (or units of hardware) and get equal returns in terms of system throughput.
2. Scalability in terms of inherent …

PECL/mysqlnd_ms is a client-side load balancer for PHP that supports any MySQL cluster. It does read-write splitting, failover, introduces a quality of service concept, supports partitioning and, of course, load balancing. New mysqli API (begin, *savepoint) calls in PHP 5.5.0 help to improve transaction awareness. New read only in MySQL 5.6 promise major performance gains (think 2x) and an option to reduce the load on a MySQL Replication master. Read how the features go together in PECL/mysqlnd_ms 1.5.

Load balancing – transaction aware?

A load balancer must not switch connections in the middle of a transaction. A load balancer must send all queries to the server a transaction has been started on until the transaction ends. Unfortunately, it is very hard to develop a transparent load balancer for MySQL. In general there are …

Today marks the first release of Drizzle Tools for MySQL servers.  Drizzle Tools aims to be a collection of useful utilities to use with MySQL servers based around the work on the Libdrizzle Redux project.

In this first version there is one utility in the tree called 'drizzle-binlogs'.  If you've seen me talk about this tool before it is because it used to be included in the Libdrizzle 5.1 source but has now been moved here to be developed independently.  For those who haven't 'drizzle-binlogs' is a tool which connects to a MySQL server as a slave, retrieves the binary log files and stores them locally.  This could be used as part of a backup solution or a rapid way to help create a new MySQL master server.

Due to the API changes before the Libdrizzle API became stable Drizzle Tools requires a minimum of Libdrizzle 5.1.3 to be …

In MySQL 5.5 and earlier, the steps to resize the InnoDB log files were a bit involved and for example included manually moving the log files out of the way as InnoDB would only create new files, if none existed.

In MySQL 5.6 a not so much talked about feature is the support to resize the log files in a way much more similar to changing other settings in MySQL. Now you simply update your MySQL configuration file and restart MySQL.

Let us look at an example. In MySQL 5.5 and earlier the total size of the InnoDB log files has to be less than 4G in total, so one way of staying within this limit is to have two files each 2047M large:

innodb $ ls -1s ib_logfile*
2096132 ib_logfile0
2096144 ib_logfile1

Now update the configuration file to take advantage of the fact that MySQL 5.6 allows much larger InnoDB log files; the actual limit is a total size of 512G, but here I will use two files each 4G large:

db4free.net‘s server has reached its limits, and it was time for a new one. Now here it is, a shiny new server, with 8 times as much memory than the previous one, and also more disk space. Time for a reboot.

Only recently, MySQL 5.6 has become GA with much better performance than 5.5 and earlier, so the combination of a faster MySQL server and a faster machine should result in a very significant performance boost for db4free.net.

I have not migrated any user databases, but they are still available on the previous/old server. The old server is available at db4free.org, the new server at db4free.net. I will leave the old server running until end of March to allow users to transfer …

SCaLE 11x’s MySQL track featured standing room only crowds on subjects from Beginning SQL, Indexes, HA, Credit Card Storage for PCI compliance, 5.6 new features, and how to solve Rubik cube-like puzzles with MySQL. Saturday and Sunday, we are Booth 71 and we are sharing the booth with the Los Angeles MySQL Users group. Come see MySQL 5.6 and workbench, get registered for the Virtual Developer Day, or just to say ‘hi’.

Mozilla has a great presence at SCALE11x, the 11th Annual Southern California Linux Expo. This annual conference is completely volunteer run and one of the best Linux conferences around. Mozilla sponsored Friday’s DevOps Day LA and were part of the selection committee for MySQL Community Day.

See Firefox OS phones
Want to see a FirefoxOS mobile phone in action? Want to learn how to write apps for FirefoxOS? Come visit us at booth #14 and we will show you the phones and how to write a great app.

Giveaways
Also at booth #14, we are giving away swag! Get the traditional Firefox logo sticker or the new blue rocketship …