Re: mysql eat memory

[Sasha Pachev]

On Tuesday 24 April 2001 09:44, Carlos Perdones wrote:
> 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
> 
> 


Because you have an oversided sort_buffer and record_buffer. Those are 
allocated per connection.

-- 
MySQL Development Team
   __  ___     ___ ____  __ 
  /  |/  /_ __/ __/ __ \/ /   Sasha Pachev <sasha@stripped>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, http://www.mysql.com/
/_/  /_/\_, /___/\___\_\___/  Provo, Utah, USA
       <___/