While doing a standard audit for a new client, I recommended a few changes to get better performance. Because I had several changes, I used the documentation at

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

and found that innodb_flush_log_at_trx_commit is a dynamic variable. I was surprised, because most operations dealing with file sizes and operations are not dynamic. But the client proceeded with:

set global innodb_flush_log_at_trx_commit = 2;

and got:

ERROR 1193 (HY000): Unknown system variable 'innodb_flush_log_at_trx_commit'

So I searched for others who may have had the same error, and ended up getting an unintentional googlewhack. Well, it’s not a real Googlewhack, because it has more than 2 …

After following the scale up path (i.e. buy bigger boxes) for a while now, we definitely need to scale out as things start to become “unhandy” and simply HUGE. (See small things are better)

We are running an OLTP application with about 200 million transaction per month. Currently we have some very large tables with more than 1,5 billion rows and the total database size is about 600 GB - all InnoDB.

What we want to implement is application based sharding, i.e. splitting up the big tables and distribute them among many smaller servers. Furthermore we need to implement some sort of archiving mechanism since the db size is growing very fast. Putting old data into compressed myisam tables seems to be a good solution here.

So, how to implement this?

These solutions come into mind immediately:

…

I’m still looking for new entries. I get quite a few suggestions, but not all of them make it into quiz questions. Do send in your suggestions!

This entry comes from Rudy Limeback

A user searching your website is moving through categories, and you want to display descriptions of these categories as the user progresses. Your basic SELECT looks like this:

SELECT id, description
FROM descriptions
WHERE id IN (23, 6, 9, 37)

You want the descriptions (records) to appear in the same order as the IDs listed in the WHERE clause.

How do you do that?

Show answer

Answer: Tag on an ORDER BY clause with the FIELD function like the following:

ORDER BY FIELD(id, 23, 6, 9, 37)

Very handy. I only wish Rudy had told me this 6 months ago when I was trying to do something exactly …

Reading Martens interview we see the quite:

"As soon as the deal closed we immediately secured a big deal with a major European national police agency," said Mickos, now SVP database products at Sun. "Key to them choosing MySQL was that we are now part of a much larger public corporation. The deal wouldn't have happened when we were private."

Cool stuff! But I'm wondering how much the opposite applies as well - this would leave small companies to seek for other ways to get the service they need ?

As company gets ready to server higher end customers it often "moves up the stack" and fails to deal with needs of lower end customers. This especially applies to Professional Service Companies.

Let me give you an …

Returning an error message is one of the tasks that may become common when working with MySQL Proxy.
Let's say that you want to prevent users from querying your database at a given time. The most sensible answer that you would send to the client that is requesting a query in the forbidden period is an error message. Not only that, the client must receive an error code and SQL state, as if the error were issued by the database server.
With MySQL Proxy, an error set is a legitimate packet to be sent back to the client, and thus you can return a customized error in answer to any query.

function error_result (msg, code,state)
  proxy.response = {
      type        = proxy.MYSQLD_PACKET_ERR,
      errmsg      = msg,
      errcode     = code,
      sqlstate    = state,
  } 
  return proxy.PROXY_SEND_RESULT
end …

Well, as most of you may know, Google Summer of Code is nearing and I have seen many
MySQL blogs urging people to contribute ideas or mentor projects.


My Idea
So I'm going to suggest an idea and as the title already mentions, I think it would be a good idea to add some new Datatypes. Specifically, a new Email datatype.

Now I already hear you saying "Email is a string, why would you need a datatype for it?", but hear me out.

So basically, I personally, have been dealing a lot with trying to clean email addresses and customer data so that my company can help their biggest client use this data in their new CRM system.

I thought it would have been a good idea to implement an email datatype inside MySQL that already has the standard regex for emails and has a defined varchar for the standard …

MySQL Workbench, the successor to DBDesigner 4 from FabForce, is a visual database design tool that integrates database design, modeling, creation and maintenance into a single, seamless environment for the MySQL database system. You can use it to design and create new database schemas, document existing databases and even perform complex migrations to MySQL.

It’s now a good week ago since our first Release Candidate numbered 5.0.15, and if you haven’t checked it out yet, now is a good time!

References:

• Wikipedia: http://en.wikipedia.org/wiki/MySQL_Workbench
• MySQL Forge: …

MySQL made some tasks from their internal task tracking tool - Worklog a while back. I just have not look at it besides checking Maria related tasks until couple of days ago as Jay announced new Forge going live.
Check it out - there are a lot of nice ideas out where. I can find a lot of things I originally submitted something like 5 years ago out there

I contributed most ideas during my first years at MySQL, some on my own some reformed customer suggestions from Sales/Support/Consulting engagements I was on. Later I practically stopped because there were not much attention to those small little convenience things - MySQL was on the road to take over enterprise market and all resources were focused on big things which allow to sell things to these customers.

In the recent months we're seeing MySQL is …

Top marks to Jay Pipes for getting the Forge 2.0 finally out after quite some time, as well as in the midst of the MySQL Conference he is organizing.

I am worried however about some of the content, as shown in the screenshot below, the opening page lists Worklog tasks/features for versions 6.x or 7.x , that’s ok, but features in 9.x. Where is the practicality of thinking more then 2 releases ahead, and just having a future bucket. Indeed, we have 5.1 and 6.0 already frozen and not releases, so 6.x is already 3 releases out.

Tonight we were told at the NY PHP Meeting MySQL 5.1 is not due to late Q2, so that’s at least June 2008.
The …

Tonight’s New York PHP community meeting was a talk by Philip Antoniades the MySQL Systems Engineering Manager.

With an interesting topic opener “A New Hope” I could not resist to hear Philip’s official MySQL presentation.

Some small points I took away from the presentation.

• Sun is committed to Postgres with Josh Berkus and a team of 20 people.
• Solaris.next is the next version of Sun, I thought that was a cool internal name, be it obvious
• A marketing slide of the highest traffic websites listed Meebo, yousendit, alexaholic, techcrunch, feedburneer, istockphoto and vimeo as reported by Pingdom. Not sure were they get their data, but Google, Yahoo, FaceBook, Wikipedia, MySpace, Fotolog are sites I think of as high traffic. Indeed 3 of these listed sites I’ve never heard of. …