List:General Discussion« Previous MessageNext Message »
From:Alexandre Vieira Date:September 6 2010 5:39pm
Subject:Re: Performance problems on MySQL
View as plain text  
Hi List,

In a 20m interval in our max load I have:

OS WAIT ARRAY INFO: reservation count 637, signal count 625
Mutex spin waits 0, rounds 19457, OS waits 428
RW-shared spins 238, OS waits 119; RW-excl spins 13, OS waits 8

(The values are the difference between the start and end of this 20m
interval)

The machine has 2 CPU's and usually has 40-50% of idle CPU.

Our workload consists on lots of parallel simple queries (SELECTs and
UPDATEs with a simple condition on the PK) on a 500k record/40MB table with
an INDEX on the PK.

| innodb_sync_spin_loops          | 20                                     |
| innodb_thread_concurrency       | 16                                     |
| innodb_thread_sleep_delay       | 10000                                  |

I've been sampling my innodb status and there are always "16 queries inside
InnoDB" and some 20-30 in queue. Therefore lowering thread_sleep_delay won't
help.

Since I have 47 spin rounds per OS Wait, would innodb gain something with
rising sync_spin_loops a little bit?

Also, should I be capping thread_concurrency with a 2 CPU machine?

Unfortunately this machine only has 2 RAID1 disks. I can't spread the disk
load (datafile/logfiles) between disks.

                    extended device statistics
    r/s    w/s   kr/s   kw/s wait actv wsvc_t asvc_t  %w  %b device
    0.0   83.7    0.0  379.3  0.0  1.0    0.1   11.5   1  94 d2 (/var)

Usually the iostat busy indicator is near 100%.

Any hints on something I could tune to have less "OS Waits" and help with
the Disk I/O?

=====================================
100906 18:33:40 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 47 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 266140, signal count 259267
Mutex spin waits 0, rounds 7407879, OS waits 179189
RW-shared spins 93878, OS waits 46196; RW-excl spins 9473, OS waits 7311

---
LOG
---
Log sequence number 62 2833945222
Log flushed up to   62 2833944847
Last checkpoint at  62 2828803314
1 pending log writes, 0 pending chkp writes
18419416 log i/o's done, 37.64 log i/o's/second

--------------
ROW OPERATIONS
--------------
16 queries inside InnoDB, 27 queries in queue
1 read views open inside InnoDB
Main thread id 11, state: sleeping
Number of rows inserted 603196, updated 9006533, deleted 111028, read
30145300
0.17 inserts/s, 18.49 updates/s, 0.00 deletes/s, 41.47 reads/s

If nothing else can be done I'll advise the client to acquire new HW for
this BD.

By the way, upgrading from 5.0.45-log to 5.1.50 would make a huge difference
in terms of performance?

BR
AJ

On Mon, Sep 6, 2010 at 10:46 AM, Alexandre Vieira <nullpt@stripped> wrote:

> Hi,
>
> We're chaning it to INT(9). Apparently someone remembered to change the
> type of data in this field from an alphanumeric value to an INT(9).
>
> I'm going to change this asap.
>
> Thanks
>
> BR
> AJ
>
>
> On Mon, Sep 6, 2010 at 5:17 AM, mos <mos99@stripped> wrote:
>
>> At 04:44 AM 9/3/2010, Alexandre Vieira wrote:
>>
>>> Hi Johnny,
>>>
>>> mysql> EXPLAIN SELECT * FROM clientinfo WHERE userid='911930694';
>>>
>>>
> +----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
>>> | id | select_type | table      | type  | possible_keys | key     |
>>> key_len
>>> | ref   | rows | Extra |
>>>
>>>
> +----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
>>> |  1 | SIMPLE      | clientinfo | const | PRIMARY       | PRIMARY | 23
>>> | const |    1 |       |
>>>
>>>
> +----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
>>> 1 row in set (0.53 sec)
>>>
>>> Thanks
>>>
>>> BR
>>> AJ
>>>
>>
>> Alexandre,
>>     Do you have UserId declared as CHAR? It looks numeric to me. If it is
>> stored as an integer then don't use the '     ' in the select statement
>> otherwise it needs to convert it.
>> If UserId values are integers and you have the column defined as CHAR,
>> then declare the column UserId as integer or BigInt and the searches should
>> be faster than searching on CHAR.
>>
>> Mike
>>
>>
>>
>>
>>  On Thu, Sep 2, 2010 at 8:52 PM, Johnny Withers <johnny@stripped>
>>> wrote:
>>>
>>> > What about an explain of this query:
>>> >
>>> >
>>> > SELECT * FROM clientinfo WHERE userid='182106617';
>>> >
>>> > -JW
>>> >
>>> >
>>> > On Thu, Sep 2, 2010 at 9:35 AM, Alexandre Vieira
> <nullpt@stripped>
>>> wrote:
>>> >
>>> >> John, Johnny,
>>> >>
>>> >> Thanks for the prompt answer.
>>> >>
>>> >> mysql> SHOW CREATE TABLE clientinfo;
>>> >>
>>> >>
>>>
> +------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>>> >> | Table      | Create
>>> >> Table
>>> >> |
>>> >>
>>> >>
>>>
> +------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>>> >> | clientinfo | CREATE TABLE `clientinfo` (
>>> >>   `userid` varchar(21) NOT NULL default '',
>>> >>   `units` float default NULL,
>>> >>   `date_last_query` datetime default NULL,
>>> >>   `last_acc` int(10) unsigned default NULL,
>>> >>   `date_last_units` datetime default NULL,
>>> >>   `notification` int(10) unsigned NOT NULL default '0',
>>> >>   `package` char(1) default NULL,
>>> >>   `user_type` varchar(5) default NULL,
>>> >>   PRIMARY KEY  (`userid`)
>>> >> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
>>> >>
>>> >>
>>>
> +------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>>> >> 1 row in set (0.00 sec)
>>> >> mysql> SHOW INDEX FROM clientinfo;
>>> >>
>>> >>
>>>
> +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
>>> >> | Table      | Non_unique | Key_name | Seq_in_index | Column_name |
>>> >> Collation | Cardinality | Sub_part | Packed | Null | Index_type |
>>> Comment |
>>> >>
>>> >>
>>>
> +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
>>> >> | clientinfo |          0 | PRIMARY  |            1 | userid      |
>>> >> A         |      460056 |     NULL | NULL   |      | BTREE      |
>>>     |
>>> >>
>>> >>
>>>
> +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
>>> >> 1 row in set (0.00 sec)
>>> >>
>>> >>
>>> >> SELECT * FROM clientinfo WHERE userid='182106617';
>>> >>
>>> >> UPDATE clientinfo SET
>>> >>
>>>
> units=0.0,date_last_query=now(),user_type='POS',last_acc=167,date_last_units=now(),notification=0
>>> >> WHERE userid='152633876';
>>> >>
>>> >> INSERT INTO clientinfo VALUES
>>> >> ('171918726',101.0,now(),1,now(),0,'D','PRE') ON DUPLICATE KEY
> UPDATE
>>> >> units=101.0, date_last_query=now(), last_acc=1,
> date_last_units=now(),
>>> >> notification=0, package='D', user_type='PRE';
>>> >>
>>> >> DELETE FROM clientinfo WHERE units='155618918';
>>> >>
>>> >> There are no other type of queries.
>>> >>
>>> >> We're running this DB on a Sun-Fire V240. 2xUIIIi 1.5ghz with 2GB
> of
>>> RAM.
>>> >>
>>> >> We also run some other applications in the server, but nothing that
>>> >> consumes all the CPU/Memory. The machine has almost 1GB of free
> memory
>>> and
>>> >> 50% of idle CPU time at any time.
>>> >>
>>> >> TIA
>>> >>
>>> >> BR
>>> >> Alex
>>> >>
>>> >>
>>> >> --
>>> >> Alexandre Vieira - nullpt@stripped
>>> >>
>>> >>
>>> >
>>> >
>>> > --
>>> > -----------------------------
>>> > Johnny Withers
>>> > 601.209.4985
>>> > johnny@stripped
>>> >
>>>
>>>
>>>
>>> --
>>> Alexandre Vieira - nullpt@stripped
>>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>
>>
>
>
> --
> Alexandre Vieira - nullpt@stripped
>
>


-- 
Alexandre Vieira - nullpt@stripped

Thread
Performance problems on MySQLAlexandre Vieira2 Sep
  • Re: Performance problems on MySQLJangita2 Sep
    • Re: Performance problems on MySQLAlexandre Vieira2 Sep
      • Re: Performance problems on MySQLJangita3 Sep
  • RE: Performance problems on MySQLTravis Ard2 Sep
    • Re: Performance problems on MySQLAlexandre Vieira2 Sep
Re: Performance problems on MySQLAlexandre Vieira3 Sep
  • Re: Performance problems on MySQLJohnny Withers3 Sep
    • Re: Performance problems on MySQLAlexandre Vieira3 Sep
      • Re: Performance problems on MySQLJohnny Withers3 Sep
        • Re: Performance problems on MySQLAlexandre Vieira3 Sep
          • Re: Performance problems on MySQLJohnny Withers3 Sep
            • Re: Performance problems on MySQLAlexandre Vieira3 Sep
              • Re: Performance problems on MySQLJohnny Withers3 Sep
                • Re: Performance problems on MySQLAlexandre Vieira3 Sep
                  • Re: Performance problems on MySQLJohnny Withers3 Sep
                    • Re: Performance problems on MySQLMySQL)5 Sep
  • Re: Performance problems on MySQLmos6 Sep
    • Re: Performance problems on MySQLAlexandre Vieira6 Sep
      • Re: Performance problems on MySQLAlexandre Vieira6 Sep