I/O read performance

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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: I/O read performance

--000e0cd5bd265d92f00499bfccad
Content-Type: text/plain; charset=ISO-8859-1

(Assuming you are on Linux)

Take a look at the output of: iostate -xk 60

The output will update every 60 secs. Take a look at the value in the %util
column after a few updates, if it's around 90% you have become disk bound
and you'll need to figure out a way to decrease some load on the disk. You
may need to increase the size of innodb_buffer_pool if you DB is mostly
innodb tables. Add more RAM to the server if needed. You may also want to
add more disks to your disk array (assuming you have one). Move logs from
this disk array to another (binary logs, query logs, etc).

You can also look at your rkB/s (reads) and wkB/s (writes). If you are read
heavy, you may need to change the kind of RAID you are using for one that
performs better when reading. Same goes for write heavy, pick the best RAID
for your use.

HTH




On Thu, Jan 13, 2011 at 1:44 PM, Steve Staples 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
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=johnny@pixelated.net
>
>


--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--000e0cd5bd265d92f00499bfccad--

re: I/O read performance

On 1/13/2011 1:44 PM, Steve Staples 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.
MyISAM? What does Show Status Like 'key%' show for read efficiency? Is
key_buffer_size near 25% of RAM? Is key_reads/key_read_requests < 1/100?

InnoDB? is innodb_buffer_pool_size approx 80% of available RAM? What
does max_connections * ( sort_buffer + record_buffer ) + max_connections
* 2 MB evaluate to, and what's available RAM?

PB

----

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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: I/O read performance

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 wrote:
> Hello,
>
> I've been noticing a little lag in my application lately, =A0it seems as
> if 1 table in 1 database is getting slower to read from. =A0 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?). =A0I'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? =A0would symlinking the file to another db make any differenc=
e
> (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
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmdykman@gmail=
..com
>
>



--=20
=A0- michael dykman
=A0- mdykman@gmail.com

=A0May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: I/O read performance

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 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 = '' 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)





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: I/O read performance

On 1/13/11 1:21 PM, Steve Staples wrote:
> 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 = '' and `status`='1';

Do you have a key on customer_email? If not, create one ASAP. What
kind of writes happen on this table?

With MyISAM, serial-like behavior can occur under certain circumstances.
All queries must obtain either a read lock or a write lock. A table
can have multiple read locks simultaneously, but a write lock must have
exclusive access. Write locks have higher priority than read locks.

What this means in practice is that if a query requests a write lock, it
is placed at the front of the queue. Any currently-running queries are
allowed to finish, but any new lock requests are queued. If the
currently-running queries take very long to complete (and "very long"
could be fractions of a second, depending on the application), then lots
of queries will get queued up behind the write lock that is pending.
All new read requests would need to wait for the write lock queue to
clear out before they can get back to reading from the table again.

Usually, this results in inconsistent performance under high load. One
solution is to switch to InnoDB, since it does row-level locking. This
is not a perfect solution for every situation, and you should do some
research before doing this. It's probably the best solution in the long
term for you, but I can't guarantee that.

Another possibility would be to queue writes in a separate table (or
memcache, or something like that). Then do the writes in batches.

Steve Meyers

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: I/O read performance

On Thu, 2011-01-13 at 13:51 -0700, Steve Meyers wrote:
> On 1/13/11 1:21 PM, Steve Staples wrote:
> > 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 = '' and `status`='1';
>
> Do you have a key on customer_email? If not, create one ASAP. What
> kind of writes happen on this table?
>
> With MyISAM, serial-like behavior can occur under certain circumstances.
> All queries must obtain either a read lock or a write lock. A table
> can have multiple read locks simultaneously, but a write lock must have
> exclusive access. Write locks have higher priority than read locks.
>
> What this means in practice is that if a query requests a write lock, it
> is placed at the front of the queue. Any currently-running queries are
> allowed to finish, but any new lock requests are queued. If the
> currently-running queries take very long to complete (and "very long"
> could be fractions of a second, depending on the application), then lots
> of queries will get queued up behind the write lock that is pending.
> All new read requests would need to wait for the write lock queue to
> clear out before they can get back to reading from the table again.
>
> Usually, this results in inconsistent performance under high load. One
> solution is to switch to InnoDB, since it does row-level locking. This
> is not a perfect solution for every situation, and you should do some
> research before doing this. It's probably the best solution in the long
> term for you, but I can't guarantee that.
>
> Another possibility would be to queue writes in a separate table (or
> memcache, or something like that). Then do the writes in batches.
>
> Steve Meyers

the only writes that happen, is when a customer has their "status"
changed, password updates, or they change their name, OR when a new
customer is created. I would say a new customer get created (insert
into) about 30 times per day, and a customer gets updated (update)
prolly about 20 times per day, and customers getting deleted or
suspended about 20 (there are rough guesses), and then customer searches
are about 200-300 times per day, and then the other part that does all
the searching, prolly about 100-200 per minute (or so). I am really
guessing here, but i am prolly pretty close.

the query that does the most, is:
mysql> EXPLAIN SELECT customer_id FROM customer_account WHERE
`full_login`='' ORDER BY `status` DESC LIMIT 1 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: customer_account
type: ref
possible_keys: NewIndex5,NewIndex4
key: NewIndex5
key_len: 202
ref: const
rows: 2
Extra: Using where
1 row in set (0.00 sec)

NewIndex5 is 'full_login', 'status'
NewIndex4 is 'full_login' (fulltext)

maybe my indexes are out of line here... maybe i was just doing this
in haste, since the indexes are both kinda the same.

I guess maybe it is just the sheer amount of queries per second/minute
that is the issue?


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: I/O read performance

On 1/13/11 2:13 PM, Steve Staples wrote:
> On Thu, 2011-01-13 at 13:51 -0700, Steve Meyers wrote:
>> On 1/13/11 1:21 PM, Steve Staples wrote:
>>> 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 = '' and `status`='1';
>>
>> Do you have a key on customer_email? If not, create one ASAP. What
>> kind of writes happen on this table?
>>
>> With MyISAM, serial-like behavior can occur under certain circumstances.
>> All queries must obtain either a read lock or a write lock. A table
>> can have multiple read locks simultaneously, but a write lock must have
>> exclusive access. Write locks have higher priority than read locks.
>>
>> What this means in practice is that if a query requests a write lock, it
>> is placed at the front of the queue. Any currently-running queries are
>> allowed to finish, but any new lock requests are queued. If the
>> currently-running queries take very long to complete (and "very long"
>> could be fractions of a second, depending on the application), then lots
>> of queries will get queued up behind the write lock that is pending.
>> All new read requests would need to wait for the write lock queue to
>> clear out before they can get back to reading from the table again.
>>
>> Usually, this results in inconsistent performance under high load. One
>> solution is to switch to InnoDB, since it does row-level locking. This
>> is not a perfect solution for every situation, and you should do some
>> research before doing this. It's probably the best solution in the long
>> term for you, but I can't guarantee that.
>>
>> Another possibility would be to queue writes in a separate table (or
>> memcache, or something like that). Then do the writes in batches.
>>
>> Steve Meyers
>
> the only writes that happen, is when a customer has their "status"
> changed, password updates, or they change their name, OR when a new
> customer is created. I would say a new customer get created (insert
> into) about 30 times per day, and a customer gets updated (update)
> prolly about 20 times per day, and customers getting deleted or
> suspended about 20 (there are rough guesses), and then customer searches
> are about 200-300 times per day, and then the other part that does all
> the searching, prolly about 100-200 per minute (or so). I am really
> guessing here, but i am prolly pretty close.
>
> the query that does the most, is:
> mysql> EXPLAIN SELECT customer_id FROM customer_account WHERE
> `full_login`='' ORDER BY `status` DESC LIMIT 1 \G
> *************************** 1. row ***************************
> id: 1
> select_type: SIMPLE
> table: customer_account
> type: ref
> possible_keys: NewIndex5,NewIndex4
> key: NewIndex5
> key_len: 202
> ref: const
> rows: 2
> Extra: Using where
> 1 row in set (0.00 sec)
>
> NewIndex5 is 'full_login', 'status'
> NewIndex4 is 'full_login' (fulltext)
>
> maybe my indexes are out of line here... maybe i was just doing this
> in haste, since the indexes are both kinda the same.
>
> I guess maybe it is just the sheer amount of queries per second/minute
> that is the issue?

That's not very many queries, honestly. I'm not sure why you have a
fulltext index on there, it's probably not really helping.

From what you've told us, there really shouldn't be a problem. I'd try
using mk-query-digest to find out if there are queries running that you
don't realize.

Steve Meyers

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: I/O read performance

--------------enigDAA693D9E210AD0C8D56AACC
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable



Am 13.01.2011 22:13, schrieb Steve Staples:

> the only writes that happen, is when a customer has their "status"
> changed, password updates, or they change their name, OR when a new
> customer is created. I would say a new customer get created (insert
> into) about 30 times per day, and a customer gets updated (update)
> prolly about 20 times per day, and customers getting deleted or
> suspended about 20 (there are rough guesses),=20

That should not make any problem

> and then customer searches
> are about 200-300 times per day, and then the other part that does all
> the searching, prolly about 100-200 per minute (or so). I am really
> guessing here, but i am prolly pretty close.

This is nothing, normally hundrets of querys per second
should not be a prolem, even thousands in most cases

Are you sure that the lags are really the query and not the connection?

I have seen on a windows server with ipv7 large lags because mysql
treid by every connect to make a dns-reverse-lookup first on ipv6
and after fail ipv4

"skip-name-resolve" in the mysql-config did the trick, but make
sure that there are up-addresses instead of hostnames in
the permissions-tables before try this

> the query that does the most, is:
> mysql> EXPLAIN SELECT customer_id FROM customer_account WHERE
> `full_login`=3D'' ORDER BY `status` DESC LIMIT 1 \G
> *************************** 1. row ***************************
> id: 1
> select_type: SIMPLE
> table: customer_account
> type: ref
> possible_keys: NewIndex5,NewIndex4
> key: NewIndex5
> key_len: 202
> ref: const
> rows: 2
> Extra: Using where
> 1 row in set (0.00 sec)
>=20
> NewIndex5 is 'full_login', 'status'
> NewIndex4 is 'full_login' (fulltext)

"NewIndex4" is useless but not used, it only should waste space
and making inserts/updates slower

How many rows have the table?

if the table is large "order by" often makes things really slow
and sometimes the optimizer from mysql can not benefit from keys
while sorting - Do you really need the sort because you only fetch
one row and if `full_login` is unique you should not need it

Anyways:
is query_cache in use?
this can improve performance really and in your case where most
querys are reads the cache will not be invalid most time, but that
should be optimizing and not the prolem!


--------------enigDAA693D9E210AD0C8D56AACC
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEYEARECAAYFAk0vge4ACgkQhmBjz394AnnVFACfSmePhDCgwRMn7jh/mJN7 kekQ
nccAoJftkNAS0Rj3IY1sze0YpYvW8F+u
=R1Id
-----END PGP SIGNATURE-----

--------------enigDAA693D9E210AD0C8D56AACC--

Re: I/O read performance

On 1/13/11 3:51 PM, Reindl Harald wrote:
> Are you sure that the lags are really the query and not the connection?
>
> I have seen on a windows server with ipv7 large lags because mysql
> treid by every connect to make a dns-reverse-lookup first on ipv6
> and after fail ipv4
>
> "skip-name-resolve" in the mysql-config did the trick, but make
> sure that there are up-addresses instead of hostnames in
> the permissions-tables before try this

This is a very good point, I hadn't thought of this. I always have name
resolution turned off in my configs.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org