mysql takin' too much memory

MySQL & MariaDB Other MySQL® Questions
#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 :confused: 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 )

#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.
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.

#3

first…thank you :smiley:

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)

#4

Can you post the output from SHOW VARIABLES instead?
That way we can see if any variable is strangely configured.

#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

#6

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.

#7
[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.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright © 2006 - 2021 Percona LLC. All rights reserved.