Here are 3 recent ' oops... wish I hadn't done that :/ ' mistakes I've made since joining moz that you might as well avoid (I'm sure there will be more, but they better not be the same)

Reviewing config files for MySQL, but not all of the defaults 

    We recently migrated a few MySQL databases to a new datacenter, and took advantage of the migration to upgrade the MySQL version(s) at the

Now that I'm back doing just database stuff, I've come to realize I've gotten a little sloppy about doing my homework.  Homework's never been my favorite thing in the world, but it often reduces stress when your under the gun during an outage or upgrade...

We had a MySQL database server that's been slow on DML changes, and based on the slowest statements being 'COMMIT', we had a good mind

Switching roles

To get a taste of the power of Tungsten Replicator, we will show how to switch roles. This is a controlled operation (as opposed to fail-over), where we can decide when to switch and which nodes are involved.

In our topology, host1 is the master, and we have three slaves. We can either ask for a switch and let the script select the first available slave, or tell the script which slave should be promoted. The script will show us the steps needed to perform the operation.

IMPORTANT! Please note that this operation is not risk free. Tungsten replicator is a simple replication system, not a complete management tool like Continuent Tungsten. WIth the replicator, you must make sure that the applications have stopped writing to the master before starting the switch, and then you should address the application to the new master when the …

INNODB has some irritating gotchas that makes disk space management hard. In 2002ish INNODB, added innodb_file_per_table to get around allot of these issues, but it does not fix everything.

If you are running innodb_file_per_table, you will notice in your database directory

• 
  
• db.opt - database characteristics file.
  
• tablename.frm - the table structure.
  
• tablename.ibd - the actual innodb table space file
  

Imagine that you have a table with 10 million rows and you delete say 5 million rows in multiple chunks around 400K chunks, because deletes are slow. Next, you notice that the table space file did not shrink. So what do you do? OPTIMIZE tablename, tada all the wasted space is reclaimed, but here is the PROBLEM the ibdata file grew!

ibdata stores all of the UNDO LOGS thus GROWS due to the deletes and …

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 …

Properties: 

Description:

This variable defines the size of each log file in a log group. While setting this variable it should be noted that combined size of all log files should be less than …

Properties:

Description:

This option, currently available only for Linux, if set enables the usage of large pages by MySQL. Many operating systems/system architectures support optional memory pages bigger than the default size (4 KB). Usually the large page size is 2 MB, this …

With all due respect to the technology and it's advocates (myself included), after a surge in articles describing the merits of using memcached I'm just pushing a thought breakpoint for developers to think whether they actually need it or not?

Recently, I ran into cases where the developers have decided to use memcached over MySQL style architecture after reading some/many good/nice articles about it without giving a damn to their requirements. I would like to list few things as a checklist for developers to decide on their architecture. There is still no precise answer but sometimes few cases can be just ruled out :).

1. What is the total size of your data? It might be a possibility that you can keep the data in memory in each node, or MySQL can just keep the whole thing (data+indexes) in a buffer.
2. How frequently your data is updated? Very frequent updates may lead to low cache hit ratio for memcached …

Properties:

Description:

This variable if enabled, makes InnoDB to create a separate .ibd file for storing indexes and data. Setting this variable makes it easy to manage disks with huge tables. Having …

I wonder what the best way is to get a feature request more visibility (convert a feature request to an actual work item).We use replicate-do-db on all our slave servers , so after many, many, restarts of our slave servers, I checked the bug list for any feature requests surrounding this, and about 6 months ago, one was opened: replicate-* rules should be dynamically configurableSadly,