Showing entries 1 to 6
Displaying posts with tag: I/O (reset)
On InnoDB I/O threads states

I was asked today what different state values for InnoDB I/O threads really mean, these ones:

I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)

I tried to search the manual and Web in general and found no useful explanation (these verbose values should be self explanatory by design it seems). As question was asked, probably it's time to try to answer it...

These states for I/O threads are set by srv_set_io_thread_op_info() function. Quick search with grep on MySQL 5.5.29 source code tree gives us some hints, the rest we can try to find out via code review based on these hits:

[openxs@chief mysql-5.5]$ grep -rn  srv_set_io_thread_op_info * …

[Read more]
Data fragmentation problem in MySQL & MyISAM

The other day at PSCE I worked on a customer case of what turned out to be a problem with poor data locality or a data fragmentation problem if you will. I tought that it would make a good article as it was a great demonstration of how badly it can affect MySQL performance. And while the post is mostly around MyISAM tables, the problem is not really specific to any particular storage engine, it can affect a database that runs on InnoDB in a very similar way.

The problem

MyISAM lacks support for clustering keys or even anything remotely similar. Its data file format allows new information to be written anywhere inside a table. Anywhere can be either at the end of a file where it can be simply appended or an empty space somewhere in the middle left after previously deleted row(s). This implies no particular order in which rows are stored unless there are absolutely no …

[Read more]
Analyzing I/O performance

There are probably thousands of articles on the Internet about disk statistics in Linux, what various columns mean, how accurate the information is, and so on. I decided to attack the problem from a little bit more practical side. Hopefully this will be just the first of many future posts on identifying various I/O related performance problems on a MySQL server.

Linux exposes disk statistics through /proc/diskstats. However the contents of this file isn’t something anyone can understand quickly. It needs a tool to transform the information into something human readable. A tool that is available for any Linux distribution is called iostat and comes with sysstat package.

How to access and read I/O statistics

Usually you want to call iostat one way:

iostat -xkd <interval> <block device>

The interval should typically be one second as it is the …

[Read more]
IOPS, innodb_io_capacity, and the InnoDB Plugin

In the InnoDB plugin, a new variable was added named innodb_io_capacity, which controls the maximum number of I/O operations per second that InnoDB will perform (which includes the flushing rate of dirty pages as well as the insert buffer (ibuf) batch size).

First off, let me just say this is a welcome addition (an addition provided by the Google Team, fwiw).

However, before this was configurable, the internal hard-coded value for this was 100. But when this became configurable, the default was increased to 200.

For many systems, this is not an issue (i.e., the overall system can perform 200 IOPS).

However, there are still many disks (which is often the bottleneck) out there that are …

[Read more]
Found an Ideal I/O Scheduler for my MySQL boxes

Today I was doing some work on one of our database servers (each of them has 4 SAS disks in RAID10 on an Adaptec controller) and it required huge multi-thread I/O-bound read load. Basically it was a set of parallel full-scan reads from a 300Gb compressed innodb table (yes, we use innodb plugin). Looking at the iostat I saw pretty expected results: 90-100% disk utilization and lots of read operations per second. Then I decided to play around with linux I/O schedulers and try to increase disk subsystem throughput. Here are the results:

Scheduler Reads per second
cfq 20000-25000
noop 35000-60000
deadline 33000-45000
[Read more]
Google Phone (Android) Demo Of Streetview With Compass

I think this is going to be really neat: you walk around the streets of San Francisco, for example, with your Android powered phone, en route to your destination 20 blocks away.

You whip out your phone, go to Google Maps, pull up the StreetView (remember this?), which zeroes in on your location using a built-in GPS, and then changes as you move the phone around using the built-in compass.

You then virtually walk the city, looking around, without actually moving an inch (looking for the closest ATM, restaurant, etc, hint-hint?).

Without further ado, let's have a look at this video from Google's I/O Conference for a demonstration?

    This video …

[Read more]
Showing entries 1 to 6