List:General Discussion« Previous MessageNext Message »
From:Steve Staples Date:January 13 2011 8:21pm
Subject:Re: I/O read performance
View as plain text  
On Thu, 2011-01-13 at 15:07 -0500, Michael Dykman wrote:
> The behaviour of mixed reads/write o your system is heavily dependant
> on what types of tables you are using.  The fully ACID tables types,
> most notably InnoDB support that model far better than MyISAM tables..
>  Not to discount the value of measuring your raw i/o performance, but
> first we should determine how your data is being stored.
> 
>  - michael
> 
> On Thu, Jan 13, 2011 at 2:44 PM, Steve Staples <sstaples@stripped> wrote:
> > Hello,
> >
> > I've been noticing a little lag in my application lately,  it seems as
> > if 1 table in 1 database is getting slower to read from.   Mind you,
> > that table is being accessed a LOT of times per second every hour of
> > every day, and then the "application" searches on this same table too.
> >
> > In my sandbox, it is fast to search (as there is no other reads/queries
> > on that table), so i don't think it is disk i/o (but you never know
> > right?).  I've also double checked all the indexing, to insure indexes
> > are used.
> >
> > What I was wondering is, are the reads/queries simultaneous, or are they
> > sequential?  would symlinking the file to another db make any difference
> > (or is that even possible)?
> >
> > any insight would be appreciated, or even any ideas on what I may do to
> > increase the performance, or even how to measure where the issue could
> > be would help as well.
> >
> > Steve


Ok, so far:
the iostat -xk 60:

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           5.10    0.00    2.20    1.76    0.00   90.95

Device:         rrqm/s   wrqm/s   r/s   w/s    rkB/s    wkB/s avgrq-sz
avgqu-sz   await  svctm  %util
sda               0.00     0.75  0.00  0.63     0.00     5.53    17.47
0.05   81.26   9.79   0.62
sdb               0.00    31.77  0.88 85.93     6.20   472.13    11.02
4.70   54.05   1.09   9.49

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           5.88    0.00    2.06    0.60    0.00   91.46

Device:         rrqm/s   wrqm/s   r/s   w/s    rkB/s    wkB/s avgrq-sz
avgqu-sz   await  svctm  %util
sda               0.00     0.73  0.00  0.53     0.00     5.07    19.00
0.01   22.25  15.50   0.83
sdb               0.00    22.51  0.12 64.66     0.47   351.14    10.86
2.34   35.85   0.53   3.43


SHOW STATUS LIKE 'key%'
Variable_name           Value     
----------------------  ----------
Key_blocks_not_flushed  0         
Key_blocks_unused       132222    
Key_blocks_used         231960    
Key_read_requests       3593619256
Key_reads               32379162  
Key_write_requests      366156623 
Key_writes              299846526 


table type is MyISAM,  it is a "customer_account" table, which holds the
email address, and the customer_id field,  the queries that are
constantly being queried is "select customer_id from customer_account
where customer_email = '<email address>' and `status`='1';

and then all the lookups that are being done, have a similar query,
based on first/last names, and email address...


there are a TONNE of tables being used, so the SHOW STATUS LIKE 'key%'
may not have anything to do with the actual issues (I am still auditing
all the queries that are being run, for efficiency).

it doesn't really look like I/O, so maybe table type?   there are a lot
of reads on this table (prolly 99.9% of all queries on this table are
reads)




Thread
I/O read performanceSteve Staples13 Jan
  • Re: I/O read performanceJohnny Withers13 Jan
  • Re: I/O read performanceMichael Dykman13 Jan
    • Re: I/O read performanceSteve Staples13 Jan
      • Re: I/O read performanceSteve Meyers13 Jan
        • Re: I/O read performanceSteve Staples13 Jan
          • Re: I/O read performanceSteve Meyers13 Jan
          • Re: I/O read performanceReindl Harald13 Jan
            • Re: I/O read performanceSteve Meyers14 Jan
re: I/O read performancePeter Brawley13 Jan