This is not strictly database or MySQL related, however DBAs often deal with large data sets so others may find it useful.

I recently discovered bbcp, a utility to copy files efficiently over a network. It has the ability to use parallel network streams and specify window sizes, which can dramatically increase the transfer rates. It copies via the ssh protocol, so it fits nicely into common security environments.

Installation:

For RHEL, Solaris x86, and MacOS they provide binaries, but I chose to compile my own copy. This was as simple as make -f Makefile.amd (for the amd64 platform) and produces a single portable binary. I simply copied this file to /usr/local/bin for any host that I wish to transfer between.

Usage:

For the most normal case, usage is as simple as:
bbcp -s …

Topics for this podcast:

* Open source getting, and going without VC investment
* Oracle contributions to Linux and open source
* Sun’s latest moves with MySQL database and version 5.1
* Linux in high-end computing

iTunes or direct download (25:50, 6.0 MB)

Something very cool, is coming very soon, for both MySQL and Drizzle :)


mysql> SELECT gman_do("reverse", "does this work?") AS test;
+-----------------+
| test |
+-----------------+
| ?krow siht seod |
+-----------------+


This is part of the huge amounts of efforts we have going on with Drizzle and the wider set of projects that we are working on (and mad props go to Eric Day for the above example UDF work for MySQL).

One typical question which frequently pops up is whenever it is better to use hardware upgrade or optimize software more. I already wrote about it, for example here.

Today I'll look at the same topic from the consultants view. When consultant should suggest hardware upgrade and when it is not in a simple checklist form.

How good is hardware ? Sometimes people use so crappy hardware it would be much cheaper for them to upgrade before purchasing much of professional services. Though in some cases people like their system to be optimal and so they want to run it on some old box even if it costs them more to optimize it. It may be valid choice allowing to take a hardware boost later down the road when you need a major performance increase and do not …

A tip of the hat to Mark Callaghan, who suggested I post our my.cnf settings for iiBench.

Instead of fiddling around with the configuration file, we adjusted everything on the command line. Here's the relevant script from iiBench/scripts/start_mysql.sh:

#
# Script to start the mysqld process.
# - Documents mysqld parameters used for iiBench testing.
#
DATADIR="/usr/local/mysql/data"
MYSQL_PATH="/usr/local/mysql/bin"

# These parameters were selected for a test machine with:
# - 16GB of memory
# - 2 socket, quad core = 8 cores total.

PARAM=" \
--no-defaults \
--user=mysql  \
--datadir=$DATADIR \
--max_connections=3000 \
--max_connect_errors=10 \
--table_cache=2048 \
--max_allowed_packet=1M \
--binlog_cache_size=1M \
--max_heap_table_size=64M \
--sort_buffer_size=64M \
--read_buffer_size=64M \
--join_buffer_size=64M \ …

How would one concatenate strings from a column (multiple rows) into a single row using MySQL? I see its possible with MS SQL Server 2005 and above. Any incite into how to achieve this in MySQL would be much appreciated.

MS SQL Server 2005 - Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SELECT Web_Account_ID,
GroupNameConcat = REPLACE(
        (
        SELECT
                Web_Account_Group_Name_ID AS [DATA()]
        FROM
                tblWebAccountGroup WAG
        WHERE
                WAG.Web_Account_ID = WA.Web_Account_ID
        ORDER BY
                Web_Account_Group_Name_ID
            FOR XML PATH ('')
        ), ' ', ',')
FROM tblWebAccounts WA
ORDER BY Web_Account_ID

Query Results Example

Source: …

As open source vendors restrict features to their enterprise products, are the development-related benefits of the OSS business model retained? READ MORE

The latest report from DBMS guru Donald Feinberg on open source database growth. READ MORE

I’d like to advertise my previous iiBench posting again (now that we are feeding into PlanetMySQL.)

Part II in our series on MySQL picks up where we left off from talking about some of the challenges, and potential issues and problems that come up with MySQL replication after you have it setup and running for some time. DBJ: Fixing MySQL Replication

In this piece we talk about some of the ways to verify your setup, and make sure your slave does not drift out of sync with the master, and to alert you if it does.