List:General Discussion« Previous MessageNext Message »
From:Nilton Moura Date:September 18 2010 5:59pm
Subject:Re: skip locking
View as plain text  
Mon,

If I understood well, skip-locking isn't for you. IMHO, if you have a
unique process (mysqld) that manages your tables and statements are
causing deadlock, maybe you should try InnoDB. Try simulate a parallel
environment changing the storage engine and tell us. (Take a look in
the modifier 'LOCK IN SHARE MODE' of InnoDB too.)

Somebody in the list can give him a better suggestion?

Regards,
Nilton Moura.

2010/9/17 monloi perez <mlp_folder@stripped>:
> Thanks Nilton and sorry for the rest for not giving details on the issue.
>
> I think the issue happened when an insert was followed by a select on the
> same table or perhaps vice versa. What's obvious is that the queries started
> to pile up and their status shows "system lock". We research on skip locking
> and tried however we still had the same issue again. So our bet was really
> on the insert and select that caused the table to be locked.
>
> Are there mysql configurations that you can suggest other than skip-locking
> to at least fix the issue?
>
> Thanks,
> Mon
>
>
> ________________________________
> From: Nilton Moura <mysql@stripped>
> To: mysql@stripped
> Sent: Thu, September 16, 2010 11:07:24 PM
> Subject: Re: skip locking
>
> Hi,
>
> The first main difference is that InnoDB has transactional
> capabilities (all-or-nothing) and MyISAM not yet. If you need to use
> COMMIT on some statements, you need to use InnoDB. The second point is
> the locking of MyISAM, which is on table-level. If your environment is
> mixed with read/write concurrency, this is not good, because
> table-level locking is exactly what you read. InnoDB has unlimited
> row-level locking.
>
> If you want to use MyISAM, read about the locking. By default, write
> requests take priority over read requests. But you can change the
> priority of your requests. For example, you can use the modifier
> LOW_PRIORITY with statements that update update tables like INSERT or
> UPDATE. But take care, because if read statements keep arriving, it is
> possible for a low-priority write request never to be performed.
>
> The last tip: even MyISAM with table-level locking, it support
> concurrent inserts. If you have many UPDATE statements and/or DELETE,
> you have holes in your tables, and this disable concurrent inserts. Or
> you optimize table (to defragment) or change the concurrent_insert
> system variable to 2, to force concurrent inserts, or both. But, read
> about this too.
>
> There are many differences about this two storage engines, and I hope
> I have helped you.
>
> Nilton Moura.
>
> 2010/9/16 monloi perez <mlp_folder@stripped>:
>> Any idea on this?
>>
>> -Mon
>>
>>
>>
>>
>> ________________________________
>> From: Michael Satterwhite <michael@stripped>
>> To: mysql@stripped
>> Sent: Mon, September 13, 2010 1:07:38 AM
>> Subject: Re: skip locking
>>
>> Received.
>>
>>
>>
>> On Sunday, September 12, 2010 09:32:12 am monloi perez wrote:
>>> Hi All,
>>>
>>> Sorry if I posted on the wrong list.
>>>
>>> I've had this issue with my previous server already, seems like
>>> skip-locking configuration does not seem to work on myisam tables.
>>>
>>> Also what is the difference between myisam and innodb tables. The reason
>>> I
>>> wanted to know is that I think skip-locking works better in InnoDB. Or
>>> perhaps the right question is when do I choose myISAM and when do I
>>> choose
>>> InnoDB. My current server is running on an 8-core cpu with 32G of memory
>>> and only running CMS or news based web applications. What do you suggest
>>> that we use for the storage engine?
>>>
>>> Thanks,
>>> Mon
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>
>>
>>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>
>
Thread
skip lockingmonloi perez12 Sep
  • Re: skip lockingMichael Satterwhite12 Sep
    • Re: skip lockingmonloi perez16 Sep
      • Re: skip lockingNilton Moura16 Sep
        • Update query problemAndy Wallace16 Sep
          • RE: Update query problemTravis Ard16 Sep
Re: skip lockingNilton Moura18 Sep