MySQL Marinate has started strong and it is not too late for you to join!

Join the MySQL Marinate self study group. We are only in week two and you can easily catch up with the rest of the class.

162 signed up for this FREE virtual self-study course from the Boston MySQL Users Group. Each week the group covers a chapter and does the homework. The homework from the first two weeks was pretty light and, if you get stuck, the participants are very helpful.

thanks a lot for your post

Thanks to all attendees of the webinar yesterday! If you missed it, you can watch the video recording.

Here are some questions that remained unanswered due to time constraints.

Q: Are there any technical considerations or best practice tips to have a replicated slave in the cloud, for example on Amazon AWS?

Hardware resources are usually limited in the cloud, and it is common that you prefer performance over data safety for a slave. So you may want to:

• relax InnoDB durability (innodb_flush_log_at_trx_commit)
• disable binary logging if it’s not useful
• look at the write queries that may cause replication lag and try to make them as efficient as possible
• have a look at indexes: the workload on slaves is often not the same …

If you need to automate backups, you might wonder about the different techniques available to you.

With regards to scheduling backups using built-in features of MySQL, you have two main options:

• Either run mysqldump (or mysqlbackup if you have an Enterprise licence) from an operating system scheduler, for example in Linux using "cron" or in Windows using the "Task Scheduler". This is the most commonly used option.
• Alternatively, use the Event Scheduler to perform a series of  SELECT ... INTO OUTFILE ... commands, one for each table you need to back up. This is a less commonly used option, but you might still find it useful.

Scheduling mysqlbackup with cron

mysqldump is a client program, so when you run it, you run it from a shell script, or at a terminal, rather than inside a MySQL statement. The following statement backs up the sakila …

Postgres replication for MySQL DBA's Working a with Postgers and MySQL replication I noticed that many MySQL DBA's have hard time grasping Postgres replication and how it works,  you may ask why would you need to know how to setup and use Postgres replication - but lets face it Postgres is becoming ever more popular and many MySQL shops also have Postgres databases. Overall a lot of startups are using a collection of databases  that serve various purposes. I"ll attempt to explain Postgres replication in plain English and use MySQL terms to help adsorb the seemingly complex subject. 
Postgres  "streaming replication" was introduced since Rel. 9.0 of Postgres and is a very useful addition that many of us were waiting for years, personally I strongly believe that the ack of replication kept Postgres from wide adoption unlike MySQL that had the replication from early releases. Replication is a great tool since it …

Funding for Ayasdi and Zettaset. NuoDB launches cloud database. And more

For 451 Research clients: NuoDB launches distributed ‘cloud data management system’ bit.ly/UO3ssM

— Matt Aslett (@maslett) January 15, 2013

For 451 clients: Armed with $20m series C, Lattice Engines looks to bring sales intelligence inside bit.ly/11z4VdF By Krishna Roy

— Matt Aslett (@maslett) January 16, 2013

Ayasdi Launches with $10 Million from Khosla Ventures and FLOODGATE. bit.ly/X7oemJ

— Matt Aslett (@maslett) …

Just like previous years, the ‘MySQL And Friends’ community is gathering to have a dinner on the Saturdaynight of FOSDEM. This time it is on February 2nd, 2013.

This year, it will not be an Italian restaurant, but more a Belgian style restaurant, about 10 minutes walk from the FOSDEM event.

Mirabelle: http://www.mirabelle.be/index.php?lang=en
Chaussée de Boondael, 455
1050, Ixelles

If you wish to attend, please register at http://fosdemmysqlandfriendsdinner2013.eventbrite.com

FOSDEM MySQL And Friends Devroom Schedule is available …

Queries in MySQL, Sphinx and many other database or search engines are typically single-threaded. That is when you issue a single query on your brand new r910 with 32 CPU cores and 16 disks, the maximum that is going to be used to process this query at any given point is 1 CPU core and 1 disk. In fact, only one or the other.

Seriously, if query is CPU intensive, it is only going to be using 3% of the available CPU capacity (for the same 32-core machine). If disk IO intensive – 6% of the available IO capacity (for the 16-disk RAID10 or RAID0 for that matter).

Let me put it another way. If your MySQL or Sphinx query takes 10s to run on a machine with a single CPU core and single disk, putting it on a machine with 32 such cores and 16 such disks will not make it any better.

But you knew this already. Question is – can you do something about it?

In case of Sphinx – indeed you can! And with very little …

We assume the new disk is /dev/sdb1 formatted as ext3
and it will be mounted as /data

# 0. make sure there is no mysqlm mysql data directory :
yum remove mysql mysql-server -y 
test -d /data/mysql/ && rm -rf /data/mysql/
test -d /var/lib/mysql/ && rm -rf /var/lib/mysql/

# 1. install Mysql
yum install mysql mysql-server -y

# 2. check the mysql  status
service mysqld status

# 3. start the mysqld if not started
service mysqld start

# 4. check the mysql status again
service mysqld status

# 5. stop mysqld in case its started, and check thre is n mysql process:
service mysqld stop
ps axu | grep mysql

# 6. make sure the /data partition is added to the /etc/fstab. If not add it:
test  `cat  /etc/fstab | grep /data | wc -l ` -eq 0  && echo "/dev/sdb1 /data ext3  defaults 1 1" >>  /etc/fstab

# 7. make sure the /data partition is mounted, 
test  `cat /proc/mounts  | grep /data | grep -v grep | wc -l` -eq 0 && mount /data

# 8. disable selinx …

Yes. I missed out a small detail about the internally added system column DB_ROW_ID. For tables, with explicit PRIMARY KEY or UNIQUE NOT NULL key, the DB_ROW_ID will not be stored in the row, even though it will be listed as one of the columns in the data dictionary object for the table (of type dict_table_t).

Thanks to Mark Callaghan and hedengcheng for pointing it out.