It can be very easy to improve scalability with a MySQL server by a few simple rules. Here is one of them.

“The most efficient way to improve an SQL statement is to eliminate it”

There are numerous ways to eliminate SQL statements, however before I give a classic example that I’ve observed again with a client, let me explain the basic premise of why this improves scalability?

The MySQL kernel can only physically process a certain number of SQL statements for a given time period (e.g. per second). Regardless of the type of machine you have, there is a physical limit. If you eliminate SQL statements that are unwarranted and unnecessary, you automatically enable more important SQL statements to run. There are numerous other downstream affects, however this is the simple math. To run more SQL, reduce the number of SQL you need to run.

Here is the output of a small sample of analyzed TCP/IP …

Many early startups that use a single server for all services or a single database server for their website talk about how they would like to achieve higher availability with MySQL. This is not possible without at least two database servers. Using MySQL replication you can then support higher availability in several varying capacities. An additional MySQL database server can satisfy several infrastructure needs including:

• A primary hot backup
• A datasource for performing backups
• Read scalability infrastructure
• A reporting server
• A benchmarking server
• A fail-over/fail back master environment

MySQL replication is very easy to configure and deploy, a task that takes < 30 minutes for an experienced MySQL DBA. However, altering your backup and recovery strategy, modifying your application to support read/write splitting or implementing a fail-over/fail back …

I’ve been playing around with some quick system automation scripts that are handy to use when you don’t want / need to setup a chef or puppet action. I like to keep all of my hostnames and login details in a MySQL database (a cmdb actually) but for this example we’ll just use a couple of nested lists. This script executes commands in parallel across the hosts you choose in the menu system via the “pdsh” command, so make sure you have that installed before running. Alternately you can change the command call to use ssh instead of pdsh for a serialized execution, but that’s not as fun or fast. With some customizations here and there you can expand this to operate parallelized jobs for simplifying daily work in database administration, usage reporting, log file parsing, or other system automation as you see fit. Here’s the code. Comments welcome as always!

#!/usr/bin/env python
## NAME: menu_parallel_execution.py
## DATE: …

If so then were is it installed on your publicly accessible website. If the location is where the documentation states not to put it, or in other popular locations then you can easily become open to an attack. The following are apache logs of a simple hacker test to find a potential security hole on a new IP public address for a client.

My advice is never have PHPMyAdmin accessible by default. You should use HTTP authentication, firewall rules and additional security practices all to protect any level of access to your data.

78.111.81.180 - - [02/Feb/2011:05:29:24 -0500] "GET //phpmyadmin/ HTTP/1.1" 401 290 "-" "Made by ZmEu @ WhiteHat Team - www.whitehat.ro"
78.111.81.180 - - [02/Feb/2011:05:29:24 -0500] "GET //phpMyAdmin/ HTTP/1.1" 401 290 "-" "Made by ZmEu @ WhiteHat Team - www.whitehat.ro"
78.111.81.180 - - [02/Feb/2011:05:29:24 -0500] "GET //pma/ HTTP/1.1" 401 290 "-" "Made by ZmEu @ WhiteHat Team - www.whitehat.ro"
78.111.81.180 - - …

While Oracle provides no official information they are planning on improving MySQL and using as a product to compete with Microsoft SQL Server, it is rather obvious from what little information you can glean from public announcements this is a clear business goal.

Microsoft however are publicly seeking a Senior Product Manager, MySQL Compete in the Marketing department. Your goal is nothing technical, it’s all PR to dispel MySQL as a viable product. I quote “you will equip field and partners to win in competitive engagements against MySQL, and you will influence market perception in favor of Microsoft technologies.” Here is the Full job description for those that want an amusing read.

This information came from an Oracle colleague of mine based in Asia.

I am pleased to announce that the Oracle ACE program has two new MySQL inductees. These people actively contribute to the MySQL community via a varied means in an unbiased and non commercial way.

Giuseppe Maxia (aka DataCharmer) is no stranger to the MySQL community having filled a position with MySQL Inc/Sun Microsystems/Oracle Corporation in the Community team for many years. Giuseppe was an active member in the community before this position, and continues to provide great input with tools such as the MySQL sandbox and many project and code snippets on the MySQL Forge as well as many writings for the Developer zone.

…

My recent account of The State of MySQL forks seems to have gotten quite a lot of attention. I promised to follow up with a separate piece about Drizzle and also PostgreSQL, as the other major open source database, so I'd better keep that promise now.

read more

Today I discovered that you are unable to compile the current stable PHP version 5.3.4 with yesterday’s MySQL 5.5.8 GA release. I was able to download the current MySQL 5.1.54 and compile without issue.

You can find all the gory details in Bug #58987 however I was able to edit a number of MySQL include file to get a build. Does this mean it’s a MySQL packaging problem or a PHP problem I don’t know, but I would hope that Oracle in the testing phase of a GA release test this against popular programming languages starting with the LAMP stack to ensure compatibility such as what I uncovered.

The follow-up blog post on moving your MySQL applications to CouchDB has been posted on the CouchOne blog. Part 2 digs into a bit more detail on the specifics of views, and how to perform some of the more common operations used in MySQL, such as paging and aggregation in your CouchDB view. You can read Part 2 here

The follow-up blog post on moving your MySQL applications to CouchDB has been posted on the CouchOne blog. Part 2 digs into a bit more detail on the specifics of views, and how to perform some of the more common operations used in MySQL, such as paging and aggregation in your CouchDB view.

You can read Part 2 here