Hi list,
Since we bought a better hardware for our dedicated MySQL Server we
have been running into some problems.
We are using:
- Fedora Core 3 - 64bit version
- Kernel: 2.6.9-1.667smp - x86_64
- MySQL 5.0.21-standard - for 64bit
- RAM: 4 GB
- RAID 5 matrix with 3 SCSI disks at 15k rotations
We are using InnoDB tables (with one or 2 exceptions... for some
FullText indexes)...
We are not using transactions... yet !
I'll drop config. details lower...
Still at given moments MySQL hangs... it does not accept connections anymore...
We can't kill the process... with KILL command... the only thing we
can do is ask the hosting provider to do a HARDWARE reset... and
someone goes to the machine and pushes the reset button... this hppens
at least once a week...
Does this happened to someone else ?
What was the problem ?
Thanx in advance !
--
Gabriel PREDA
Senior Web Developer
-----------------------------------------------------------------------------------------------------
------------------- CONFIG DATA
----------------------------------------------------------
-----------------------------------------------------------------------------------------------------
- PROCESORS from 0 to 7:
processor : 0 (up to processor 7 the specs. are the same)
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 8
cpu MHz : 2793.261
cache size : 16 KB
physical id : 0
siblings : 4
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov
pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm
pni monitor ds_cpl est cid cx16 xtpr ts
bogomips : 5537.79
clflush size : 64
cache_alignment : 128
address sizes : 36 bits physical, 48 bits virtual
TOP output:
top - 00:59:44 up 10:14, 1 user, load average: 0.46, 0.54, 0.64
Tasks: 82 total, 1 running, 81 sleeping, 0 stopped, 0 zombie
Cpu0 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si
Cpu1 : 0.3% us, 0.3% sy, 0.0% ni, 99.3% id, 0.0% wa, 0.0% hi, 0.0% si
Cpu2 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si
Cpu3 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si
Cpu4 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si
Cpu5 : 6.3% us, 1.7% sy, 0.0% ni, 91.4% id, 0.0% wa, 0.3% hi, 0.3% si
Cpu6 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si
Cpu7 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si
Mem: 4038412k total, 1614864k used, 242
Mem: 4038412k total, 1617176k used, 2421236k free, 135060k buffers
Swap: 2048276k total, 0k used, 2048276k free, 367664k cached
MY.CNF:
[mysqld]
set-variable = max_connections=900
safe-show-database
set-variable = max_heap_table_size=64M
set-variable = tmp_table_size=64M
set-variable = query-cache-size=500M
set-variable = query_cache_limit=30M
set-variable = long_query_time=3
set-variable = table_cache=600
set-variable = thread_cache_size=32
set-variable = thread_concurrency=8
set-variable = key_buffer_size=32M
set-variable = interactive_timeout=60
set-variable = wait_timeout=60
set-variable = max_allowed_packet=3M
set-variable = sort_buffer_size=6M
set-variable = ft_min_word_len=3
set-variable = binlog_cache_size=0
set-variable = innodb_additional_mem_pool_size=20M
set-variable = innodb_thread_concurrency=8
set-variable = innodb_buffer_pool_size=400M
set-variable = innodb_flush_log_at_trx_commit=0
set-variable = innodb_autoextend_increment=50M
set-variable = innodb_fast_shutdown=0
set-variable = innodb_log_buffer_size=4M
set-variable = innodb_max_dirty_pages_pct=75
set-variable = innodb_status_file
SHOW VARIABLES:
+---------------------------------+-----------------------------------------------+
| Variable_name | Value
|
+---------------------------------+-----------------------------------------------+
| automatic_sp_privileges | ON
|
| back_log | 50
|
| basedir | /
|
| binlog_cache_size | 4096
|
| bulk_insert_buffer_size | 8388608
|
| completion_type | 0
|
| concurrent_insert | 1
|
| connect_timeout | 5
|
| delay_key_write | ON
|
| delayed_insert_limit | 100
|
| delayed_insert_timeout | 300
|
| delayed_queue_size | 1000
|
| div_precision_increment | 4
|
| engine_condition_pushdown | OFF
|
| expire_logs_days | 0
|
| flush | OFF
|
| flush_time | 0
|
| group_concat_max_len | 1024
|
| have_archive | YES
|
| have_compress | YES
|
| have_crypt | YES
|
| have_geometry | YES
|
| have_innodb | YES
|
| have_query_cache | YES
|
| have_rtree_keys | YES
|
| have_symlink | YES
|
| innodb_additional_mem_pool_size | 20971520
|
| innodb_autoextend_increment | 1000
|
| innodb_buffer_pool_awe_mem_mb | 0
|
| innodb_buffer_pool_size | 419430400
|
| innodb_checksums | ON
|
| innodb_commit_concurrency | 0
|
| innodb_concurrency_tickets | 500
|
| innodb_data_file_path | ibdata1:10M:autoextend
|
| innodb_data_home_dir |
|
| innodb_doublewrite | ON
|
| innodb_fast_shutdown | 0
|
| innodb_file_io_threads | 4
|
| innodb_file_per_table | OFF
|
| innodb_flush_log_at_trx_commit | 0
|
| innodb_flush_method |
|
| innodb_force_recovery | 0
|
| innodb_lock_wait_timeout | 50
|
| innodb_locks_unsafe_for_binlog | OFF
|
| innodb_log_arch_dir |
|
| innodb_log_archive | OFF
|
| innodb_log_buffer_size | 4194304
|
| innodb_log_file_size | 5242880
|
| innodb_log_files_in_group | 2
|
| innodb_log_group_home_dir | ./
|
| innodb_max_dirty_pages_pct | 75
|
| innodb_max_purge_lag | 0
|
| innodb_mirrored_log_groups | 1
|
| innodb_open_files | 300
|
| innodb_support_xa | ON
|
| innodb_sync_spin_loops | 20
|
| innodb_table_locks | ON
|
| innodb_thread_concurrency | 8
|
| innodb_thread_sleep_delay | 10000
|
| interactive_timeout | 60
|
| join_buffer_size | 131072
|
| key_buffer_size | 33554432
|
| key_cache_age_threshold | 300
|
| key_cache_block_size | 1024
|
| key_cache_division_limit | 100
|
| large_files_support | ON
|
| large_page_size | 0
|
| large_pages | OFF
|
| local_infile | ON
|
| locked_in_memory | OFF
|
| log | OFF
|
| log_bin | OFF
|
| log_bin_trust_function_creators | OFF
|
| log_error |
|
| log_slave_updates | OFF
|
| log_slow_queries | OFF
|
| log_warnings | 1
|
| long_query_time | 3
|
| low_priority_updates | OFF
|
| max_allowed_packet | 3144704
|
| max_binlog_cache_size | 18446744073709551615
|
| max_binlog_size | 1073741824
|
| max_connect_errors | 10
|
| max_connections | 900
|
| max_delayed_threads | 20
|
| max_error_count | 64
|
| max_heap_table_size | 67107840
|
| max_insert_delayed_threads | 20
|
| max_join_size | 18446744073709551615
|
| max_length_for_sort_data | 1024
|
| max_prepared_stmt_count | 16382
|
| max_relay_log_size | 0
|
| max_seeks_for_key | 18446744073709551615
|
| max_sort_length | 1024
|
| max_sp_recursion_depth | 0
|
| max_tmp_tables | 32
|
| max_user_connections | 0
|
| max_write_lock_count | 18446744073709551615
|
| multi_range_count | 256
|
| myisam_data_pointer_size | 6
|
| myisam_max_sort_file_size | 9223372036854775807
|
| myisam_recover_options | OFF
|
| myisam_repair_threads | 1
|
| myisam_sort_buffer_size | 8388608
|
| myisam_stats_method | nulls_unequal
|
| net_buffer_length | 16384
|
| net_read_timeout | 30
|
| net_retry_count | 10
|
| net_write_timeout | 60
|
| new | OFF
|
| old_passwords | OFF
|
| open_files_limit | 4510
|
| optimizer_prune_level | 1
|
| optimizer_search_depth | 62
|
| pid_file | /var/lib/mysql/db02.pid
|
| prepared_stmt_count | 0
|
| port | 3306
|
| preload_buffer_size | 32768
|
| protocol_version | 10
|
| query_alloc_block_size | 8192
|
| query_cache_limit | 31457280
|
| query_cache_min_res_unit | 4096
|
| query_cache_size | 524288000
|
| query_cache_type | ON
|
| query_cache_wlock_invalidate | OFF
|
| query_prealloc_size | 8192
|
| range_alloc_block_size | 2048
|
| read_buffer_size | 131072
|
| read_only | OFF
|
| read_rnd_buffer_size | 262144
|
| relay_log_purge | ON
|
| relay_log_space_limit | 0
|
| rpl_recovery_rank | 0
|
| secure_auth | OFF
|
| server_id | 0
|
| skip_external_locking | ON
|
| skip_networking | OFF
|
| skip_show_database | OFF
|
| slow_launch_time | 2
|
| socket | /var/lib/mysql/mysql.sock
|
| sort_buffer_size | 6291448
|
| sql_mode |
|
| sql_notes | ON
|
| sql_warnings | ON
|
| storage_engine | MyISAM
|
| sync_binlog | 0
|
| sync_frm | ON
|
| system_time_zone | MST
|
| table_cache | 600
|
| table_lock_wait_timeout | 50
|
| table_type | MyISAM
|
| thread_cache_size | 32
|
| thread_stack | 262144
|
| time_format | %H:%i:%s
|
| time_zone | SYSTEM
|
| timed_mutexes | OFF
|
| tmp_table_size | 67108864
|
| tmpdir |
|
| transaction_alloc_block_size | 8192
|
| transaction_prealloc_size | 4096
|
| tx_isolation | REPEATABLE-READ
|
| updatable_views_with_limit | YES
|
| version | 5.0.21-standard
|
| version_comment | MySQL Community Edition - Standard
(GPL) |
| version_compile_machine | x86_64
|
| version_compile_os | unknown-linux-gnu
|
| wait_timeout | 60
|
+---------------------------------+-----------------------------------------------+
SHOW STATUS:
Variable_name Value
Aborted_clients 100753
Aborted_connects 1
Bytes_received 78
Bytes_sent 76
Compression OFF
Connections 343641
Created_tmp_disk_tables 0
Created_tmp_files 330
Created_tmp_tables 1
Delayed_errors 0
Delayed_insert_threads 0
Delayed_writes 0
Flush_commands 1
Innodb_buffer_pool_pages_data 14663
Innodb_buffer_pool_pages_dirty 52
Innodb_buffer_pool_pages_flushed 27043
Innodb_buffer_pool_pages_free 10548
Innodb_buffer_pool_pages_latched 2
Innodb_buffer_pool_pages_misc 389
Innodb_buffer_pool_pages_total 25600
Innodb_buffer_pool_read_ahead_rnd 7
Innodb_buffer_pool_read_ahead_seq 97
Innodb_buffer_pool_read_requests 1865339266
Innodb_buffer_pool_reads 9478
Innodb_buffer_pool_wait_free 0
Innodb_buffer_pool_write_requests 97431
Innodb_data_fsyncs 16857
Innodb_data_pending_fsyncs 0
Innodb_data_pending_reads 0
Innodb_data_pending_writes 0
Innodb_data_read 241750016
Innodb_data_reads 10072
Innodb_data_writes 36074
Innodb_data_written 895450112
Innodb_dblwr_pages_written 27043
Innodb_dblwr_writes 3674
Innodb_log_waits 0
Innodb_log_write_requests 9569
Innodb_log_writes 6490
Innodb_os_log_fsyncs 9506
Innodb_os_log_pending_fsyncs 0
Innodb_os_log_pending_writes 0
Innodb_os_log_written 7760384
Innodb_page_size 16384
Innodb_pages_created 41
Innodb_pages_read 14622
Innodb_pages_written 27043
Innodb_row_lock_current_waits 0
Innodb_row_lock_time 0
Innodb_row_lock_time_avg 0
Innodb_row_lock_time_max 0
Innodb_row_lock_waits 0
Innodb_rows_deleted 397
Innodb_rows_inserted 5497
Innodb_rows_read 1730152157
Innodb_rows_updated 9485
Key_blocks_not_flushed 0
Key_blocks_unused 18519
Key_blocks_used 8514
Key_read_requests 107424953
Key_reads 231661
Key_write_requests 18975369
Key_writes 18544
Last_query_cost 0.000000
Max_used_connections 202
Not_flushed_delayed_rows 0
Open_files 116
Open_streams 0
Open_tables 335
Opened_tables 0
Qcache_free_blocks 7737
Qcache_free_memory 482530304
Qcache_hits 6020261
Qcache_inserts 411651
Qcache_lowmem_prunes 0
Qcache_not_cached 295697
Qcache_queries_in_cache 19782
Qcache_total_blocks 48210
Questions 19314726
Rpl_status NULL
Select_full_join 0
Select_full_range_join 0
Select_range 0
Select_range_check 0
Select_scan 1
Slow_launch_threads 0
Slow_queries 0
Sort_merge_passes 0
Sort_range 0
Sort_rows 0
Sort_scan 0
Table_locks_immediate 1092432
Table_locks_waited 836
Tc_log_max_pages_used 0
Tc_log_page_size 0
Tc_log_page_waits 0
Threads_cached 21
Threads_connected 116
Threads_created 2710
Threads_running 2
Uptime 36881
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
