First of all, I forgive the rather lengthy post.
Thanks for the repl(y|ies) Benjamin. Decreasing the key_buffer should
be my first step. Back to the questions:
3) I'm somewhat at a loss for this one and perhaps the answer is more
obvious than not. I have 257 total tables from my main DB and mysql. I
figured this by a "ls -al var/ | grep -c MYD". How can I possibly have
512 (which is also the table_cache value) open tables?
| Open_tables | 512 |
| Open_files | 776 |
| Open_streams | 0 |
| Opened_tables | 1499 |
I also see Opened_tables is 1499, which is 3x the number of open tables.
I would consider this number average and not think about increasing
table_cache. OTOH, one of my slaves has this:
| Open_tables | 256 |
| Open_files | 459 |
| Open_streams | 0 |
| Opened_tables | 3532 |
Where 256 is the table_cache limit. I'd consider the opened tables to
be big, and would probably want to increase the table_cache size.
4) How can I reliably determine how much RAM MySQL is indeed using?
>From the manual:
"ps and other system status programs may
report that mysqld uses a lot of memory"
ps shows each instance of MySQL using 91760k of RAM x ~40 processes =
3.6GB, which is how much real RAM I have. The rest of the swap is
probably due to the rest of the system processes running.
To further assistance here, here are my settings for the master, which
is the DB that's swapping:
First, we start with variables...
back_log | 50
basedir | /usr/local/mysql/
binlog_cache_size | 32768
character_set | latin1
character_sets | latin1 dec8 dos german1 hp8 koi8_ru
latin2 swe7 usa7 cp1251 danish hebrew win1251 esto
nia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5
concurrent_insert | ON
connect_timeout | 5
datadir | /usr/local/mysql//var/
delay_key_write | ON
delayed_insert_limit | 100
delayed_insert_timeout | 300
delayed_queue_size | 1000
flush | OFF
flush_time | 0
have_bdb | NO
have_gemini | NO
have_innodb | NO
have_isam | YES
have_raid | NO
have_ssl | NO
init_file |
interactive_timeout | 28800
join_buffer_size | 131072
key_buffer_size | 805302272
language | /usr/local/mysql/share/mysql/english/
large_files_support | ON
locked_in_memory | OFF
log | OFF
log_update | OFF
log_bin | ON
log_slave_updates | OFF
log_long_queries | OFF
long_query_time | 10
low_priority_updates | OFF
lower_case_table_names | 0
max_allowed_packet | 10484736
max_binlog_cache_size | 4294967295
max_binlog_size | 1073741824
max_connections | 150
max_connect_errors | 10
max_delayed_threads | 20
max_heap_table_size | 16777216
max_join_size | 4294967295
max_sort_length | 1024
max_user_connections | 0
max_tmp_tables | 32
max_write_lock_count | 4294967295
myisam_recover_options | 0
myisam_max_extra_sort_file_size | 256
myisam_max_sort_file_size | 2047
myisam_sort_buffer_size | 67108864
net_buffer_length | 16384
net_read_timeout | 30
net_retry_count | 10
net_write_timeout | 60
open_files_limit | 0
port | 3306
protocol_version | 10
record_buffer | 2093056
record_rnd_buffer | 2093056
query_buffer_size | 0
safe_show_database | OFF
server_id | 1
slave_net_timeout | 3600
skip_locking | ON
skip_networking | OFF
skip_show_database | OFF
slow_launch_time | 2
socket | /tmp/mysql.sock
sort_buffer | 2097144
sql_mode | 0
table_cache | 512
table_type | MYISAM
thread_cache_size | 8
thread_stack | 65536
transaction_isolation | READ-COMMITTED
timezone | CDT
tmp_table_size | 33554432
tmpdir | /tmp/
version | 3.23.42-log
wait_timeout | 28800
Now we go to extended-status:
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 3812 |
| Aborted_connects | 45294 |
| Bytes_received | 730613037 |
| Bytes_sent | 4013231198 |
| Connections | 7981869 |
| Created_tmp_disk_tables | 8590 |
| Created_tmp_tables | 16402 |
| Created_tmp_files | 50 |
| Delayed_insert_threads | 1 |
| Delayed_writes | 11156016 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 15398143 |
| Handler_read_first | 4561849 |
| Handler_read_key | 78715268 |
| Handler_read_next | 2518057153 |
| Handler_read_prev | 2759123 |
| Handler_read_rnd | 51014466 |
| Handler_read_rnd_next | 639269479 |
| Handler_update | 6824117 |
| Handler_write | 25044236 |
| Key_blocks_used | 433327 |
| Key_read_requests | 487346581 |
| Key_reads | 287506 |
| Key_write_requests | 48832063 |
| Key_writes | 39747681 |
| Max_used_connections | 150 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 512 |
| Open_files | 776 |
| Open_streams | 0 |
| Opened_tables | 1500 |
| Questions | 96797568 |
| Select_full_join | 2062 |
| Select_full_range_join | 0 |
| Select_range | 1641042 |
| Select_range_check | 0 |
| Select_scan | 745637 |
| Slave_running | ON |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 14 |
| Slow_queries | 1975 |
| Sort_merge_passes | 25 |
| Sort_range | 1038190 |
| Sort_rows | 48617052 |
| Sort_scan | 253737 |
| Table_locks_immediate | 87007001 |
| Table_locks_waited | 179956 |
| Threads_cached | 2 |
| Threads_created | 25669 |
| Threads_connected | 36 |
| Threads_running | 11 |
| Uptime | 2020009 |
+--------------------------+------------+
And lastly, free..
total used free shared buffers
cached
Mem: 3703180 3694624 8556 0 12800
2158160
-/+ buffers/cache: 1523664 2179516
Swap: 2096440 1582784 513656
I certainly appreciate the help.
-J
---------------------------------------------------------------------
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
