mysql
Using MySQL Stored Procedures for User Authentication
Scenario: You want to authenticate users into a private web site by accepting an e-mail address and a password. You don’t want to do a bunch of security on the front end however you do want to use a token on the client for various purposes. Also, you want the tokens to expire automatically and [...]
Creating a MySQL Function to convert miles and kilometers
MySQL has the ability to create user defined functions that can be called just like the built-ins. There was a recent project that involved some distance calculations. As part of the performance testing, we experimented with moving the logic around to different parts of the application stack. Here’s a short example of two functions created [...]
Testing MySQL data migrations Part 1
On occasion as database administrators we have to migrate data for various reasons. No matter the reason or the origin, we have to test the migration to see if the result is what we expect. The question is: How? In Part 1 of this series, I’m going to explore a simple “smoke test” of a [...]
MySQL Insert On Duplicate Update Madness
you may need to perform an INSERT INTO SELECT FROM statement. Often when you do this, you’ll run into duplicate key errors. To overcome this, you’ll need to add ON DUPLICATE KEY UPDATE syntax to the end of your insert.
MySQL Best Practice: Forget ALTER, use RENAME instead
MySQL is notorious for a few bummer scenarios. One of them is when you have a table with a couple hundred thousand rows or more and an indexed column or two, and you’ve been asked to add a column to meet a data model requirement. You know this will cause an outage or at least [...]
Creating a Date Dimension Stored Procedure
Our data warehouse was getting pretty big. In fact, it was it was getting into the hundreds of gigabytes of fact data. We started looking for ways to save hard drive space. It became apparent that storing redundant varchars, datetimes, enums and other large column types was unnecessary and causing the scale constraints. Part of [...]
Sysbench for MySQL Burn-In Testing
The reason for my last post about getting Sysbench installed on OS X, was that I had a chore to burn-in four MySQL hosts. They are currently replicating which is normal behavior for this group. I wanted to add some stress testing. Here is the script I created for his purpose. You can modify the [...]
How to install Sysbench on OS X
Before I start this post, I want to thank Alexey Kopytov for his assistance. Alexey, you’re a cool guy. Also, for reference sake, I’m running Mac OS X 10.6.4 and installed mysql-5.5.6-rc-osx10.6-x86.dmg Sysbench is a tool for MySQL DBAs to performance test or burn-in installations. I like to use it as a sanity check for [...]
MySQL Partitioning and open_file_limit
We ran into an interesting problem recently and wanted to share the research. MySQL partitioning is a great way to improve performance in some circumstances, but it has a behavior that’s not widely noted. That is specifically with the MyISAM storage engine. MySQL seems to open two (2) file pointers for each table or partition [...]
