Hi,
I have the following problem: One table becomes locked and the lock is never
released. I do not use any form of LOCK TABLES to lock the table, I only do
SELECT, INSERT and UPDATE queries. The table stays locked until the server
reaches max_connections and server becomes unavailable. The problem occurs quite
randomly: sometimes once in every 2-5 days, but sometimes 2-3 times during one
hour.
I think that there is a problem with one table (atfoorum.teemad), but I don't
know to solve it. The problem might be something to do with the fact that I
converted the table from latin1 to utf8 and started inserting russian
characters.
I have saved many times the output of 'show processlist' before i killed the
server. It looks always the same:
The first query is SELECT query in the state 'Sorting result'. This select query
queries from multiple tables, including 'atfoorum.teemad' using LIKE.
The next queries in the processlist are UPDATES in the 'Locked' state (all
queries deal with table 'atfoorum.teemad').
I deleted the database and rebuilt it using mysqldump, but it did not help.
Has anyone had this kind of problem and/or knows how to solve this issue ?
I use 4.1.1-alpha-Max-log, the OS is RedHat AS 3.0. The server is dedicated to
run only MySQL.
Server parameters:
4GB RAM
2x2.8Ghz P4 Xeon
SCSI hard drives 10000 rpm in RAID 1 array.
Mysql queries per second average: 500
Here are the output of 'SHOW PROCESSLIST' command when the
problem occurred and output of 'SHOW VARIABLES'.
SHOW PROCESSLIST(1)
+-----------------------+------+--------------------+---------------------------
---------------------------------------------------------------------------+
| Id Command | Time | State | Info
|
+-----------------------+------+--------------------+---------------------------
---------------------------------------------------------------------------+
| 11837 Sleep | 89 | |
|
| 52084 Query | 231 | Sorting result | SELECT teemad.pealkiri AS
teemad_pealkiri, teemad.id AS teemad_id, postitusi, teemad.grupid_nimi AS |
| 52318 Sleep | 227 | |
|
| 52461 Sleep | 224 | |
|
| 52789 Delayed_insert | 215 | Waiting for INSERT |
|
| 53164 Query | 210 | Locked | SELECT teemad.* FROM
atfoorum.teemad WHERE id='4677'
|
| 53165 Query | 210 | Locked | UPDATE atfoorum.teemad SET
viimane_postitaja='VVV', viimane_postitaja_email='', postitusi=postitusi+ |
| 53205 Query | 208 | Locked | SELECT sonumid.teemad_id,
sonumid.pealkiri AS sonumi_pealkiri, teemad.pealkiri AS teema_pealkiri, te |
| 53220 Query | 208 | Locked | SELECT sonumid.teemad_id,
sonumid.pealkiri AS sonumi_pealkiri, teemad.pealkiri AS teema_pealkiri, te |
| 53295 Query | 206 | Locked | SELECT teemad.pealkiri AS
teemad_pealkiri, teemad.id AS teemad_id, postitusi, teemad.grupid_nimi AS |
| 53473 Query | 203 | Locked | SELECT sonumid.teemad_id,
sonumid.pealkiri AS sonumi_pealkiri, teemad.pealkiri AS teema_pealkiri, te |
| 53895 Query | 194 | Locked | SELECT teemad.id AS
teemad_id, teemad.pealkiri AS teemad_pealkiri, foorumite_grupid.pealkiri AS foor
|
| 53914 Query | 193 | Locked | SELECT teemad.id AS
teemad_id, teemad.pealkiri AS teemad_pealkiri, foorumite_grupid.pealkiri AS foor
|
| 53967 Query | 192 | Locked | SELECT teemad.id AS
teemad_id, teemad.pealkiri AS teemad_pealkiri, foorumite_grupid.pealkiri AS foor
|
| 54068 Query | 190 | Locked | SELECT sonumid.teemad_id,
sonumid.pealkiri AS sonumi_pealkiri, teemad.pealkiri AS teema_pealkiri, te |
| 54070 Query | 189 | Locked | SELECT COUNT(*) AS ridu
FROM atfoorum.teemad, atfoorum.foorumite_grupid_foorumid, atfoorum.foorumid |
| 54174 Query | 185 | Locked | SELECT teemad.*,
SUBSTRING(teemad.viimane_postitus_aeg,1,5) AS viimane_postitus_aeg,
UNIX_TIMESTAMP( |
| 54283 Query | 185 | Locked | SELECT sonumid.teemad_id,
sonumid.pealkiri AS sonumi_pealkiri, teemad.pealkiri AS teema_pealkiri, te |
| 54319 Query | 184 | Locked | SELECT sonumid.teemad_id,
sonumid.pealkiri AS sonumi_pealkiri, teemad.pealkiri AS teema_pealkiri, te |
| 54361 Query | 184 | Locked | SELECT teemad.id AS
teemad_id, teemad.pealkiri AS teemad_pealkiri, foorumite_grupid.pealkiri AS foor
|
| 54391 Query | 183 | Locked | SELECT teemad.id AS
teemad_id, teemad.pealkiri AS teemad_pealkiri, foorumite_grupid.pealkiri AS foor
|
| 54446 Query | 181 | Locked | SELECT teemad.*,
SUBSTRING(teemad.viimane_postitus_aeg,1,5) AS viimane_postitus_aeg,
UNIX_TIMESTAMP( |
| 54588 Sleep | 0 | |
|
| 54643 Sleep | 178 | |
|
| 54675 Query | 172 | Locked | SELECT teemad.id AS
teemad_id, teemad.pealkiri AS teemad_pealkiri, foorumite_grupid.pealkiri AS foor
|
| 54679 Sleep | 176 | |
|
| 54687 Query | 172 | Locked | SELECT teemad.id AS
teemad_id, teemad.pealkiri AS teemad_pealkiri, foorumite_grupid.pealkiri AS foor
|
| 54694 Query | 170 | Locked | SELECT sonumid.teemad_id,
sonumid.pealkiri AS sonumi_pealkiri, teemad.pealkiri AS teema_pealkiri, te |
| 54705 Query | 171 | Locked | SELECT teemad.id AS
teemad_id, teemad.pealkiri AS teemad_pealkiri, foorumite_grupid.pealkiri AS foor
|
| 54757 Query | 170 | Locked | SELECT sonumid.teemad_id,
sonumid.pealkiri AS sonumi_pealkiri, teemad.pealkiri AS teema_pealkiri, te |
| 54787 Query | 170 | Locked | SELECT teemad.id AS
teemad_id, teemad.pealkiri AS teemad_pealkiri, foorumite_grupid.pealkiri AS foor
|
| 54788 Query | 170 | Locked | SELECT sonumid.teemad_id,
sonumid.pealkiri AS sonumi_pealkiri, teemad.pealkiri AS teema_pealkiri, te |
| 54805 Query | 169 | Locked | SELECT teemad.*,
CONCAT(teemad.viimane_postitus_paev, _utf8' ',teemad.viimane_postitus_aeg) AS
order |
| 54841 Query | 168 | Locked | SELECT teemad.*,
SUBSTRING(teemad.viimane_postitus_aeg,1,5) AS viimane_postitus_aeg,
UNIX_TIMESTAMP( |
| 54855 Sleep | 170 | |
|
| 54925 Query | 169 | Locked | SELECT SUM(t.postitusi)
FROM atfoorum.foorumite_grupid AS fg, atfoorum.foorumite_grupid_foorumid AS |
| 54938 Query | 168 | Locked | SELECT teemad.*,
SUBSTRING(teemad.viimane_postitus_aeg,1,5) AS viimane_postitus_aeg,
UNIX_TIMESTAMP( |
| 55267 Query | 163 | Locked | SELECT sonumid.teemad_id,
sonumid.pealkiri AS sonumi_pealkiri, teemad.pealkiri AS teema_pealkiri, te |
| 55277 Query | 162 | Locked | SELECT teemad.*,
SUBSTRING(teemad.viimane_postitus_aeg,1,5) AS viimane_postitus_aeg,
UNIX_TIMESTAMP( |
| 55284 Query | 163 | Locked | SELECT teemad.id AS
teemad_id, teemad.pealkiri AS teemad_pealkiri, foorumite_grupid.pealkiri AS foor
|
| 55314 Sleep | 163 | |
|
| 55361 Sleep | 161 | |
|
| 55452 Sleep | 158 | |
|
| 55572 Sleep | 156 | |
|
| 55591 Query | 155 | Locked | SELECT sonumid.teemad_id,
sonumid.pealkiri AS sonumi_pealkiri, teemad.pealkiri AS teema_pealkiri, te |
| 55666 Query | 153 | Locked | SELECT teemad.id AS
teemad_id, teemad.pealkiri AS teemad_pealkiri, foorumite_grupid.pealkiri AS foor
|
| 55676 Query | 153 | Locked | SELECT sonumid.teemad_id,
sonumid.pealkiri AS sonumi_pealkiri, teemad.pealkiri AS teema_pealkiri, te |
| 55771 Sleep | 151 | |
|
| 55825 Query | 150 | Locked | SELECT teemad.id AS
teemad_id, teemad.pealkiri AS teemad_pealkiri, foorumite_grupid.pealkiri AS foor
|
| 55857 Sleep | 149 | |
|
| 56031 Sleep | 146 | |
|
| 56046 Sleep | 146 | |
|
| 56085 Query | 145 | Locked | SELECT sonumid.teemad_id,
sonumid.pealkiri AS sonumi_pealkiri, teemad.pealkiri AS teema_pealkiri, te |
| 56108 Query | 144 | Locked | SELECT teemad.id AS
teemad_id, teemad.pealkiri AS teemad_pealkiri, foorumite_grupid.pealkiri AS foor
|
| 56171 Sleep | 143 | |
And so on ...
SHOW VARIABLES
+---------------------------------+---------------------------------------------
----------------+
| Variable_name | Value
|
+---------------------------------+---------------------------------------------
----------------+
| back_log | 50
|
| basedir | /
|
| bdb_cache_size | 8388600
|
| bdb_log_buffer_size | 262144
|
| bdb_home | /var/lib/mysql/
|
| bdb_max_lock | 10000
|
| bdb_logdir |
|
| bdb_shared_data | OFF
|
| bdb_tmpdir | /tmp/
|
| binlog_cache_size | 32768
|
| bulk_insert_buffer_size | 8388608
|
| character_set_server | latin1
|
| character_set_system | utf8
|
| character_set_database | latin1
|
| character_set_client | latin1
|
| character_set_connection | latin1
|
| character-sets-dir | /usr/share/mysql/charsets/
|
| character_set_results | latin1
|
| collation_connection | latin1_swedish_ci
|
| collation_database | latin1_swedish_ci
|
| collation_server | latin1_swedish_ci
|
| concurrent_insert | ON
|
| connect_timeout | 5
|
| datadir | /var/lib/mysql/
|
| date_format | %Y-%m-%d
|
| datetime_format | %Y-%m-%d %H:%i:%s
|
| default_week_format | 0
|
| delay_key_write | ON
|
| delayed_insert_limit | 100
|
| delayed_insert_timeout | 300
|
| delayed_queue_size | 1000
|
| expire_logs_days | 0
|
| flush | OFF
|
| flush_time | 0
|
| ft_boolean_syntax | + -><()~*:""&|
|
| ft_min_word_len | 4
|
| ft_max_word_len | 254
|
| ft_query_expansion_limit | 20
|
| ft_stopword_file | (built-in)
|
| have_bdb | YES
|
| have_crypt | YES
|
| have_compress | YES
|
| have_innodb | YES
|
| have_isam | NO
|
| have_raid | YES
|
| have_symlink | YES
|
| have_openssl | YES
|
| have_query_cache | YES
|
| init_file |
|
| innodb_additional_mem_pool_size | 1048576
|
| innodb_buffer_pool_size | 8388608
|
| innodb_buffer_pool_awe_mem_mb | 0
|
| innodb_data_file_path | ibdata1:10M:autoextend
|
| innodb_data_home_dir |
|
| innodb_file_io_threads | 4
|
| innodb_open_files | 300
|
| innodb_force_recovery | 0
|
| innodb_thread_concurrency | 8
|
| innodb_flush_log_at_trx_commit | 1
|
| innodb_fast_shutdown | ON
|
| innodb_file_per_table | OFF
|
| innodb_flush_method |
|
| innodb_lock_wait_timeout | 50
|
| innodb_log_arch_dir | ./
|
| innodb_log_archive | OFF
|
| innodb_log_buffer_size | 1048576
|
| innodb_log_file_size | 5242880
|
| innodb_log_files_in_group | 2
|
| innodb_log_group_home_dir | ./
|
| innodb_mirrored_log_groups | 1
|
| innodb_max_dirty_pages_pct | 90
|
| interactive_timeout | 28800
|
| join_buffer_size | 131072
|
| key_buffer_size | 402653184
|
| key_cache_block_size | 1024
|
| key_cache_division_limit | 100
|
| key_cache_age_threshold | 300
|
| language | /usr/share/mysql/english/
|
| large_files_support | ON
|
| local_infile | ON
|
| locked_in_memory | OFF
|
| log | OFF
|
| log_update | OFF
|
| log_bin | ON
|
| log_slave_updates | OFF
|
| log_slow_queries | ON
|
| log_warnings | OFF
|
| long_query_time | 10
|
| low_priority_updates | ON
|
| lower_case_table_names | OFF
|
| max_allowed_packet | 1047552
|
| max_binlog_cache_size | 4294967295
|
| max_binlog_size | 1073741824
|
| max_connections | 400
|
| max_connect_errors | 10
|
| max_error_count | 64
|
| max_delayed_threads | 20
|
| max_heap_table_size | 16777216
|
| max_join_size | 4294967295
|
| max_relay_log_size | 0
|
| max_seeks_for_key | 4294967295
|
| max_length_for_sort_data | 1024
|
| max_prepared_statements | 64
|
| max_sort_length | 1024
|
| max_user_connections | 0
|
| max_tmp_tables | 32
|
| max_write_lock_count | 4294967295
|
| myisam_max_extra_sort_file_size | 268435456
|
| myisam_max_sort_file_size | 2147483647
|
| myisam_repair_threads | 1
|
| myisam_recover_options | OFF
|
| myisam_sort_buffer_size | 67108864
|
| net_buffer_length | 8192
|
| net_read_timeout | 30
|
| net_retry_count | 10
|
| net_write_timeout | 60
|
| new | OFF
|
| old_passwords | ON
|
| open_files_limit | 2010
|
| pid_file | /var/lib/mysql/ourserver.ee.pid
|
| log_error | ./ourserver.ee
|
| port | 3306
|
| protocol_version | 10
|
| preload_buffer_size | 32768
|
| pseudo_thread_id | 0
|
| query_alloc_block_size | 8192
|
| query_cache_limit | 1048576
|
| query_cache_min_res_unit | 4096
|
| query_cache_size | 67108864
|
| query_cache_type | ON
|
| secure_auth | OFF
|
| query_prealloc_size | 8192
|
| range_alloc_block_size | 2048
|
| read_buffer_size | 2093056
|
| read_only | OFF
|
| read_rnd_buffer_size | 262144
|
| relay_log_purge | ON
|
| rpl_recovery_rank | 0
|
| server_id | 1
|
| slave_net_timeout | 3600
|
| read_only | OFF
|
| skip_external_locking | ON
|
| skip_networking | OFF
|
| skip_show_database | OFF
|
| slow_launch_time | 2
|
| socket | /var/lib/mysql/mysql.sock
|
| sort_buffer_size | 2097144
|
| sql_mode |
|
| table_cache | 512
|
| table_type | MYISAM
|
| thread_cache_size | 0
|
| thread_stack | 196608
|
| tx_isolation | REPEATABLE-READ
|
| time_format | %H:%i:%s
|
| timezone | EET
|
| tmp_table_size | 268435456
|
| tmpdir |
|
| transaction_alloc_block_size | 8192
|
| transaction_prealloc_size | 4096
|
| version | 4.1.1-alpha-Max-log
|
| version_bdb | Sleepycat Software: Berkeley DB 4.1.24:
(December 1, 2003) |
| version_comment | Official MySQL RPM
|
| version_compile_machine | i686
|
| version_compile_os | pc-linux
|
| wait_timeout | 500
|
+---------------------------------+---------------------------------------------
----------------+
Many thanks in advance,
Elmo
[EMAIL PROTECTED]
-----------------------------------------
ITV - Sinu lemmiksaated internetis!
http://www.itv.ee
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
