List:General Discussion« Previous MessageNext Message »
From:jkraai Date:May 2 1999 1:36pm
Subject:Re: 3.22.21 RAM Usage
View as plain text  
Michael Widenius wrote:
> 
> >>>>> "jkraai" == jkraai  <jkraai@stripped> writes:
> 
> jkraai> Just upgraded from 3.21.33b to 3.22.21.  Running
> jkraai> on RH5.2.
> 
> jkraai> Previously, when I'd do a top, mysqld's were using
> jkraai> around 20M of RAM each.
> 
> jkraai> Here's the /etc/my.cnf:
> jkraai> [mysqld]
> jkraai> log-update = /usr/local/var/mysql.update
> jkraai> set-variable = key_buffer=16M
> jkraai> set-variable = table_cache=256
> jkraai> set-variable = sort_buffer=8M
> jkraai> set-variable = record_buffer=1M
> 
> jkraai> Now, when I do a top, mysqld's are using around 6.5M.
> 
> jkraai> Was I just seeing things before?
> 
> jkraai> Thanks!
> 
> Hi!
> 
> I agree;  With a 16M key_buffer, your mysqld process should use a bit
> more than 16M;  Have you checked your current status with 'mysqladmin
> variables' ?
> 
> I just checked this, and it works as expected for me on RH5.2
> 
> shell> mysqld-i686 -O key_buffer=64M &
> shell> ps uagx | grep mysqld
> monty      403  6.3 25.8 67976 66572  p3 S    12:29   0:00 mysqld-i686 -O key_bu
> monty      408  0.0 25.8 67976 66572  p3 S    12:29   0:00 mysqld-i686 -O key_bu
> monty      409  0.0 25.8 67976 66572  p3 S    12:29   0:00 mysqld-i686 -O key_bu
> 
> Regards,
> Monty
> 
> PS: You should decrease your sort_buffer, as the sort buffer is NOT shared
>     among threads, its very unlikely that you want a 8M sort_buffer.

Monty,

Thanks for the great note!  Turns out problem isn't mysql, it's top.

For all processes, RSS is being displayed in both the 
SIZE & RSS columns.

ps augx | grep mysqld:
root     10336  0.0  0.9 20392  2540  p1 S N  02:16   0:00 /usr/local/libexec/mysqld ...
root     10338  0.0  0.9 20392  2540  p1 S N  02:16   0:00 /usr/local/libexec/mysqld ...
root     10339  0.0  0.9 20392  2540  p1 S N  02:16   0:00 /usr/local/libexec/mysqld ...
root     10346  1.9  0.9 20392  2540  p1 S N  02:24   6:59 /usr/local/libexec/mysqld ...
                         ^^^^^

top:
  PID USER     PRI  NI  SIZE  RSS SHARE STAT  LIB %CPU %MEM   TIME COMMAND
10336 root       5   5  2540 2540  1276 S N     0  0.0  0.9   0:00 mysqld
10338 root       5   5  2540 2540  1276 S N     0  0.0  0.9   0:00 mysqld
10339 root       5   5  2540 2540  1276 S N     0  0.0  0.9   0:00 mysqld
10346 root       5   5  2540 2540  1276 S N     0  0.0  0.9   6:59 mysqld
                        ^^^^

Thanks also for the tip on sort_buffer--I was hoping someone would 
give me advice about the contents of my.cnf.  This (new) box needs 
to support 100k queries/day against approx. 20 million rows of data.
Box has ample disk, 256M RAM (will go to 512M shortly), 256M swap.
Sound OK?

mysqladmin variables follows signoff.

--jim

+----------------------------+---------------------------------+
| Variable_name              | Value                           |
+----------------------------+---------------------------------+
| back_log                   | 5                               |
| connect_timeout            | 5                               |
| basedir                    | /usr/local/                     |
| datadir                    | /usr/local/var/                 |
| delayed_insert_limit       | 100                             |
| delayed_insert_timeout     | 300                             |
| delayed_queue_size         | 1000                            |
| join_buffer                | 131072                          |
| flush_time                 | 0                               |
| key_buffer                 | 16773120                        |
| language                   | /usr/local/share/mysql/english/ |
| log                        | OFF                             |
| log_update                 | ON                              |
| long_query_time            | 10                              |
| low_priority_updates       | OFF                             |
| max_allowed_packet         | 1048576                         |
| max_connections            | 100                             |
| max_connect_errors         | 10                              |
| max_delayed_insert_threads | 20                              |
| max_join_size              | 4294967295                      |
| max_sort_length            | 1024                            |
| net_buffer_length          | 16384                           |
| port                       | 3306                            |
| protocol-version           | 10                              |
| record_buffer              | 1044480                         |
| skip_locking               | ON                              |
| socket                     | /tmp/mysql.sock                 |
| sort_buffer                | 8388600                         |
| table_cache                | 256                             |
| thread_stack               | 65536                           |
| tmp_table_size             | 2097144                         |
| tmpdir                     | /tmp/                           |
| version                    | 3.22.21-log                     |
| wait_timeout               | 28800                           |
+----------------------------+---------------------------------+
Thread
DISTINCT and COUNT() SQL QueryMike Machado1 May
  • Re: DISTINCT and COUNT() SQL Queryjkraai1 May
    • Re: DISTINCT and COUNT() SQL QueryPaul DuBois1 May
  • RE: DISTINCT and COUNT() SQL QueryDon Read1 May
  • Re: DISTINCT and COUNT() SQL QueryMike Machado1 May
    • Re: DISTINCT and COUNT() SQL QueryThimble Smith1 May
  • Re: DISTINCT and COUNT() SQL QueryJim Faucette1 May
  • 3.22.21 RAM Usagejkraai2 May
    • 3.22.21 RAM UsageMichael Widenius2 May
  • Re: 3.22.21 RAM Usagejkraai2 May
  • a problemglen19 May
    • Re: a problem (The table 'SQL134597_0' is full)Pete Harlan20 May
      • Re: a problem (The table 'SQL134597_0' is full)glen20 May
        • Re: a problem (The table 'SQL134597_0' is full)Benjamin Pflugmann21 May
        • Re: a problem (The table 'SQL134597_0' is full)Michael Widenius24 May