Categories
Software

Database Power Savings

I think this is a pretty amusing tidbit. A few weeks ago I noticed a MySQL database taking a little more CPU than expected. Nothing dramatic. This is a small VM on a host running several and as the graph shows it doesn’t even break 25% capacity.

After about an hour I was able to reduce the CPU consumption considerably by updating, making sure indexes were optimal etc. etc. Nothing incredibly dramatic. Hardly a herculean task.

Now a week later I can clearly see the power consumption dropped a little bit on this particular host as a result of my optimization work.

Just goes to show, even if you run stuff in house and have extra computational power handy, it can add up.

1W of electricity @ $0.17 running 24 x 7 x 365 = $1.50/year. Literally a measurable improvement.

Categories
Open Source Software

MySQL To Percona Server Gottcha

Decided to replace the aging MySQL 5.1.x on a CentOS Box with a newer Percona Server 5.6. First step was to update MySQL 5.1 to 5.5. This went relatively smoothly after I figured out some mySQL transaction kung-fu and ran mysql_upgrade. Step two was to replace it with Percona Server. It installed fine. Almost to simple. So naturally I ran:

etc/init.d/mysql start

which resulted in a dreaded:

Starting MySQL (Percona Server).... ERROR! The server quit without updating PID file (/var/lib/mysql/SERVERNAME.pid)

After a few minutes of pouring through the logs I noticed this little nugget:

2015-04-25 19:18:16 18234 [ERROR] /usr/sbin/mysqld: unknown variable 'table_cache=7K'

Apparently around MySQL 5.1.3 they replaced table_cache with table_open_cache. A simple rename in my.ini, and we’re on our way. Now running a little faster thanks to some much newer DB binaries.

Categories
Open Source

MySQL Going Closed Source

I was a little concerned when MySQL AB was purchased by Sun, however Sun had a pretty long history with open source and mostly seemed to know how to balance things. When Sun was purchased by Oracle, I was quite uneasy knowing Oracle has a way of killing everything that isn’t Oracle. Seems like that time has finally come. Thankfully there are some forks of MySQL already. It might be time to start reading up and making sense of them.

Categories
Open Source Programming

DBSlayer + Node.js

Lately it seems the rage among developers is to take node.js and combine it with something else unusual. So here’s my contribution.

DBSlayer is a project by NYTimes a few years ago that seems to be somewhat forgotten but is pretty cool. It’s another MySQL proxy, but with a slight twist. Rather than use a binary protocol, or XML, they went with JSON. It supports things like connection pooling, round-robin distribution to slaves, automatic fallover, and it’s mutithreaded. It’s pretty fast and easy to work with. It’s almost like turning a MySQL database into a REST API. You pass a SQL query as a query argument and it gives you a JSON response.

Once you start it you can do something query using a request like:

http://localhost:9090/db?%7B%22SQL%22%3A%22SELECT%20*%20FROM%20facts%20WHERE%20id%3D1%3B%22%7D%20

That will give you a JSON object containing the result of your query.

So doing that in node.js is roughly:

var sql = ‘{"SQL":"SELECT * FROM facts WHERE id=7;"}’;
 
var http = require(‘http’);
var client = http.createClient(9090, ‘localhost’);
 
var request = client.request(‘GET’, ‘/db?’ + escape(sql), {});
request.end();
request.on(‘response’, function (response) {
  console.log(‘STATUS: ‘ + response.statusCode);
  console.log(‘HEADERS: ‘ + JSON.stringify(response.headers));
  response.setEncoding(‘utf8’);
  response.on(‘data’, function (chunk) {
    console.log(‘BODY: ‘ + chunk);
  });
});

Running that looks like this:

$ node test.js
STATUS: 200
HEADERS: {"date":"Fri, 27 May 2011 02:02:27 GMT","server":"dbslayer/beta-12","content-type":"text/plain; charset=utf-8","content-length":"290","connection":"close"}
BODY: {"RESULT" : {"HEADER" : ["id" , "fact" , "author" , "ip" , "timestamp"] , "ROWS" : [[7 , "1+1=2" , "raccettura" , "127.0.0.1" , 123456]] , "TYPES" : ["MYSQL_TYPE_LONG" , "MYSQL_TYPE_VAR_STRING" , "MYSQL_TYPE_VAR_STRING" , "MYSQL_TYPE_VAR_STRING" , "MYSQL_TYPE_LONGLONG"]} , "SERVER" : "db"}

You could obviously clean that up and create a little library to hide the HTTP parts.

It’s an interesting JS centric way to abstract your database while maintaining SQL level control. JSON is becoming the new XML.

Categories
Open Source

MySQL Staying Open

Sun was initially thinking of a commercial fork for MySQL with some enhanced things like encryption and compression backup for commercial users. Obviously this created some outcry. It appears they’ve now reconsidered and those features will be open source. To quote Kaj Arnö:

…expect Sun/MySQL to continue experimenting with the business model, and with what’s offered for the community and what’s offered commercial-only. We won’t always know the right answer from the beginning, but we want MySQL to be the most popular database for both paying and non-paying users.

The willingness to listen to community feedback, and look for a balance means Sun may not prove to be a bad thing for MySQL, of course time is the ultimate test. More than once a product has been written off after an acquisition only blossomed, or has failed when success seemed certain.

Balancing open source in business is no easy matter, both from producing and from consuming. It forces many people into new rolls, developers, visionaries into lawyers, and lawyers into tech savvy computer elitists. There’s no standard model for everyone to follow as every project and every company is unique. Striking a balance in such a dynamic and evolving environment is tough, when there’s no simple formula to help model business plans, it’s even more complicated.

Given open source adoption in the enterprise is on the rise, and corporate backing of open source seems to be following that, I suspect there will be some innovation in this field in the next few years as some of the more clever individuals find new ways to strike that magic balance.

Categories
Google Mozilla Open Source

Summer Of Code 2008

Google announced the project lists for Summer Of Code 2008. Some of the more interesting projects from my perspective:

Adium

Dojo Foundation

FFmpeg

Gallery

Inkscape

Joomla!

The Mozilla Project

MySQL

PHP

Pidgin

WebKit

WordPress

Categories
Programming

Getting A Random Row In MySQL

This is a great page on RAND() in MySQL. Very nice breakdown of how to gain performance with one of the biggest “it should be simple but it isn’t” problems in DBMS’s.

Categories
Software Web Development

MySQL bustage

So my dev instance of MySQL crashed with some InnoDB corruption (as in service won’t even start). Still no idea exactly what caused it, but I don’t really care. The only database using InnoDB wasn’t anything I actually used, just junk that should have been cleaned up anyway. The downside is that I couldn’t even get MySQL running. So here was my repair technique once I realized there wasn’t an ‘easy button’:

First always have a backup of your data. Just in case you screw up your repair attempt.

I deleted the directory containing the tables for the database I knew to be fubar‘d. And restarted MySQL not as a service but as a app using:

mysqld-nt.exe --verbose

Yes! It’s alive. Kinda. Still giving some errors, due to it’s inability to find tables (due to me deleting the corrupt database), and other related garbage. I don’t really care, I just want to fix the ones I do care about.

So next I make a dump of the entire server:

mysqldump --all-databases -u root -p > database.sql

Enter root password at the prompt. Sit back, relax, and hope things go well. It’s slow.

Now I decided to completely uninstall MySQL (and delete the data directory) and reinstall the latest version. Why not get the latest patched version? Lean, mean and up to date.

Now to get my data back

mysql --u root -p < database.sql

Again enter root password, sit back and relax. A few minutes later the process is done. Restart MySQL and all looks good.

This isn’t the only way around things, and may not even be the best, but it’s worked for me.

Categories
Web Development

PHP 5.0 is Released!

The PHP development team is proud to announce the official release of PHP 5.

Some of the key features of PHP 5 include:

  • The Zend Engine II with a new object model and dozens of new features.
  • XML support has been completely redone in PHP 5, all extensions are now focused around the excellent libxml2 library (http://www.xmlsoft.org/).
  • A new SimpleXML extension for easily accessing and manipulating XML as PHP objects. It can also interface with the DOM extension and vice-versa.
  • A brand new built-in SOAP extension for interoperability with Web Services.
  • A new MySQL extension named MySQLi for developers using MySQL 4.1 and later. This new extension includes an object-oriented interface in addition to a traditional interface; as well as support for many of MySQL’s new features, such as prepared statements.
  • SQLite has been bundled with PHP. For more information on SQLite, please visit their website (http://www.sqlite.org/).
  • Streams have been greatly improved, including the ability to access low-level socket operations on streams.
  • And lots more…

Enjoy!
PHP Development Team

Sweet, I’ll have to update Bender this weekend so I can toy around with it.

Categories
Accettura Media

Blog finally up and running

Due to a server move at the end of April/early May, the blog was broke due to an inconsistency between the version of Berkley DB on the old and new host. Now running on mySQL, it’s back up and running!