James M. Luedke wrote: > 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? >
mysqld uses threads, not processes; so all memory is shared. mysqld is using 52Mb only (not 624Mb). This has already been discussed several times on this list; you can also check MySQL documentation: http://www.mysql.com/doc/en/MySQL_threads.html http://www.mysql.com/doc/en/Memory_use.html Chances are that all remaining memory is used by Linux kernel cache. > > ---------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... have_raid has nothing to do with HW raid. It was mainly used to break the 2Gb/file limit of some filesystems by spreading data of a MyISAM table in several files. This option is not very useful nowadays since this 2Gb limit has been removed in all recent OS. > > > Thank you all so much for your help, MySQL is a great app, and I > apreciate all the hard work you have done... > Thanks, but I haven't done that much, I am just a user ;) > > > -James... > > Hope this helps Joseph Bueno --------------------------------------------------------------------- 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
