My TMPDIR is actually not separate from the data. There is plently of disk
space. In fact, thinking it was some sort of RAM problem I reduced the size
of the database by almost 30% with no change in performace.
You say this should not happen, yet as I've been searching the mailing list
there's at least 2-3 threads in the last year talking about this problem.
Most posts have gone unanswered.
I acutally have over 250 unrelated threads that get locked during a 1 minute
long query. Since my web server is set for 256 MAX processes, the website
that links to the DB dies until the offending query ends.
This happens with many queries. All are tuned and have worked for months
until recently. In fact no code has changed in our scripts. Unrelated table
just started locking all of a sudden. Also we have run isamchk
100% of the tables are in RAM. The only disk access should be updates. This
may be useful. This is the mysqladmin ext output when NO query is locked:
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 1 |
| Aborted_connects | 1 |
| Created_tmp_tables | 7809 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 69600 |
| Handler_read_first | 251 |
| Handler_read_key | 6190764 |
| Handler_read_next | 19966429 |
| Handler_read_rnd | 1085708844 |
| Handler_update | 940141 |
| Handler_write | 593065 |
| Key_blocks_used | 59010 |
| Key_read_requests | 18022185 |
| Key_reads | 57325 |
| Key_write_requests | 600364 |
| Key_writes | 277250 |
| Max_used_connections | 256 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 511 |
| Open_files | 106 |
| Open_streams | 0 |
| Opened_tables | 1173 |
| Questions | 675496 |
| Running_threads | 5 |
| Slow_queries | 20005 |
| Uptime | 23360 |
+--------------------------+------------+
> Michael Griffith writes:
> > Using SHOW PROCESSLIST or mysqladmin proc
> >
> > Every time a table reports status of 'copying to tmp table' all other
UPDATE queries are locked, even in unrelated tables.
> >
> > For example:
> >
> > Query #1: SELECT * FROM a JOIN B WHERE.... Status: copying to tmp
table
> > Query #2: UPDATE C SET x=x+1 WHERE...... Status: locked
> >
> > Even though table C is not used is query #2 it is locked until query #1
completed. This can cause huge problems when hundreds of quick updates are
locked for more than a few seconds
> >
> > Why does a tmp table on one table lock another?
> > How can I prevent this from happening?
> >
>
> Hi!
>
> The above should not happen.
>
> You should look at entire processlist.
>
> And check the size of your TMPDIR partition.
>
>
> Regards,
>
> Sinisa
>
> ____ __ _____ _____ ___ == MySQL AB
> /*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
> /*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
> /*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaka, Cyprus
> /*/ /*/ /*/\*\_/*/ \*\_/*/ |*|____
> ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^
> /*/ \*\ Developers Team
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
