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 |
+----------------------------+---------------------------------+
