My mind is playing "Suffering Succotash..."

I have been working on MySQL performance for a while now, and the team I am in have discovered that SysBench could do with a couple of tweaks for Solaris.

Sidebar - sysbench is a simple "OLTP" benchmark which can test multiple databases, including MySQL. Find out all about it here , but go to the download page to get the latest version.

To simulate multiple users sending requests to a database, sysbench uses multiple threads. This leads to two issues we have identified with SysBench on Solaris, namely:

• The implementation of random() is explicitly identified as unsafe in multi-threaded applications on Solaris. My team has found this is a real issue, with occasional core-dumps happening to our …

Like a few others I’ve seen this week, I had two proposals for the MySQL Conference & Expo 2009 accepted. I’m very excited for both topics (for different reasons), and will be blogging about each of them in more detail soon. They are:

• libdrizzle: A new client library for Drizzle and MySQL - This will be a detailed introduction of the new library, covering important topics like client-side buffering, non-blocking I/O, and concurrent queries. If time permits I’ll also go over features in the new Drizzle protocol.
• Map/Reduce and Queues for MySQL using Gearman - I’ll be co-presenting this one with Brian Aker, and we will show how the work we’ve been doing with Gearman can be tied into MySQL (and Drizzle).

I spent this past week down in San Jose, CA at my employer’s office for team meetings and to officially kick-off my next big project. The design and architecture was very well received, and I drummed up some excitement with Gearman and working with the OSS community in general (which we’ve not done too much of in the past). We’ll be developing it entirely on Launchpad under GPLv2, and I’ll be writing a number of blog posts covering each component in detail. Why would anyone else find this interesting? It covers many topics of how to write a high-performance application in the cloud. Specific topics will include Gearman, persistent Gearman queues, eventual consistency data models (and related schemas), lightweight Map/Reduce for real-time applications, and how to combine all this with …

I was doing some database sizing in MySQL 5.1.30 GA for memory tables. Generally I have used INFORMATION_SCHEMA.TABLES data_length,index_length as a reasonable guide.

However working with a MEMORY table, after deleting rows, the size did not decrease as expected. I deleted 10% of rows, and saw 0% reduction. This was confirmed by doing a subsequent ALTER where I saw the 10% reduction in memory size.

It requires more investigation, however I found these results unexpected and worthy of publishing.

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.30    |
+-----------+

+-----------------+--------+------------+------------+----------------+-------------+-------------+------------+
| table_name      | engine | row_format | table_rows | avg_row_length | total_mb    | data_mb     | index_mb   |
+-----------------+--------+------------+------------+----------------+-------------+-------------+------------+
| …

FSF sues Cisco for GPL violations. JasperSoft and Lucid Imagination raise funding. Sun updates OpenSolaris. WaveMaker launches IDE for the cloud. And more.

Official announcements
Free Software Foundation Files Suit Against Cisco For GPL Violations Free Software Foundation

Jaspersoft Secures $12.5 Million in Venture Funding JasperSoft

Sound Investment Red Hat

Jaspersoft Announces New Community Edition of the World’s Most Widely Deployed Business Intelligence Software JasperSoft

…

The 127th edition of of Log Buffer, the weekly review of database blogs, has been published by Robert Treat on zillablog.

As the web’s only technology-neutral compendium of database blogs, Log Buffer thrives on reader contributions, such as story suggestions, comments, and foremost, volunteer editors like Robert (who, by the way, today joins LB’s multiple-edition group). He knows that publishing an edition of Log Buffer on his blog is an excellent way (and a fun way) to present his perspective on database blogs in an established venue. I invite you to do the same— send me an email and I’ll get you started.

Here is Robert’s …

My mind is playing "Suffering Succotash..."

I have been working on MySQL performance for a while now, and the team I am in have discovered that SysBench could do with a couple of tweaks for Solaris.

Sidebar - sysbench is a simple "OLTP" benchmark which can test multiple databases, including MySQL. Find out all about it here , but go to the download page to get the latest version.

To simulate multiple users sending requests to a database, sysbench uses multiple threads. This leads to two issues we have identified with SysBench on Solaris, namely:

• The implementation of random() is explicitly identified as unsafe in multi-threaded applications on Solaris. My team has found this is a real issue, with occasional core-dumps happening to our …

My mind is playing "Suffering Succotash..."

I have been working on MySQL performance for a while now, and the team I am in have discovered that SysBench could do with a couple of tweaks for Solaris.

Sidebar - sysbench is a simple "OLTP" benchmark which can test multiple databases, including MySQL. Find out all about it here , but go to the download page to get the latest version.

To simulate multiple users sending requests to a database, sysbench uses multiple threads. This leads to two issues we have identified with SysBench on Solaris, namely:

• The implementation of random() is explicitly identified as unsafe in multi-threaded applications on Solaris. My team has found this is a real issue, with occasional core-dumps happening to our …

One of the best practices on using MySQL is to avoid filesort. However there are cases where it is inevitable (e.g. ordering the result of fulltext search by modification date), and although in most cases we only the top N rows of sorted resultset are needed, MySQL does not implement top N sort.

After wondering for couple of months if I should hack the MySQL core to implement top-N-sort, today I decided to write a UDF that performs top N sort, and see how well it performs. And here it is: top-n-sort.c.

First the benchmark. When performing order by 〜 limit on an unindexed column of a 100k row table, top N sort is more than two times faster than the internal sort algorithm.

mysql> SELECT id,priority FROM testsort ORDER BY priority LIMIT 10;
(snip)
10 rows in set (0.11 sec)

mysql> SELECT topn_get(v) AS …

cmon 0.12 is now released and can be downloaded from www.severalnines.com/cmon.

Release notes:

• connection handling - cmon will now retry connects forever. cmon will also recover and automatically reconnect to cluster after a cluster failure or if the connection to the mysql server crashes where cmon logs information. This means you will have to terminate the cmon using e.g 'killall cmon' or /etc/init.d/cmon stop
• disk data - cmon now gathers information about the amount of table space used and this is presented in the web interface
  
• init.d scripts - fixed bugs in creating the init.d scripts during make install-extra
• fixed compiler warnings - now 0 compiler warnings on Linux systems
• mysql_statistics table - now explicitly using engine=myisam
• web interface - changed printout if cmon …