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
