Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 6

Displaying posts with tag: I/O (reset)

On InnoDB I/O threads states
+5 Vote Up -0Vote Down
I was asked today what different state values for InnoDB I/O threads really mean, these ones:

--------
FILE I/O
--------
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]$













  [Read more...]
Data fragmentation problem in MySQL & MyISAM
+2 Vote Up -0Vote Down

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

  [Read more...]
Analyzing I/O performance
+0 Vote Up -0Vote Down

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>

  [Read more...]
IOPS, innodb_io_capacity, and the InnoDB Plugin
+4 Vote Up -0Vote Down
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







  [Read more...]
Found an Ideal I/O Scheduler for my MySQL boxes
+0 Vote Up -0Vote Down

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 anticipatory 22000-29000

Notice: The box can’t be restarted to check with clean caches and stuff, but I was doing full reads from this huge table on a machine with 16Gb RAM so all caches were washed out

  [Read more...]
Google Phone (Android) Demo Of Streetview With Compass
+0 Vote Up -0Vote Down

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

  [Read more...]
Showing entries 1 to 6

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.