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)
