Members Members   Search Search   Help Help   Register Register   Login Login   Home Home
Home » Performance » MySQL » mysql takin' too much memory
mysql takin' too much memory [message #1992] Thu, 04 October 2007 16:27 Go to next message
Leinad is currently offline Leinad
Messages: 7
Registered: September 2007
Junior Member
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 :/ but the only way to learn is to ask

Variable_name Value
Aborted_clients 2
Aborted_connects 4
Binlog_cache_disk_use 0
Binlog_cache_use 0
Bytes_received 2585754887
Bytes_sent 268500406169
Com_admin_commands 3302054
Com_alter_db 0
Com_alter_table 5
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_change_db 3363237
Com_change_master 0
Com_check 0
Com_checksum 0
Com_commit 0
Com_create_db 0
Com_create_function 0
Com_create_index 0
Com_create_table 6
Com_dealloc_sql 0
Com_delete 9787
Com_delete_multi 0
Com_do 0
Com_drop_db 0
Com_drop_function 0
Com_drop_index 0
Com_drop_table 0
Com_drop_user 0
Com_execute_sql 0
Com_flush 18
Com_grant 18
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_help 0
Com_insert 75255
Com_insert_select 0
Com_kill 0
Com_load 0
Com_load_master_data 0
Com_load_master_table 0
Com_lock_tables 525
Com_optimize 6
Com_preload_keys 0
Com_prepare_sql 0
Com_purge 0
Com_purge_before_date 0
Com_rename_table 0
Com_repair 0
Com_replace 9
Com_replace_select 0
Com_reset 0
Com_restore_table 0
Com_revoke 0
Com_revoke_all 0
Com_rollback 0
Com_savepoint 0
Com_select 7259376
Com_set_option 2334
Com_show_binlog_events 0
Com_show_binlogs 7
Com_show_charsets 155
Com_show_collations 155
Com_show_column_types 0
Com_show_create_db 0
Com_show_create_table 48
Com_show_databases 165
Com_show_errors 0
Com_show_fields 155
Com_show_grants 60
Com_show_innodb_status 0
Com_show_keys 59
Com_show_logs 0
Com_show_master_status 0
Com_show_ndb_status 0
Com_show_new_master 0
Com_show_open_tables 0
Com_show_privileges 0
Com_show_processlist 1685
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 2
Com_show_storage_engines 11
Com_show_tables 403
Com_show_variables 343
Com_show_warnings 0
Com_slave_start 0
Com_slave_stop 0
Com_stmt_close 0
Com_stmt_execute 0
Com_stmt_prepare 0
Com_stmt_reset 0
Com_stmt_send_long_data 0
Com_truncate 7
Com_unlock_tables 525
Com_update 1914903
Com_update_multi 23
Connections 6228
Variable_name Value
Created_tmp_disk_tables 3526
Created_tmp_files 4
Created_tmp_tables 590018
Delayed_errors 0
Delayed_insert_threads 0
Delayed_writes 0
Flush_commands 1
Handler_commit 0
Handler_delete 68743
Handler_discover 0
Handler_read_first 8599
Handler_read_key 5373778
Handler_read_next 4359485
Handler_read_prev 88398917
Handler_read_rnd 502955474
Handler_read_rnd_next 23347149768
Handler_rollback 30
Handler_update 985894
Handler_write 9221844
Key_blocks_not_flushed 0
Key_blocks_unused 322129
Key_blocks_used 1230
Key_read_requests 22489243
Key_reads 1940
Key_write_requests 189011
Key_writes 92434
Max_used_connections 152
Not_flushed_delayed_rows 0
Open_files 168
Open_streams 0
Open_tables 102
Opened_tables 170
Qcache_free_blocks 1823
Qcache_free_memory 31220400
Qcache_hits 35556149
Qcache_inserts 7194111
Qcache_lowmem_prunes 10403
Qcache_not_cached 65093
Qcache_queries_in_cache 1743
Qcache_total_blocks 5369
Questions 48198716
Rpl_status NULL
Select_full_join 0
Select_full_range_join 0
Select_range 0
Select_range_check 0
Select_scan 3779581
Slave_open_temp_tables 0
Slave_retried_transactions 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 0
Sort_merge_passes 0
Sort_range 0
Sort_rows 503136508
Sort_scan 399973
Table_locks_immediate 9143713
Table_locks_waited 121768
Threads_cached 6
Threads_connected 96
Threads_created 996
Threads_running 1
Uptime 503350

regards Smile
Report message to a moderator
Re: mysql takin' too much memory [message #1993 is a reply to message #1992 ] Thu, 04 October 2007 18:24 Go to previous messageGo to next message
sterin is currently offline sterin
Messages: 324
Registered: March 2007
Location: Sweden
Senior Member
Leinad wrote on Thu, 04 October 2007 22:27


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.
From what I can gather from the status variables you are averaging
100 queries per second.
Which is a pretty decent figure.
But I can't see that anything is directly wrong from those variables.
Report message to a moderator
Re: mysql takin' too much memory [message #2004 is a reply to message #1993 ] Sat, 06 October 2007 02:26 Go to previous messageGo to next message
Leinad is currently offline Leinad
Messages: 7
Registered: September 2007
Junior Member
first...thank you Very Happy

and it means it consumes a lot of RAM (from their point of view)
I honestly think they didn't expect our site to be that popular. So I'm askin' you, the experts in mysql performance

(our server consumes like %40 of the total RAM right now (4gb))

regards...anything else? (really appreciated)
Report message to a moderator
Re: mysql takin' too much memory [message #2006 is a reply to message #1992 ] Sat, 06 October 2007 05:25 Go to previous messageGo to next message
sterin is currently offline sterin
Messages: 324
Registered: March 2007
Location: Sweden
Senior Member
Can you post the output from SHOW VARIABLES instead?
That way we can see if any variable is strangely configured.
Report message to a moderator
Re: mysql takin' too much memory [message #2008 is a reply to message #1992 ] Sat, 06 October 2007 06:19 Go to previous messageGo to next message
Leinad is currently offline Leinad
Messages: 7
Registered: September 2007
Junior Member
there ya go
Variable_name 	Value
back_log 	50
basedir 	/
binlog_cache_size 	32768
bulk_insert_buffer_size 	8388608
character_set_client 	utf8
character_set_connection 	utf8
character_set_database 	latin1
character_set_results 	utf8
character_set_server 	latin1
character_set_system 	utf8
character_sets_dir 	/usr/share/mysql/charsets/
collation_connection 	utf8_unicode_ci
collation_database 	latin1_swedish_ci
collation_server 	latin1_swedish_ci
concurrent_insert 	ON
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
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 	NO
have_bdb 	NO
have_blackhole_engine 	NO
have_compress 	YES
have_crypt 	YES
have_csv 	NO
have_example_engine 	NO
have_geometry 	YES
have_innodb 	YES
have_isam 	NO
have_merge_engine 	YES
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_data_file_path 	ibdata1:10M:autoextend
innodb_data_home_dir 	 
innodb_fast_shutdown 	ON
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_table_locks 	ON
innodb_thread_concurrency 	8
interactive_timeout 	28800
join_buffer_size 	131072
key_buffer_size 	402653184
key_cache_age_threshold 	300
key_cache_block_size 	1024
key_cache_division_limit 	100
language 	/usr/share/mysql/english/
large_files_support 	ON
lc_time_names 	en_US
license 	GPL
local_infile 	ON
locked_in_memory 	OFF
log 	OFF
log_bin 	OFF
log_error 	 
log_slave_updates 	OFF
log_slow_queries 	OFF
log_update 	OFF
log_warnings 	1
long_query_time 	10
low_priority_updates 	OFF
Variable_name 	Value
lower_case_file_system 	OFF
lower_case_table_names 	0
max_allowed_packet 	1047552
max_binlog_cache_size 	18446744073709551615
max_binlog_size 	1073741824
max_connect_errors 	10
max_connections 	750
max_delayed_threads 	20
max_error_count 	64
max_heap_table_size

[Updated on: Sat, 06 October 2007 06:20]

Report message to a moderator
Re: mysql takin' too much memory [message #2011 is a reply to message #1992 ] Sat, 06 October 2007 11:02 Go to previous messageGo to next message
sterin is currently offline sterin
Messages: 324
Registered: March 2007
Location: Sweden
Senior Member
I think you missed about 50% of the output because there is a lot more. Wink

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.
Report message to a moderator
Re: mysql takin' too much memory [message #2013 is a reply to message #2011 ] Sat, 06 October 2007 23:26 Go to previous message
scoundrel is currently offline scoundrel
Messages: 58
Registered: August 2006
Location: Toronto, ON, Canada
Member
sterin wrote on Sat, 06 October 2007 11:02

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.

Alexey Kovyrin, MySQL Performance Expert
MySQL Performance Blog
MySQL Consulting
Report message to a moderator
Previous Topic:mysql tables crashing
Next Topic:Windows Memory and Page File Question
Goto Forum:
  

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [ ]

Current Time: Sun Jul 5 17:59:34 EDT 2009

Total time taken to generate the page: 0.01505 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 2.7.7.
Copyright ©2001-2007 FUD Forum Bulletin Board Software

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.