[Webdev] Optimizing mysql

[Donncha O Caoimh]

something of interest to many of us I think..

Donncha.


-------- Original Message --------
Subject: Re: how to use memory above 512M
Date: Tue, 15 Aug 2000 00:17:14 -0700
From: "Jeremy D. Zawodny" <jzawodn at yahoo-inc.com>
To: mars lu <mars at classover.com>
CC: mysql at lists.mysql.com
References: <003201c006eb$390b7fb0$5700a8c0 at classover.com>
<20000814221423.A30855 at yahoo-inc.com>
<000d01c006fa$4f47f380$5700a8c0 at classover.com>

On Tue, Aug 15, 2000 at 01:49:26PM -0700, mars lu wrote:

> i use the server as a dedicated mysql server , and the web server
> use the database through 100M network .  i see from `top` , that
> mysql alloc 514M memory , it is the max i'v seen .i want to alloc
> more memory.
>
> Thank you for your replying , below is my my.cnf ,
> 
> [mysqld]
> port=3306
> socket=/tmp/mysql.sock
> set-variable = back_log=5
> set-variable = max_connections=1024
> set-variable = key_buffer=1024M
> set-variable = max_allowed_packet=64M

Looks good. The big key_buffer should be helpful. I've not taken mine
much above 384MB on a 1GB system. I just didn't see any real
performance boost going higher, but it depends on your usage patterns.

> set-variable = table_cache=256M

I belive that table-cache is supposed to specify a number of tables,
not an amount of memory. Try taking the `M' off the end.

> set-variable = sort_buffer=256M
> set-variable = record_buffer=256M
> set-variable = join_buffer=256M
> set-variable = net_buffer_length=48K

Woah!

Those are all allocated on a *per thread* basis and not shared
globally like the key_buffer is. You could easily cause your system to
swap like crazy if you get several threads starting to use big sort or
record or join buffers. You probably want turn those way down.

I suggest you look over Monty's presentation on optimizing
MySQL. You'll find lots of helpful information in it:

   
http://www.mysql.com/information/presentations/presentation-oscon2000-20000719/index.html

Enjoy,

Jeremy
-- 
Jeremy D. Zawodny, <jzawodn at yahoo-inc.com>
Technical Yahoo - Yahoo Finance
Desk: (408) 328-7878    Fax: (408) 530-5454
Cell: (408) 439-9951