Category Archives: MySQL

Databases, Linux, MySQL, Networking

Dumping all remote mysql queries to a server

Leave a comment

For a project recently we needed to see which users were accessing which databases on a remote mysql server. After a little bit of reading and experimenting I came up with this command – hopefully it will be useful for someone in the future.

tshark -i any -n \
    -d tcp.port==3306,mysql \
    -T fields -e timestamp -e ip.src -e mysql.user -e mysql.schema -e mysql.query \
    -f "dst port 3306" \
    -Y "len(mysql.user) > 0 || len(mysql.query) > 0"

This uses the excellent wireshark tool to capture traffic. The -f flag specifies a filter to only inspect inbound traffic to the server (as we don’t care about the responses).

The line beginning -T specifies the fields we wish to dump (in a tab-separated fashion), -d ensures that all traffic will attempt to be decoded as mysql. The final -Y flag ensures that only packets with queries or logins will be dumped, rather than TCP overhead or placeholder binding etc.

Note that user information is only given at the beginning of a session. Schema (database name) information may be on that same line, or you may have to scan the queries to look for a USE database type command as there are two different ways of setting the current database that will be accessed.

Android, Databases, Mongo DB, MySQL, PostgreSQL, Web Apps

The hardest task to do well in every frontend/backend system – paging

Leave a comment

What is the hardest code to write efficiently in pretty much every frontend and backend system? I think it is paging.

Nearly every system I’ve worked on as full-stack developer has had some sort of reporting whether it is searching and filtering or news-feed style item display, all of these depend on paging. Whilst simple paging is trivial to implement, making it work quickly at scale is a very tricky challenge.

A simple approach (based on SQL, but could be with any backend data store) would be something like:

items = SELECT * FROM <table> WHERE <query> ORDER BY <order> LIMIT <rows>,<offset>;
count = SELECT COUNT(*) FROM <table> WHERE <query>;

It looks simple and it works in small-scale development systems. But there a massive number of issues and inefficiencies with this approach:

  • Whilst you need to redo the items query for every page, the count query only needs to be done once for each <query>. Counting is a very expensive operation as it essentially has to fetch all the rows, unless there are some very well optimized keys on your table. This especially the case if you are running over a table larger than a few megabytes on a mobile device, so you need to cache this as much as possible.
  • Often times as the <order> logic has gotten more and more complex I’ve found myself in a situation where some rows are equal in ordering and so they can move around as you switch between pages or redo the query. Always ensure that the last item of ordering is something which is well ordered to avoid this, for example an integer primary key
  • What happens with a LIMIT/OFFSET type query if you get a row inserted before the OFFSET? All the rows shift forwards by one so when you scroll down on your infinite feed you get a duplicate, or the user goes to the next page and sees a repeat of the last entry of the previous page. What happens when a row prior to the OFFSET is deleted? A row between the pages is not seen by the user. In some databases it may be possible to use a long-lived cursor but often in stateless backends for webapps this is not possible. Wherever possible you should try to get a column on the <order> which is simply increasing or decreasing and redo the previous query with use a > against that to ensure you fetch all the rows from the previous point the user saw regardless of what changed above it. This is easy when sorting by primary key, difficult or even impossible otherwise.
  • How do you handle a laggy backend when the user presses the next button multiple times? Does it re-fire the request for the next page multiple times to the backend (consuming unnecessary resources), ignore any presses after the first (better), or does it proceed forward multiple pages, potentially running past the last page and onto blank pages? I’d usually opt for the second approach but limiting so that it always stops on the last page, but it may vary depending on usage case (would users typically want to skip multiple pages quickly, or always want to page through one-at-a-time), backend latency (ie is the data stored locally or remotely, what is the connectivity like)
  • Following on from the above, if you are firing multiple requests for pages, or for infinite scrolling how do you cope with the fact that responses can come back out-of-order? Many times I see it would simply display the content from the last page returned, but this is open to a number of bugs if you are talking about remote services over wireless
  • How do you efficiently abort in-fly calls and database queries if the user changes page – on a large system this could be a significant performance gain

There are quite a large number of optimizations that can be done with paging code, whether this is with a remote backend or locally, for example:

  • Prioritize getting the first page of data to display, say “Showing results 1-50 of …” while the count is loading
  • Track historical data on how long it takes to generate counts or paging, and especially if running single-threaded (eg on a mobile device), delay the count until the main queries have completed and there is some idle time
  • Is it possible to parallelize the two querys against the database so that the query takes half the real-time for the user? Can you use non-blocking IO to open two database connections, one for each query and return a response when both have completed? Can you run 2 separate queries one for count and one for items and update your pager and results separately? For a few rows in a test system this won’t make any difference, but over millions of rows, especially if you need an accurate count, this can save seconds of time
  • Do you actually need a totally accurate count, or can you approximate it, does your database backend support something like this for queries? This can save significant time and resources
  • Have you set up good indexes and verified that they are correctly being used at the scale that you will have in your live environment? Do you optimize well for the common use case?
  • You don’t always need to bother running a count query – if the items returned are less than the items requested you know that you have finished paging and you can automatically count the total from that. This is true whether on the first on the 100th page.
  • Fetch the number of rows that you want to display plus one, so that you know if there is a next page or not. If the number of items is less than you requested you know there won’t be a next page no matter what the total actually is
  • If running on mobile devices or html, limit the number of items that are displayed on an infinite scroller as this can be very memory intensive. Perhaps allow infinite scrolling over 500-1000 rows and then put a next button at the bottom of the page. Or you can do some trickery to replace the top items with a large blank box but this can be very complex and difficult to do well
  • Don’t display a spinner when infinite scrolling (you are starting to load more items before the user hits the bottom of the list right?) Only display the spinner after a short time period or when the user hits the very bottom of the list to give visual feedback that there are more items loading to display

If this looks like a lot to think about, I don’t disagree. Simple, buggy and inefficient paging is easy to do, but fully-optimized, highly responsive complex paging is very tricky to do well. But if you want to scale your app you need to focus on this.

Databases, High Performance Linux, Mongo DB, MySQL, NoSQL, PostgreSQL

Mongo DB Pain

1 Comment

In my latest contract we’re gathering a lot of data very quickly (100k inserts/sec would not be unreasonable) and trying to save it into a database. Before I started the contract the decision had been made to use Mongo Database for this very reason, so when I joined I was glad to finally get a chance to play with some NoSQL software – up until now I’ve just used MySQL and PostgreSQL. There’s some software and programming languages where the more you use them the more they pleasantly surprise you. AngularJS is one of those pieces of software where you start with basic templating and form-element binding but quickly discover there’s a mass of great plugins available. PostgreSQL is another case in point – I had been using MySQL quite happily for years but since starting to use PostgreSQL for some geolocation projects (with the excellent PostGIS extension), I’ve started using it by default for any new projects or designs. At the start it seems like just another database server with some geospatial additions, but then you think “Perhaps I can get the database to validate my input data before inserting”. No problem for Postgres you can specify a regexp constraint for example CHECK (mac ~ '^[0-9a-f]{12}$'::text) to validate a mac address (but you probably want to use the built-in type macaddr). Good luck with doing this in MySQL – by default it doesn’t even care about inserting the wrong data type into a column just silently messes it up… Or perhaps you want to import data from another data source into Postgres – again Foreign Data Wrappers (FDW) to the rescue connecting in to pretty much any other database or data source that you could want.

With Mongo however the more I’ve learnt of it the less impressed I’ve become. Sure, you can do some nice searches without needing schemas and it’s pretty high performance however once you go past simple store/retrievs there are some pretty basic issues that have not been addressed. Here are two I’ve come across in the past week:

Firstly, we have a big table (500m records) with data in a composite primary key (id, timestamp). We have the index on that, and we want to get a list of all the ids in the table. No problem you think – it’s a simple btree; just walk it and you’ll pull out the ids. And yes, it should be like this however there’s no easy way to do this in Mongo. The first thing you see in the documentation is that there is a .distinct() function or a .group() function. Great to get the distinct ids just run db.collection.distinct('id'). However rather than doing something sensible and returning the distinct fields with a cursor it tries to return them all in a single BSON object which is capped at 16mb size. So, if you have less then perhaps 1 million distinct ids in your table it would work, once you go over some magic threshold then your entire process needs redesigning.

Then you notice that in the latest versions of MongoDB, the .aggregate function can return a cursor. So, something like:

db.collection.aggregate( [ { $group: { _id: "$id" } } ], { cursor: {} } )

should do it. Technically speaking yes, however an open bug report from 4 years ago explains that actually the $group function can’t use indexes. If you add an explain: true to the aggregate you see that it does a full collection scan. But, perhaps if you are clever and force the sort like:

db.collection.aggregate( [ { $sort: { id: 1 } }, { $group: { _id: "$id" } } ], { cursor: {} } )

it will use the index, and indeed the explain output shows that it does. However even then it has to search through the whole table before it returns the first item for the cursor! So, if your distinct result is possibly more than 16mb of data in a large table there doesn’t seem to be any way to get the data in a timely manner from the database.

Another issue that I ran into and reported upstream is to do with very poor index selection on the aggregate. See the ticket for more details, but if you run an aggregate query on a collection with the foo column as a unique key:

db.col.aggregate([ { "$match" : { "foo" : "abcd" } }, { "$sort" : { "_id" : 1 } } ])

for some reason Mongo doesn’t follow the obvious and use the first item in the aggregate pipeline as the index. Instead, it chooses to use the _id index meaning that the $match does a full table scan to find the one result that could have been instantly looked up if it had used the correct index. The .find().sort() pipeline doesn’t have this issue.

There was also a fun issue I discovered whereby when you get a hot row in the database (for example a lot of processes trying to use $inc to update a counter) performance gets absolutely killed by the spinlocks on the data structure. Rather than 50k upsert/sec on the cluster when we had this hot row it went down to about 3k upserts/sec.

This is not to say that Mongo is a bad product, certainly looking at posts from a few years ago it seems that it has come on leaps and bounds since then. However it’s not the be all and end all that many NoSQL champions would claim. And I’m not convinced that the other NoSQL type databases would be all that much better – certainly for basic store/fetch operations but as you get closer to needing the functionality of a traditional RDBMS they are just too immature. Other issues for us recently have been lack of JOIN statements, the lack of schema that means a simple typo on a query can cause it to hang doing a full table scan for a key that doesn’t exist, etc etc. These are not problems with Mongo per-se but rather an issue with the incredible flexibility that NoSQL products offer.

Would I use Mongo again in other projects? Yes probably, but only if we had a requirement for very high performance or schema-less flexibility that something like PostgreSQL simply couldn’t provide.

Linux, MySQL, PowerDNS

Blacklisting domains using PowerDNS Recursor

5 Comments

I recently had a client that was wanting to provide a recursive DNS service within his company, however wanted to blacklist a lot of domains to redirect internally. And I mean a lot – over 1 million porn/spam/… domains. It’s one thing to use the excellent unbound recursive DNS software and set up the blocks using the local-data argument, but it was requiring over 6gb of memory to load the list and crashing the process because of that.

As it turns out, yet again PowerDNS to the rescue. I love PowerDNS for its flexibility (so much so that I created a very high performance DNS backend for it and also run a company consulting on DNS deployments).

As the full list of domains would not fit in memory we had to use a database, I took inspiration from a previously posted Lua script which used a tinycdb. Unfortunately tinycdb requires manual compilation and so wasn’t an option, and as the client already had the list of domains in a MySQL deployment we ended up using that. Both PowerDNS authoritative server and recursor can support Lua scripting to do pretty much anything you need, and there are a number of database options that Lua can use. I started off using luadbi as it seemed to have a nicer interface, however unfortunately luadbi only supports lua 5.1 whereas the debian build of PowerDNS uses lua 5.2. This meant switching to use luasql which is a bit lower-level.

So, the following Lua script will redirect any blacklisted subdomains (in the mysql table domains with field name) to 127.0.0.1:

driver = require "luasql.mysql"
env = assert( driver.mysql() )

function preresolve ( remoteip, domain, qtype )
        con = assert(env:connect("database_name", 'username', 'password'))

        domain = domain:gsub("%.$", "")

        while domain ~= "" do
                local sth = assert (con:execute( string.format("SELECT 1 FROM domains WHERE name = '%s'", con:escape( domain )) ) )
                if sth:fetch() then 
                        return 0, { { qtype=pdns.A, content="127.0.0.1" } }
                end

                domain = domain:gsub("^[^.]*%.?", "")
        end

        return -1, {}
end

As establishing a MySQL connection each request is quite a high overhead it might well be worth switching to use SQLite in the future, this should be very simple to do by just changing the driver name and connection string.

Databases, High Performance Linux, MySQL, PostgreSQL

How (not) to use PostgreSQL functional indexes

Leave a comment

Working on a project where we want to store searchable (using LIKE, meaning we can’t use the inbuilt Postgres macaddr type) MAC addresses in a PostgreSQL table I decided create a unique index in order to only allow one modem per entry in the table and also to speed up searches. However we also want to ensure that we dodn’t get upper- and lower-case versions of the same MAC inserted. PostgreSQL to the rescue – you can create a functional index on the table:

create unique index on modem (( lower(mac) ));

Basically this means that it should first lowercase anything that gets inserted, and then check that the unique constraint isn’t violated. It should also mean that something like:

select 1 from modem where mac = ?

should be indexed and hence work really quickly.

However, while profiling the application I noticed the select statements getting slower and slower the more rows we put into the table – the classical way of seeing that a column is not properly indexed. Looking at the postgres manual I remembered that a functional index actually only speeds up queries where you apply the function to the column, for example something like the below would use the index:

select 1 from modem where lower(mac) = lower(?)

Functionally this was the same as what I was doing, but I didn’t want to have the hassle of remembering to apply that function everywhere we wanted to search by mac. In the end I decided to drop the functional index and just use a standard unique index. I then added a type constraint check which also enforces the value being hex:

alter table modem add constraint ensure_mac_correct CHECK (mac ~ '^[0-9a-f]{12}$');

Did I ever mention how I love postgres? Take that MySQL!

Databases, MySQL, perl, PostgreSQL

Easily dumping sample data from any database

Leave a comment

Following on from my previous post on the wonders of perl’s DBIx::Class::Schema::Loader, after you have dumped the schema using either the perl scripts provided or the dbicdump commandline tool, you can easily dump the first few rows of each table in JSON using the following very simple perl script:

use Parts::Schema;
use JSON;
my $dbic = Parts::Schema->connect('dbi:Pg:...', USER, PASSWORD);
my %data;
for my $source ($dbic->sources) {
    my $rs =
    $data{$source} = [
        map +{ $_->get_inflated_columns }, $dbic->resultset($source)->search({}, { rows => 10 })
    ]
}
print encode_json( \%data );

To dump the whole table simply remove that rows => 10 bit (although as it loads each table into memory and keeps it there before dumping I wouldn’t advise this!)

Alternatively for a NoSQL database like MongoDB:

use MongoDB;
use JSON;
my $db =MongoDB::MongoClient->new(XXX)->get_database(XXX);
my %data;
$data{$_} = [ $db->get_collection($_)->query->limit(10)->all ] for $db->collection_names;
print encode_json( \%data )

Databases, MySQL, perl, PostgreSQL

Perl’s killer feature – DBIx::Class::Schema::Loader

1 Comment

After I tell people I’m a programmer and they ask ‘which language’, they’re usually pretty shocked when I tell them that for server-side I use Perl out of preference. I guess most people have seen some really bad scripting in perl patched together by successive non-developers at some point in their life and assume that’s what I mean, but that’s a story for another day.

Anyway, one of the main reasons I use perl as my language of choice is because of the excellent ORM, DBIx::Class which allows you to do a lot of complex database queries and joins without having to touch SQL. Fine, you can get a decent Object-Relational Mapper (ORM) in many different languages – PHP, ruby, python, node.js for example. DBIx::Class does seem to have a lot more databases supported than your typical backend though. However the killer feature is not DBIx::Class but a separate module, DBIx::Class::Schema::Loader.

Here’s the beauty of DBIx::Class::Schema::Loader – with a typical ORM you need to define every table’s schema and relationships in code before you can start using it fully, however this module goes into the database and pulls out all of the tables, indexes, relationships and creates the full set of classes which you are then free to edit or update as you wish. The next time you run it it will pull in any database schema updates and keep the custom stuff you wrote. I’m yet to see this functionality in any other language – please point it out to me if it exists.

Now I know at this point there are many devs who like to define a database schema in code and then push it to the database so you can have versioning etc (which by the way these modules also support if you want to use them that way round). Whilst that might be OK for a new project, it’s a nightmare for an existing database or project – just think about how many days work would it be to build up ORM models of 100 tables manually. Also in my experience writing schemas in Ruby, Node.JS or even DBIx::Class is pretty nasty and repetitive – you can do a lot better using SQL CREATE TABLE statements. Another issue is that it’s very difficult to define indexes properly – perhaps you can define an index using the ORM schema definition language, but I’d wager most ORMs don’t know much about how to create a partial or functional index for example in postgres you can easily specify complicated indecies for example:

CREATE INDEX people_names ON people ((first_name || ' ' || last_name));

or

CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)
WHERE NOT (client_ip > inet '192.168.100.0' AND
           client_ip < inet '192.168.100.255');

Certainly once you go into the realms of complex geo-spatial data-types it’s impossible not to touch the database manually no matter what language or ORM you use.

An example of using DBIx::Class::Schema::Loader to connect to a database and dump all of the schema we simply have the following as a library (under dbic_lib/Parts/Schema.pm):

package Parts::Schema;
use v5.14
use base 'DBIx::Class::Schema::Loader';

our $conn_info = [ 'dbi:Pg:dbname=...', $USER, $PASS ];

sub connect { shift->SUPER::connect( @$conn_info ) }

1

And as the dumper script:

use v5.14;
use FindBin::libs qw< base=dbic_lib use=1 >;
use Parts::Schema;

Parts::Schema->loader_options({ dump_directory => "lib" });
Parts::Schema->connect;

That was easy wasn’t it? This works whether you have 2 tables or 200. So far I’ve used it with MySQL, PostgreSQL, MSSQL, Oracle, SQLite and it works brilliantly in all of these thanks to the excellent driver support in DBI which all of this is based on.

In a previous job we had an MSSQL data-warehouse that we wanted to connect to and pull out various views that defined customer lists so could email them. We used this module to refresh the schema dump on demand, reload it into the app and allow people using just a web browser to choose a view from the data warehouse, define columns or search criteria put these into a template (also created by them on the frontend) and email out. All this in about 30 lines of perl code for the database interface.

As another example just this week I was given the task of figuring out a client’s database structure with over 150 tables only about 5 of which even had a primary key. First thing – whip out the code above and get a full dump of all the tables into a perl class structure (by the way it was an oracle database – I’ve never used that brand of SQL before, wouldn’t know what command-line linux client to use and it’s a bit strange it doesn’t even have LIMIT – not a problem for DBIx::Class though). Within about 5 minutes I had a full dump of all tables including indexes, column types (and relationships if any had been defined). Within about 10 more minutes I could write a script to dump complex data based on relationships between the tables into a JSON format.

Please show me another language that has such great database-agnostic ORM support.

Update: DBIx::Class::Schema::Loader comes with a tool called dbicdump. If you just want to take a simple dump of the schema of the database above rather than writing the dump script yourself you can just run:

dbicdump Parts::Schema 'dbi:Pg:dbname=...' USER PASS

Linux, MySQL

Recovering from strange mysql crash

Leave a comment

So, yesterday my server started emailing some cron errors over. One particular script that runs every hour was throwing an error about 10 seconds in to running that appeared to be the server hanging up the query. I thought I may have set some timeout too low such that the server wouldn’t allow queries longer than 10 seconds (as this is a web server then nothing should take that long apart from a few analytics scripts that run overnight). Running the query by hand showed the same problem, so I started doing some analysis of the component parts to see which was taking so long. Then, I looked at the timeouts set and noticed:

mysql> show status like "%time%";
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Uptime                     | 105   |
| Uptime_since_flush_status  | 105   |
+----------------------------+-------+

Hmmm that looks bad. Looking in dmesg confirms that mysql has not been hanging up – it’s actually been crashing!

[504510.549172] init: mysql main process (15317) terminated with status 1
[504510.549186] init: mysql main process ended, respawning

I ran mysqlcheck on the database in question and the server crashed again, even though I was able to query the table fine and even added an index before realizing that there was some issue with it. So, rather than restore from a backup I thought I’ll just clone the table and replace the existing one with it:

mysql> create table t like client_songs;
mysql> insert t select * from client_songs;
mysql> check table t;

New table looks fine, lets do a final update (as it’s continually being inserted into)

mysql> insert t select * from client_songs cs where client_song_ts >= ( select max(client_song_ts) from t ) on duplicate key update select_count= cs.select_count, download_count = cs.download_count, rating = cs.rating, client_song_ts = cs.client_song_ts, print_count = cs.print_count;

Then put it live:

mysql> drop table client_songs;
mysql> rename table t to client_songs;
mysql> check table client_songs;

Everything working again. I wish mysql (5.5.37 from ubuntu 14.04 LTS) was more reliable that’s why I tend to use postgres for new projects these days. It’s really strange that the table could be read fine but one particular query caused it to crash – probably a case of the particular index that was being for the query being corrupted but not the row-data.