MySQL Forums
Forum List  »  Performance

Re: Server just hanging (More Information)
Posted by: Thomas Deliduka
Date: August 24, 2005 04:05PM

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!

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Server just hanging (More Information)
12715
August 24, 2005 04:05PM
8011
December 07, 2006 10:30AM
9388
September 23, 2005 01:56AM
4688
October 05, 2005 04:58AM
5843
October 05, 2005 11:49PM
5183
October 06, 2005 01:34AM
6405
October 06, 2005 04:57PM
6215
January 21, 2008 10:14AM
5634
January 21, 2008 10:53AM
5065
May 19, 2008 09:41AM
5322
February 05, 2008 10:27AM
5801
March 12, 2008 08:37AM
5903
November 14, 2008 12:31AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.