In my previous post, I discussed scaling web database performance in MySQL Cluster using auto-sharding and active/active geographic replication - enabling users to scale both within and across data centers.  

I also mentioned that while scaling write-performance of any web service is critical, it is only 1 of multiple dimensions to scalability, which include:

- The need to scale operational agility to keep pace with demand. This means being able to add capacity and performance to the database, and to evolve the schema – all without downtime;

- The need to scale queries by having flexibility in the APIs used to access the database – including SQL and NoSQL interfaces;

- The need to scale the database while maintaining continuous availability.

All of these subjects are discussed in more detail in …

At Kscope this year, I attended a half day in-depth session entitled Data Warehousing Performance Best Practices, given by Maria Colgan of Oracle. My impression, which was confirmed by folks in the Oracle world, is that she knows her way around the Oracle optimizer.

These are my notes from the session, which include comparisons of how Oracle works (which Maria gave) and how MySQL works (which I researched to figure out the difference, which is why this blog post took a month after the conference to write). Note that I am not an expert on data warehousing in either Oracle or MySQL, so these are more concepts to think about than hard-and-fast advice. In some places, I still have questions, and I am happy to have folks comment and contribute what they know.

One interesting point brought up:
Maria quoted someone (she said the name but I did not grab it) from …

MySQL Workbench is a great tool for developers to map out and visualise databases. What many people know is that it’s also a great way to make upgrading and writing upgrade scripts for your components really painless too.

Let’s say you created a small component that became popular over night. Tens of thousands of downloads from on the Joomla Extensions Directory. So you get inspired and put some more work into improving and bugfixing this component even more, and while doing that you change and add on to the database schema.

Now you need to create and distribute an update script with the next version of your component, and MySQL Workbench makes creating this update script really easy. Here is what you need to do:

Install MySQL Workbench

I’ll not help you with this. Just go to the MySQL Workbench homepage and follow the instructions from there. …

This Thursday (February 18th, 17:00 UTC - note the different starting time!), Marc Alff will present the brand new Performance Schema: Instrumenting Code. Marc is the implementor of the Performance Schema feature. His presentation will show how developers maintaining

• the MySQL server code,
• a storage engine,
• or any type of plugin

can instrument the code to collect data for the new performance schema instrumentation.

For MySQL University sessions, point your browser to this page. You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. (Dimdim is the …

This Thursday (February 18th, 17:00 UTC - note the different starting time!), Marc Alff will present the brand new Performance Schema: Instrumenting Code. Marc is the implementor of the Performance Schema feature. His presentation will show how developers maintaining

• the MySQL server code,
• a storage engine,
• or any type of plugin

can instrument the code to collect data for the new performance schema instrumentation.

For MySQL University sessions, point your browser to this page. You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. (Dimdim is the …

This Thursday (February 18th, 17:00 UTC - note the different starting time!), Marc Alff will present the brand new Performance Schema: Instrumenting Code. Marc is the implementor of the Performance Schema feature. His presentation will show how developers maintaining

• the MySQL server code,
• a storage engine,
• or any type of plugin

can instrument the code to collect data for the new performance schema instrumentation.

For MySQL University sessions, point your browser to this page. You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. (Dimdim is the …

I’ve previously talked about table metadata in Drizzle and how we use the table protobuf message to describe a table (see Drizzle FRM Replacement and others). The model in Drizzle is that the engine is responsible for its metadata. For schemas (you may be thinking ‘database’ but we’re moving to the Schema terminology in Drizzle) we also have a small amount of metadata.

The protobuf message is specified in drizzled/message/schema.proto and is incredibly short. In fact, here it is in its entirety:

Interesting questions: ([updated] with answers from the development team

• [Q] Are there plans to administer MySQL Cluster with Workbench?
  [A] Not that we know of.
  
• [Q] Can Workbench deal with user permission maintenance across servers? (especially in cases where development and production users can't have …

Once in a while we been in situations where you need to quickly understand/analyze a database given to you by others.  We can go through each table and try to understand but it is usually lot simpler if you can visualize all the tables involved and relationships between those.  Enter SchemaSpy: a free tool to do that.    For sake of specifics, say the database given is MySQL and your pc is running some version of Windows.

A. Download and Install Pre-requsites (if you dont have already)

• Graphviz  (http://www.graphviz.org/Download_windows.php)
  • use MSI installer (see warnings specific to vista on the download page)
• Java (http://www.java.com/en/download/index.jsp)
  …

The easiest way to see the differences between two schemas on a non-Windows machine is to run:

mysqldump -h server1 --no-data --all-databases > file1.sql
mysqldump -h server2 --no-data --all-databases > file2.sql
diff file1.sql file2.sql

However, this will show also trivial differences, such as the value of AUTO_INCREMENT. It also does not give you a way to patch one schema to be like another.

We frequently are asked to “do a schema diff and create a script that will ‘patch’ one server.” Usually this is done to take a development or test schema and move it to production for a release.

We like to use the best tool for the job, and while diff is good, I like to use MySQL workbench. The OSS (Community) edition provides all the functionality we need for creating a schema diff and …