tl;dr:
Make sure you flash an LSI-9211 to IT firmware rev#14 to get it to work 
with Linux and SSD trim.  You may have to downgrade from newer firmware
to older firmware to get the card to work.

Finding a SATA III controller with more than one PCI-e lane
After a recent hardware issue I decided to upgrade my computer to use new Intel 520 120MB SSD drives in RAID for improved performance.  The motherboard I use (an ASUS Rampage III extreme) has a Marvel SATA III controller with two ports, but I discovered that it is connected via only a single PCI-e lane (each lane can do at most 400MB/sec*).  This means that it can't effectively support even a single Intel 520 because one device can saturate the SATA III bus (An Intel 520 is rated at up to 550MB/sec sequential write).

So I went on a quest for a new SATA 3




  [Read more...]

Here’s the recording!

Heyo!

Now that we’ve gone through the Percona Live MySQL Conference and Expo review and had an amazing turnout to talk about it, it’s time to open up the discussion around things that DBAs want to talk about and need to be conscientious of. Join us on Wednesday at 12:00pm PDT (19:00 GMT) to take part in the discussion and share your knowledge and experience with the following topics:

• Filesystems and MySQL — Which do you use and why?
  • Do you handle I/O alignment? How do you do it?
  • Scheduler changes?
• Nagios checks! — Any new checks you’ve added recently?
• The worst settings in MySQL that you always change
• What are the most important variables to you,

  [Read more...]

Hey everybody!

The Percona Live MySQL Conference and Expo just wrapped up last week and we’re looking to get some conference in review talk from everyone! Talk about your favorite sessions, new things you learned, and your overall opinion of the conference!

Hop online Wednesday at 12:00pm PDT (19:00 GMT) to join the discussion and share your experience from the 2013 Percona Live: MySQL Conference and Expo.

Be sure to watch this twitter search or this blog post get a link for the google hangout tomorrow!

Some talks that were specifically called out:

• Monitoring MySQL with OpenTSDB by Geoffrey Anderson

  [Read more...]

Hi everyone!

Here’s a video of this week’s #DBHangOps:

March 27th at 12:00pm PDT (19:00 GMT) is when the next #DBHangOps is gonna go down. Check out the twitter search and hop on the google hangout to contribute some discussion! This week’s topics are:

• MySQL and puppet
  • Sheeri Cabral shared a pastebin of a puppet manifest used at Mozilla: http://scabral.pastebin.mozilla.org/2251358
• MySQL “Gotchas” (round 2)
  • MySQL Bug on predicate deadlocks due to locking non-existent rows
• A shoutout for NWCNA 2013 from Sheeri. Submit papers and check it out!

  [Read more...]

Here’s a solution to the not-so-long-standing issue of the Percona yum repo being broken for the CentOS 6 x86_64 version of the Percona-toolkit package. The repo listing is reporting an older version of the RPM which is not available on the site, so to fix this you just have to download the newer file and tell yum to add it locally. The side benefit is that you can use Yum to manage the RPM without adding the Percona repo, since the default settings for their repo could/have/had caused conflicts with Base Repo versions of MySQL packages; the Percona repo instructions set ‘enabled=1′ — not a great idea if you’re not setup to use the Yum priorities method of repo weighting.

So, if you see this after installing the repo via the instructions on their site:
Downloading Packages:

  [Read more...]

There have been many articles about the downtime issue with Cloudflare last week, so I won’t get into the technical details of that. However, there’s the fine print to remember. Consider this a subtle reminder that core Internet infrastructure services like Cloudflare’s DNS-based “Always Online” caching and packet inspection security services do not come with Service Level Agreements even at the “Pro” account level. Even with a Pro account you are paying for a service with no uptime guarantee and you must only hope that it resolves your sites the majority of the time. This is fine, this is what the contract says: no SLA unless you pay for the Business account. An odd naming convention given that most Professionals are using their websites for business and would want the SLA, but I digress.

So,

  [Read more...]

This is just for testing purposes, but you might want to play around with MariaDB 5.5.29 coming via the CentOS 6 repositories as mentioned in this post. Please test it out and report bugs if required. The process was simple on a fresh install:

yum update
cd /etc/yum.repos.d/
wget http://dev.centos.org/centos/6/mariadb/mariadb.repo
yum list mariadb\*
yum install mariadb-server mariadb
/etc/init.d/mysqld start

That’s it, it just works. It comes with MEMORY, CSV, MRG_MYISAM, BLACKHOLE, MyISAM, PERFORMANCE_SCHEMA, ARCHIVE, FEDERATED, InnoDB (XtraDB) and Aria.

Remember this replaces mysql-libs, and is set to replace MySQL in your install. Here’s hoping it hits mainline CentOS soon.

Related posts:

  [Read more...]

I’m going to take the numbers from my previous post, MySQL Modularity, Are We There Yet? for the “kernel” size of MySQL – that is, everything that isn’t a plugin or storage engine.

For Linux kernel, I’m just going to use the a-bit-old git tree I have on my laptop. I’ve decided that the following directories are for “plugins” drivers/ arch/ sound/ firmware/ crypto/ usr/ virt/ tools/ scripts/ fs/*/* and everything else is core kernel code.

Version Total LoC Total Plugin LoC Remaining (kernel) MySQL 5.6.10 1,049,344 265,189 784,155 (74% kernel) MariaDB 5.5 1,142,118  [Read more...]

Building clusters is usually a fun time. Here’s one of my setups at the Equinix LAX1 facility that is being used for VPN services, OpenVZ clustering, and general RADIUS and MySQL clustering integration. Once the clustering design is finalized, it’s still in flux state while I try out different setups, I’ll post some physical+logical architecture diagrams to show “How to Build a Fault Tolerant Infrastructure for Virtualized MySQL NDB Cluster + Python-based VPN systems.” Stay tuned for more.

Sometimes we encounter odd application responses that seem to make no sense. One of these such issues is related to running virtual server instances (OS Containers not Para-Virtualized VMs) and attempting to back up their data to Amazon’s S3 cloud storage. For moderately sized virtual machines running MySQL databases or Python/PHP based websites and code repositories this can be an inexpensive, quickly provisioned, and easy way to provide disaster recovery backups in numerous geographic locations, since we generally want DR content to be located in a physically distant location. Nevertheless, we can encounter errors if using an S3 mount in a distance location from our server if the timezone/sync data is incorrect.

The commonly seen error is as follows – and it doesn’t give much information for

  [Read more...]

EDIT: Video from today’s #DBHangOps!

Hey there peeps!

February 27th at 12:00pm PST it is! Check back here for more info and keep a watchful eye on the twitter search (and feel free to join the conversation!). The topics we’re looking to cover this week are:

• Day-to-day pain points (what takes more time than it should?)
  • rolling restarts and upgrades
  • Performing checksums on your data
    $ /usr/bin/pt-table-checksum \
--quiet \
--ignore-databases=mysql,percona,information_schema,per
formance_schema \
--lock-wait-time=50 \
--chunk-size-limit=0 \
--no-check-plan \
--no-check-binlog-format \
--max-lag=1 \
--replicate percona.checksums \
h=database.example.com










    

  [Read more...]

I’m pleased to announce that my first book, the InnoDB Quick Reference Guide, is now available from Packt Publishing and you can download it by clicking here. It covers the most common topics of InnoDB usage in the enterprise, including: general overview of its use and benefits, detailed explanation of seventeen static variables and seven dynamic variables, load testing methodology, maintenance and monitoring, as well as troubleshooting and useful analytics for the engine. The current version of MySQL ships with InnoDB as the default table engine, so whether you program your MySQL enabled applications with PHP, Python, Perl or otherwise, you’ll likely benefit from this

  [Read more...]

UPDATE: Here’s the recording, enjoy!

Hello everybody!

#DBHangOps coming at you this week, Wednesday 1/30/12 at 12:00pm PST. Be sure to check out this blog post tomorrow or check this twitter search to grab the link to join the Google Hangout.

This week’s topics were:
* bug fixes in recent versions
* Plugins
** authorization plugins (e.g. LDAP)
** audit plugin API — http://dev.mysql.com/doc/refman/5.5/en/writing-audit-plugins.html
*** state transitions of records in MySQL with a plugin?
* InnoDB Status variables from twitter — https://github.com/twitter/mysql/wiki/InnoDB-Status-Variables
* Table alters/schema changes
** Twitter patch for non-blocking alter table (throws a different error than

  [Read more...]

Update: Recording!

Finally, the end of year madness is over and we’re in a fresh new year. That must mean it’s time to get back into #DBHangops! Tune in on Wednesday, 1/16/13 at 5:00pm PST to get in on the fun and share your experiences.

Check back on this blog post tomorrow or check this twitter search to grab the link to join the Google Hangout.

Topics for this weeks hangout include:
* triggers: maintenance, problems, etc.
* Oracle MySQL utilities (similar to Percona toolkit)
** Slideshow of MySQL utilities
* Database caching and cache

  [Read more...]

Just a quick note to say that the site has been updated to a new theme which is based on the super awesome Twitter Bootstrap UI framework. To make life easier, since this site is also using WordPress at the core, I’ve made use of the WordPress Bootstrap plugin which allows for very simple integration. However, that wasn’t enough because the Bootstrap plugin comes with rather basic and boring generic styles; so I added the plugin for Google Font support and then modified the CSS accordingly.

You will also notice that the site is undergoing some reorganization of categories and content tags. This should help clean up search results as

  [Read more...]

In my ongoing efforts to migrate my fun side projects and coding experiments from SVN to Git I’ve come across some of my favorite Python based apps – which are all available in their respective repos on BitBucket, as follows:

IRC Bot Commander

• What it does: it’s an IRC bot that takes commands and does your bidding on whichever remote server the bot is installed on.
• How it does it: the bot runs on whatever server you install it on, then it connects to the IRC server and channel you configured it to connect to and it waits for you to give it commands, then it execs the commands and returns the output to your IRC chat window.

  [Read more...]

Unfortunately jQuery doesn’t come with default form validation to check for ip-addresses or subnet masking. So without a long winded explanation here’s the code. Just include this as a separate JS file like the rest of your page’s JS.

// 'ipv4': IPv4 Address Validator
$.validator.addMethod('ipv4', function(value) {
    var ipv4 = /^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$/;    
    return value.match(ipv4);
}, 'Invalid IPv4 address');

// 'netmask': IPv4 Netmask Validator
$.validator.addMethod('netmask', function(value) {
    var mask = /^[1-2]{1}[2,4,5,9]{1}[0,2,4,5,8]{1}\.
[0-2]{1}[0,2,4,5,9]{1}[0,2,4,5,8]{1}\.
[0-2]{1}[0,2,4,5,9]{1}[0,2,4,5,8]{1}\.
[0-9]{1,3}$/;    
    return value.match(mask);
}, 'Invalid IPv4 netmask');

You can use it like this.

$("#myform_here").validate({
    rules:{
	ipaddress:{

  [Read more...]

As a follow up to the previous post about logstash, here are a couple of related init scripts for anyone implementing the OpenSource Log Analytics setup that is explained over at divisionbyzero. These have been tested on CentOS 6.3 and are based on generic RC functions from Redhat so they will work with Redhat, CentOS, Fedora, Scientific Linux, etc.

• Init script for ElasticSearch: located in the repo by clicking here – servicewrapper required
• Init script

  [Read more...]

Now that the thanksgiving holiday is over, it seems like the perfect time to have another #DBHangOps. If you’re interested, check this twitter search or check back on my blog here for a link to the google hangout.

Items currently on our agenda for today:
* Data corruption!
* Monitoring — what do you monitor, why, etc.
* Query killing — do you kill queries in production regularly, or in emergencies?
* Fun or useful configurations

Looking forward to chatting about MySQL stuff today!
Fun time hitting these topics this week. Check out the recording:

If you’ve ever used non-opensource code, or applications that you don’t have complete control over, then you may have run into situations you need to alter data on a per-row basis but been unable to do so for lack of application SQL access. The solution to this type of problem is to use a MySQL TRIGGER, which allows us to execute arbitrary SQL commands when defined events occur. Why is this useful and how does it work? Well…

For example, I have a freeRADIUS server that uses MySQL as a backend for the user authentication, and one of my server applications (HostBill) provides a freeRADIUS plugin that allows my users to manage their RADIUS accounts; however the default freeRADIUS schema lacks a DATETIME column on the user table. When a user is created (INSERT) or has their password changed (UPDATE)

  [Read more...]