One of the most common usage of partitioning is where one wants to partition
by date. One might want to have one partition per year or per month or per
week or per day. This blog entry shows how to handle this requirement using
MySQL 5.1.

The most common method to partition in this case is by range.
Partitioning in 5.1 uses a function on one or more fields of the table. In 5.1
there are some requirements on these fields if unique indexes or primary keys
also exist in the table. The reason is that 5.1 doesn't have support for global
indexes. Development of support for this have however started so should be in
some future release of MySQL.

In 5.1 functions have to return an integer. There are two functions that has
special support in the MySQL server. These are TO_DAYS() and YEAR(). Both
of these functions take a DATE or DATETIME argument and return …

I noticed this piece from Johan Andersson on Writing NDBAPI programs?connecting to MySQL Cluster last week, which shows you how to use the NDBAPI?the programming interface to the MySQL Cluster system. By coincidence, we enabled the NDBAPI documentation today. It consists of two elements:

read more

Update: MySQL 4 and 5 behave differently.
In MySQL5 there is a steady activity on some handlers, probably caused by the status queries themselves. This does not happen in 4.1. I have issued a support call with MySQL and will see what to do about it.

Update 2: See the follow-up post for more information on how to work around this.

MySQL Administrator is one of the graphical tools MySQL provide to manage their database servers. Apart from other things like server daemon control and a log file viewer this tool includes visual controls to display the load of the database server.

Even though the out-of-the-box configuration already contains some useful diagrams, I added some …

As I wrote earlier in Using the MySQL Test Suite I found an issue with using the current MySQL Sakila Sample Database as a test with mysqltest.

I was running an older version of 5.1.7 beta so I figured the best course of action was to upgrade to 5.1.11 beta.

Well the problem still exists, and I found that the cause was due to the syntax of the DELIMITER command. Assuming the creation of the schema tables, here is an example of what I found.

Running in an interactive mysql session the following works

DROP TRIGGER ins_film;
DELIMITER ;;
CREATE TRIGGER `ins_film` AFTER INSERT ON `film` FOR EACH ROW BEGIN
    INSERT INTO film_text (film_id, title, description)
        VALUES (new.film_id, new.title, new.description);
  END;;
DELIMITER ;

On a side note, why does DROP TRIGGER not …

Paul has released Version 0.95 of his PBXT MySQL Transactional Storage Engine.

Here is what I did to get it operational under CentOS 4.3.

su -
useradd pbxt
su - pbxt
wget http://www.primebase.com/xt/download/mysql-4.1.16-pbxt-0.9.5.tar.gz
tar xvfz mysql-4.1.16-pbxt-0.9.5.tar.gz
cd mysql-4.1.16-pbxt-0.9.5
./configure --with-debug=full --with-pbxt-storage-engine --without-query-cache --with-mysqld-user=pbxt  --prefix=/home/pbxt/mysql
make
make install
scripts/mysql_install_db
cd /home/pbxt/mysql
./bin/mysqld_safe --user=pbxt --basedir=/home/pbxt/mysql &

Now, lets test and confirm PBXT is operational.

bin/mysql -uroot mysql
Reading table information for completion of table and column names
You can turn off this …

As Sun continues to debate just how they will open-source Java and deal with things like forking and perceived loss of control they would be well served to take a look at how successful open source projects are managed and developed. In the majority of cases there is a company or organization that acts as the Shepard of the project and manages the development roadmap. Case in point: the MySQL database is managed by MySQL AB, Apache is managed by the ASF, and Spring is guided by Interface 21. There are exceptions where the community takes over either and self-governs (like PHP) or creates fissures that result into multiple iterations of the base code (like Linux) which likely …

I've written a lot recently about MySQL index and table structure, primary keys, surrogate keys, and related optimizations. In this article I'll explain how MySQL's index structures enable an extremely important query optimization, and how that differs between storage engines. I'll also show you how to know and predict when the optimization is triggered, how to design tables and queries so it'll be used, and how to avoid defeating it with poor practices. Plus, I'll peek a bit into InnoDB internals to show you what's going on behind the scenes.

MySQL provides two different tools to test the MySQL Server with SQL statements. One is mysqltest and in 5.1 mysqlslap. Both of these tools have quite different purposes. This is a quick review of the usage of mysqltest.

Current Usage

Under Linux deploys, the README in the mysql-test directory gives you all the information you need to know to run.

To run the full test suite.

cd /opt/mysql/mysql-test
 ./mysql-test-run

Rather easy, it does take some time, and I was surprised to find a number of tests marked as ’skipped’. The general purpose of having tests in a product is to provide coverage of software functionality, and tests should always be forward …

Our company, RightMedia is in the process of evaluating a storage subsystem for our ever growing MySQL databases. It seems that our data is growing at an alarming rate (this is a good thing) and our internal SCSI disks aren't handling the load of massive inserts, plus large queries. So the time is now to evaluate a storage subsystem that will grow with us and scale for our performance needs.

I was really reluctant when I first heard iSCSI proposed as a potential solution. I am familiar with large storage subsystems all hooked in through fibre channel. It's an expensive solution, but performance is great. There are a few iSCSI vendors, and we had lots of problems with kernel version & driver version. But, we perservered, and have systems where we can benchmark the performance of iSCSI.

Now before I get to the actual numbers, let me give you the environment for the …

Interview with Mark Spencer:
http://www.asteriskvoipnews.com/asterisk_news/new_mark_spencer_interview_creator_of_asterisk.html

Mark Spencer is the author of Asterisk, the open source PBX system,
and is the primary owner of the company Digium which commercializes
it. Asterisk has the real potential to have a long term ability to
commoditize much of the telecom industry. Used Skype Out? Then your
call was probably routed via Asterisk by one of Skype's partners.

If you are an employee at MySQL then you have used Asterisk since it is our PBX system.

Catch the last question to Mark and his answer:
Question: Why are you planing to release a new stable version every 6
month? Many people have the feeling that a PBX software should …