Announcement

Announcement Module
Collapse
No announcement yet.

mysql takin' too much memory

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • mysql takin' too much memory

    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 )

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

    Comment


    • #3
      first...thank you

      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)

      Comment


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

        Comment


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

          Comment


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

            Comment


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

              Comment

              Working...
              X