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 <[email protected]> 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 <[email protected]> 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 <[email protected]>
>>> 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 <[email protected]>
>>> 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 - [email protected]
>>> >>
>>> >>
>>> >
>>> >
>>> > --
>>> > -----------------------------
>>> > Johnny Withers
>>> > 601.209.4985
>>> > [email protected]
>>> >
>>>
>>>
>>>
>>> --
>>> Alexandre Vieira - [email protected]
>>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/[email protected]
>>
>>
>
>
> --
> Alexandre Vieira - [email protected]
>
>
--
Alexandre Vieira - [email protected]
