Re: Server just hanging (More Information)

[Thomas Deliduka]

Thank you so much Erin for your input! Here is what I know and don't know. Yes, they are all MyIASM I don't think we have any innodb.

1. Based on your e-mail I changed some of my vars but not all because I didn't understand.

set-variable = max_connections=300
set-variable = tmp_table_size=500M
set-variable = query_cache_size=100M
set-variable = query_cache_limit=3M
set-variable = key_buffer_size=396M
set-variable = query_buffer_cache_size=164M
set-variable = table_cache=2400
set-variable = sort_buffer_size=16M
set-variable = read_buffer_size=5M
set-variable = wait_timeout=15

You mentioned my "Memory Buffers" should be global buffers + (thread_buffers * max_connections) but I don't know which concrete variables are memory buffers or thread_buffers. I see global buffers are key_buffer_size and query_cache_size but are my thread buffers read_buffer_size, sort_buffer_size... ? What should I set those to? Are 5mb and 16mb fine? Is it whatever I want?

Should I be adding up the buffers to get the number "thread_buffers" that is multiplied with max_connections?

Sorry I'm such a dolt, I simply am a novice at tweaking an sql server. I can do queries all day!

Regarding my Kernel. I know you can't really help me with this but I wanted to give you my experience with this and perhaps you recognize what needs to be done.

If I type: "ulimit" I get "unlimited"

However sysctl says the values are:
kern.maxproc=2048
kern.maxprocperuid=1000

According to OS X documentation within the /etc/sysctl-macosxserver.conf file it says:

# If you wish to override these or any other sysctl settings, create /etc/sysctl.conf
# and put your settings there, not here.
#
# The /etc/rc script first processes /etc/sysctl-macosxserver.conf, then /etc/sysctl.conf

However, editing /etc/sysctl.conf and rebooting doesn't really fix anything at all, the defaults still exist.

What's worse is if I use sysctl to manually change the values the max I can change both of those to are 2068 I can't go any higher (strange number to have for a ceiling). I am completely confused by this. I went through this with my Web Server having too many files open and so I tried to fix it and failed.

What would you suggest for custom variables using a machine with Dual 2Ghz G5's (say equivalent to Dual 3.2Ghz Xeons) and 4GB of RAM? Assuming I have unlimited on my procs.

My server has been running since this morning when my boss complained again that his site was slow. Here are the outputs:

Variables:
back_log 50
basedir /usr/local/mysql-standard-4.0.25-apple-darwin7.9.0-powerpc/
binlog_cache_size 32768
bulk_insert_buffer_size 8388608
character_set latin1
character_sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5
concurrent_insert ON
connect_timeout 5
convert_character_set
datadir /volumes/data/mysql/
default_week_format 0
delay_key_write ON
delayed_insert_limit 100
delayed_insert_timeout 300
delayed_queue_size 1000
flush OFF
flush_time 0
ft_boolean_syntax + -><()~*:""&
ft_min_word_len 4
ft_max_word_len 254
ft_max_word_len_for_sort 20
ft_stopword_file (built-in)
have_bdb NO
have_crypt YES
have_innodb YES
have_isam YES
have_raid NO
have_symlink YES
have_openssl NO
have_query_cache YES
init_file
innodb_additional_mem_pool_size 1048576
innodb_autoextend_increment 8
innodb_buffer_pool_size 8388608
innodb_data_file_path ibdata1:10M:autoextend
innodb_data_home_dir
innodb_file_io_threads 4
innodb_force_recovery 0
innodb_thread_concurrency 8
innodb_flush_log_at_trx_commit 1
innodb_fast_shutdown ON
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
innodb_max_purge_lag 0
innodb_table_locks ON
interactive_timeout 28800
join_buffer_size 131072
key_buffer_size 104857600
language /usr/local/mysql-standard-4.0.25-apple-darwin7.9.0-powerpc/share/mysql/english/
large_files_support ON
license GPL
local_infile ON
locked_in_memory OFF
log OFF
log_update OFF
log_bin OFF
log_slave_updates OFF
log_slow_queries OFF
log_warnings 1
long_query_time 10
low_priority_updates OFF
lower_case_file_system ON
lower_case_table_names 2
max_allowed_packet 1048576
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connections 1000
max_connect_errors 10
max_delayed_threads 20
max_insert_delayed_threads 20
max_heap_table_size 16777216
max_join_size 4294967295
max_relay_log_size 0
max_seeks_for_key 4294967295
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 8388608
net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
new ON
open_files_limit 12288
pid_file /volumes/data/mysql/luke.xenocast.com.pid
log_error
port 3306
protocol_version 10
query_alloc_block_size 8192
query_cache_limit 3145728
query_cache_size 104857600
query_cache_type ON
query_cache_wlock_invalidate OFF
query_prealloc_size 8192
range_alloc_block_size 2048
read_buffer_size 5238784
read_only OFF
read_rnd_buffer_size 262144
rpl_recovery_rank 0
server_id 0
slave_net_timeout 3600
skip_external_locking ON
skip_networking OFF
skip_show_database OFF
slow_launch_time 2
socket /tmp/mysql.sock
sort_buffer_size 16777208
sql_mode 0
table_cache 5639
table_type MYISAM
thread_cache_size 0
thread_stack 196608
tx_isolation REPEATABLE-READ
timezone EDT
tmp_table_size 524288000
tmpdir /var/tmp/
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
version 4.0.25-standard
version_comment Official MySQL-standard binary
version_compile_os apple-darwin7.9.0
wait_timeout 28800

Status:
Aborted_clients 0
Aborted_connects 0
Bytes_received 391056046
Bytes_sent 1568337608
Com_admin_commands 568
Com_alter_table 1
Com_analyze 0
Com_backup_table 0
Com_begin 158
Com_change_db 43980
Com_change_master 0
Com_check 0
Com_commit 158
Com_create_db 0
Com_create_function 0
Com_create_index 0
Com_create_table 0
Com_delete 1944
Com_delete_multi 0
Com_drop_db 0
Com_drop_function 0
Com_drop_index 0
Com_drop_table 0
Com_flush 0
Com_grant 0
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_insert 9613
Com_insert_select 4
Com_kill 0
Com_load 2
Com_load_master_data 0
Com_load_master_table 0
Com_lock_tables 0
Com_optimize 1
Com_purge 0
Com_rename_table 0
Com_repair 0
Com_replace 9041
Com_replace_select 0
Com_reset 0
Com_restore_table 0
Com_revoke 0
Com_rollback 0
Com_savepoint 0
Com_select 161696
Com_set_option 2
Com_show_binlog_events 0
Com_show_binlogs 0
Com_show_create 0
Com_show_databases 0
Com_show_fields 5
Com_show_grants 0
Com_show_keys 0
Com_show_logs 0
Com_show_master_status 0
Com_show_new_master 0
Com_show_open_tables 0
Com_show_processlist 63
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 25079
Com_show_innodb_status 25077
Com_show_tables 1
Com_show_variables 3
Com_slave_start 0
Com_slave_stop 0
Com_truncate 0
Com_unlock_tables 0
Com_update 101747
Com_update_multi 0
Connections 32778
Created_tmp_disk_tables 190
Created_tmp_tables 25568
Created_tmp_files 3
Delayed_insert_threads 0
Delayed_writes 0
Delayed_errors 0
Flush_commands 1
Handler_commit 0
Handler_delete 8466
Handler_read_first 40853
Handler_read_key 18083378
Handler_read_next 75098150
Handler_read_prev 6706
Handler_read_rnd 1242106
Handler_read_rnd_next 65951595
Handler_rollback 0
Handler_update 6509268
Handler_write 6517327
Key_blocks_used 23681
Key_read_requests 32873482
Key_reads 22742
Key_write_requests 103563
Key_writes 57977
Max_used_connections 40
Not_flushed_key_blocks 0
Not_flushed_delayed_rows 0
Open_tables 931
Open_files 1790
Open_streams 0
Opened_tables 941
Questions 2637105
Qcache_queries_in_cache 39073
Qcache_inserts 149889
Qcache_hits 2224843
Qcache_lowmem_prunes 0
Qcache_not_cached 11809
Qcache_free_memory 43640264
Qcache_free_blocks 1295
Qcache_total_blocks 80208
Rpl_status NULL
Select_full_join 413
Select_full_range_join 0
Select_range 2555
Select_range_check 7
Select_scan 44739
Slave_open_temp_tables 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 2
Sort_merge_passes 0
Sort_range 13893
Sort_rows 1531567
Sort_scan 29329
Table_locks_immediate 358469
Table_locks_waited 513
Threads_cached 0
Threads_created 32777
Threads_connected 17
Threads_running 1
Uptime 25079

I know enough to be dangerous. Thanks for being patient!