Jonathan Levin's Axioms

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

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!

MySQL & NoSQL Survey

2011-03-13T21:54:00.000Z

Hello,

Could you please take the time and fill in this short survey about using MySQL and NoSQL in companies.
I will publish the results in a week.

Thank you for your time.

<p>Loading...</p>

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.

Violin Memory - We're Getting One!

2010-09-23T19:40:00.002+01:00

I attended the IT 360 conference in London today and I had a change to talk to the people who sell Violin Memory.

We were already interested, but we had some technical questions for them.

After chatting them for a bit it (looks likely that) we're getting one!

Mmmmmmmmmmm memory.....

We will be the first or one of the first people in the UK to own one, so that pretty exciting.

On a side note, the Violin sales paper were super nice. They were like: "Just take it for 30 days, give it a try. We'll help you with whatever you need for installing it and any other issues you have".
Very down to earth, friendly and helpful people.

I can't wait to run my MySQL (multi-day) data cleansing processes and see how well they do on this!

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.000+01:00

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.

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.

Compressed MyISAM is..

2010-03-17T20:14:00.000Z

..kind of cool.

Here is a good use case for compressed MyISAM:
We have a rather large database that doesn't get updated very frequently.
We have several slave servers that have this database, but because the database is big, they have a bit of a hard time querying it.

Disk I/O is a big issue for us and compressing the data, offloads some of that onto the CPU for decompression. It also allows the system cache to keep more of the tables in memory.

This database only gets updated every few weeks and it's pretty much a manual process.
To update the slave servers, we simply copy the MyISAM tables over to them (yes, you can do that) and because they are compressed, they are easier to send over the network.

Unlike Archive tables, compressed MyISAM can have indexes (Archive tables now allows 1 index) so you can use them for fast querying.

Compressed MyISAM tables are read-only, which for us means they are much less likely to get corrupted.

So there you have it. One good use case for compressed MyISAM.

Denormalization - Examples Needed

2010-03-15T15:16:00.001Z

Dear MySQL Community

I would like to ask for your help as I am writing a report for use cases for denormalization.
Could you please add a comment if you have used denormalization in the past to help solve a certain problem. If you could add what was the problem, how did you apply denormalization and how effective (or not) was it?

Thank you in advance.

328 Thousand Queries Per Second

2010-02-10T21:37:00.002Z

Well, I did it again. Two days ago I improved my server's QPS from 20k to 109k.
Today, after upgrading the MySQL version which allowed me to allocate more memory to the key buffer size (12 Gb), I managed to get it to 328k QPS.

The server is similar to the one from the previous post. Its a 4 year old, but quite meaty server.

The server ran triggers and stored functions that I wrote to help connect different data sets together. Basically reference another table using fairly complicated search logic, get an ID back and store it. The code was then tweaked to be as fast and light as possible.

This time I ran:

99 million row table to reference a 101 million row table
45 million row table to reference the same 101 million row table
35 million row table (same reference)
29 million row table (same reference)
28 million row table (same reference)

All at the same time..

The server hovered consistently around the 300k QPS mark, with a here and there high of 322-328k.
Also, I had to modify the code of Mtop (the application used to monitor MySQL in the picture) to ask for "Queries" and not "Questions" from MySQL after I upgraded it.

I'm very happy with the result. I can't wait to get my hands on a stronger server to try to get a higher number, and in the meantime I will try to keep tweaking the settings on this server to hopefully get it from a load of 4 to 10.

Top Speed - Queries per Second

2010-02-08T22:54:00.002Z

Today I reached 109k Queries per Second. I was quite impressed by it.
Some background on the situation.
I developed some stored procedures to process some rather large tables we had in our database.
I managed to get the stored procedures to be very efficient and quick.
I then wanted to test it out and tried to overload the server to see how much it could take.
Normally, the server would do around 1k at best with these kinds of tasks. I have recently been able to tweak it to 20k QPS. But today, for some reason, the cache managed to get itself in the right position and produced this result.

The Server:
A 4+ year old Dell server, with SAS drives, 1 Quad-core CPU and 16Gbs of memory.

Database:
MySQL 5.0.48 - with MyISAM tables only

The Tasks:
Reference a 101 million row table (12+ Gbs) to fill in a column in three 8-9 million row tables (2-5Gbs).
Reference a 700k row table to fill in a 7 million row table.
So 4 tasks at the same time.

Click on the picture to see the full screenshot
Applications used: Mtop & Htop

I Lost Weight

2010-01-07T00:36:00.010Z

I lost weight! A lot of weight!
I lost 28 kgs (61 lbs / 4 st 5.7 lbs)
I started seriously on the 22nd of September, so roughly 3+ months.

Here are some Before pictures

And here are some After pictures

How it started

It all started when I met my mother on august the 26th. I had put on weight gradually and I didn’t really notice because we didn’t have a scale in our home (probably on purpose). My mom noted on my weight and told me to go on her scale to see what my situation is. It was very bad. I weighed 134kgs and it’s the most I have ever weighed in my life. 6 months earlier I was 128kgs, so not a huge difference. I think a number of microwave and McDonalds meals took it over the top.

I got very depressed after knowing my new weight because I knew very strongly that there was very little I could do about it after trying at least 10 different diets over the years and watching the weight come back with extra at the end of it.

Then I was reminded that there were 2 occasions when I did lose weight for a certain time:

It was when I did a 2 month physically-intensive course. The food they served there was terrible and I was too depressed at the time to eat anything. So I believe I got to 106kgs.
The second time was a period when I finished studying and was looking for work in a foreign country. Looking back on it now, I was highly optimistic. So while I was looking for work, I was still enrolled and could use the university gym. Every weekday I went to the gym for 3 hours without fail. After I found a job, I stopped going to the gym, but I left there weighing 118kgs.

So as you can see, I have never been thin during my life. Even at the ages of 16-17 I weight 95kgs. But I did have 2 references, although not very positive ones to losing weight. One is be depressed and don’t eat and the other is work out 3 hours a day for a month and a half to lose 5-8kgs maximum.

Getting Serious and Finding Answers

At this time I was looking for answers and I started doing some research. Part of my research recommended I get a proper scale that not only measures weight, but fat percentage and other things as well. I bought an expensive Tanita scale that could tell me weight, fat, visceral, bone weight, calories needed per day, muscle weight and body water.

It also has a calculation that guesses your body type to a certain age. The healthier you are the lower the age (hopefully less then your actual age) and the unhealthier, the higher the age. It told me my age was that of a 50 year old man. Considering that the average age for men to die is 70-78, this to me meant that I have 20+ more years to live. I took this news rather alarmingly and decided to take action.

The Diet

The diet that I ended up choosing was the pH miracle. Funnily enough, I had heard its principles already at the end of 2006, tried them for a while and then decided it couldn’t be done. One of the principle there of food combining, which says that you shouldn’t eat carbohydrates and protein together as the stomach can only secrete juices to break one of those types. Those juices are acid for protein and alkaline for carbohydrates. If you mix those 2 types of stomach juices, then they cancel each other out. This causes the body to try to make more of those juices and you feeling tired after eating a meal.

At the time, I tried it and it did work quite well at raising my energy levels, but I found it almost impossible to find places to eat that kept to this rule.

This time, I bought the book for the diet and took it very seriously. I found it very useful to get all the information from the book, specifically the science behind it. To me, it made a lot of sense, but I did try some of what the book was talking about and verified that it had positive effects on my body.

The diet itself is very basically: a strict raw vegetable eating diet followed by green drinks and supplements. To me, moving to this diet seemed impossible. Luckily, the book did offer a path to move to this extreme vegan diet and after seeing results, it was easier to persuade myself to do it full time.

The thing I liked a lot about this diet is that it comes from a healing background. A lot of the examples and testimonials in the book are about people who were severally sick and physically tired all the time and they managed to quite literally, turn their life around. The book itself talks a lot about cleansing and getting rid of acid build ups in your body. I didn’t really think about doing a cleanse prior to that book, but I was aware that it was good for you.

I purchased the green drink that the owner of the book sells to start the diet. This didn’t really bother me because a) I used to drink wheatgrass juice when I went to the gym every day and b) I did research on this green drink which showed me it was very good (although being the most expensive). I started eating much more green vegetables during my meals. Then I went to the cleansing part, which I never really did completely. What I was supposed to do was be on a green drink and green vegetable juice diet for 3-10 days. This type of cleanse is recommended through many other diets. What I did was eat a white grapefruit for breakfast, huge salad (and I mean really huge) for lunch and for dinner I did take the green vegetable juice. The thing is that this half-cleanse was quite easy for me to maintain, so I continued doing it for about 3 weeks.

The weight really started to come off quickly. I used to lose between 300-600 grams a day and I didn't really feel hungry. I was asked how it would be possible to loose so much weight and they showed me a calculation about how many calories I would have to burn (at the time I wasn’t really exercising). I told them that burning fat is not the only way to get rid of fat from your body. In the book, it says that the fat in your body is there because your body is too acidic. Your body uses the fat to cover the arteries to protect it from acid and prevent holes in those arteries (which will kill you). Once your body is less acidic, your body doesn't really need the extra fat.

During the diet, I was very worried that once I stop the diet, I would gain all the weight back. I took 2-3 periods off the diet, in total 6 weeks off to eat half regularly and still sticking to the principles of the diet. On the whole, I would not lose any weight this way (I did manage to lose 1 kg after a 3 week period), but I would not gain it either. Convinced that the diet does work long term, I continued with it.

The Physical Exercise

At the middle of September, my wonderful wife managed to find for me a cross trainer for our house. This was particularly amazing, for a couple of reasons:

I was registered to a gym (an expensive one) and always thought how much time and money I could save with a cross trainer and a bit of weights.
It is extremely difficult to find any gym equipment that is over the 120kg weight restriction. This one was 140kg.
Even though at the time, we weren’t in a position to buy an expensive cross-trainer, my wife found one at a decent price and we decided that it would be a good investment.

Once I had the cross-trainer, I was able to start exercising again which helped a lot to lose weight. I go by the “slow-burn” methodology, which states that you shouldn’t over do it. If at any time your vision is not very clear of focused and you can’t hold a conversation without breathing heavily, then you need to slow down. This made it rather easy to keep exercising every day; it wasn’t painful and from a fat burning point-of-view, yielded the best results.

The Mental Exercise

While exercising, I listen to personal-development CDs and sometimes things from the teaching company to keep me motivated and make good use of my time. The motivation was a huge huge help to stay on this diet and probably one of the corner stones of my weight loss.

The main CDs I listen to is:

Anthony Robbins - Get the Edge CD - Hour of Power. (15, 30, 45 or 60 minute routines)
Joe Vitale - The Awakening Course - Clearning Technique (17 mins)

These audio CDs do the following for me:

Get a good feeling of gratitude
Plan goals and envision myself already achieving them. Again, I will get a good feeling from it.
Go over the things that happened to me recently which were special or magic moments.
Lastly, from the Joe Vitale CD, I go over situations that upset me in my life and practice "letting them go" and not feeling upset about them any more. I do it slightly different then recommended on the CD but at the end I feel very calm and happy.

Its important to note that I don't have a support system outside of my immediate family. I don't have a fitness instructor (or a gym membership) to push me and motivate me. Its very important that I motivate myself even on those days when I just don't want to exercise.

The exercise makes me feel very happy. So happy, that I look forward to the next time I will exercise and the results I can expect if I complete it.

Also, its much more interesting exercising your brain and your body at the same time. If I exercise without anything to listen to, I keep looking at the watch to see how long it will take to finish.

Other motivations was watching The Biggest Loser US. I was already half way on my diet when I started watching this. The things I learnt from it most is what the human mind and body are capable of doing. The other thing I learned from Jillian Michaels is that even if it hurts, it's not the end of the world and you can keep going on for 5 more minutes, 10, 15 and even 30 more minutes after you think you cant.

Weight Loss Recordings

Date	Kgs	Fat %	Visceral	Bone Mass(kgs)	BMR(kcal)	Muscle Mass(kgs)	Water %
25/08/2009	133.3	45.6	22	3.6	2318	69.7	44.2
22/09/2009	132.8	45.0	22	3.6	2317	69.7	44.5
16/11/2009	120.2	38.2	17	3.7	2300	70.7	46.2
08/12/2009	115.1	37.4	16	3.6	2221	68.6	46.5
15/12/2009	113.1	36.2	15	3.6	2221	68.8	46.9
19/12/2009	111.4	34.8	15	3.6	2220	68.8	47.0
05/01/2010	107.3	32.4	13	3.6	2205	69.0	48.3
06/01/2010	106.6	32.3	12	3.6	2191	68.6	48.3

Final Words

When I started the diet, I had a goal for 106kg like the last time I was quite thin (but this time without being depressed and starved to achieve it). At some stage, I decided that the goal should have a deadline and I decided that it would be the 11/12/09. I missed the deadline by a month or so, but I am very happy with the results.

My next goal will be 86kgs, but I plan to take it a bit slower then I did this time. Just maintain my diet. Maybe even relax for a bit and then go back to taking it seriously.

I started with over 134kgs and now I am 106kgs. I lost 13.3% fat. My visceral is now on 12, which puts me out of "high risk". I am much more healthier and energetic then I was before. I plan on living a long and happy life with my lovely wife which was my main motivation to start this process.

My new years resolution this year is.... buy some new clothes.