Playing with TokuDB updates I noticed in SHOW PROCESSLIST unsual for MySQL State.

PLAIN TEXT CODE:

1. mysql> show processlist;
2. +----+------+-----------+--------+---------+------+---------------------------+-----------------------------+
3. | Id | User | Host      | db     | Command | Time | State                     | Info                        |
4. +----+------+-----------+--------+---------+------+---------------------------+-----------------------------+
5. |  3 | root | localhost | sbtest | Query   |   30 | Updated about 764000 rows | update sbtest set email=zip |
6. ...
7. mysql> …

fadvise is a system call that can be used to give Linux hints about how it should be caching files. It has a few options for caching, not caching, read ahead, and random access. I was looking into used fadvise because a client ran into an issue where some infrequently used myisam data files were being pushed out of the filesystem cache by binary logs and other activity. The files are used infrequently for queries but when they are used they need to be fast. When the files weren’t cached the particular query ran in about 30 seconds. When they were cached the query ran in .8 seconds — huge difference. The fix seemed pretty trivial, call fadvise on the myd files, they stay in cache, the queries are consistently faster and the problem is solved. It seemed simple but it wasn’t. I’ll cover the myisam issue in more detail in another post, this is all about fadvise and debugging.

I couldn’t find an rpm that contained a simple tool to …

So Ronald Bradford has been getting some EC2 AMI’s setup for Waffle the last couple of days. Really he is the first person to work with us on testing out the 0.5 cream release. It’s funny but when you are heads down in the code you tend to overlook some of the easy and obvious bugs. Just by having him walk through the install we have opened up several new bugs and come up with some new Idea’s on how to make things easier in the future. I hope by early next week we can have some of annoying bugs fixed and code pushed to launchpad. When that happens I would encourage others to give it a whirl and help us make Waffle Grid better performing, more user friendly, and more stable.

You can check out some of the bugs here.

Thanks to Ronald for his help on this!

Writeback is a feature that allows you to modify OLAP cell values and see the effects ripple through the data set, automatically modifying child and parent cells, and also cells derived using calculations. This allows you to perform 'what if' analysis and applications such as budgeting.
I have added experimental support for writeback to Mondrian.
In Mondrian's case, the term 'writeback' is a bit misleading. In a ROLAP system such as Mondrian, writing back to the database would be difficult, since values are stored in a fact table but we allow cells of any granularity to be modified. One modified cell might contain thousands of fact table rows. So, we don't write cells back to the database, but just retain the modified cells in memory, and propagate the modifications to related cells.
Here's how to use the experimental writeback support. Some of the details may change later as we make the feature more usable.
First, …

This post is for storage engine developers that may be interested in implementing multiple clustering keys.

After blogging about TokuDB’s multiple clustering indexes feature, Baron Schwartz suggested we contribute the patch to allow other storage engine to implement the feature. We filed a feature request to MySQL to support this, along with a proposed patch. The patch, along with known issues, can be found here.

What the patch contains:

This patch has the changes necessary to introduce the new grammar for clustering indexes, and to tell the storage engine what indexes are defined as clustering. With this patch, all indexes that are defined as clustering …

So, when you connect on the fly to a database your subject to a variety of issues, like when the db is not available and when the db does not have a route.

One of the main reasons why a dev may want to connect on the fly is because they have too many front ends to hold a persistent connection on the backends. Since mySQL does not use libevent, holding open threads to mySQL is much more costly. Threads == Memory.

But, that's here nor there. The main purpose of this post is to talk about how to recover from failed connections that block apache threads.

Common Failures:
No route to Host
Flapping NIC
Locked Tables
Recovering from a Crash
more of the same.


My Environment:
I have a bunch of webservers (200+) that all have 300 possible threads (60000 possible connections to a single DB) behind a load balancer that uses the LB least connections …


install_driver(mysql) failed: Can't load '/usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/mysql/mysql.so' for module DBD::mysql: /usr/lib64/libmysqlclient.so.15: version `libmysqlclient_15' not found (required by /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/mysql/mysql.so) at /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/DynaLoader.pm line 230.
at (eval line 3


After a lot of mucking around I noticed that the MySQL-shared-community RPM was installed, not the MySQL-shared-compat RPM like my usual installs. So…


rpm -Uvh MySQL-shared-compat-5.1.33-0.rhel5.x86_64.rpm


Dear community,

We are pleased to announce the build16 of MySQL server® with Percona patches.

Since the build13 there was a couple of customer specific releases, which explains cutover in numbering and a pause between the builds. Also we prepared build for both 5.0.77 and 5.0.82 versions.

Since that time new patches were added:

• profiling_slow.patch

This patch adds information from SHOW PROFILE to query information in slow.log output.
profiling_server. This variable ( ON / OFF ) enables profiling of all queries (in all connections).
profiling_use_getrusage. This variable ( ON / OFF ) enables usage of getrusage function in profiling.
Be careful, enabling profiling_server may cause performance degradation, especially with …

Enter the sb script (available in version 3.0.03). Now I can shorten my typing experience with sandboxes quite a lot:

  $ sb 5135
  # same as calling 
  # …

I discovered while compiling Wafflegrid today that by default, the Ubuntu binaries for memcached are not-multithreaded.

Following the installation of memcached from apt-get and libmemcached I ran memslap for:

$ memslap -s localhost
    Threads connecting to servers 1
    Took 1.633 seconds to load data

$ memstat -s localhost
Listing 1 Server

Server: localhost (11211)
     pid: 23868
     uptime: 54
     time: 1244575816
     version: 1.2.2
     pointer_size: 32
     rusage_user: 0.90000
     rusage_system: 0.120000
     curr_items: 10000
     total_items: 10000
     bytes: 5430000
     curr_connections: 1
     total_connections: 3
     connection_structures: 2
     cmd_get: 0
     cmd_set: 10000
     get_hits: 0
     get_misses: 0 …