Hi,
Please provide the log extracts from /var/log/mysql_error.log in your
box when the crashes occurred and also you havent mentioned the table
types thats you are using. MyISAM tables are the deafult for your
configuration. This table type is not recommeded for highly concurrent
usage. If you are using this table type increase the key_buffer_size.
If you are using innodb tables increase Innodb_buffer_pool_size, it is set
to a very low 8MB.
Please follow the link
http://dev.mysql.com/doc/refman/5.1/en/server-parameters.html. Pay
attention to the last 4 paragraphs before user comments.
Hope this helps.
Thanx
Alex
On Wed, 04 Jan 2006 15:25:39 +0530, Jonathan Chong <[EMAIL PROTECTED]>
wrote:
Hi Alex
Thanks for this, and sorry for the late reply.
That was the entire my.cnf file in my last email. This is from SHOW
VARIABLES in mysql.
+---------------------------------+------------------------------------------+
| Variable_name |
Value |
+---------------------------------+------------------------------------------+
| auto_increment_increment |
1 |
| auto_increment_offset |
1 |
| automatic_sp_privileges |
ON |
| back_log |
50 |
| basedir |
/ |
| binlog_cache_size |
32768 |
| bulk_insert_buffer_size |
8388608 |
| character_set_client |
latin1 |
| character_set_connection |
latin1 |
| character_set_database |
latin1 |
| character_set_results |
latin1 |
| character_set_server |
latin1 |
| character_set_system |
utf8 |
| character_sets_dir |
/usr/share/mysql/charsets/ |
| collation_connection |
latin1_swedish_ci |
| collation_database |
latin1_swedish_ci |
| collation_server |
latin1_swedish_ci |
| completion_type |
0 |
| concurrent_insert |
1 |
| connect_timeout |
5 |
| datadir |
/var/lib/mysql/ |
| date_format |
%Y-%m-%d |
| datetime_format | %Y-%m-%d
%H:%i:%s |
| default_week_format |
0 |
| 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 |
| ft_boolean_syntax |
+ -><()~*:""&| |
| ft_max_word_len |
84 |
| ft_min_word_len |
4 |
| ft_query_expansion_limit |
20 |
| ft_stopword_file |
(built-in) |
| group_concat_max_len |
1024 |
| have_archive |
YES |
| have_bdb |
NO |
| have_blackhole_engine |
NO |
| have_compress |
YES |
| have_crypt |
YES |
| have_csv |
NO |
| have_example_engine |
NO |
| have_federated_engine |
NO |
| have_geometry |
YES |
| have_innodb |
YES |
| have_isam |
NO |
| have_ndbcluster |
NO |
| have_openssl |
NO |
| have_query_cache |
YES |
| have_raid |
NO |
| have_rtree_keys |
YES |
| have_symlink |
YES |
| init_connect
| |
| init_file
| |
| init_slave
| |
| innodb_additional_mem_pool_size |
1048576 |
| innodb_autoextend_increment |
8 |
| innodb_buffer_pool_awe_mem_mb |
0 |
| innodb_buffer_pool_size |
8388608 |
| 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 |
1 |
| innodb_file_io_threads |
4 |
| innodb_file_per_table |
OFF |
| innodb_flush_log_at_trx_commit |
1 |
| 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 |
1048576 |
| innodb_log_file_size |
5242880 |
| innodb_log_files_in_group |
2 |
| innodb_log_group_home_dir |
./ |
| innodb_max_dirty_pages_pct |
90 |
| 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 |
20 |
| innodb_thread_sleep_delay |
10000 |
| interactive_timeout |
28800 |
| join_buffer_size |
131072 |
| key_buffer_size |
8388600 |
| key_cache_age_threshold |
300 |
| key_cache_block_size |
1024 |
| key_cache_division_limit |
100 |
| language |
/usr/share/mysql/english/ |
| large_files_support |
ON |
| large_page_size |
0 |
| large_pages |
OFF |
| license |
GPL |
| local_infile |
ON |
| locked_in_memory |
OFF |
| log |
OFF |
| log_bin |
OFF |
| log_bin_trust_function_creators |
OFF |
| log_error |
/var/log/mysql_error.log |
| log_slave_updates |
OFF |
| log_slow_queries |
ON |
| log_warnings |
1 |
| long_query_time |
5 |
| low_priority_updates |
OFF |
| lower_case_file_system |
OFF |
| lower_case_table_names |
0 |
| max_allowed_packet |
1048576 |
| max_binlog_cache_size |
18446744073709551615 |
| max_binlog_size |
1073741824 |
| max_connect_errors |
10 |
| max_connections |
500 |
| max_delayed_threads |
20 |
| max_error_count |
64 |
| max_heap_table_size |
16777216 |
| max_insert_delayed_threads |
20 |
| max_join_size |
18446744073709551615 |
| max_length_for_sort_data |
1024 |
| 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 |
350 |
| 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 |
ON |
| open_files_limit |
2510 |
| optimizer_prune_level |
1 |
| optimizer_search_depth |
62 |
| pid_file |
/var/lib/mysql/chongnet1.pid |
| port |
3306 |
| preload_buffer_size |
32768 |
| protocol_version |
10 |
| query_alloc_block_size |
8192 |
| query_cache_limit |
1048576 |
| query_cache_min_res_unit |
4096 |
| query_cache_size |
20971520 |
| 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 |
| slave_compressed_protocol |
OFF |
| slave_load_tmpdir |
/tmp/ |
| slave_net_timeout |
3600 |
| slave_skip_errors |
OFF |
| slave_transaction_retries |
10 |
| slow_launch_time |
2 |
| socket |
/var/lib/mysql/mysql.sock |
| sort_buffer_size |
2097144 |
| sql_mode
| |
| sql_notes |
ON |
| sql_warnings |
ON |
| storage_engine |
MyISAM |
| sync_binlog |
0 |
| sync_frm |
ON |
| sync_replication |
0 |
| sync_replication_slave_id |
0 |
| sync_replication_timeout |
10 |
| system_time_zone |
GMT |
| table_cache |
64 |
| table_lock_wait_timeout |
50 |
| table_type |
MyISAM |
| thread_cache_size |
40 |
| thread_stack |
262144 |
| time_format |
%H:%i:%s |
| time_zone |
SYSTEM |
| timed_mutexes |
OFF |
| tmp_table_size |
33554432 |
| tmpdir
| |
| transaction_alloc_block_size |
8192 |
| transaction_prealloc_size |
4096 |
| tx_isolation |
REPEATABLE-READ |
| updatable_views_with_limit |
YES |
| version |
5.0.17-standard-log |
| version_comment | MySQL Community Edition - Standard
(GPL) |
| version_compile_machine |
x86_64 |
| version_compile_os |
unknown-linux-gnu |
| wait_timeout |
28800 |
+---------------------------------+------------------------------------------+
I can't seem to find mysqld.err - sorry, but my knowledge about this
is limited. Please let me know where it could be and I'll post the
contents here.
On 04/01/06, Alex <[EMAIL PROTECTED]> wrote:
Hi,
Please provide details like what tables are you using, the entire
my.cnf
and the information from the mysqld.err when the crashes occurred.
--Alex
On Wed, 04 Jan 2006 06:11:24 +0530, Jonathan Chong <[EMAIL PROTECTED]>
wrote:
> Hi everyone
>
> I run a relatively popular forum that gets overloaded on match days.
> On normal days, the site is coping fine, but when there are plenty of
> users on the forum (about 50-70 odd) the load goes up and the site
> crawls, before crashing, and the server rebooted
>
> The forum software is phpBB.
>
> The URL is: http://www.arsenal-mania.com/
>
> On normal days though with about the same amount of users, the site is
> fine.
>
> The site is running on:
>
> AMD Athlon 64 3000+
> 1GB 400MHz DDR RAM
> CentOS 3.62
> mySQL 5.0.17
>
> I am assuming that the reason the site is slowing down is because when
> a match ends, people are logging on to the site to check full-time
> scores, and this creates a number of database connections
> simultaneously.
>
> This is what I have in my my.cnf file:
>
> [mysqld]
> set-variable = max_connections=500
> set-variable = max_user_connections=350
> set-variable = long_query_time=5
> set-variable = thread_cache_size=40
> set-variable = wait_timeout=20
> query_cache_size = 20M
> safe-show-database
> log_error = /var/log/mysql_error.log
> log-slow-queries = /var/log/mysql_slow_queries.log
>
> Short of increasing the amount of physical RAM, I am hoping to explore
> configuration options beforehand. If anyone has any ideas I can try
> out, I'd appreciate it if you could please let me know.
>
> I can provide more stats if necessary!
>
> Thanks in advance.
>
> --
> Jonathan Chong
>
> http://www.arsenal-now.com/
> http://www.arsenal-mania.com/
> http://www.ashburrn.com/
> http://www.jonathan-chong.com/
>
--
Jonathan Chong
http://www.arsenal-now.com/
http://www.arsenal-mania.com/
http://www.ashburrn.com/
http://www.jonathan-chong.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
