Database locking

some questions abt table locking (assume using myisam)

1. If a slow query is running on a table, is update/insert operations
blocked?

2. If a slow query is running on a table (slave), is replication
operations blocked?

thanks.

"howa" <howachen@gmail.com> wrote:
> some questions abt table locking (assume using myisam)
>
> 1. If a slow query is running on a table, is update/insert operations
> blocked?
>
> 2. If a slow query is running on a table (slave), is replication
> operations blocked?

Regarding MyISAM there are three groups of statements:

1. SELECT
2. INSERT at end of table (append)
3. DELETE, UPDATE, INSERT not at end of table

At a given point in time and for a given table there can be multiple
statements from group 1 and up to one statement from group 2
*or* one statement from group 3.

In other words:

- reads are done concurrently
- all writes (also append) are serialized
- reads mix with append

The replication SQL thread is not different from any other client
thread in that respect.


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

Axel Schwenke ¼g¹D¡G

> In other words:
>
> - reads are done concurrently
> - all writes (also append) are serialized
> - reads mix with append
>
> The replication SQL thread is not different from any other client
> thread in that respect.
>
>

ok, are there any method to reduce the priority of SELECT threads?

since in our system, some slow query under heavy system load will
affect the write performance, we want to give write a higher priority,
is it possible?

"howa" <howachen@gmail.com> wrote:
> Axel Schwenke =BCg=B9D=A1G
>
>> - reads are done concurrently
>> - all writes (also append) are serialized
>> - reads mix with append
>>
>> The replication SQL thread is not different from any other client
>> thread in that respect.
>
> ok, are there any method to reduce the priority of SELECT threads?
>
> since in our system, some slow query under heavy system load will
> affect the write performance, we want to give write a higher priority,
> is it possible?

This is the default already. As soon as there is a write pending for
a table, all new read requests are enqeued to be executed after the
write. See here for details:

http://dev.mysql.com/doc/refman/5.0/...e-locking.html


If MyISAMs coarse locking kills the performance of your database,
consider using InnoDB tables.


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

Axel Schwenke ¼g¹D¡G

> "howa" <howachen@gmail.com> wrote:
> > Axel Schwenke =BCg=B9D=A1G
> >
>
> If MyISAMs coarse locking kills the performance of your database,
> consider using InnoDB tables.
>
>


okay, it is a good practice to use InnoDB as Master, MyISAM as slave in
this case?
(assume using replication)

"howa" <howachen@gmail.com> wrote:
> Axel Schwenke =BCg=B9D=A1G
>>
>> If MyISAMs coarse locking kills the performance of your database,
>> consider using InnoDB tables.
>
> okay, it is a good practice to use InnoDB as Master, MyISAM as slave in
> this case?

There is nothing wrong with that. In fact this is an often seen
solution if one needs both transactional behaviour of a table
(InnoDB) and MyISAM-only features like FULLTEXT indexes.


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

Axel Schwenke ¼g¹D¡G

1. SELECT
2. INSERT at end of table (append)
3. DELETE, UPDATE, INSERT not at end of table



one more thing:

1. why i can't update/delete at the end of the table while running a
slow query
2. what will happen if a slow query is running, and update/delete not
at the end of the table?


thanks.