List:General Discussion« Previous MessageNext Message »
From:Chris Knipe Date:May 3 2005 5:23pm
Subject:mysql mem usage
View as plain text  
Hi,

I have a P4 system with 1GB Ram and 512MB Swap (a little low I know).

I'm running a multi threaded MySQL installation on it.  With a bit of shock, 
I realised today that I ran completely out of swap space!!!  Each MySQL 
thread is consuming about 160MB of ram, and I had close to 40 threads 
running.  All my databases currently are approximately 1.5GB as far as file 
size goes, so it's not *that* much really.  The biggest table is MyISAM (for 
delayed inserts), holding approximately 2million records currently.

Is there a way to limit the amount of threads that MySQL can start, and to 
"manage" the pool of threads?  I.E. min servers, max servers, idle servers, 
etc....

And, can someone point me to a good side regarding getting optimum values 
for my.cnf?  I'm running MySQL 4.1.11

my.cnf:
[mysqld]
log-queries-not-using-indexes
skip-name-resolve
skip-show-database
skip-external-locking

port               = 3306
socket             = /tmp/mysql.sock
key_buffer_size    = 8M
max_allowed_packet = 4M
bind-address       = x.x.x.x.122
server-id          = 100

set-variable       = back_log=10
set-variable       = connect_timeout=10
set-variable       = interactive_timeout=28800
set-variable       = interactive_timeout=20
set-variable       = join_buffer=256000
set-variable       = key_buffer_size=64M

set-variable       = max_allowed_packet=1M
set-variable       = max_connect_errors=512
set-variable       = max_connections=64
set-variable       = max_user_connections=64

set-variable       = myisam_sort_buffer_size=16M

set-variable       = net_buffer_length=32K
set-variable       = net_read_timeout=30
set-variable       = net_retry_count=10000
set-variable       = net_write_timeout=60

set-variable       = query_cache_size=16M
set-variable       = query_cache_limit=1M
set-variable       = query_cache_type=1

set-variable       = read_buffer_size=2M
set-variable       = sort_buffer=2M
set-variable       = table_cache=64

set-variable       = thread_stack=64k
set-variable       = thread_cache=16
set-variable       = thread_concurrency=2

set-variable       = tmp_table_size=200000
set-variable       = wait_timeout=28800

set-variable       = delayed_insert_limit=2000
set-variable       = delayed_insert_timeout=1800
set-variable       = delayed_queue_size=8000
set-variable       = max_delayed_threads=32


--
Chris.

I love deadlines. I especially love the whooshing sound they make as they 
fly by..." - Douglas Adams, 'Hitchhiker's Guide to the Galaxy' 

Thread
mysql mem usageChris Knipe3 May
  • Re: mysql mem usageDan Nelson3 May
  • Re: mysql mem usageChris Knipe3 May
    • Re: mysql mem usageDan Nelson3 May
  • Re: mysql mem usageChris Knipe3 May