return to OCLUG Web Site
A Django site.
June 13, 2011

Pythian
pythian
» Upgrade to MySQL 5.1.56 on Bacula server using 5.0.x and MyISAM tables

Hello there, it’s me again, with another blog about a DBA situation that a typical Linux Administrator may find themselves in.

In this blog, i’m going to review a recent MySQL upgrade I have done on one of the systems I am involved in administering. This is a real world example of an upgrade project, and hopefully when we’re done, there may even be an overall performance boost.

There are several reasons to perform upgrades (of any kind), for me an important one is to keep current for security and bug fixes, but general performance improvements and new features are always welcome as well.

This system is running Bacula, an open source enterprise backup system. In this particular case Bacula is configured to store data in a MySQL database. The data stored include status reports, backup content lists, lists of all the files on all the systems, schedules and other related information. While everything has been operating “generally fine” for several years, this also means several components of the system are quite out of date.

The system is using CentOS 5.3, and the versions of MySQL and Bacula that shipped with it (5.0.45 and 2.2.8 respectively) which are both quite old by todays standards. Officially MySQL 5.0.x is now completely out of support by Oracle/MySQL.com, so upgrading is quite important.

The OS definitely could/should be updated to CentOS 5.6 (current at the time of this writing), but that actually wouldn’t make a significant dent on the version of MySQL (or likely Bacula) itself. The OS upgrade, and the Bacula upgrade discussion i’ll leave to the appropriate System Administration team, i’ll just be reviewing what has been done with MySQL itself.

As the main application (Bacula) will not be upgraded – this means that any new features in MySQL 5.1.56 won’t really be used unless it’s something I can configure in the my.cnf file.

Initially my plan was to use the remi yum archives (http://rpms.famillecollet.com/) which I have seen used in several situations. The MySQL RPM’s in these yum repositories generally replace the RPM’s that are shipped in RHEL/CentOS, but are current GA versions. For non-DBA’s, I find these archives a really good way of moving to new releases without a lot of effort outside the regular system upgrading activities.

Unfortunately the server in question doesn’t have direct external Internet access. It only has access to some internal repositories, and access to/from other internal systems. I figured that if i’m going to have to copy RPM files manually anyways, I will use the official Oracle/mysql.com packages which are generally the preferred method (speaking as a DBA).

The overall plan was/is the following:

1) Schedule outage with all monitoring tools, and notify the appropriate people of a pending outage to the backup system
2) Confirm current backup is in place for the existing database and system files
3) Copy packages downloaded from mysql.com to a location accessible to the system in question (in my case an NFS mounted home directory)
4) Stop mysql and bacula-director

/etc/init.d/mysql stop
/etc/init.d/bacula-dir stop

5) remove mysql and mysql-server – ignoring the complaint about libmysqlclient dependancies

rpm -e –nodeps mysql mysql-server

6) install the packages downloaded from mysql.com

rpm -Uvh MySQL-server-community-5.1.56-1.rhel5 MySQL-shared-compat-5.1.56-1.rhel5 MySQL-client-community-5.1.56-1.rhel5 MySQL-devel-community-5.1.56-1.rhel5

7) upgrade the mysql database (using mysql_upgrade) access controls

/usr/sbin/mysqld –user=mysql –skip-grant &
mysql_upgrade
killall mysqld

8) restart mysql to confirm it starts up fine with no config changes.

/etc/init.d/mysqld start
tail -f /var/log/mysqld.log

9) update my.cnf to enable some new features (innodb plugin)

vi /etc/my.cnf

and add the following lines to the [mysqld] section

ignore_builtin_innodb
plugin-load=ha_innodb_plugin.so
innodb_file_per_table
innodb_file_format=Barracuda

10) restart mysql and confirm it has now loaded the innodb plugin, and is still operating fine.

/etc/init.d/mysql restart
tail /var/log/mysqld.log -n 20

you should see something like this:

InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
110505 16:38:33 InnoDB: Initializing buffer pool, size = 768.0M
110505 16:38:33 InnoDB: Completed initialization of buffer pool
110505 16:38:33 InnoDB: highest supported file format is Barracuda.
110505 16:38:33 InnoDB Plugin 1.0.15 started; log sequence number 18094312675
110505 16:38:33 [Note] Event Scheduler: Loaded 0 events
110505 16:38:33 [Note] /usr/sbin/mysqld: ready for connections.
Version: ’5.1.56-community-log’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3306 MySQL Community Server (GPL)

11) Finally, restart the Bacula director

/etc/init.d/bacula-dir start

Other than a stray config value present in the old my.cnf file that was now being interpreted differently (basedir) this part of the upgrade went smoothly.

When we reached this point I confirmed with the SysAdmin responsible for backups that Bacula was still able to perform it’s usual duties, and that the old version of Bacula seems to be fine with the new version of MySQL.

We paused at this stage for about a week, so the system could do a full run through of the weekly backups, and so we could see how well the system performs and if there is any real difference (so far).

Very little speed difference was expected at this point because the only new features enabled are for using the InnoDB storage engine which was not actually being used. It’s not actually being used yet as all the Bacula tables were still using the MyISAM storage engine which is the default engine for older versions of MySQL such as 5.0. This of course means the next major stage of the upgrade was to convert all the Bacula tables from MyISAM to InnoDB and I want to do this because of details i’ll mention below.

I found several references on the web to Bacula supporting the use of the InnoDB storage engine perfectly fine, so i’m not concerned about compatibility differences between the storage engine affecting actual functionality.

There are plenty of differences between InnoDB and MyISAM, but InnoDB has one big advantage over MyISAM that should affect Bacula – the locking method. MyISAM uses table locking, but InnoDB will do row level locking.

This means if you have parallel inserts and updates all going into the same table (like if you’re doing backups from multiple systems at the same time), MyISAM performing global locks could cause some problems – and indeed it has.

In this specific situation, we have seen locked threads on this database during the weekly backups Sunday at 4:00am, so that means to me that we could see a bit of a performance improvement, and a lot less locked threads.

Ok, so now I needed to change all the databases to InnoDB format. By far the easiest way to do this is to run a series of “ALTER TABLE {table} engine=innodb;” statements. As this system has definite times of the day when there is no activity, this is a safe option.

The main drawback of using the ALTER TABLE method to do the conversion is the disk space required. During the conversion, the old table and the new table will both exist at the same time.

The entire database is about 7GB in size and there is currently about 7GB of free space on the file system being used by MySQL. Normally this would be fine if there was a number of medium sized tables as only one table is duplicated at a time. Unfortunately this is not the case with Bacula – it has many small tables, and one really large table called ‘File’. This means converting ‘File’ may be an issue.

At this point I’m attempting to do the remainder of the upgrade without the help of the SysAdmin which means I no longer have access to ‘sudo’ or ‘root’ access. This means several of the typical options for distributing disk space (like soft linking db files to other file systems, etc..) are not really available to me.

I did eventually come up with a solution that worked due to the way Bacula operates and clears old data – I used ‘OPTIMIZE TABLE’. This actually cleared up a fairly significant amount of the disk space being used leaving me with 9GB free which is more than enough to do the conversion.

mysql> optimize table File;

Once the disk space issue was covered, I needed to generate the list of alter statements I want to execute for changing the engine. The following command was used to generate the list:

bash# mysql -uavail -p bacula -s -e ‘select “alter table “,table_name,” engine=innodb;” from information_schema.tables where tables.table_schema=”bacula”‘

The command generated this output which was then pasted into the mysql console:

alter table BaseFiles engine=innodb;
alter table CDImages engine=innodb;
alter table Client engine=innodb;
alter table Counters engine=innodb;
alter table Device engine=innodb;
alter table File engine=innodb;
alter table FileSet engine=innodb;
alter table Filename engine=innodb;
alter table Job engine=innodb;
alter table JobMedia engine=innodb;
alter table Location engine=innodb;
alter table LocationLog engine=innodb;
alter table Log engine=innodb;
alter table Media engine=innodb;
alter table MediaType engine=innodb;
alter table Path engine=innodb;
alter table Pool engine=innodb;
alter table Status engine=innodb;
alter table Storage engine=innodb;
alter table UnsavedFiles engine=innodb;
alter table Version engine=innodb;

I didn’t accurately measure the total time it took, but I would guess it took about an hour to convert everything. Most tables took less than a minute, except the ‘Filename’ table which took about 5 minutes, and the ‘File’ table which took all the rest.

We’ve now completed the major milestone for this upgrade – Bacula is now using the InnoDB storage plugin for all of its tables with the newer version of MySQL.

As suspected while working on this project, there was not a huge performance difference for the overall time it takes to perform the weekly backups, although there still is an improvement. The backups with a large number of files (+300,000) now complete faster than they previously did – approximately 20 minutes faster for a 7hr backup. This was likely purely due to the improvements in locking.

May 20, 2011

Pythian
pythian
» Using KateSQL to connect to an Oracle database in Kate

Among the features announced with the release of version 4.6 of the KDE Software Compilation is KateSQL, a SQL Query plugin for the Kate text editor providing the basic functionality of a SQL client. It leverages the Qt SQL module, allowing you to make a connection to most types of databases. Out of the box it includes support for MySQL and SQLite, and with this post I’ll show you how to get it to connect to Oracle databases as well.

Since I am an Ubuntu user (Note: you’ll need to be on 11.04 and either be running Kubuntu or have the ‘kate’ package installed) these instructions will be specific to that operating system, though they will likely also apply to other Debian-based distributions with minimal modification. However, if you are using an RPM-based distribution such as openSUSE or Fedora, you’ll need to adapt the instructions somewhat to make sure you have the necessary development packages installed.

The first thing we’ll need to do in order to get this to work is to obtain an Oracle client. This can be downloaded from the Oracle Technology Network and requires that you have a valid Oracle.com Single Sign-On account. For the purposes of this article we’re going to use the Linux x86 Oracle Instant Client and the files we’re going to download are:

oracle-instantclient11.2-basic-11.2.0.2.0.i386.rpm
oracle-instantclient11.2-devel-11.2.0.2.0.i386.rpm

The first package is the client itself, providing the files required to run OCI applications. The second is the Instant Client SDK which includes header files for developing Oracle applications with Instant Client: in our case an OCI library for the Qt SQL module.

Users of RPM-based distributions can just go ahead and install these packages while others will need to convert them to .deb format first. The best way to do this that I am aware of is with the tool called Alien. Let’s install it, convert the Oracle Instant Client packages, and install them too.

cd /path/to/oracle/instant/client/files
sudo apt-get install alien
sudo alien oracle-instantclient11.2-basic-11.2.0.2.0.i386.rpm
sudo alien oracle-instantclient11.2-devel-11.2.0.2.0.i386.rpm
sudo dpkg -i oracle-instantclient11.2-basic_11.2.0.2.0-2_i386.deb oracle-instantclient11.2-devel_11.2.0.2.0-2_i386.deb

The next step is to install all of the dependencies necessary to build the Qt SQL OCI library.

sudo apt-get build-dep qt4-x11
sudo apt-get install qt4-qmake libqt4-dev libaio1

Then we will need the actual source code for the qt4-x11 package. This can be obtained with the following command:

apt-get source qt4-x11

To recap, at this point we’ve downloaded and installed the Oracle Instant Client and all of the dependencies required to build the qt4-x11 package, and we’ve downloaded the qt4-x11 source code. Next we’re going to build the Qt SQL OCI library itself.

sudo ln -s /path/to/qt4-x11-4.7.0/src /usr/include/src
sudo cp -r /path/to/qt4-x11-4.7.0/include/QtSql/private /usr/include/qt4/QtSql/
cd /usr/include/src/plugins/sqldrivers/oci/
qmake "INCLUDEPATH+=/usr/include/oracle/11.2/client64" "LIBS+=-L/usr/lib/oracle/11.2/client64/lib -Wl,-rpath,/usr/lib/oracle/11.2/client64/lib -lclntsh -lnnz11" oci.pro
make
sudo make install

Now that the libqsqloci.so module has been built and installed, Kate should be able to connect to an Oracle database. Launch Kate (or close and re-open it if it was already running), click Settings -> Configure Kate, expand the Application tree if necessary, click Plugins, check ‘SQL Plugin‘ and click OK. Then click SQL -> Add connection to launch the connection wizard. In addition to the MySQL and SQLite database drivers, you should now also have drivers named QOCI and QOCI8 available. Choose the appropriate one, enter relevant connection details, and complete the connection wizard. You can now execute queries against the database and display result data.

April 7, 2011

Pythian
pythian
» MySQL Backup Concepts for (Linux) System Administrators – Part 1

Hello, My name is James McOrmond. I am a MySQL DBA (and part time SysAdmin) at the ‘The Pythian Group’ headquarters here in Ottawa.

In my previous employment I was the primary System Administrator for a local Linux OS software company (for over 8 years). Server backups were of course something I was doing as part of my job, but i’ve learned that the backups I was doing for the MySQL databases were probably not what a DBA would have set up.

In this blog post, I will cover 3 basic types of MySQL backups for stand-alone database systems. I will not be covering fancy GUI applications, or really complicated processes – just the basic concepts – which is what I think System Administrators (ie – non DBA’s) need to know to have a good/valid backup.

1) ‘mysqldump’

This is the most basic form of MySQL backup – and likely the one most system administrators have used in the past.

This program reads data from mysqld, and ‘dumps’ clear text to stdout which you can redirect to a file anywhere on the system, or directly to a file. The text output is actually a sequence of SQL “INSERT” commands which would be used to reload the data. Commonly I see the filenames containing portions of the current date, but you can do whatever you want based on your overall backup retention policies.

Various arguments can be given to mysqldump to include all databases on the system, just a subset of databases, or even limiting to specific tables in one database. If you’re looking for “quick and dirty” – the ‘–all-databases’ argument will get you a dump of all the databases on the system. If you have stored procedures or events, you’ll also want to make sure these are part of your dumps.

    mysqldump --all-databases | gzip - >/mnt/backups/mysqlbackup-`date +%Y-%m-%d_%H.%M.%S`.gz

If your databases are large, it’s probably advisable to at least export each database schema into a different file. This would allow you to have different retention schedules for each database. It also makes restoring a single database (or table) easier – if for example someone deletes a single table. This of course never happens in the ‘real world’ right? :-)

While using mysqldump is definitely the “easiest” way to perform a backup, it also has a significant issue that make many serious DBA’s shutter if these are the only backups available.

The main problem of course relates to making sure the data is consistent across all tables within the same backup. If you don’t, one table may reference entries in another table that may have been modified (or even no longer exists) between the start and end of the backup process.

The most convenient way to enforce this is by using the ‘–lock-all-tables’ argument. “Unfortunately” this does exactly what it’s supposed to do – it puts a read only lock all tables (in all databases). That means no other process can modify data in the database until the backup is done. If your database has mostly reads – or if you can put your application into a read only or maintenance mode, this may be fine, but otherwise you will probably eventually notice several write statements in locked state – waiting for the backup to finish.

If your backup takes hours, having a read lock on the data may be quite inconvenient, and you should investigate another backup method.

2) File System Snapshots

File System snapshots (as the name suggests), happen at the File System layer, not within the database itself. Depending on your environment, you may already be doing snapshots to backup other parts of your environment.

If you are using a SAN for your storage, snapshotting is likely a built in feature. If not – you can use LVM snapshots at the OS level, as long as you have created the necessary logical volumes of the correct areas of your system.

As with mysqldump, you need to make sure the data is consistent in your backup, or else it may not be very useful to you when you try to do a restore, at 3am on a Saturday morning, with a customer on the phone ‘asking’ you when everything will be working again :-)

There are two kinds of flows typical with this type of backup:

   perform a 'FLUSH TABLES"
   perform a 'FLUSH TABLES WITH READ LOCK"
   create the snapshot
   perform an 'UNLOCK TABLES'

At this point, you’re safe to mount the snapshot to a new location, and then copy the database files somewhere else – possibly using whatever method you’re using to backup the rest of the system (tar, rsync or a tool like bacula).

The interruption to the active database is fairly minimal here – only between the “FLUSH TABLES WITH READ LOCK”, and the “UNLOCK TABLES” commands. Performing the first “FLUSH TABLES” ahead of time makes sure the interruption is minimal.

Unfortunately, while this sounds like the perfect solution, some storage engines like InnoDB may essentially ignore the “FLUSH TABLES WITH READ LOCK”, so data may not be perfectly consistent on disk when the snapshot happens. It will be closer than just doing a mysqldump, but still not perfect.

All is not lost, as mysqld will actually do a “crash recovery” based on the various log files available to clean up the database as part of the startup process.

The best and easiest way to be absolutely sure the data is consistent for the snapshot, is to actually shut down the mysqld process entirely while the snapshot is being performed.

   perform a 'FLUSH TABLES"
   shutdown the database
   create the snapshot
   restart the database

The outage in this case will be complete – no reads or writes will be possible at all. The duration of the outage will be quite short so this outage may be acceptable. It’s entirely possible it will be less than a minute from the database shutdown, to the restart.

Your application should still be put into a maintenance mode if possible, but it should be scheduled during an off time, and you may decide it is short enough that end users won’t notice.

3) xtrabackup

xtrabackup is an open source tool available from Percona. Among other things, it is capable of performing backups of InnoDB databases – while the database is still running.

Percona provides yum/apt repositories for Red Hat/CentOS/Fedora, Debian and Ubuntu, as well a generic .tar.gz for everyone else. You should be able to get it installed on your chosen distribution fairly easily. The package will contain many different files most notably the xtrabackup binary itself, as well as the innobackupex-1.5.1 wrapper script.

This innobackupex wrapper script will save you a lot of time when you’re trying to get your backups done. In it’s simplest mode, it will take a single argument – the backup destination directory, and then it will do a fairly complete backup into that directory. If your my.cnf is not in /etc/ then you should use the –defaults-file argument.

Typically you’ll want these backups run through a program like tar, so the –stream option is your friend. This also allows you to compress the backups using a program like gzip, which is of course always a good idea.

   innobackupex-1.5.1 --stream=tar /mnt/backups | gzip - > mysqlbackup.`date +%Y-%m-%d_%H.%M.%S`.tar.gz

To really have the backup at the fully consistent level we want, xtrabackup needs to be done in multiple stages. The first stage simply does the backup into a directory (which it will name based on the date), and the second one re-runs innobackupex with the –apply-log argument so it applies the updates. If you wish, you can also then create a compressed tarball of this backup directory.

These additional stages however don’t need to be done at the time of your backups, they can be done at the time of the restore which is likely far less often. It all depends on when you want your system spending the CPU cycles.

Final Thoughts

This blog is by no means a fully exhaustive list of the tools available, but they essentially cover one example of the 3 basic types – logical, cold and hot.

It is entirely valid and common to use more than one backup type.  Often I have seen a logical backup along with one of the other two backup types.

Care should be taken with the security of your backups of course, and please remember that if you specific user name and password on the command line, that they can be viewed by any user logged into the system that can run commands like “ps” or “top”.

If you are using multiple servers in a master/slave configuration, command line arguments also exist in the different tools for recording relevant binlog positions and those definitely should be used.

…But that is a topic for another time.

Some relevant links

mysqldump documentation on mysql.com – http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html
Linux LVM HOWTO – http://www.tldp.org/HOWTO/LVM-HOWTO/
Percona Xtrabackup – http://www.percona.com/docs/wiki/percona-xtrabackup:start

June 18, 2010

Pythian
pythian
» Keeping Up

I found I never published this post as it was sitting in my drafts few months now — it was written in 13th February, 2010. I’m publishing it without any changes. I learn therefore I am! I’ve just wrote few bits about learning a new technology and after skimming through my Google Reader, I noticed [...]

May 13, 2010

Pythian
pythian
» An SSH tool to make your life easier

A MySQL user group member saw that I use Poderosa as my ssh-on-Windows tool, and asked why I did not use PuTTY. My response was that I like having tabbed windows and hate having to keep opening another PuTTY program every time I want to open another connection. With Poderosa I can open a new [...]

May 7, 2010

Pythian
pythian
» Liveblogging: Seeking Senior and Beyond

I am attending the Professional IT Community Conference – it is put on by the League of Professional System Administrators (LOPSA), and is a 2-day community conference. There are technical and “soft” topics — the audience is system administrators. While technical topics such as Essential IPv6 for Linux Administrators are not essential for my job, many of the “soft” topics are directly applicable and relevant to DBAs too. (I am speaking on How to Stop Hating MySQL tomorrow.)

So I am in Seeking Senior and Beyond: The Tech Skills That Get You Promoted. The first part talks about the definition of what it means to be senior, and it completely relates to DBA work:
works and plays well with other
understands “ability”
leads by example
lives to share knowledge
understands “Service”
thoughtful of the consequences of their actions
understands projects
cool under pressure

Good Qualities:
confident
empathetic
humane
personal
forthright
respectful
thorough

Bad Qualities:
disrespective
insensitive
incompetent
[my own addition - no follow through, lack of attention to detail]

The Dice/Monster Factor – what do job sites see as important for a senior position?

They back up the SAGE 5-year experience requirement
Ability to code in newer languages (Ruby/Python) is more prevalent (perhaps cloud-induced?)

The cloud allows sysadmin tasks to be done by anyone…..so developers can do sysadmin work, and you end up seeing schizophrenic job descriptions such as

About the 5-year requirement:
- Senior after 5 years? What happens after 10 years?
- Most electricians, by comparison, haven’t even completed an *apprenticeship* in 5 years.

Senior Administrators Code
- not just 20-line shell scripts
- coding skills are part of a sysadmin skill
- ability to code competently *is* a factor that separates juniors from seniors
- hiring managers expect senior admins to be competent coders.

If you are not a coder
- pick a language, any language
- do not listen to fans, find one that fits how you think, they all work…..
- …that being said, some languages are more practical than others (ie, .NET probably is not the best language to learn if you are a Unix sysadmin).

Popular admin languages:
- Perl: classic admin scripting language. Learn at least the basics, because you will see it in any environment that has been around for more than 5 years.

- Ruby: object-oriented language for people who mostly like Perl (except for its OO implementation)

- Python: object-oriented language for people who mostly hate Perl, objects or no objects. For example, you don’t have to create a String object to send an output.

But what if you do not have time to learn how to program?

- senior admins are better at managing their time than junior admins, so perhaps managing time
- time management means you’ll have more time to do things, it doesn’t mean all work work work.
- Read Time Management for System Administrators – there is Google Video of a presentation by the author, Tom Limoncelli.

Consider “The Cloud”
- starting to use developer APIs to perform sysadmin tasks, so learning programming is good.
- still growing, could supplant large portions of datacenter real estate
- a coder with sysadmin knowledge: Good
- a sysadmin with coding knowledge: Good
- a coder without sysadmin knowledge: OK
- a sysadmin with no coding interest/experience: Tough place to be in

Senior Admins Have Problems Too
Many don’t document or share knowledge
Maany don’t do a good job keeping up with their craft
Cannot always be highlighted as an example of how to deal with clients
Often reinvent the wheel – also usually there is no repository
Often don’t progress beyond the “senior admin” role

….on the other hand…..
cynicism can be good…..

Advice:
learn from the good traits
observe how others respond to their bad traits
think about how you might improve upon that
strive to work and play well with others, even if you don’t have a mentor for good/bad examples.

Now he’s going into talking about Patterns in System Administration….

June 25, 2009

Pythian
pythian
» Scalable Internet Architectures

My old friend and collaborator Theo Schlossnagle at OmniTI posted his slides from his Scalable Internet Architectures talk at VelocityConf 2009.

The slides are brilliant even without seeing Theo talk and I highly recommend the time it takes to flip through them, for anyone who is interested in systems performance. If anyone took an mp3 of this talk I’m dying to hear it, please let me know.

For those of you unfamiliar with OmniTI, Theo is the CEO of this rather remarkable company specializing in Internet-scale architecture consulting. They generalize on Internet-scale architecture, not on one specific dimension the way Pythian specializes on the database tier. This allows them to see Internet-scale workloads from a unique systemic, multidisciplinary point of view; from the user experience all the way up the stack, through the load balancer (or not), the front-end cache, the application server, the database server, the operating system, the storage, and so on. This approach lets them build Internet architectures and solve scalability problems in a unique and powerful, wholistic way.

Pythian first collaborated with OmniTI in 2001, and they deserve all of their success and profile that they’ve built since then. Trivia: both Pythian and OmniTI were founded in September 1997 and both companies continue to be majority-owned and controlled by founders (in Pythian’s case, yours truly).

Here’s the slide deck. Let me know your thoughts.

March 17, 2009

Pythian
pythian
» How to Have a Good Presentation

In about 15 minutes, Giuseppe Maxia will begin a webinar in which the main focus is a presentation on “How to have a good presentation”. Talk about meta!

Giuseppe posted how to join the free webinar.

The slides can be found at http://datacharmer.org/downloads/2009_03_Presentation.pdf.

December 19, 2008

Pythian
pythian
» Log Buffer #128: a Carnival of the Vanities for DBAs

Welcome to the 128th edition of Log Buffer, the weekly review of database blogs.

Let’s begin with some PostgreSQL blogs. Jignesh Shah shares his recipe for making a PostgreSQL 8.3 appliance based on OpenSolaris using VirtualBox. While we’re on appliances, Dave Page shows off PostgreSQL management on the iPhone with an application he himself wrote. Stealth DBA for the bus-rise home.

On Database Soup, Josh Berkus has been finding useless indexes. He begins, “I’d say, in general, that you can’t have really well-chosen indexes without the help of a performance expert. However, anyone can improve their use of indexes in PostgreSQL fairly easily using a few system tools … and a little improvement is all that a lot of users need.” And it’s what Josh offers.

Sometimes a DBA is asked to make something real purty-like, contrary to his or her nature though that may be. On the Postgres OnLine Journal, Leo Hsu and Regina Obe offer some help with the first of a series on Fusion charts and PostgreSQL. (”Fusion Charts . . . is a flash-based charting product that makes beautiful flash charts.”)

And now—hey what’s MySQL maven Baron Schwartz doing with a Postgres post on xaprb? He’s asking, what are your favorite PostgreSQL performance resources?

Maybe he’s considering crossing the floor out of weariness with all the contention in the MySQL world? Can’t say I blame him. Lately, the conversation in the MySQL ’sphere has been dominated by non-technical talk of the pluses and minuses of 5.1, of forking, community vs. enterprise, and so on. This week was no exception.

The week began with Jay Pipes’s advice to MySQL: “Drop the current roadmap . . . Forget Windows for now . . . Clean up the abysmal messiness of the code base . . .” It’s strong stuff and worth a read.

Lukas Kahwe Smith followed with his advice to the database division at Sun, purveyor or patron now of MySQL, Drizzle, and PostgreSQL.

Jeremy Zawodny surveyed this new MySQL landscape, full as it now is of patches, forks, and Drizzle, and liked what he saw.

Speaking of which, the MySQL Performance Blog announced the Percona XtraDB Storage Engine: a drop-in replacement for standard InnoDB.

Ronald Bradford got some unexpected results while looking into the size of memory tables. Can you help Ronald out?

On High Availability MySQL, Mark Callaghan showed us how to make MySQL faster in one hour. Nice stuff. And real purty charts, too.

Let’s see what happened in SQL Server now. Kalen Delany opined that there is no such thing as a SQL Server, and she’s not the only one with an opinion on this (one would think) straight-forward matter.

Lichi Shea asserted that there is a limit to set-based solutions: “After all, some procedural solutions are not so bad!  . . .  Now, it’s time for me to dodge the set-based solution crowd.”

Lots of thoughtful comment on that one, and a blog response from Ward Pond, who says that Linchi Shea makes an interesting point about hints, vis-a-vis the set-based and procedural paradigms.

The Data Management Journal looked into extracting numbers with SQL Server: “We all have perfectly normalized tables, with perfectly scrubbed data, right? I wish! Sometimes we are stuck with dirty data in legacy applications. What’s worse is that we are sometimes expected to do interesting things with dirty data. In this blog, I will show you how to extract a number from a varchar column that contains letters and numbers.”

TJay Belt published his reflection, Cloud Computing and me. Like many DBAs, TJay has some thoughts on how the advent of “The Cloud” is going to affect databases and database administration.

Moving into things Oracle, Chen Shapira was thinking about a musical analogy for the DBA. Their not “rockstar programmers” or “jazz programmers”, says Chen. But I won’t give away her conclusion—click on.

Chet Justice, the Oracle Nerd, was pursuing ontology too in the second part of his Application Developers vs. Database Developers. (I wonder if it’s generally true that apps developers have such terrible manners.)

Gary Myers responded, “Oraclenerd has opened that can of worms about OO, ORMs and Databases,” in his item, The Certainty Bottleneck (and ORMs).

On An Expert’s Guide to Database Solutions, James Koopman suggested, maybe it’s time to extend the DBA’s realm of influence, using tools like Spotlight on Oracle.

Or perhaps with other tools, such as TOra? Here on the Pythian Blog, Brad Hudson posted his howto, Installing TOra with Oracle support on Ubuntu 8.04LTS (Hardy Heron).

One last important bit of news from this week—Thomas LaRock, AKA SQLBatman, is America’s Most Exciting DBA.

Until next time, Happy Holidays to all our readers!

August 1, 2008

Pythian
pythian
» Please join us! Pythian Europe Launch Event in Prague on Wednesday

Invitation - Pythian Europe Launch Party

I’m pleased to announce that there will be the formal launch of Pythian Europe at the premises of the Canadian Embassy in Prague on Wednesday the 6th of August from 17:00 to 18:30. This historic event will be announced by Mrs. Sameena Qureshi, Trade Counsellor, Embassy of Canada; and Paul Vallée, President and Founder, The Pythian Group. Present will be various members from the press (IT and Business), as well as representatives from Oracle and Sun Microsystems, the Canadian Chamber of Commerce in Prague, and many more. We will prepare some unusual and very tasty snacks and refreshments.

We would love for readers of this blog to join us, so please consider this your special, personal invitation from me. Please come if you’re in Prague on Wednesday. If you plan to attend, please contact Dan at elbl@pythian.com.

» Next week, meet me in Frankfurt, Stuttgart, Munich or Prague!

I am traveling to Europe next week to brief major prospects in Germany (Daimler, MAN) as well as to attend to administrative matters at Pythian Europe in Prague and would love to meet any readers of this blog during this trip!

I’m especially interested in meeting:

  • DBAs, Applications Administrators and Systems Administrators,
  • Potential customers (IT Directors, DBA Managers, Supply Managers for IT), and
  • Potential partners (IT product of service companies that could partner with Pythian to delight our mutual customers)

Here is my itinerary:

  • Sunday, August: Frankfurt,
  • Monday, August 4: Stuttgart,
  • Tuesday, August 5: Munich, and
  • Wednesday, August 6 through Saturday, August 9: Prague, Czech Republic.

Please reach out to me using vallee@pythian.com if you would like to meet!

January 8, 2008

Pythian
pythian
» How to advocate for good backups! Or, how NOT to advocate for good backups!

I try to do a decent job of advocating for caring about good backups and business continuity strategies in my 7 Deadly Habits article.

But this one beats them all:

Grave Warning

Just too funny and great not to share. Found via this reddit article, where there is a lively discussion underway.

November 21, 2007

Pythian
pythian
» How to find out the machine ID on various UNIXes

It recently came up that it would be helpful if we had a cheat sheet to find out the machine names for any given UNIX. I knew these off the top of my head but it would be great if people added more as comments.

HP/HP-UX: /bin/uname -i
IBM/AIX: /bin/uname -m
SGI/IRIX: /sbin/sysinfo -s
Sun/Solaris: /usr/ucb/hostid

These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Slashdot
  • Google
  • del.icio.us
  • Facebook
  • bodytext
  • Technorati
  • TwitThis
  • Reddit
  • Spurl
  • De.lirio.us
  • Furl
  • blogmarks
  • Ma.gnolia
  • E-mail this story to a friend!