Hi everyone.
Here's the situation.
I have third-party software which is written in PHP. It makes hard
use of MySQL (several queries per second, according binlog). It works
smoothly most of time, but sometimes it stops accepting connections. It
continues 1-20 minutes and then it works again for a couple of hours
(but sometimes for only 5 minutes). Then it has a "jinx" again.
It still allow some connections when in "jinx", although most of them
are refused.
I googled and found a couple of similar problems posted
to several forums (not answered, unfortunately). I also found some
articles regarding mysql variables, and tried to play with them (settings
I came up with are below). I feel like there was no effect.
I also made "mysqladmin extended-status" run and log results every 10
minutes. Here what I got :
Value 1: 9 minute before a 4-minute "jinx",
Value 2: in the middle
Value 3: 7 minutes after a "jinx"
(I skipped those rows in which values are equal to zero in all columns)
+--------------------------+-----------+-----------+-----------+
| Variable_name | Value 1 | Value 2 | Value 3 |
+--------------------------+-----------+-----------+-----------+
| Aborted_clients | 2 | 2 | 2 |
| Bytes_received | 95609376 | 96518404 | 97434013 |
| Bytes_sent | 173188019 | 174439427 | 175704155 |
| Com_change_db | 175784 | 177762 | 179781 |
| Com_delete | 196589 | 198302 | 200060 |
| Com_insert | 18448 | 18730 | 19024 |
| Com_replace | 116112 | 117792 | 119507 |
| Com_select | 403220 | 406248 | 409306 |
| Com_show_status | 68 | 69 | 70 |
| Com_show_variables | 67 | 68 | 69 |
| Com_update | 93629 | 94707 | 95657 |
| Connections | 176006 | 177986 | 180007 |
| Created_tmp_disk_tables | 61 | 61 | 61 |
| Created_tmp_tables | 1106 | 1106 | 1106 |
| Flush_commands | 1 | 1 | 1 |
| Handler_delete | 8100 | 8187 | 8296 |
| Handler_read_first | 133774 | 134072 | 134375 |
| Handler_read_key | 468972 | 472250 | 475440 |
| Handler_read_next | 3669012 | 3673273 | 3677673 |
| Handler_read_rnd | 374439 | 376033 | 377638 |
| Handler_read_rnd_next | 4908985 | 4946426 | 4987720 |
| Handler_update | 426222 | 428628 | 430917 |
| Handler_write | 456499 | 458461 | 460470 |
| Key_blocks_used | 7031 | 7067 | 7121 |
| Key_read_requests | 2382943 | 2400186 | 2417184 |
| Key_reads | 4565 | 4583 | 4614 |
| Key_write_requests | 204037 | 205366 | 206814 |
| Key_writes | 89353 | 90672 | 92094 |
| Max_used_connections | 4 | 4 | 4 |
| Open_tables | 37 | 37 | 37 |
| Open_files | 62 | 62 | 62 |
| Opened_tables | 43 | 43 | 43 |
| Questions | 1179931 | 1191672 | 1203489 |
| Select_range | 1123 | 1123 | 1123 |
| Select_scan | 62075 | 62340 | 62605 |
| Sort_range | 81 | 81 | 81 |
| Sort_rows | 42281 | 42281 | 42281 |
| Sort_scan | 2124 | 2124 | 2124 |
| Table_locks_immediate | 928772 | 936804 | 944807 |
| Table_locks_waited | 660 | 674 | 711 |
| Threads_cached | 4 | 4 | 3 |
| Threads_created | 5 | 5 | 5 |
| Threads_connected | 1 | 1 | 2 |
| Threads_running | 1 | 1 | 1 |
| Uptime | 40290 | 40890 | 41490 |
+--------------------------+-----------+-----------+-----------+
I'm running MySQL 4.0.17 on 4-processor machine under Win2k3 (PHP is
installed on the same machine)
Here's my.cnf:
[mysqld]
port=3306
skip-locking
set-variable = connect_timeout=15
set-variable = flush_time=0
set-variable = interactive_timeout=30
set-variable = key_buffer_size=64M
set-variable = max_allowed_packet=10M
set-variable = max_connections=1000
set-variable = max_connect_errors=100
set-variable = max_user_connections=1000
set-variable = table_cache=128
set-variable = thread_cache_size=128
set-variable = thread_concurrency=8
set-variable = wait_timeout=15
log-bin
How do I go about fixing this?
Any advise is highly appreciated.
Thanks,
Vasiliy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
