From: Date: April 24 2001 5:44pm Subject: mysql eat memory List-Archive: http://lists.mysql.com/bugs/10605 Message-Id: MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_0054_01C0CCE6.301EE790" ------=_NextPart_000_0054_01C0CCE6.301EE790 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Hello, Why my mysql eat all memory over 1Gb. and abort connets and clients?. Please help my --------------------------------------------- TOP 4:54pm up 5:12, 3 users, load average: 15.61, 23.99, 19.37 136 processes: 135 sleeping, 1 running, 0 zombie, 0 stopped CPU states: 13.2% user, 22.6% system, 0.0% nice, 64.1% idle Mem: 2074332K av, 2073040K used, 1292K free, 109292K shrd, 41940K buff Swap: 1052248K av, 905028K used, 147220K free 32132K cached PID USER PRI NI SIZE RSS SHARE STAT LIB %CPU %MEM TIME COMMAND 5 root 11 0 0 0 0 DW 0 8.0 0.0 12:00 kswapd 21316 root 14 0 16356 11M 1272 S 0 6.8 0.5 0:03 mysqld 21449 root 15 0 16356 11M 1272 S 0 6.6 0.5 0:00 mysqld 21402 nobody 10 0 381M 381M 2236 D 0 5.9 18.8 0:20 httpd 21447 root 9 0 16356 11M 1272 S 0 4.9 0.5 0:01 mysqld 21408 nobody 11 0 352M 352M 2104 D 0 4.7 17.3 0:20 httpd 21007 root 8 0 732 696 344 R 0 4.2 0.0 0:40 top 21265 root 5 0 16356 11M 1272 S 0 3.4 0.5 0:00 mysqld 21448 root 6 0 16356 11M 1272 S 0 3.0 0.5 0:00 mysqld 20937 nobody 4 0 2508 2404 1716 S 0 2.4 0.1 0:02 httpd 21418 nobody 2 0 2956 2768 2072 S 0 2.4 0.1 0:00 httpd 21439 nobody 3 0 3028 2844 2104 D 0 2.4 0.1 0:00 httpd 20933 nobody 2 0 3168 2944 2152 S 0 2.1 0.1 0:02 httpd 21431 nobody 1 0 3032 2856 2120 S 0 1.5 0.1 0:00 httpd 21415 nobody 3 0 2912 2740 2040 S 0 1.3 0.1 0:00 httpd 21080 nobody 3 0 2616 2332 1684 D 0 1.1 0.1 0:02 httpd 21076 nobody 1 0 2596 2288 1648 S 0 0.9 0.1 0:01 httpd 21113 nobody 3 0 2832 2712 2040 D 0 0.9 0.1 0:01 httpd 21407 nobody 1 0 3028 2856 2168 S 0 0.7 0.1 0:00 httpd 21425 nobody 1 0 3168 2996 2160 S 0 0.7 0.1 0:00 httpd 20969 nobody 1 0 2624 2508 1700 D 0 0.5 0.1 0:02 httpd 21060 nobody 1 0 3160 2936 2164 S 0 0.5 0.1 0:02 httpd 21392 nobody 1 0 3072 2912 2192 S 0 0.5 0.1 0:00 httpd 21416 nobody 1 0 3064 2908 2184 S 0 0.5 0.1 0:00 httpd 20922 nobody 2 0 3168 3116 2368 S 0 0.3 0.1 0:03 httpd 20951 nobody 2 0 2740 2656 1952 S 0 0.3 0.1 0:02 httpd 20957 nobody 1 0 646M 76M 7524 D 0 0.3 3.7 0:33 httpd 21086 nobody 1 0 2608 2272 1612 S 0 0.3 ............ ............ numero de procesos httpd: 64 --- mysqld: 65 /usr/local/mysql/bin/mysqladmin Ver 8.18 Distrib 3.23.36, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.36-log Protocol version 10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 21 min 17 sec Threads: 53 Questions: 63637 Slow queries: 389 Opens: 158 Flush tables: 1 Open tables: 152 Queries per second avg: 49.833 ----------------------------------------------------- cat /etc/my.cnf [mysqld] port = 3306 socket = /tmp/mysql.sock set-variable = max_connections=500 set-variable = key_buffer=256M set-variable = max_allowed_packet=8M set-variable = table_cache=128M set-variable = sort_buffer=64M set-variable = record_buffer=64M set-variable = myisam_sort_buffer_size=64M set-variable = thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable = thread_concurrency=4 #log-bin server-id = 1 [isamchk] set-variable = key_buffer=128M set-variable = sort_buffer=128M set-variable = read_buffer=2M set-variable = write_buffer=2M [myisamchk] set-variable = key_buffer=128M set-variable = sort_buffer=128M set-variable = read_buffer=2M set-variable = write_buffer=2M [mysqlhotcopy] interactive-timeout ---------------------------------------- Red Hat Linux release 6.2 (Zoot) Linux 2.2.14-5.0smp glibc-2.1.3-21 java version "1.2.2-RC2" Classic VM (build 1.2.2-RC2-K, green threads, javacomp) /usr/local/mysql/bin/mysqladmin extended-status /usr/local/mysql/bin/mysqladmin: Out of memory (Needed 8164 bytes) /usr/local/mysql/bin/mysqladmin: unable to show status; error: '' --------------------------------------------------- /usr/local/mysql/bin/mysqladmin extended-status +--------------------------+----------+ | Variable_name | Value | +--------------------------+----------+ | Aborted_clients | 81 | | Aborted_connects | 0 | | Bytes_received | 590944 | | Bytes_sent | 10174612 | | Connections | 1078 | | Created_tmp_disk_tables | 26 | | Created_tmp_tables | 26 | | Created_tmp_files | 0 || Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Delayed_errors | 0 | | Flush_commands | 1 | | Handler_delete | 57 | | Handler_read_first | 17 | | Handler_read_key | 684 | | Handler_read_next | 2067818 | | Handler_read_prev | 0 | | Handler_read_rnd | 31048 | | Handler_read_rnd_next | 5019061 | | Handler_update | 1787 | | Handler_write | 2517 | | Key_blocks_used | 200 | | Key_read_requests | 49973 | | Key_reads | 174 | | Key_write_requests | 1858 | | Key_writes | 296 | | Max_used_connections | 89 | | Not_flushed_key_blocks | 0 | | Not_flushed_delayed_rows | 0 | | Open_tables | 72 | | Open_files | 86 | | Open_streams | 0 | | Opened_tables | 78 | | Questions | 17956 | | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 0 | | Select_range_check | 0 | | Select_scan | 4807 | | Slave_running | OFF | | Slave_open_temp_tables | 0 | | Slow_launch_threads | 10 | | Slow_queries | 0 | | Sort_merge_passes | 0 | | Sort_range | 141 | | Sort_rows | 31048 | | Sort_scan | 65 | | Table_locks_immediate | 7103 | | Table_locks_waited | 739 | | Threads_cached | 2 | | Threads_created | 187 | | Threads_connected | 73 | | Threads_running | 10 | | Uptime | 242 | +--------------------------+----------+ The log-slow-queri file show # User@Host: genteirc[genteirc] @ localhost [] # Time: 14 Lock_time: 1 Rows_sent: 149 SELECT * FROM genteirc WHERE Inicial='B' and OK='1' and Sexo='1' ORDER BY Nick; # User@Host: genteirc[genteirc] @ localhost [] # Time: 19 Lock_time: 6 Rows_sent: 100 SELECT * FROM genteirc WHERE Inicial='C' and OK='1' and Sexo='1' ORDER BY Nick LIMIT 0,100; # User@Host: genteirc[genteirc] @ localhost [] # Time: 16 Lock_time: 3 Rows_sent: 100 SELECT * FROM genteirc WHERE Inicial='S' and OK='1' and Sexo='1' ORDER BY Nick LIMIT 0,100; # Time: 010424 17:02:04 # User@Host: genteirc[genteirc] @ localhost [] # Time: 13 Lock_time: 0 Rows_sent: 492 SELECT * FROM genteirc WHERE Inicial='T' and OK='1' and Sexo='0' ORDER BY Nick; # User@Host: genteirc[genteirc] @ localhost [] # Time: 11 Lock_time: 0 Rows_sent: 458 SELECT * FROM genteirc WHERE Inicial='E' and OK='1' and Sexo='0' ORDER BY Nick; Number of processes running now: 0 010424 17:16:32 mysqld restarted Regards ------=_NextPart_000_0054_01C0CCE6.301EE790--