Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

MYSQL Swapping Issue / Memory Usage

joeojoeo EntrantInactive User Role Beginner
I'm running MYSQL on a Linux box using basically the my-medium.cnf file. The machine has 1GB of RAM. After MYSQL has been running for a day or so, it starts to use very much memory.
Here is top:

top - 00:52:44 up 67 days, 57 min, 3 users, load average: 0.07, 1.19, 2.39
Tasks: 95 total, 1 running, 94 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.0% us, 0.0% sy, 0.0% ni, 99.7% id, 0.3% wa, 0.0% hi, 0.0% si
Mem: 1034084k total, 666440k used, 367644k free, 29476k buffers
Swap: 2096472k total, 463748k used, 1632724k free, 64872k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
24782 mysql 15 0 750m 401m 2476 S 0.0 39.7 37:53.80 mysqld
24820 joeo 24 0 600m 33m 1744 S 0.0 3.3 1:00.73 java
24558 root 16 0 8624 2688 1084 S 0.0 0.3 0:59.94 ddclient
30880 nobody 15 0 9920 1832 1272 S 0.0 0.2 0:00.87 httpd
30441 nobody 15 0 10072 1820 1256 S 0.0 0.2 0:01.09 httpd
24082 nobody 15 0 9920 1808 1256 S 0.0 0.2 0:01.06 httpd
30882 nobody 15 0 9832 1784 1228 S 0.0 0.2 0:01.00 httpd



Earlier today, it was using 1.6GB of VIRT space.
Sometimes, during usage, the machine will start to swap so much, that is is un-usable. Usually after a long period of swapping the memory drops back down to around 750MB as it is now.

Any ideas?

Here is my /etc/my.cnf file:
[mysqld]
datadir=/usr/mysql_data
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 2
query_cache_size = 8M

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Thanks for any ideas!

-Joe
http://www.lovehorsepower.com

Comments

  • joeojoeo Entrant Inactive User Role Beginner
    The memory usage is back up now:
    24782 mysql 15 0 1343m 389m 2288 S 0.0 38.5 54:25.98 mysqld

    According to my-medium.cnf:
    # This is for a system with little memory (32M - 64M) where MySQL plays
    # an important part, or systems up to 128M where MySQL is used together with
    # other programs (such as a web server)

    Any ideas why the usage is so high?
    Thanks!

    -Joe
    http://www.lovehorsepower.com
  • mikecmikec Contributor Inactive User Role Beginner
    Depending on how many connections you have...
    If you are using MyISAM tables...and doing selects requiring
    sorts (as in group by, order by, etc)...

    The myisam_sort_buffer_size=8M for 100 connections that
    would be 800M.

    Try dropping this down to 1M and see if good performance
    doesn't last longer.

    Also running MySQL by itself on its own server would be a better
    idea.
  • joeojoeo Entrant Inactive User Role Beginner
    Currently there are only about 3 connections as this is in development. There is a process running that is performing many inserts, but only through one connection.
    I will try this anyway and see what happens.

    Thanks!

    -JoeO
  • mikecmikec Contributor Inactive User Role Beginner
    Some ideas to try...

    When the problem occurs do show processlist there should be
    only a few processes running in MySQL or are there a lot of
    processes? How about when the problem isn't occurring?

    Reboot MySQL and do a show status. Then do a show status when
    the problem is occurring and compare them.

    What is the code that is accessing MySQL? Maybe you have
    a memory leak.
  • joeojoeo Entrant Inactive User Role Beginner
    Good idea. Here are the results after running for a long while.
    MySQLd is currently using 350M of RAM, and 1004M of VIRT space.


    top:
    PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
    24782 mysql 15 0 1004m 358m 2156 S 1.7 35.5 90:50.05 mysqld

    show processlist

    Id User Host db Command Time State Info







    3454 joeo 192.168.0.2:4862 zanydb Sleep 21777 (null)
    3455 joeo 192.168.0.2:4863 zanydb Query 0 (null) show processlist
    3477 joeo cybrina.mine.nu:10042 zanydb Sleep 0 (null)
    3705 joeo cybrina.mine.nu:24794 zanydb Sleep 38 (null)


    show table status

    Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment

















    CONCEPTS MyISAM 10 Dynamic 0 0 0 281474976710655 1024 0 (null) 8/31/2007 12:17:52 AM 8/31/2007 12:17:52 AM 8/31/2007 1:23:01 AM latin1_swedish_ci (null)
    ENTITY MyISAM 10 Dynamic 1304297 79 103582900 281474976710655 31250432 0 (null) 8/31/2007 12:17:56 AM 10/1/2007 4:03:27 PM 8/31/2007 12:17:56 AM latin1_swedish_ci (null)
    LINKS MyISAM 10 Dynamic 2250683 225 507099016 281474976710655 339712000 0 (null) 9/13/2007 1:08:30 AM 10/1/2007 4:03:27 PM 9/13/2007 1:10:39 AM latin1_swedish_ci (null)
    TOKEN MyISAM 10 Dynamic 11986918 29 349799444 281474976710655 266305536 0 (null) 8/31/2007 12:18:01 AM 10/1/2007 4:03:27 PM 8/31/2007 12:18:01 AM latin1_swedish_ci (null)


    show status

    Variable_name Value

    Aborted_clients 7398
    Aborted_connects 3
    Binlog_cache_disk_use 0
    Binlog_cache_use 0
    Bytes_received 1125
    Bytes_sent 19158
    Com_admin_commands 0
    Com_alter_db 0
    Com_alter_table 0
    Com_analyze 0
    Com_backup_table 0
    Com_begin 0
    Com_change_db 1
    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 0
    Com_dealloc_sql 0
    Com_delete 0
    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 0
    Com_grant 0
    Com_ha_close 0
    Com_ha_open 0
    Com_ha_read 0
    Com_help 0
    Com_insert 0
    Com_insert_select 0
    Com_kill 0
    Com_load 0
    Com_load_master_data 0
    Com_load_master_table 0
    Com_lock_tables 0
    Com_optimize 0
    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 0
    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 14
    Com_set_option 4
    Com_show_binlog_events 0
    Com_show_binlogs 0
    Com_show_charsets 1
    Com_show_collations 1
    Com_show_column_types 0
    Com_show_create_db 0
    Com_show_create_table 0
    Com_show_databases 1
    Com_show_errors 0
    Com_show_fields 0
    Com_show_grants 0
    Com_show_innodb_status 0
    Com_show_keys 0
    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 4
    Com_show_slave_hosts 0
    Com_show_slave_status 0
    Com_show_status 1
    Com_show_storage_engines 0
    Com_show_tables 0
    Com_show_triggers 0
    Com_show_variables 1
    Com_show_warnings 11
    Com_slave_start 0
    Com_slave_stop 0
    Com_stmt_close 0
    Com_stmt_execute 0
    Com_stmt_fetch 0
    Com_stmt_prepare 0
    Com_stmt_reset 0
    Com_stmt_send_long_data 0
    Com_truncate 0
    Com_unlock_tables 0
    Com_update 0
    Com_update_multi 0
    Com_xa_commit 0
    Com_xa_end 0
    Com_xa_prepare 0
    Com_xa_recover 0
    Com_xa_rollback 0
    Com_xa_start 0
    Compression OFF
    Connections 3724
    Created_tmp_disk_tables 0
    Created_tmp_files 11
    Created_tmp_tables 8
    Delayed_errors 0
    Delayed_insert_threads 0
    Delayed_writes 0
    Flush_commands 3
    Handler_commit 0
    Handler_delete 0
    Handler_discover 0
    Handler_prepare 0
    Handler_read_first 2
    Handler_read_key 0
    Handler_read_next 4464163
    Handler_read_prev 0
    Handler_read_rnd 8
    Handler_read_rnd_next 405
    Handler_rollback 0
    Handler_savepoint 0
    Handler_savepoint_rollback 0
    Handler_update 0
    Handler_write 528
    Innodb_buffer_pool_pages_data 19
    Innodb_buffer_pool_pages_dirty 0
    Innodb_buffer_pool_pages_flushed 0
    Innodb_buffer_pool_pages_free 493
    Innodb_buffer_pool_pages_latched 0
    Innodb_buffer_pool_pages_misc 0
    Innodb_buffer_pool_pages_total 512
    Innodb_buffer_pool_read_ahead_rnd 1
    Innodb_buffer_pool_read_ahead_seq 0
    Innodb_buffer_pool_read_requests 77
    Innodb_buffer_pool_reads 12
    Innodb_buffer_pool_wait_free 0
    Innodb_buffer_pool_write_requests 0
    Innodb_data_fsyncs 3
    Innodb_data_pending_fsyncs 0
    Innodb_data_pending_reads 0
    Innodb_data_pending_writes 0
    Innodb_data_read 2494464
    Innodb_data_reads 25
    Innodb_data_writes 3
    Innodb_data_written 1536
    Innodb_dblwr_pages_written 0
    Innodb_dblwr_writes 0
    Innodb_log_waits 0
    Innodb_log_write_requests 0
    Innodb_log_writes 1
    Innodb_os_log_fsyncs 3
    Innodb_os_log_pending_fsyncs 0
    Innodb_os_log_pending_writes 0
    Innodb_os_log_written 512
    Innodb_page_size 16384
    Innodb_pages_created 0
    Innodb_pages_read 19
    Innodb_pages_written 0
    Innodb_row_lock_current_waits 0
    Innodb_row_lock_time 0
    Innodb_row_lock_time_avg 0
    Innodb_row_lock_time_max 0
    Innodb_row_lock_waits 0
    Innodb_rows_deleted 0
    Innodb_rows_inserted 0
    Innodb_rows_read 0
    Innodb_rows_updated 0
    Key_blocks_not_flushed 0
    Key_blocks_unused 0
    Key_blocks_used 14497
    Key_read_requests 145870080
    Key_reads 580288
    Key_write_requests 31037715
    Key_writes 30653236
    Last_query_cost 0.000000
    Max_used_connections 24
    Not_flushed_delayed_rows 0
    Open_files 21
    Open_streams 0
    Open_tables 15
    Opened_tables 0
    Qcache_free_blocks 1
    Qcache_free_memory 8379904
    Qcache_hits 13
    Qcache_inserts 44
    Qcache_lowmem_prunes 0
    Qcache_not_cached 87207
    Qcache_queries_in_cache 0
    Qcache_total_blocks 1
    Questions 14963997
    Rpl_status NULL
    Select_full_join 0
    Select_full_range_join 0
    Select_range 0
    Select_range_check 0
    Select_scan 8
    Slave_open_temp_tables 0
    Slave_retried_transactions 0
    Slave_running OFF
    Slow_launch_threads 3
    Slow_queries 2
    Sort_merge_passes 0
    Sort_range 0
    Sort_rows 8
    Sort_scan 2
    Ssl_accept_renegotiates 0
    Ssl_accepts 0
    Ssl_callback_cache_hits 0
    Ssl_cipher
    Ssl_cipher_list
    Ssl_client_connects 0
    Ssl_connect_renegotiates 0
    Ssl_ctx_verify_depth 0
    Ssl_ctx_verify_mode 0
    Ssl_default_timeout 0
    Ssl_finished_accepts 0
    Ssl_finished_connects 0
    Ssl_session_cache_hits 0
    Ssl_session_cache_misses 0
    Ssl_session_cache_mode NONE
    Ssl_session_cache_overflows 0
    Ssl_session_cache_size 0
    Ssl_session_cache_timeouts 0
    Ssl_sessions_reused 0
    Ssl_used_session_cache_entries 0
    Ssl_verify_depth 0
    Ssl_verify_mode 0
    Ssl_version
    Table_locks_immediate 14231643
    Table_locks_waited 49584
    Tc_log_max_pages_used 0
    Tc_log_page_size 0
    Tc_log_page_waits 0
    Threads_cached 8
    Threads_connected 4
    Threads_created 1981
    Threads_running 2
    Uptime 326624



    Does anything there look out of place?
    Thanks!

    -Joe
    http://videos.lovehorsepower.com
  • mikecmikec Contributor Inactive User Role Beginner
    aborted clients seems really hi.

    Try reboot of MySQL and rerun your app.
    Motor thru different parts of your app and keep an eye on how
    this variable goes up. This may be a memory leak of some kind.
  • joeojoeo Entrant Inactive User Role Beginner
    Thank you! I will check this and get back to you!

    -JoeO
    http://www.lovehorsepower.com
Sign In or Register to comment.

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