MySQL, High Availability and other goodies…

Letters to the Editor (1)

March 25, 2008
MySQL Search Engine

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.
Everything is working great, and I'm to the point where we are going to implement a search engine for forum posts.  I'm afraid that applying full-text searching to the forum posts is going to cause MySQL to get very... upset... if/when we have 12 million or more posts in the forums (very possible).

In your opinion, would a properly constructed natural language search system against the MyISAM engine produce better results than trying to write our own keywords search tables on the backend?  I've googled to no avail - I find TONS of tutorials on using LIKE and % for "search engines" but nothing that really discusses high volume/high traffic like I'm hoping to deal with.

Thanks,
Tony M.
Read more →

MyQ Gadgets 0.0.9 released

February 28, 2008
MyQ Gadgets

A new version of myq_gadgets has been released and you can download it here.

Changelog since the last released version:

  • Version 0.0.9 — Documented new reports in README; updated thds column in the cttf report to read crtd instead.
  • Version 0.0.8 — Added a bunch more innodb reports, looking for the good stuff.
  • Version 0.0.7 — Added an innodb mode to myq_status to support 5.0+ STATUS variables. myq_innodb_status continues to handle SHOW INNODB STATUS output, mostly for 4.1 and earlier.
Read more →

The scoop on Innodb reads

February 28, 2008
InnoDB MySQL Storage Engine

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.  
Read more →

STATUS variables: the difference between innodb_pages_read and innodb_buffer_pool_reads

February 25, 2008
MySQL Storage Engine

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?

Trackback URL for this post:

http://mysqlguy.net/trackback/15
Read more →

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

February 20, 2008
Events MySQL Partitioning

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)
);
Read more →