skip locking

--0-1550954470-1284301932=:62674
Content-Type: text/plain; charset=us-ascii

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



--0-1550954470-1284301932=:62674--

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=gcdmg-mysql-2@m.gmane.org

Re: skip locking

--0-105817566-1284639766=:95708
Content-Type: text/plain; charset=us-ascii

Any idea on this?

-Mon




________________________________
From: Michael Satterwhite
To: mysql@lists.mysql.com
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=mlp_folder@yahoo.com



--0-105817566-1284639766=:95708--

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 :
> Any idea on this?
>
> -Mon
>
>
>
>
> ________________________________
> From: Michael Satterwhite
> To: mysql@lists.mysql.com
> 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 choo=
se
>> 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: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmlp_folder@ya=
hoo.com
>
>
>

--
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

Update query problem

So I'm having a problem with an update query. I have three tables:

Table: A
Columns: acnt, name, company, email, domain

Table: AM
Columns: acnt, m_id

Table: M
Columns: m_id, name, company, email, domain

and I want to conditionally update the columns in one to values from the
other. i.e., I want to put the value of A.name into M.name, but only
if M.name is currently NULL, AND A.name has a usable value (not an empty
string).

This is what I came up with, but it doesn't work - it only replaces the
values where the column in M is not null.


update A
join AM on A.acnt = AM.acnt
join M on AM.m_id = M.m_id
SET M.name = IF( (!M.name AND A.name != ''), A.name, M.name),
M.company = IF( (!M.company AND A.company != ''), A.company, M.company),
M.email = IF( (!M.email AND A.email != ''), A.email, M.email),
M.domain = IF( (!M.domain AND A.domain != ''), A.domain, M.domain)

Any thoughts?

THanks,
andy

--
Andy Wallace
iHOUSEweb, Inc.
awallace@ihouseweb.com
(866) 645-7700 ext 219
--
There are two ways to build software:
Make it so simple that there are obviously no bugs,
or make it so complex that there are no obvious bugs.

--
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: Update query problem

Try using the IS NULL operator instead of !

-Travis

-----Original Message-----
From: Andy Wallace [mailto:awallace@ihouseweb.com]
Sent: Thursday, September 16, 2010 10:47 AM
To: mysql@lists.mysql.com
Subject: Update query problem

So I'm having a problem with an update query. I have three tables:

Table: A
Columns: acnt, name, company, email, domain

Table: AM
Columns: acnt, m_id

Table: M
Columns: m_id, name, company, email, domain

and I want to conditionally update the columns in one to values from the
other. i.e., I want to put the value of A.name into M.name, but only
if M.name is currently NULL, AND A.name has a usable value (not an empty
string).

This is what I came up with, but it doesn't work - it only replaces the
values where the column in M is not null.


update A
join AM on A.acnt = AM.acnt
join M on AM.m_id = M.m_id
SET M.name = IF( (!M.name AND A.name != ''), A.name, M.name),
M.company = IF( (!M.company AND A.company != ''), A.company,
M.company),
M.email = IF( (!M.email AND A.email != ''), A.email, M.email),
M.domain = IF( (!M.domain AND A.domain != ''), A.domain, M.domain)

Any thoughts?

THanks,
andy

--
Andy Wallace
iHOUSEweb, Inc.
awallace@ihouseweb.com
(866) 645-7700 ext 219
--
There are two ways to build software:
Make it so simple that there are obviously no bugs,
or make it so complex that there are no obvious bugs.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=travis_ard@hotmail.com



--
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: skip locking

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 :
> 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 star=
ted
> to pile up and their status shows "system lock". We research on skip lock=
ing
> and tried however we still had the same issue again. So our bet was reall=
y
> on the insert and select that caused the table to be locked.
>
> Are there mysql configurations that you can suggest other than skip-locki=
ng
> to at least fix the issue?
>
> Thanks,
> Mon
>
>
> ________________________________
> From: Nilton Moura
> To: mysql@lists.mysql.com
> 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 :
>> Any idea on this?
>>
>> -Mon
>>
>>
>>
>>
>> ________________________________
>> From: Michael Satterwhite
>> To: mysql@lists.mysql.com
>> 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 reaso=
n
>>> 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 memor=
y
>>> and only running CMS or news based web applications. What do you sugges=
t
>>> that we use for the storage engine?
>>>
>>> Thanks,
>>> Mon
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmlp_folder@y=
ahoo.com
>>
>>
>>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:=A0 =A0 http://lists.mysql.com/mysql?unsub=3Dmlp_folder@ya=
hoo.com
>
>
>

--
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