mysql

mysql

So, what's the bottleneck?

I recently released some RAID testing I did using the sysbench testing framework. In light of the recent attention paid to multi-core CPU scalability, I have been working on some related tests trying to identify sources of contention using that same set of tests.

After effectively turning off every single innodb safety setting (like flush_log_at_trx_commit, checksums, doublewrite, etc.), and not seeing any real performance increase, I started to wonder what was going on.

Surely my test client server wasn't the problem, it had plenty of idle CPU according to top, right? Wrong.

I've been able to drive more QPS to my mysql test servers by starting up parallel sysbench tests from multiple test servers, but using (more or less) the same number of total test threads.

Letters to the Editor (1)

I recently got this email from a reader on my site and since I haven't posted for a while, I thought it might be a good discussion:

Jay,

I've been reading your site and had a question that you might have good insight for.  

I'm working on a high-traffic website that includes forums.  All the code is custom PHP, including the forums, simply because the PHP BB software out there doesn't scale as large and as well as I wanted.  I've implemented heavy memcached usage, distributed databases, etc. to handle any level of growth we may hit.

3 comments

Read more

The scoop on Innodb reads

I spent some time going through the source the other day in order to try to understand the difference between these SHOW STATUS variables in 5.x:

Innodb_buffer_pool_read_requests

Innodb_buffer_pool_reads

Innodb_pages_read

Here's what I discovered that I'm not sure is 100% right, but I suspect is at least on the right track:

Innodb_buffer_pool_read_requests: requests to get something from the buffer pool. This isn't really that great of a mystery. I suspect these are 'direct' read requests, meaning those caused directly from a query of some kind.

1 comment

Read more

STATUS variables: the difference between innodb_pages_read and innodb_buffer_pool_reads

I haven't gotten any responses to my question from Friday, so I figured I'd post a more direct question:

The two variables 'innodb_pages_read' and 'innodb_buffer_pool_reads' are different. 'Innodb_pages_read' seems consistently higher than the buffer_pool_reads. Certainly the buffer pool reads 'pages', but what else would be counted as a page read?

On the flip side, 'innodb_pages_written' and 'innodb_buffer_pool_pages_flushed' seem consistent.

Anyone have any clues?

8 comments

Using Events to manage Table Partitioning by Date: wrap-up

As a follow up to the series of posts I've been making, I wanted to post what I ended up with. Thanks to everyone who posted comments, your help was extremely useful. To recap, I have a working pair of events to add and remove partitions to this table:

create table log (
    logged datetime not null,
    id int not null auto_increment,
    text varchar(256),
    PRIMARY KEY ( logged, id )
)
PARTITION BY RANGE( TO_DAYS( logged ) ) (
    PARTITION p20080206 VALUES LESS THAN (733444),
    PARTITION p20080207 VALUES LESS THAN (733445),
    PARTITION p20080208 VALUES LESS THAN (733446)

2 comments

Read more

mysqlguy.net

So, what's the bottleneck?

Letters to the Editor (1)

The scoop on Innodb reads

STATUS variables: the difference between innodb_pages_read and innodb_buffer_pool_reads

Using Events to manage Table Partitioning by Date: wrap-up

About Me