Netflix went down over three hours ago, and everyone is on edge here. My son just started reciting the script to "Monty Python and the Holy Grail" in an attempt to keep our courage up. This may be the last thing I ever write, so — Oh, never mind, it's back up again ... Crisis averted, and on to this week's developer news.

We have an App Store Appstore for that!

Amazon this week unleashed their own Appstore for Android devices. Apple took umbrage at the use of the (evidently trademarked) term "App Store" and fired a salvo of …

This was a request yesterday on a mailing list about how to I block DROP statements from running on a slave. There were responses including MySQL could not do this, or try other replication technologies however I thought I would share how you can easily do this on a slave.

You simply deny access on the slave. It is as simple as changing the MySQL privileges for the user(s) in question to remove DROP or ALTER capabilities.

Of course this will break replication rather then simply ignore the statement however I suspect that is the intended goal anyway. The first statement that uses the table will fail regardless. With appropriate monitoring of replication you should know in about 15 seconds. (Hint: If you don’t monitor replication or monitor it frequently, DO SO NOW!)

These leads to the question, what if the statements are legitimate? There are several solutions. Enable the specific privileges on the slave when the DBA …

Topology Participating hosts

* 1x BIG-IP VE
* 1x Debian Squeeze “Provisioning Server” serving DHCP, TFTP & DNS
* 3x Nortel/Avaya 1120E hard phones flashed to SIP1120e04.01.13.00
* 2x Debian Squeeze + digium asterisk packages:

$ grep asterisk /etc/apt/sources.list
deb http://packages.asterisk.org/deb squeeze main
deb-src http://packages.asterisk.org/deb squeeze main

DUNDi

*CLI> module reload pbx_dundi.so
*CLI> dundi show peers
*CLI> dundi show mappings
*CLI> dundi lookup 4012@extensions bypass
*CLI> dundi set debug on

SIP

*CLI> sip show peers

dialplan

*CLI> dialplan show RegisteredDevices

Work in progress

Yesterday, I installed a trixbox virtual machine using the …

Various people have complained about Linux swapping unexpectedly on boxes running mysqld, when apparently mysqld was not using all the memory and there was quite a bit of free memory available.

There’s also an article by Jeremy Cole. However, his solution requires a one-line change to mysqld_safe which while it’s small does not work very well if you have to maintain a large number of servers and are using packages asa package upgrade will overwrite the modified file mysqld_safe and then restart mysqld with the unmodified script. This leads to the need to repatch the script and then restart mysqld. Not very helpful.

So I was looking for another solution and came up with this option which basically requires a minor change to /etc/my.cnf and the use of a small shell wrapper script. The change to my.cnf is simply to …

I am one of the crazy individuals(*) that will be speaking at both the regular O’Reilly MySQL Conference and the IOUG Collaborate conference both being held in the second week of April. My 4 presentations are:

• Monday 3:45-4:45pm in Orlando – Common MySQL Scalability Mistakes
• Wednesday 2:00-3:00pm in Santa Clara – Add to Your O’Reilly Schedule – Best Practices for MySQL DBAs and Developers Part 1
• Wednesday 3:05-3:50pm in Santa Clara – …

For all those in New York this is an upcoming MySQL presentation held in conjunction with our colleagues at General Assembly on March 22nd 2011.

This presentation “How Better Indexes Save You Money” will be discussing how one simple technique can result in huge MySQL performance improvements and with zero code changes necessary. Many people think they know indexes, however MySQL and MySQL Storage Engines have some specifics that differ from more traditional RDBMS products. Learn some of the key analysis and verification techniques and be able to see immediate potential results in performance.

You can find more details at Meetup.com EffectiveMySQL. This new group is all about highly technical MySQL related content “no fluff, just stuff”.

I recently got an inquiry on how to receive an email every time a query was executed via the MySQL proxy. This is very simple and you can achieve it by simply piping the query to the *nix mail command. Here is the sample code (in a file caled send_mail.lua):

function read_query(packet)
  if string.byte(packet) == proxy.COM_QUERY then
    print("Hello world! Seen the query: " .. string.sub(packet, 2))
    print("Sending query to email:")
    os.execute("echo " .. string.sub(packet, 2) .. "| mail -s'Lua test' your.email.here@example.com ")
  end
end

To execute this, install proxy as per the instructions in the manual. Then simply run the proxy and point it at your script:

shell> bin/mysql-proxy --proxy-lua-script=send_email.lua &

Finally, connect to the proxy and issue a query:

…

The config files that come with MySQL server are generally not that good. They almost never work well for enterprise server loads and will leave most people wondering why the database needs someone to come fix it. In fact that might be why they roll those configs by default, to keep the consulting services alive and profitable. Personally I have no issues with consulting services, but everyone should start out with good configs to begin with. Well, no worries… here are config files for different system RAM configurations. Just choose the one that fits how much RAM your server has and make sure the cnf file is in place before you install the MySQL server RPM/tar/deb packages. Obviously you’ll want to remove the extension of the filename so it reads “my.cnf” and not “my.cnf_64GB” or the like. These cnfs have default settings in the comments so you know what is changed. There are also several equations listed so you know why I am choosing …

I thought I would upload a small script I use which I find quite useful. log_processlist.sh is normally run minutely from cron and collects statistics from any MySQL instance it finds. Data is kept for up to a week and then rotated. This allows you to check what happened over a period of time, for example if an instance crashes or seems to have a lot of load.

I have found it quite invaluable on the instances I’m monitoring and once setup it’s usually nice and silent. If you find this useful or have suggestions for improvements please let me know.

Given the popular response from my first lesson in improving scalability where I detailed simple ways to eliminate unnecessary SQL, let me share another common bottleneck with MySQL scalability that can be instantly overcome.

Analyzing the writes that occur on a system can expose obvious potential bottlenecks. The MySQL Binary Log is a wealth of information that can be mined. Simple DML Counts per table can be achieved by a single line command.

Let’s look at the following example output of a production system:

mysqlbinlog /path/to/mysql-bin.000999 |  \
   grep -i -e "^update" -e "^insert" -e "^delete" -e "^replace" -e "^alter"  | \
   cut -c1-100 | tr '[A-Z]' '[a-z]' |  \
   sed -e "s/\t/ /g;s/\`//g;s/(.*$//;s/ set …