This is a short post just to make a point on the current performance level of available InnoDB engine implementations. I've made the current testing not in the goal to isolate performance issues, but to see how well the code works now when it meets the business constrain to flush on every transaction!..

The problem is that I've tested recently Oracle 10g with dbSTRESS during several customer's demands and was curious how well MySQL competes now with Oracle :-)) Due Oracle license restrictions I cannot present any Oracle results here (sorry folks, I still need my job :-)) But I've put MySQL into the similar conditions and was curious to see what is the current performance level we may expect on the same platform..

Until now I've always used "innodb_flush_log_at_trx_commit = 2" setting as I expected to isolate internal bottlenecks rather my storage array - but now it'll be "innodb_flush_log_at_trx_commit = 1". Bin log is not used …

Update: Before posting this, I had made an effort to verify that this was not a technical problem. However, Lenz from the MySQL Community team has now replied that indeed Zak Greant's RSS feed had changed it's URL address (see comments), which is why new posts did not appear. So rather than telling anything about Planet MySQL, this post is good evidence of my and others mistrust towards it - thankfully still unfounded. (The roots of this is also discussed in the comments.)

NOTE: Heading of this post was subsequently changed.

Not that I would be surprised of anything anymore, but still.

read more

I’ve had plenty of comments on specific products to Monitoring MySQL Options before providing the completed list. Here are the results from my survey to give everybody a more complete list.

I wanted to provide an update on two things:

• Firstly, we have opened registration for InnoDB/XtraDB training in Los Angeles (Nov 18th).
• The second is that while I am in New York City for training (Oct 29th), I'll be giving a (free) talk at the MySQL Meetup group there (Oct 28th).

We love to speak at Meetup groups.  If you are an organizer feel free to let us know you are interested.  There's no guarantee that we can get schedules to align - but there are a number of Percona consultants that travel regularly.

Entry posted by …

This presentation was be done by Sheeri Cabral of The Pythian Group and went into how to use SHOW ENGINE INNODB STATUS to get more information about your Innodb tables, foreign keys and transactions. This is a great presentation to learn how InnoDB works.

It also went through how to use SHOW ENGINE INNODB STATUS to tune several InnoDB variables:

innodb_adaptive_hash_index
innodb_commit_concurrency
innodb_concurrency_tickets
innodb_file_io_threads
innodb_log_buffer_size
innodb_max_purge_lag
innodb_sync_spin_loops
innodb_thread_concurrency
innodb_thread_sleep_delay

The slides can be downloaded from:

http://technocation.org/files/doc/ShowEngineInnoDBStatus.pdf

(Note that the slides open up to the middle section, which …

How important is page size matter for an IO-bound load? For this note results are limited to a disk-based server. InnoDB uses a 16kb page and you can recompile it to use an 8kb page. After making a few changes to it, I was able to use 4kb pages but I am not sure if that is safe to use in production.

I used sysbench fileio to measure IOPs for different page sizes using an 8-core x86 server with several fast disks. The …

I regret to say, there is no Log Buffer this week, as we’ve all been busy preparing for the Big New Thing coming in a few days. The good news is, we have a Big New Thing coming in a few days. Stay tuned for that, you won’t want to miss it.

LB will be back in a week, with Gerry Narvaja at the helm. In the meantime, I invite you to leave a comment with your favourite DB blogs from this week — MySQL, Oracle, Microsoft SQL Server, DB2, Postgres, Ingres, or other relational/NoSQL databases.

I've seen a few people link to an INFORMATION_SCHEMA query to be able to find any indexes that have low cardinality, in an effort to find out what indexes should be removed.  This method is flawed - here's the first reason why:

PLAIN TEXT SQL:

1. CREATE TABLE `sales` (
2. `id` int(11) NOT NULL AUTO_INCREMENT,
3. `customer_id` int(11) DEFAULT NULL,
4. `status` enum('archived','active') DEFAULT NULL,
5. PRIMARY KEY (`id`),
6. KEY `status` (`status`)
7. ) ENGINE=MyISAM AUTO_INCREMENT=65691 DEFAULT CHARSET=latin1;
8.  
9. mysql> SELECT count(*), STATUS FROM sales GROUP BY STATUS;
10. +----------+---------+
11. | count(*) | STATUS  |
12. +----------+---------+
13. |    65536 | archived |
…

Part 1 of our series set-up our "test"  application and looked at boosting performance of the application by buffer MySQL with memcached.  Our test application is simple and requires only 3 basic operations per transaction 2 reads and 1 write.  Using memcached combined with MySQL we ended up nearly getting a 10X performance boost from the application.  Now we are going to look at what we could achieve if we did not have to write to the database at all.  So let's look at what happens if we push everything including writes into memcached.

Wow that's shockingly fast isn't it! I guess being completely in memory helps for this app.  What is very interesting is accessing 100% of the data in memcached gives very similar numbers to accessing 100% of the data in memory in the DB ( part 1 …

Olio0.2 is comming