Hello:
I have a few questions about how MySQL uses Ram. I have a machine
that I am using as a database server. The machine has close to 1G
of ram. It is running Slackware Linux 8.1 With a 2.4.18 kernel.
The main use of this server is a backend for Radius Authentication.
I have read all of the documentation I have been able to get my
hands on, and believe I have a very good understanding of how MySQL
works in general. However, this has me a little stumped.
Please take a look at the output from top, my.cnf, and the processlist
below.
-------begin top output-----------------
top - 15:21:46 up 56 days, 10:57, 11 users, load average: 0.03, 0.16, 0.15
Tasks: 61 total, 1 running, 58 sleeping, 2 stopped, 0 zombie
Cpu(s): 5.6% user, 2.0% system, 0.0% nice, 92.5% idle
Mem: 900472k total, 896048k used, 4424k free, 22956k buffers
Swap: 907664k total, 3676k used, 903988k free, 744248k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ Command
2361 mysql 9 0 53416 52m 2068 S 0.3 5.9 2:48.61 mysqld
2357 mysql 9 0 53416 52m 2068 S 0.0 5.9 0:00.18 mysqld
2359 mysql 9 0 53416 52m 2068 S 0.0 5.9 0:00.28 mysqld
2360 mysql 9 0 53416 52m 2068 S 0.0 5.9 0:11.29 mysqld
2362 mysql 9 0 53416 52m 2068 S 0.0 5.9 17:28.20 mysqld
2371 mysql 9 0 53416 52m 2068 S 0.0 5.9 0:02.82 mysqld
2386 mysql 9 0 53416 52m 2068 S 0.0 5.9 0:00.12 mysqld
2395 mysql 9 0 53416 52m 2068 S 0.0 5.9 0:01.01 mysqld
2419 mysql 9 0 53416 52m 2068 S 0.0 5.9 0:00.21 mysqld
2427 mysql 9 0 53416 52m 2068 S 0.0 5.9 17:00.51 mysqld
2428 mysql 9 0 53416 52m 2068 S 0.0 5.9 0:03.32 mysqld
2458 mysql 9 0 53416 52m 2068 S 0.0 5.9 0:01.15 mysqld
-------end top output-----------------
Notice that when viewing top output for user mysql that there are
12 seprate processes? I am assuming that this is done for connections
that the MySQL server may need to field. Does MySQL spawn a new
process for every connection that comes in? And if so why do I have
12 processes when the output from show processlist returns 7 rows.
Is this how you would expect the MySQL server to behave? It appears
that MySQL is sucking up a large amount of system recourses even
when no-one is connected... There is little else running on this
system besides MySQL. I have read similar messages to this email
in the mysql list archive, and am familiar with the fact that MySQL
will use key_buffer amount of ram for the mysqld process. According
to the top output It would appear to me that MySQL is using
52M*12 of ram which is 624M. Am I correct in my understanding?
I would assume that MySQL would share the cache between processes.
Is there a reason why each process needs key_buffer ram?
Am I doing something horribly wrong?
---------show processlist output -----
+-----+-------------+----------------+------------+-------------+-------+---------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State
| | Info |
+-----+-------------+----------------+------------+-------------+-------+---------------------------------------------+------------------+
| 1 | radiusd | localhost | radiusd_db | Sleep | 0 |
| | NULL |
| 20 | root | localhost | customers | Sleep | 23750 |
| | NULL |
| 49 | radiusd | 216.250.251.22 | radiusd_db | Sleep | 23076 |
| | NULL |
| 80 | root | localhost | customers | Sleep | 22168 |
| | NULL |
| 403 | radiusd | 64.139.37.2 | radiusd_db | Sleep | 2 |
| | NULL |
| 524 | replication | 64.139.37.2 | NULL | Binlog Dump | 9218 | Slave
|connection: waiting for binlog update | NULL |
| 769 | root | localhost | radiusd_db | Query | 0 | NULL
| | show processlist |
+-----+-------------+----------------+------------+-------------+-------+---------------------------------------------+------------------+
-----end show processlist output -----
-----/etc/my.cnf options -------------
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
set-variable = net_buffer_length=512K
set-variable = key_buffer=64M
set-variable = max_allowed_packet=1M
set-variable = table_cache=512
set-variable = sort_buffer=2M
set-variable = record_buffer=2M
set-variable = thread_cache=8
set-variable = max_connections=300
set-variable = thread_concurrency=4
set-variable = myisam_sort_buffer_size=64M
log-bin
binlog-do-db=radiusd_db
server-id = 1
[mysqldump]
quick
set-variable = max_allowed_packet=16M
[mysql]
no-auto-rehash
#safe-updates
[isamchk]
set-variable = key_buffer_size=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
[myisamchk]
set-variable = key_buffer_size=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
[mysqlhotcopy]
interactive-timeout
-----end /etc/my.cnf options ---------
-----show variables output -----------
+---------------------------------+---------------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------------+
| back_log | 50 |
| basedir | /usr/local/mysql/ |
| binlog_cache_size | 32768 |
| character_set | latin1 |
| character_sets | latin1 dec8 dos german1 hp8 koi8_ru latin2 |
| concurrent_insert | ON |
| connect_timeout | 5 |
| datadir | /serve/mysql/ |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| flush | OFF |
| flush_time | 0 |
| have_bdb | NO |
| have_gemini | NO |
| have_innodb | NO |
| have_isam | YES
| have_raid | NO |
| have_openssl | NO |
| init_file | |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 67104768 |
| language | /usr/local/mysql/share/mysql/english/ |
| large_files_support | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_update | OFF |
| log_bin | ON |
| log_slave_updates | OFF |
| log_long_queries | OFF |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 1047552 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connections | 300 |
| max_connect_errors | 10 |
| max_delayed_threads | 20 |
| max_heap_table_size | 16777216 |
| max_join_size | 4294967295 |
| max_sort_length | 1024 |
| max_user_connections | 0 |
| max_tmp_tables | 32 |
| max_write_lock_count | 4294967295 |
| myisam_max_extra_sort_file_size | 256 |
| myisam_max_sort_file_size | 2047 |
| myisam_recover_options | 0 |
| myisam_sort_buffer_size | 67108864
| net_buffer_length | 523264 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| open_files_limit | 0 |
| pid_file | /serve/mysql//fbi.pid |
| port | 3306 |
| protocol_version | 10 |
| record_buffer | 2093056 |
| record_rnd_buffer | 2093056 |
| query_buffer_size | 0 |
| safe_show_database | OFF |
| server_id | 1 |
| slave_net_timeout | 3600 |
| skip_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slow_launch_time | 2 |
| socket | /tmp/mysql.sock |
| sort_buffer | 2097144 |
| sql_mode | 0 |
| table_cache | 512 |
| table_type | MYISAM |
| thread_cache_size | 8 |
| thread_stack | 65536 |
| transaction_isolation | READ-COMMITTED |
| timezone | GMT |
| tmp_table_size | 33554432 |
| tmpdir | /tmp/ |
| version | 3.23.52-log |
| wait_timeout | 28800 |
+---------------------------------+---------------------------------------------+
-----end show variables output -------
Just a side question. I noticed in the show variables output the variable
have_raid? I do have the data directory stored on an external raid device,
however the variable is set to NO. Will this cause me any problems...
Thank you all so much for your help, MySQL is a great app, and I
apreciate all the hard work you have done...
-James...
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
