Leinad
October 4, 2007, 2:57pm
1
I have this game (vaperida.com ) which usually has btween 30 and 100 users online at everytimes.
our host is telling us that mysql is takin’ way too much resources.
I ran a show status in mysql and here are the results
can you tell me if you see anything abnormal there?? cause I have no idea
Variable_name Value
regards )
sterin
October 4, 2007, 4:54pm
2
[B]Leinad wrote on Thu, 04 October 2007 22:27[/B]
our host is telling us that mysql is takin’ way too much resources.
And what does that mean exactly?
CPU heavy?
Consumes a lot of RAM?
That they don't want a heavy site running on their server?
Because it looks like you have a popular site there.
Leinad
October 6, 2007, 12:56am
3
first…thank you
and it means it consumes a lot of RAM (from their point of view)
(our server consumes like %40 of the total RAM right now (4gb))
regards…anything else? (really appreciated)
sterin
October 6, 2007, 3:55am
4
Can you post the output from SHOW VARIABLES instead?
Leinad
October 6, 2007, 4:49am
5
there ya go
Variable_name Valueback_log 50basedir /binlog_cache_size 32768bulk_insert_buffer_size 8388608character_set_client utf8character_set_connection utf8character_set_database latin1character_set_results utf8character_set_server latin1character_set_system utf8character_sets_dir /usr/share/mysql/charsets/collation_connection utf8_unicode_cicollation_database latin1_swedish_cicollation_server latin1_swedish_ciconcurrent_insert ONconnect_timeout 5datadir /var/lib/mysql/date_format %Y-%m-%ddatetime_format %Y-%m-%d %H:%i:%sdefault_week_format 0delay_key_write ONdelayed_insert_limit 100delayed_insert_timeout 300delayed_queue_size 1000expire_logs_days 0flush OFFflush_time 0ft_boolean_syntax + -><()~*:“”&|ft_max_word_len 84ft_min_word_len 4ft_query_expansion_limit 20ft_stopword_file (built-in)group_concat_max_len 1024have_archive NOhave_bdb NOhave_blackhole_engine NOhave_compress YEShave_crypt YEShave_csv NOhave_example_engine NOhave_geometry YEShave_innodb YEShave_isam NOhave_merge_engine YEShave_ndbcluster NOhave_openssl NOhave_query_cache YEShave_raid NOhave_rtree_keys YEShave_symlink YESinit_connect init_file init_slave innodb_additional_mem_pool_size 1048576innodb_autoextend_increment 8innodb_buffer_pool_awe_mem_mb 0innodb_buffer_pool_size 8388608innodb_data_file_path ibdata1:10M:autoextendinnodb_data_home_dir innodb_fast_shutdown ONinnodb_file_io_threads 4innodb_file_per_table OFFinnodb_flush_log_at_trx_commit 1innodb_flush_method innodb_force_recovery 0innodb_lock_wait_timeout 50innodb_locks_unsafe_for_binlog OFFinnodb_log_arch_dir innodb_log_archive OFFinnodb_log_buffer_size 1048576innodb_log_file_size 5242880innodb_log_files_in_group 2innodb_log_group_home_dir ./innodb_max_dirty_pages_pct 90innodb_max_purge_lag 0innodb_mirrored_log_groups 1innodb_open_files 300innodb_table_locks ONinnodb_thread_concurrency 8interactive_timeout 28800join_buffer_size 131072key_buffer_size 402653184key_cache_age_threshold 300key_cache_block_size 1024key_cache_division_limit 100language /usr/share/mysql/english/large_files_support ONlc_time_names en_USlicense GPLlocal_infile ONlocked_in_memory OFFlog OFFlog_bin OFFlog_error log_slave_updates OFFlog_slow_queries OFFlog_update OFFlog_warnings 1long_query_time 10low_priority_updates OFFVariable_name Valuelower_case_file_system OFFlower_case_table_names 0max_allowed_packet 1047552max_binlog_cache_size 18446744073709551615max_binlog_size 1073741824max_connect_errors 10max_connections 750max_delayed_threads 20max_error_count 64max_heap_table_size
sterin
October 6, 2007, 9:32am
6
I think you missed about 50% of the output because there is a lot more.
But from the part I got I can see that the key_buffer_size is set to 400MB, so if your database is big enough (you never wrote how big it actually is in MB) that is how much MySQL will use for the index cache.
Then you have the per connection buffers lite sort buffer etc which can if you have a lot of concurrent connections open be quite large.
[B]sterin wrote on Sat, 06 October 2007 11:02[/B]
you never wrote how big it actually is in MB
Yep, and how much memory do you have on your box, and what table types do you use, and is this box is dedicated to mysql, or it is shared between mysql and apache, etc, etc… Give us mroe information, please.
but the only way to learn is to ask
