This is part 3 of our series.  In part 1 we talked about boosting performance with memcached on top of MySQL, in Part 2 we talked about running 100% outside the data with memcached, and now in Part 3 we are going to look at a possible solution to free you from the database.  The solution I am going to discuss here is Tokyo Cabinet and Tyrant.

I am not going to give you a primer  or Tutorial on Tyrant and Cabinet, there are plenty of these out there already.  Instead I want to see what sort of performance we can see compared to MySQL and Memcached, and later on other NoSQL solutions.  Tokyo actually allows you to use several types of databases that are supported, there are hash databases which …

For the my previous post, there was comment to suggest to test db_STRESS benchmark on XtraDB by Dimitri. And I tested and tuned for the benchmark. I will show you the tunings. It should be also tuning procedure for general heavy writing workloads.

At first, <tuning peak performance>. The next, <tuning purge operation> to stabilize performance  and to avoid decreasing performance.

<test condition>

Server:
PowerEdge R900, Four Quad Core E7320 Xeon, 2.13GHz, 32GB Memory, 16X2GB, 667MHz

db_STRESS:
32 sessions, RW=1, dbsize = 1000000, no thinktime

XtraDB: (mysql-5.1.39 + XtraDB-1.0.4-current)
innodb_io_capacity = 4000
innodb_support_xa = false
innodb_file_per_table = …

Since I started doing the occasional consulting job for Open Query, I've seen a lot of MySQL servers that have been installed once and then forgotten about. This gave me the idea to do a short presentation about some basic MySQL server configuration. The first go was at DrupalCampMelbourne and I recently tried (and failed) to cram it into a three minute lightning talk slot at the LUV September meeting.

The title of the talk is (now) MySQL > YourSQL. I chose this not because I think that MySQL is better than the $other_database you use or because I may or may not run a newer version of MySQL on better hardware, but because I use InnoDB and …

As you may know, InnoDB has 2 limits for unflushed modified blocks in the buffer pool. The one is from physical size of the buffer pool. And the another one is oldness of the block which is from the capacity of transaction log files.

In the case of heavy updating workload, the modified ages of the many blocks are clustered. And to reduce the maximum of the modified ages InnoDB needs to flush many of the blocks in a short time, if these are not flushed at all. Then the flushing storm affect the performance seriously.

We suggested the "adaptive_checkpoint" option of constant flushing to avoid such a flushing storm. And finally, the newest InnoDB Plugin 1.0.4 has the new similar option "adaptive_flushing" as native.

Let's check the adaptive flushing options at this post.

HOW THEY WORK

< adaptive_checkpoint=reflex (older method)>

Just in case you missed the live event, we have a recording of the ZFS + SSD for databases webcast

Listen Now

You can also download the slides from Slide share. Download Slides

I will co-present in webinar on Performance Challenges and Solutions for IO Bound Workloads in MySQL. My part of the presentation will be speaking about why switching from CPU bound workload to IO bound is such an important event, how to prepare to it as well as how to keep your application performance good as the data growths.

The Brian's portion of webinar will focus on the Schooner offering as example of flash based appliance - one of solutions I mention in my presentation.

It should be interesting whenever you're interested in Schooner appliance offerings, flash or scaling MySQL in General.

You can follow this url to register.

Entry posted by peter | …

Ronald Bradford last week posted about memcached not being multi-threaded on Ubuntu, something he discovered via some small utilities that are bundled with libmemcached, written by Brian Aker.

When I noticed there were no Ubuntu packages for libmemcached (or the CLI tools) I decided to create some.

For your enjoyment: http://ubuntu.cafuego.net/dists/jaunty-cafuego/memcached/ (Source debs are included)

The repository also contains a memcached that has been re-compiled with multithreading enabled.

I wanted to share a little rule of thumb I sometimes use to decide which columns should come first in an index. This is not specific to MySQL, it's generally applicable to any database server with b-tree indexes. And there are a bunch of subtleties, but I will also ignore those for the sake of simplicity.

Let's start with this query, which returns zero rows but does a full table scan. EXPLAIN says there are no possible_keys.

PLAIN TEXT SQL:

1. SELECT * FROM tbl WHERE STATUS='waiting' AND source='twitter'
2.  AND no_send_before <= '2009-05-28 03:17:50' AND tries <= 20
3.  ORDER BY date ASC LIMIT 1;

Don't try to figure out the meaning of the query, because that'll add complexity to the example In the simplest case, we want to put the most …

Brian Miezejewski will be presenting at the North Texas MySQL Users Group on June 1st on performance tuning on your system. Brian is a top-level tuning guru and this is your chance to get an expert to examine your system.

So on your system, run the following:

mysqladmin -u -p va >varis.txt


Then during your peak usage time (if possible):

mysqladmin -u -p ex -i 15 -r >stats.txt


Let it run for 10 minutes before hitting ctrl-c to kill it. Bring in the varis.txt and the stats.txt files for tuning and review.


Meeting: June 1st
7:00 PM
Sun Offices
Suite 700
16000 Dallas Tollway
Dallas


NorthTexasMySQL.org

Yesterday, The Pythian Group issued a press release about my book, Pythian’s partnership with Sun, and our new “MySQL Adoption Accelerator Package”. I am not a marketing guru, but I can tell you what we the package means in terms of new work that the MySQL teams have been doing.

Basically, the MySQL Adoption Accelerator Package combines customized training with a comprehensive audit of systems. The name “Adoption Accelerator” makes it sound like it’s only for new applications that are almost ready to go live. What the program actually does is have us evaluate your systems, and intensively train you in the areas you want and need. The program is designed to suit all your needs, whether it’s teaching you about one topic (say, query optimization) or an entire range of topics, from Architecture to ZFS (special issues with running MySQL on ZFS, that is, but that did not fit a cute …