Indexing and Caching

MariaDB's Virtual Columns

2012-04-19T22:25:00.001+01:00

I wanted to evaluate MariaDB's virtual column and see if it could store business rules next to table data. With virtual columns this could be done and if you specify as 'virtual', would not even take up space on disk.

The imaginary use case is that there is a sales team in a company and you would like to evaluate if a particular salesperson is eligible for a bonus. In order to get a bonus, you need to sell above the average for the day and be in the top 5 amongst the salespeople.

So here is what I did:

MariaDB [test]> create table salespeople (id int unsigned not null auto_increment primary key, salesperson_id int unsigned not null , `date` datetime not null default 0, sold decimal(15,2) not null default 0, day_avg decimal(15,2) not null default 0, above_avg char(1) as (if(sold>day_avg,'Y','N')) virtual);
Query OK, 0 rows affected (0.02 sec)

So far so good.

MariaDB [test]> insert into salespeople(salesperson_id, `date`, sold, day_avg) values (1,now(),300,150);
Query OK, 1 row affected (0.01 sec)
MariaDB [test]> select * from salespeople;
+----+----------------+---------------------+--------+---------+-----------+
| id | salesperson_id | date | sold | day_avg | above_avg |
+----+----------------+---------------------+--------+---------+-----------+
| 1 | 1 | 2012-04-19 13:16:32 | 300.00 | 150.00 | Y |
+----+----------------+---------------------+--------+---------+-----------+
1 row in set (0.00 sec)

Cool, just like mashing a view into a table.

MariaDB [test]> update salespeople set sold = 149 where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [test]> select * from salespeople;
+----+----------------+---------------------+--------+---------+-----------+
| id | salesperson_id | date | sold | day_avg | above_avg |
+----+----------------+---------------------+--------+---------+-----------+
| 1 | 1 | 2012-04-19 13:59:57 | 149.00 | 150.00 | N |
+----+----------------+---------------------+--------+---------+-----------+
1 row in set (0.00 sec)

Works again. No triggers for UPDATE needed.
Now lets try to add another business rule:

MariaDB [test]> drop table salespeople;
Query OK, 0 rows affected (0.03 sec)

MariaDB [test]> create table salespeople (
-> id int unsigned not null auto_increment primary key,
-> salesperson_id int unsigned not null,
-> `date` datetime not null default 0,
-> sold decimal(15,2) not null default 0,
-> day_avg decimal(15,2) not null default 0,
-> above_avg char(1) as (if(sold>day_avg,'Y','N')) virtual,
-> day_position int not null default 0,
-> give_bonus char(1) as (if(above_avg='Y' and day_position between 1 and 5,'Y','N') )virtual
-> );
ERROR 1900 (HY000): A computed column cannot be based on a computed column
MariaDB [test]>

DOH! >.<

ADDITION:

I have found a way to store more business rules with the limitation of not being able to use another virtual column to "build" a more complex rule set:

MariaDB [test]> create table salespeople (
-> id int unsigned not null auto_increment primary key,
-> salesperson_id int unsigned not null,
-> `date` datetime not null default 0,
-> sold decimal(15,2) not null default 0,
-> day_avg decimal(15,2) not null default 0,
-> day_position int not null default 0,
-> bonus_amount decimal(15,2) as (
-> case when sold>day_avg and day_position between 1 and 5
-> then 0.2 * (sold - day_avg) #20% bonus
-> when sold>day_avg and day_position >5
-> then 0.05 * (sold - day_avg) #5% bonus
-> else 0
-> end )virtual
-> );
Query OK, 0 rows affected (0.75 sec)

MariaDB [test]> insert into salespeople(salesperson_id, `date`, sold, day_avg, day_position) values (1,curdate(),300,150,3);
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> select * from salespeople;
+----+----------------+---------------------+--------+---------+--------------+--------------+
| id | salesperson_id | date | sold | day_avg | day_position | bonus_amount |
+----+----------------+---------------------+--------+---------+--------------+--------------+
| 1 | 1 | 2012-04-20 00:00:00 | 300.00 | 150.00 | 3 | 30.00 |
+----+----------------+---------------------+--------+---------+--------------+--------------+
1 row in set (0.00 sec)

In the above example, the actual bonus amount was calculated.

How to get a query-digest report from TCPdump on a set time

2012-04-09T00:25:00.000+01:00

Note to self

Getting a query-digest report for 20 seconds worth of tcp traffic on linux
(no special slow log/microslow patch needed):

tcpdump -s 65535 -x -nn -q -tttt -i any -c 99999 port 3306 | pt-query-digest --type tcpdump --run-time 20s

thanks to aaron brown

Connect to Anemometer instead of using slow log

#crontab entry
*/5 * * * * tcpdump -s 65535 -x -nn -q -tttt -i any -c 99999 port 3306 | pt-query-digest --type tcpdump --run-time 20s --user=anemometer --password=superSecurePass --review h=db.example.com,D=slow_query_log,t=global_query_review --review-history h=db.example.com,D=slow_query_log,t=global_query_review_history --no-report --limit=0% --filter=" \$event->{Bytes} = length(\$event->{arg})"

Mastering MySQL Indexing

2012-04-05T22:35:00.000+01:00

Indexes are tricky things. In my experience, indexes are added whenever SQL queries are too slow. This makes sense. However, sometimes these indexes were added without being thought through enough (I am guilty of this). Sometimes they were thought through, but the table itself now has so many indexes that the optimizer doesn't know which one to choose from.
The overhead of indexes greatly effects tables that need to be written to or altered often (even indexes on NoSQL database greatly effect writes).
Over indexed tables inflate the database size dramatically which adds to people's concerns that the database becomes unwieldy.

Why You Need a Part-Time Remote MySQL Expert

2012-03-20T19:03:00.000Z

When I thought to start to write this post, I was thinking about all the logical reasons why you would consider hiring a part-time remote MySQL database administrator or MySQL expert.
It then occurred to me that most of the readers, who are reading this post now, already know or can imagine the benefits of hiring a DBA to take care of their system.

So what I want to offer is a case based on the emotional benefits and value a part-time DBA can offer you and your business.

1) Peace of Mind

A DBA can offer you peace of mind that one of your core business components is being taken care of. It is like maintaining your house gas boiler or car every now to keep it running in good working order and preventing it from blowing up. Except that in this example, if it does break, you can't use the entire house or car until they are fixed.
What is important to remember here is that there is no immediate justification for spending money on maintenance. There is even a sense of loss when you do spend the money without seeing any value from it. You are left in a mental tug-of-war between the possibility of downtime and loss of profits to the immediate hit to your cash flow.

What usually helps here is to calculate the cost of downtime with the estimated time and effort to recover from it divided by the chance that it will happen.

In the event that your main database servers breaks down:

How much money are you losing from sales?
How much money are you losing from possible future sales? (For example, sales people and marketeers cannot send people to the website or loss of reputation)
How much money will it cost to get people (even at the dead of night) to resolve the problem? (people multiplied by hours or overtime hours)
How much disruption will it cause later on? (For example, sales people need to speak with clients to assure them everything is OK for two days instead of finding new clients)

Divided by - the chance that your database servers will break down.

Are we expecting any seasonal bursts of traffic and will the database servers handle it?
Will the hardware that the database server sits on hold up in the next 6 months?
How many single-points-of-failure are there in the environment where the database servers are?
How many people have DROP TABLE access to your servers and how often to people run "heavy" reporting queries on them?
How high is your developers skill level when it comes to writing efficient queries that prevent high database usage? (When releasing a new product or feature)

Once you answered all (or some) of those questions and jotted down some numbers, you will then have a figure of how much money you should spend to prevent downtime. This is a very basic risk management formula for managers and will allow you to get some perspective.
The mental tug-of-war can take a break as you are now allocating resources for something you planned a head of time.

2) Preventing loss of confidence

More often then not, I see a lot of small companies have people maintain their databases who do not have a lot of expertise to do it. Typically, if you have some developers, you ask one of them to do it. If you have a system administrator then that usually falls under that responsibility.

What happens then is that those people (usually, but not always) not fully understand their databases and just "keep them running". If something comes up that is a bit out of the ordinary then things start to get stressful.

I've even seen the database becomes the "don't touch it or it will break" system in the company.
At that point, you lose confidence in your system and prefer to not have to deal with it as much as possible.

3) Energy, Focus and Flow

When you have people with database expertise on hand, you can solve database related problems very quickly. Problems that were once difficult and complicated suddenly become straight forward.
For example, instead of having meetings discussing how to implement a very data intensive operation in the best way, you now have someone that can provide a quick answer or at least a direction to help implement it based on best practice and experience.
Your team can become more cross-functional as the expertise gets shared and solutions come more quickly.
You feel more in a "flow" - that things are moving in the right direction. You can focus on things which you would rather focus on you feel more energetic because of it.

4) Trust, Knowledge and Speed

Having an expert in your business and part of your team is very comforting. The more this expert is involved your business, the more this expert knows, understands and appreciate how your business runs and solves theirs problems. There is a cost benefit of having someone you trust and is knowledgeable about your business when you compare it to:

Finding a new expert (Possible additional fees for system or agent that found the expert).
Evaluating the person is in fact, an expert in their field.
Explaining what the business problem is
Double-checking if their solution does work or fully solves your problem.
If you are not happy with them, go to step 1.
If there was a misunderstanding in communication, go to step 3
Implement solution

Put a price on each step and compare it to writing an email to your expert saying "we have a problem with that thing we spoke about a while back. Can you please take a look at it?"

In fact, I would go one step further. If your expert is part of your team and they "own" and are responsible for your database system, there is a good chance that they would have come to you after finding the problem themselves and with a possible solution.

I never fully appreciated outsourcing a lot of core parts of a business to one-time development shops. I believe there is valuable business-knowledge that gets lost and that the additional overhead to manage everything becomes very high.

Conclusion

I truly hope that this post may benefit people by giving them peace of mind, confidence and a clear direction that will help them with their business and make it more enjoyable.

Parsing a Set in SQL

2012-03-09T17:14:00.002Z

I was trying to parse the data from the common platform enumeration which is a big dictionary of all or most of commercially or freely available applications, operating systems and hardware (the hardware part I didn't really understand).

Here is an example for "Microsoft Access 2000 sp2"
cpe:/a:microsoft:access:2000:sp2

As you can see, the data is separated by colons.
To get the parts that I want in that string, I used the following SQL statement:

*edit from Scot's comment*

mysql> set @v="cpe:/a:microsoft:access:2000:sp2";
Query OK, 0 rows affected (0.00 sec)

mysql> set @p=2 /*the part I want */;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@v,':',@p),':',-1);
+---------------------------------------------------------------------+
| SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@v,':',@p),':',-1); |
+---------------------------------------------------------------------+
| /a |
+---------------------------------------------------------------------+

mysql> set @p=4;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@v,':',@p),':',-1);
+---------------------------------------------------------------------+
| reverse(SUBSTRING_INDEX(reverse(SUBSTRING_INDEX(@v,':',@p)),':',1)) |
+---------------------------------------------------------------------+
| access |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

Hope someone else finds this useful..

McAfee MySQL Audit Plugin

2012-03-09T13:33:00.000Z

I'm work at McAfee at the moment and I stumbled across this:

They have a free (GNU General Public License according to the download) MySQL plugin for auditing MySQL - https://github.com/mcafee/mysql-audit/downloads
(yay, for using github)

They also have an enterprise-level database security product which of course is not free (no idea how much) and a video explaining what it does can be found here.

Another MySQL security company is, of course, GreenSQL with their open source and commercial SQL firewall.

I did evaluate GreenSQL once and recommended it to my boss at the time, but his reaction was:
"Well, its your job to secure MySQL, innit?"

Hopefully, other DBAs will have more luck then I had recommending security products where security is important.

Tmpfs with IO_Direct

2012-02-22T20:15:00.001Z

bad idea..

tmpdir..
..on tmpfs

upgrade..
..to 5.5

default engine..
..innodb

create..
..temp tables statements

optimizing my.cnf...
..io_direct sounds good..

tmpfs..
..and io_direct
do not..
..play well together

..lucky...
..just slave..
..had backup

LOAD DATA INFILE to resolve locks

2012-02-07T12:58:00.000Z

In relation to this MPB post, there is a command to output data into a file and then to load it back into MySQL to resolve locks. (An example of a really big reporting query that could be quite heavy, can be found here.)

There is an issue with outputting data into a file through MySQL if the file already exists. For example:

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> select * from user into outfile '/tmp/user.sql';
Query OK, 10 rows affected (0.00 sec)

mysql> select * from user into outfile '/tmp/user.sql';
ERROR 1086 (HY000): File '/tmp/user.sql' already exists

So to resolve this, I played around with the \! command (which lets you run commands on your OS):

mysql> \! rm -rf /tmp/user.sql
mysql> select * from user into outfile '/tmp/user.sql';
Query OK, 10 rows affected (0.00 sec)

This seemed to have worked.

Apart from security issues and OS specific commands, anyone see anything wrong with this approach?

Perhaps I should wrap it around in a stored procedure..

Query-Digest on Windows

2012-01-15T20:24:00.000Z

The best way to run pt-query-digest is by making absolutely sure it is not running on any server that is even slightly important to production.

I recently had a case where pt-query-digest took up all the memory and swap on a (linux) development server. I think the reason was that the slow log had blob data in it.
Even though it wasn't a production server, it managed to upset some people.

So I:

Installed strawberry perl on my windows computer
Downloaded the gzip'd slow log file (using WinSCP)
Unzip'd it
Saved percon.com/get/pt-query-digest to a .pl file on my laptop
Went to start menu
In the run part I typed cmd
In the new command window, I typed pt-query-digest.pl slow.log > digest.txt

And walla!
My laptop was a bit hot for a few hours, but nothing crashed and no one was upset.

Explaining Indexes with a Library Metaphor - Reloaded

2011-12-27T14:41:00.001Z

I wanted to build on the metaphor I used to explain indexes and continue a bit further into disk and memory usage.

Sorting without index cards

Let us say that we would like to get a list of all books written by J.R. Hartley and we would like this list ordered by the most recently published books.
What we would do is:

Enter the library
Speak to our trusty librarian about the list that we need
The librarian would consult his or her index cards and would then give us a list of where all those books are on the shelves in the library.
We then head over to find those books in the different positions in the library.
Once located, we can do one of two things in order to sort the books:

We can get the information we need from the books and simply remember the list and sort it in our heads (in memory)
We can take all the books over to an available desk (temporary table on disk) then take a pad and pencil and write down that information on it. After that is done, we can sort that list on the pad and produce the list we want.

As you can imagine, doing calculations in memory would be much faster than sitting down next to a table and writing it down. The reasons why we would prefer to write it down would be:

The list of items would be too long to keep in our memory.
We may need to keep a text that is or may be too long to hold in our memory (such as text or blob datatype).

Sorting with index cards

Let us suppose that the piece of information we need is kept within the librarian's index cards.
What we would then do is:

Enter the library
Ask for the index cards from the librarian
Place it on his or her counter and change their positions until they are in the order that we want.
Leave the library with the ordered list that we wanted

http://blogs.fashion.arts.ac.uk/snapshot/2007/10/24/student-focus-group/

As you can imagine, using indexes to sort the data can be much faster than the alternative.

How can we use indexes to speed up our sorting?

One example of where you can use indexes to speed up your sorting is by using a multi-column index in your table structure. The index you set should first cover the search condition and then cover the sorting requirement.
For example:

SELECT * FROM table1 WHERE a = 100 ORDER BY b

Your index should be idx_sort(a,b)

Try it out on your own system and see if you notice any improvements!

Get the 95% for Your Index Prefix

2011-11-27T22:32:00.002Z

I was playing around with an idea recently...
I wanted to find out, what is the lowest number of characters needed to satisfy 95% of the values in a column? 95% is to rule out outliers.

I plan on using this when I want to get a bit agressive with the indexes on a table that gets inserted to very often. But until now, I haven't had a good query to find it quickly.

So, I thought a bit and came up with the following query:

mysql> show create table filenames\G
*************************** 1. row ***************************
Table: show_filename
Create Table: CREATE TABLE `filenames` (
`id` int(11) NOT NULL,
`filename` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `filename` (`filename`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select min(cc.l) as ninty_five_percent (
select length(filename) as l, count(distinct filename) as c, @ccount := @ccount + count(distinct filename) as t from filenames, (select @ccount := 0) as foo group by length(filename)) as cc where cc.t >= (@ccount*0.95) order by cc.t;
+---------------------+
| ninty_five_percent |
+---------------------+
| 48 |
+---------------------+
1 row in set (34.67 sec)

I now can run:

ALTER TABLE filenames DROP INDEX `filename`, ADD INDEX `filename`(`filename`(48));

95% may not be the ideal percentage to use, but it probably will reduce the size of outlier values.
It can take me a while to get a good percentage that reduces the index size, reduce the INSERT/UPDATE overhead, but still give good response time to SELECT queries.
I'm going to try this query to help me get there.

Thanks to Shlomi Noach for his talk in Percona London that helped inspire this query.

Installing Maatkit when you have Perl-DBD-MySQL issues

2011-10-04T10:57:00.000+01:00

Note to self:

If I have a problem installing Maatkit scripts because maybe Percona server created conflicts with dependencies, here is what I did to solve that:

wget http://maatkit.googlecode.com/files/maatkit-7540-1.noarch.rpm
yum localinstall --nogpgcheck maatkit-7540-1.noarch.rpm --skip-broken

On point 2, make sure what you install before you say "yes".

New Binary Log API

2011-08-01T20:40:00.002+01:00

I am quite excited at the MySQL Labs feature to have a Binary log API .
The reason is that I see this as a possible disk-based queuing system for MySQL.

An example of how you could do this, is instead of having a trigger with its own overhead, you could create a program to parse the binary log and run a trigger yourself a little bit later with no "real-time" overhead. As in, it would be run when the user isn't waiting for it.
You could also, for another example, develop a program that counts the rows in a table and update memcached directly, instead of querying MySQL for it.
This can already be done with other queuing systems like Gearman, but with this API, you can develop other APIs or applications on top of it. Like, memcache_table_counter demon.

In any case, it would be very interesting to see how this will turn out. I believe it would largely depend on the community of developers who will use this API.

How to Make MySQL Cool Again

2011-03-28T13:53:00.000+01:00

I feel that MySQL has lost its coolness factor.
Why do I think that? Well, because most people are interested in other things nowadays...
Other things like NoSQL - It's what all the cool kids are talking about.

NoSQL is as cool as it is partly because MySQL wasn't performing as fast as some people would have liked and eventually they looked for other alternatives.
Meaning that MySQL was the default database when people were installing their PHP/Ruby/Java/Python...etc website, but after some time, it got slow and difficult to maintain.

Difficult to Maintain (or not Web scale)

Lets try to analyse what "difficult to maintain" really means...
It could mean any, some or all of the following situations:
Your website got bigger -

Immediately (flood of users/went viral) and you had no idea what to do (you are a Facebook app)
After a short time, but you are working on new features and have no time (your sales/marketing department are ninjas)
After a long long time (more than likely that you just haven't archived your old data in the last 10 years as well as you have more users now)
Because you added so many features to the site that everything runs at a crawl (forgot to benchmark on hardware or optimize processes for your hardware).

Who should solve these problems?

Well, usually (in the companies I've seen) it mainly falls on the developers and maybe the system administrators. Companies of a certain size don't tend to hire someone specific to maintain the databases as their only purpose in the company.

Developers (in most cases) just are not interested in this database stuff. Not all of them, but a lot of them that I ran into would much rather discuss design patterns, agile development... and generally solving interesting programming problems.

System administrators usually have a lot on their plate to be able to mess around with the database beyond the minimum to keep things running.

Now, I am going to make an assumption that developers either have to solve all or part of the problems that the database causes or get very frustrated with these problems occurring. Probably both.

Web companies put a lot of pressure on these developers to release new features or fix existing bugs. If the developers say that by switching to a different technology, they will get things done faster, there is a certain possibility that these companies will implement those changes.

"Big Data"

"Big Data" is the new thing. Its interesting and cool. But what is "Big Data"?
Wikipedia defines it as: "Big data are datasets that grow so large that they become awkward to work with using on-hand database management tools."

So the difference between "Big Data" and a large or very large dataset is if using your DBMS is too awkward to handle it. With a definition like that, it is clear to me why more companies are trying to move away from relational databases.

My understanding of the trend is this: companies with, in their mind, very large datasets (which can honestly range from a main table that is 10Gb to 100Gb, from real life examples) and have a very difficult time maintaining or scaling their database, would (of course) consider themselves to have "Big Data" and look for solutions for it. Considering that its now interesting and cool, why not at least take a look at it and even give it a try in the company?

Is NoSQL the Enemy?

NoSQL is not the enemy. Key/Value stores, document-based databases and similar NoSQL databases can be a very good tool for a certain problem.
We've been using Key/Value stores in memcached for years. Document-based databases can be perfect as a replacement for Entity-Attribute-Value data structures or unstructured data.

There are also companies that their main function is only to process large amounts of data and they do it very well with NoSQL databases with Map/Reduce.

I didn't go into this at all, but for a website with a lot of transactions, NoSQL could also be a very good solution, depending on the situation.

The "enemy" is the hype around NoSQL as the one-size fits all solution and the FUD about scaling MySQL.
(in my opinion)

How to Make MySQL Cool Again

So without further ado, here is my completely opinionated list of how to make MySQL cool:

Gearman

I believe this is one of the MAIN things that will make MySQL cool again.

One of the main problems with MySQL is that it doesn’t do things in parallel very well.

Gearman can work with MySQL to run queries in parallel. Gearman can also work as an addon to branch out work once data is updated in the database. This leaves a space for MySQL to do some very interesting things and doing those things asynchronously.

With regards to processing “Big Data”, you can always process some parts of the data you need or all the parts at offline times or off peak times. Doing this using Gearman can prove to be very very scalable and efficient.

Map/Reduce

Map/Reduce isn’t just for NoSQL. It can also be used with relational databases. There are already proprietary databases that use some form of Map/Reduce, such as Greenplum, Teradata and Aster.

There is an argument that Map/Reduce can or should be used more for “offline” work, but I do not know enough about that to make a judgement on it.

There is already uses of Hadoop with MySQL and things could get interesting if this trend increases.

Column-Based Storage Engines

MySQL has a few of these already as storage engines: InfiniDB and Infobright. What these engines do is store the data in a way that allows you to do run queries on large data. These queries are specifically used for reports (these engines are not terribly good at many small insert/deletes). These engines save you from guessing which index to put on where because every column is a sort of index. Lastly, they also make an effort to compress the data.

Now these storage engines are not always that cool, at least not to developers, because they usually require some sort of work on the data so that it can be used efficiently. This work is called ETL (extract, transform and load) and now we’ve stumbled onto the world of Data Warehouses.

Data warehouses have been around for decades and have been a very good solution for querying large data sets for business intelligence.
The problem is that traditionally, companies who have used business intelligence have been quite big and with a lot of money. The tended to hire database administrators, database developers, data analysts and “power” business users analysts (think experts at Excel and Pivot Tables). Those people are already familiar with Data Warehouses and business intelligence and it won’t be too difficult for them to implement it (albeit it will take them a long time and be expensive).

Startups don’t tend to hire these kinds of people, at least not straight away and are not too keen on the “a long time and expensive” part of the deal.
Even though there are open source BI suites (Pentaho and Jaspersoft) and you can use MySQL or Postgres as well, the knowledge and skill set for implementing business intelligence is limited and possibly expensive.

NoSQL advocates claim that NoSQL with Map/Reduce will give small companies the power to compete with the “Big Boys”. However, I do not see a way for NoSQL databases to give tools to business users to query and analyse data on their own the same way BI suites or even SQL as a language does at the moment.

Play Around with Shiny New Hardware

MySQL is getting better and better at scaling on faster hardware. Why not have fun with it and load up on memory or experiment with new storage like RAID, SSDs, FusionIO/Verident or SANs.

Forget commodity hardware. Adding 32Gb of memory to a server that comes with a default 8Gb nowadays is inexpensive.

It could be quite cool to buy something for a few hundred dollars/pounds and see a dramatic increase in speed on your MySQL server.

Have Out-of-the-box Tailored Configuration for MySQL

No really. Why can’t the regular installation of MySQL ask you 3-5 questions about “how do you plan to use MySQL?” and then change the my.cnf file accordingly.

It would be kind of like those 3-4 different my.ini files that came/comes with MySQL for Windows.
Questions can be like “Do you mainly want the database to be transactional?” and “I notice that you have 16Gb of memory, would you like me to allocate 50% of that memory for the database (innodb buffer pool)?”
You can get a lot of mileage out of MySQL if you just configure it slightly.

It can be quite cool if MySQL “just works” for much longer if configured more suitably to the server it’s on. In addition, you can add to this idea at the end: “MySQL has now been configured to your server. Now you can relax”.

Materialized Views

It is apparently a well kept secret in the Web world, but almost all the proprietary databases have something called Materialized Views (some not with that exact name).
What is it? It’s basically a copy of a select query that’s already been processed and is on disk for you to reference. There is no need to generate the query from the main table over and over again. If the data that you need is in the Materialized View, then querying it directly will be (should be) very fast and non-locking to the main table it’s referencing.
These proprietary databases usually also maintain this view themselves and allow you to specify how often you would like the data to be updated.

So imagine you have a large table where you keep all your orders. This table is inserted to quite often and at the same time, the business users in the company like to run reports on this table. This can cause problems sometimes and usually the way to solve this issue, is to allow the business users to run their reports on a slave database (which is always a good idea).
If you know that the business users are only interested in the grouped by data of this table for each month, you can compile a separate table with this data which would be much much smaller and faster to generate reports from.

MySQL doesn’t have built in Materialized Views, but luckily, there is Flexviews which can do the same thing for you.
In a very short summary, it’s a package that parses your MySQL binary log with PHP (think tail –f on the binlog) and then applies it to the tables which will be written to disk. You can use it to generate the whole table (which would be the same as: CREATE TABLE mv_orders AS SELECT * FROM orders GROUP BY MONTH(`date`),YEAR(`date`);) and you can use it to generate incrementally as I have explained above.
It’s really not intimidating to use and Justin Swanhart at Percona has recently written some blog posts to explain how to use it.

For short, it could be quite cool to generate this kind of view quickly and give it to the business users (or even your own reporting application). Everyone is happy and you can back to what you were doing before.

Sharding

Sharding is all the rage and in particularly useful when you do use commodity hardware. While its quite cool to talk about it, implementing it here can actually be a little more work and a little less cool.

I’ll give an example of how application sharding could work:
Lets say you have your website, everything is running fine apart from this 1 client that really kills your server. So you decide that you will “shard” the data and give this 1 client his own server.
You then go over all your code and add IF statements to say:

IF customer_id = 1 THEN use private_server1
ELSE use regular_server

You then go over all your tables and decide which are “global” tables and should be on both servers, which are primary tables which can be sharded and which are child tables which rely on the primary table.
You also might like fail-over and redundancy, so you create a Slave server for both servers. Now you have 4 servers and 2 backups that you need to maintain.

There is a company that might help you do this, but from what I saw, they are mainly for Java apps.
You can also try using MySQL proxy.
Whatever you choose to do, you might run into some gotchas.

What you will get is smaller databases that run faster which can be cool and you can definitely go to some conference to talk about it.

Tungsten Replicator

Tungsten Replicator made it on my list because it can solve quite a few scaling problems MySQL can have. The important ones to me are:

Multi-source replication - You can have 1 to many MySQL servers collecting data into 1 "hub" and then replicate that over to the other servers.
Solve replication lag by "sharding" the replications into several streams. I know quite a lot of companies that split up their databases by which application uses them (functional partitioning) to solve the problem that way.
The enterprise version allows you to change masters instantly in the event of a crash. Its high availability at a cost but can save you a lot of time and trouble.
Hidden ETL functionality - Can replicate data from several MySQL DBs (or Postgres) into 1 DB for reporting or back ups.

I haven't played around with it that much, but it looks to have similar functionality to Flexviews where you basically tail -f the binlogs and then you parse it in some way.

In any case, it's quite cool and there is a lot of blogs and presentations out about it recently.

MySQL Cluster

Also on the topic of using commodity hardware, you have MySQL Cluster.
Not yet as cool to initially set up, but can be very cool for high transactional websites. It has been battle tested by a large number of telecoms companies. MySQL Cluster has also recently received a lot of attention inside Oracle (I believe this is because it’s a bit more difficult to maintain and its easier to sell commercial support for it). New developments to it is to integrate it with memcached directly and easier to work with it using Java.

Personally, I am waiting for MySQL Cluster to get a bit more critical mass. The work on memcached API looks interesting and I also recommended a name change to make it sound cool.

Scaling Up MySQL

If you do feel you have “Big Data” with your MySQL databases, please consider doing the following:

Search the Internet – there is a HUGE amount of information available on the internet to help you with your MySQL problems. There is (still) a very friendly community behind MySQL and there are people falling over themselves to help you on stackoverflow, serverfault as well as MySQL forums and IRC.
Hire someone to take care of your MySQL Databases – either hire a full time/part time MySQL DBA or talk to a MySQL consultancy company. Your main options are: Percona, SkySQL (both only support MySQL) and Pythian, Blue Gecko and PalaminoDB (which do a mixture of MySQL, Oracle and MS SQL). There's also this little known company called Oracle which owns MySQL and supports it.
Send someone in your company on a MySQL course. Percona and SkySQL do those as well.
Buy better hardware. Again, get some memory, faster disks or buy a new server entirely. In a lot of cases, this can be the cheapest option if you consider how much lost hours of productivity your developers are wasting trying to tweak the code to your existing MySQL database.

Disclaimer:

All that I have written here is my opinion. There is no hard data supporting my claims. These are just trends that I have noticed and am expressing my thoughts in my blog. I reserve the right to completely change my mind if I or someone else finds new data or different trends.

Thank you for reading this blog post.

MySQL & NoSQL Survey - Results

2011-03-20T23:23:00.000Z

Thank you for everyone that participated in the survey.
The number of participants was 57 and here are the results.

This is a general trend right across the board.
32.9% of companies (from people that participated in the survey) use both MySQL and NoSQL in their companies and 67.1% use just MySQL.

This is the results by company size (by people), however because I didn't have a lot of participants in some groups, I rounded the some number in the graph below.

Again, the trend here is the 67% vs 32% stated in the pie chart and it seems more or less equal across the company sizes.

Programming languages used had a slightly different trend. PHP not adopting NoSQL as much as other programming languages, but again I only had a certain number of participants to say for sure.
Below the number of languages used in the company.

Thanks again for those who participated in the survey!

Deduplicating Your Customer Data

2010-12-20T20:26:00.001Z

Here is my presentation from the UK Oracle User Group at the beginning of December.

Deduplicating Your Customer Data from Jonathan Levin on Vimeo.

Sorry for the late publishing.
At the presentation I met some really great people: Ronald Bradford, Giuseppe Maxia and Andrew Poole.
I really did enjoy myself and benefited from attending.
The staff that took care of the speakers there did an excellent job.

Wikileaks - Swedish TV Report

2010-12-12T21:53:00.000Z

I have seen a report about wikileaks and I have to say that I was impressed.

Having spent time in China myself and noticed similar behaviour of denying information by other countries like Iran, I decided to "contribute to the world by making sure that ideas, truth and knowledge go through barriers and reach people faster and easier".

I would like to share this report with you and ask you to watch it soon as it will go off line in a day or two.

My Presentation for UKOUG

2010-11-23T18:53:00.000Z

I've put up my slides for my presentation for the UKOUG in Birmingham.

If its seems interesting, be sure to drop by.

Cleanliness is next to Godliness

View more presentations from JonathanL.

Oracle UKOUG

2010-10-30T11:21:00.000+01:00

..I'll be speaking in it: http://techandebs.ukoug.org/default.asp?p=5434&dlgact=shwprs&prs_prsid=5690&day_dayid=47

There are quite a few good speakers from the MySQL community there, I noticed.
I would very much like to say hi to them when I'm there.
Very much looking forward to it.

So the planet has less money. Now what?

2010-09-05T17:01:00.000+01:00

I've been reading a lot of "gloom and doom" predictions about the world economy recently. Everyone is under the impression that there will be a "double dip" recession, taxes are going to be sky high and benefits are going to almost completely go away. Banks are not giving out loans to small or new companies, mortgage lending is quite bad (at least when I looked recently and decided to give up) and people are thinking twice now about going to university to improve their chances of getting a higher paying job (find articles about "college bubble").
I don't even really need to link news stories to the sentences above, because there really are that many.

So the planet seems to have less money, now. At the very least, the number of people controlling the wealth of the planet has shrunk considerably (I would actually have to find some articles to make that assumption, but anyway...).

My point is, so what now? Having less money doesn't mean you have to be less happy or fulfilled in your life.

I predict that people will change the way they look at happiness (once they come out of their personal depression) and that they will pursue the following areas more:

Experience over possessions (there is already a small trend for this)
More family and community (connecting with people makes you happier and live longer)
An increase in Spirituality (usually happens after long periods of pain or depression)

Overall, I see this as an exciting time to be human as we are going to discover a lot more about ourselves and others.

To help us start our path to happiness, I have attached a small booklet from neweconomics.org that gives you 5 ways to be happier everyday.

Open publication - Free publishing - More well-being

My Opinion on NoSQL DBs

2010-09-05T11:59:00.000+01:00

I'll let the following express my opinion about NoSQL

and..

Where have all the MySQL DBAs gone?

2010-07-03T20:04:00.001+01:00

New Addition: Why you need a Part-time Remote MySQL Expert

I have a friend who works as a MySQL recruiter. He recently told me that he cannot find any more MySQL DBAs in the UK or in the neighbouring countries. His words were "I've exhausted the market".

I myself know that there is a shortage of MySQL DBAs and that a lot of recruitment agencies are looking for them. So I started to wonder how come the situation is the way it is?

Experience curve

Usually what I see in companies, is that they start out with the developers taking care of the databases along side their usual coding duties. At some point, they complain to management that they need a professional DBA to handle the database. When the complaints get loud enough, management looks for a new DBA.
The problem is that the DBA they want needs to have 3-5 years experience as a MySQL DBA.
It seems to me that there is no "phasing into" being a MySQL DBA or at least, you are expected to know a lot to begin with.

As my recruiter friend said, DBAs come from 2 streams: developer or sysadmin.
I, myself, came from the developer side. I started as a developer and got more and more interested in the database. At some point, I was the only person in the company that knew anything about databases, so I was the first one to turn to when there was a problem. Next thing I knew, I was a DBA.
Since I come from a developer background, I have some understanding of the code and how it interacts with the database. I usually put an emphasis on queries and schema design over server settings.
DBAs from sysadmin background, start as a system or network administrator that are at some point assigned responsibility over the databases (with or without their consent) .
Most jobs do actually prefer a DBA from a sysadmin background over one from a developer background.

The problem with phasing into being a MySQL DBA position, is that you would need to have other responsibilities apart from looking after databases. Eventually, you can phase into a position that needs most of your time to do that.
Its just that the in between is such a large chasm to cross, that many people don't really try to (especially developers).

Its not that interesting

For some people being a DBA most of the time isn't that interesting. I have seen DBAs opening their own companies or trying to become managers. I, myself, do a lot of data projects in addition to looking after the databases.
Companies that try to find a DBA, because the other developers complained for one, will almost definitely not have a lot of work for a that DBA a few months after they come in.

Unless you work for a huge company like facebook or a consultancy like pythian, you will not find enough work as a MySQL DBA to avoid you from being bored.

Salary

There is also a problem with salaries. From my experience, most companies look at MySQL DBAs as PHP developers with a bit extra. And in most companies (but recently this has changed a bit), PHP developers get paid quit low.
(To be honest, when I say "most companies" I mean LAMP shops or LAMP e-commerce companies. Usually, if you are the type of company to adopt open source, you wont have enough money to pay people well. This is of course changing and my current company is one of those exceptions, but more often then not, its that way.)
So accordingly, MySQL DBAs get offered salaries similar to mid to high level PHP developers.

This can be fine for some people, but when you look over at the MS SQL and Oracle side.. it leave a lot to be desired. And back to the point of "phasing into" being a DBA, the proprietary databases have all sorts of levels. From beginner at "not so bad salary" to expert at "extremely well paid salary" to consultant at "obscenely high salary". There are very few MySQL DBA consultancy jobs that I have seen (almost all of them in London) and the pay is literately half of Oracle or MS SQL consultancy jobs.

It would be nice to see more balanced salaries for DBAs across the different technologies and recruiters like my friend try to achieve that.

What needs to be done

I think its a difficult situation, to be honest. Even if companies try to encourage more people to become MySQL DBAs, it will still take some years for anything to change. This adds to the problem, I believe, of companies accepting MySQL as their database of choice. They can find MS SQL and Oracle DBAs easy enough (they just have to pay a lot), but how can they justify MySQL when it takes (at least) 3-6 months to secure a MySQL DBA (if at all).
However, companies can try to accommodate DBAs by allowing for more mixed positions (either developer or sysadmins) as well as training.

It will be interesting seeing this space in the market develop.

Addition:
Another post that talks about this problem can be found here:
http://www.iheavy.com/2011/12/19/mythical-mysql-dba/

1 Million QPS

2010-06-20T17:45:00.000+01:00

How to Safetly Restart Your MySQL Slave Server

2010-05-18T11:44:00.003+01:00

Make sure that nothing can access it
(take it off the load balancer).
in mysql client do: STOP SLAVE;
in mysql client do: FLUSH TABLES;
(if it gets stuck here, then you might need to fix it. You can try UNLOCK TABLES.)
in command line do: /etc/init.d/mysql stop

...do your what you need to do here...

in command line do: /etc/init.d/mysql start
(check for errors, sometimes in my.cnf)
in mysql client do: START SLAVE;
(normally this would be done automatically when you restart mysql)
in mysql client do: SHOW SLAVE STATUS\G
(check that replication is working properly)
After you are satisfied, set up the slave server back on the load balancer

Yet Another Opinion on the State of MySQL

2010-04-19T00:46:00.000+01:00

I’ve been keeping my eye open and my ears to the ground with regards to the Oracle purchase of MySQL. I was waiting for the O’Reilly conference to get a better idea of what might happen to MySQL in the future.

After watching the presentations from the conference I can conclude that from what I saw - people are still generally worried about MySQL.
Those people are divided into two camps. On the left side you have the people that say, Oracle has a lot of money and lets make the best out of a bad situation. On the right side, you have people saying that we should band around MySQL and continue adding to the community version.

What I understood from Oracle is that they would like to continue supporting MySQL and they would like to move MySQL into their stack. Meaning that Oracle would like to be the main provider of backup, monitoring and support - the areas where you can make money around MySQL. You can think of it like targeting the medium to large companies that already use MySQL and getting them into Oracle contracts for taking care of MySQL.
What I don’t think Oracle will do (or at least succeed in doing) is try to move MySQL customers to use the main Oracle database. I think there is a case for those same companies to start using Oracle databases for business intelligence or reporting and if they are already in the Oracle/MySQL “stack” then it would be easy for Oracle to sell them additional features.
This might not necessarily be a bad thing, since it ensures that when a company grows, there will be someone to turn to when they need help.

What this means to me is that Oracle will invest in that surrounding stack (backup, monitoring and support) where it can make money and perhaps invest just enough in MySQL to keep it relevant.
Perhaps this is a good strategy. Perhaps this was what Sun should have done. What you can’t deny is that Oracle has a proven track record of making money and they will stick to what they know.

From my point of view, I would like to take a closer look at using MariaDB in production. Its features are becoming increasingly relevant and I am confident that the companies that contribute to it, both care (deeply in some cases) about it and want to see it improve.