The MySQL explain plan is a great tool to help developers and database administrators to improve the performance of specific queries happening against a database. It is very easy to use, but its output can be confusing, so I thought I’d show a very simple example.

Let’s begin by looking at a table called recipes

mysql> desc recipes;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| name        | varchar(400) | NO   | MUL | NULL    |                |
| description | text         | YES  |     | NULL    |                |
| category_id | int(11)      | YES  | MUL | NULL    |                |
| chef_id     | int(255)     | NO   | MUL | NULL    |                |
| created     | datetime     | YES  | …

This recent session at OSDC 2011 Canberra is based on part of an Open Query training day, and (due to time constraints) without much of the usual interactivity, exercises and further MySQL specific detail. People liked it anyway, which is nice! The info as presented is not MySQL specific, it provides general insight in how databases implement concurrency and what trade-offs they make.

See http://2011.osdc.com.au/SQLL for the talk abstract.

Anyone interested in this should subscribe to or otherwise watch http://bugs.mysql.com/bug.php?id=57480 which is where work on this is being done. Very extensive work has been done. Among other things the work has included:

1. Moving many allocations to the heap instead of malloc.
2. Merging multiple allocations into one in several cases.
3. Better estimates of sizes needed for some allocations.
4. Defer allocation of upd_buff, of the size of the data row, that’s only needed if a row is updated.

Those who have an Oracle MySQL support subscription can also watch its mirror bug 11764622 in that system.

I asked for escalation of your patch for innodb_max_dirty_pages_pct and that’s been assigned to a developer. No ETA, just means it’s likely to get done sometime reasonably soon. Not guaranteed, of course.

This isn’t the …

I have been the Community Liaison and a Senior DBA at PalominoDB for 15 months, and doing remote DBA work for 4 years.  In that time I have learned that "consultant" need not be a dirty word, and that in the DBA world it is actually extremely valuable to have a remote DBA with lots of outside experience, and a team of remote DBAs for when your primary contact is sick or goes on holiday.As with everything, there are downsides to remote database management.  Even though there is a lot of architecture experience among the remote DBAs I know, we are not often invited to architecture meetings.  This is because time is the unit of currency, and while sitting in an hour-long meeting to give 5 minutes of feedback can save hours down the road, it's hard to see that.  Many clients have gotten around this by having all DDL checked and performed by remote DBAs, and that helps a lot.There is also no ownership - we can recommend solutions and …

The ScaleBase Analysis tool gives a schema a grade between 1 and 100 for being “sharding compatible”. It’s a neat feature, but many ask me how the grade is calculated. Well – here goes.

First of all, a good schema is one that is easy to shard. Database Normalization is usually a good thing when sharding. It means that finding the sharding key is easy, relationships between tables are clear, and the queries themselves are usually much simpler. So we try to give a grade on how well the schema is normalized.

After the sharding configuration is determined (see here on how this should be done), we review your MySQL General Log, to understand the value you can expect from the sharding configuration:

• Statements that run on sharded tables, and hit a …

I am Cloud Camp in Stockholm today. Some interesting ideas are bounced around, pretty cool stuff.

One thing hit me today though: the lack in innovation, in IT as a whole and in databases in particular is stunning. I have thus decided to write a few blogpost on this I think should, and probably eventually has to change, but noone wants to change it, and few even see it as a problem.

That said, I still got a few interesting ideas today, and I will test some products I saw here, and I will write a few blogpost on some of them.

I think the good usecases for clouds is also getting clearer, and that is a good thing. In difference to the current IT trends, IT press and many high-profile bloggers as well as IT influencers, I do not think that cloud computing will help resolve the conflict in the middle east. Also, I do not believe that the introduction of …

I have for the first time been spending some time trying to understand the performance_schema. It is not easy to understand everything unless you are very well-versed in server internals (what I am not) and much information available here is probably more useful for server developers and testers than for ordinary users. But anyway some of the P_S tables are reasonable simple and useful. For instance the ‘accounts’ and ‘hosts’ tables (introduced in MySQL 5.6x) are immensely useful for everybody.

The reason why I spent time with this now was (I am totally egoistic, of course) that we just released the first beta in a new MONyog release cycle where you may expose P_S contents for monitoring. Please refer this Blog.

However I have two …

New Euro Partnership offers safe-haven from US Patriot Act
Severalnines and City Network launch first fully European ‘Database as a Service’

STOCKHOLM and LONDON, 23rd November -Severalnines, provider of automation and management software for cloud database platforms, and City Network, the ‘data center in a browser’ company have announced the first fully European Database as a Service (DBaaS) solution - in beta form. The City Cloud Database Service is based and operated by companies in the European Union - offering European customers full compliance with EU Directive on Data Protection 95/46/EC and a safe haven from the reaches of the US Patriot Act.

EU customers can now benefit from the savings and flexibility enabled by cloud-based database services safe in the knowledge that they …

=====================

Let me say NO, but nothing really bad just annoying and thanks to Oli (always great) less annoying then how it could be.

Anyhow, I had to put all up in a RED HAT box on EC2.

So I start to take a look here an there to see what is the best way to do it, I also take the configurator from Severalnines, but for the moment I am not going to use it,

and in any case I never use it for the my.cnf parameters.

But what was the scope of all this?

Overview

=================

I have a customer that could be interested in using a scaling MySQL solution, for his business.

Requirements as usual are 100% HA (OK we know this is not possible), scaling in writes as much as possible, geographic distribution.

Data set will be starting from 40G (peanuts) to 6 Tb (well not so peanuts) in 2 years.

More the scalability need to be "out" and "in" so …

In last weeks OurSQL postcast (episode 65)  Sheeri,  Sarah and Jerry talked about making MySQL safe with SSL.  Encryption always seems to be such a confusing subject. I think every database should be using SSL by default.  So, I was wondering just how easily SSL could be setup.

Most existing examples I found  setup SSL authentication and encryption.  If you are handling  PCI DSS or HIPAA data you must encrypt the data on the wire, but passwords are …