This is what I'm currently using on the dual 2GHz w/4GB of RAM:

set-variable = query_cache_type=1
set-variable = query_cache_size=1024M
set-variable = net_buffer_length=32760
set-variable = bulk_insert_buffer_size=32M
set-variable    = key_buffer=1024M
set-variable    = join_buffer_size=128M
set-variable    = max_allowed_packet=8M
set-variable    = table_cache=1024
set-variable    = myisam_sort_buffer_size=1024M

set-variable    = thread_concurrency=16
set-variable = thread_cache_size=16
set-variable = sort_buffer=8M
set-variable = read_rnd_buffer_size=8M
set-variable    = record_buffer=8M


If you're using only MyISAM tables for your data, then key_buffer should generally have as much RAM as it can have (up to the point where it makes sense, i.e., only has much RAM as is needed to store your index data). myisam_sort_buffer_size ought to be about 1/4 of your RAM.


If you're using InnoDB as well, or if you use InnoDB tables only, then you should give innodb_buffer_pool_size a good portion of your RAM, and perhaps 16-32MB or so for innodb_additional_mem_pool_size.

- Gabriel

On Friday, November 7, 2003, at 06:32 PM, Jan Pieter Kunst wrote:

At 13:21 -0500 07-11-2003, Gabriel Ricard wrote:

[1.8 GHz G5:]

select: Total time: 1428 wallclock secs (67.50 usr 25.18 sys + 0.00 cusr 0.00 csys = 92.68 CPU)

[dual 2.0 GHz G5:]


select: Total time: 146 wallclock secs (42.52 usr 33.74 sys + 0.00 cusr 0.00 csys = 76.26 CPU)

How the select portion of the test could be 10x slower, I'm not sure. Have you tuned MySQL at all? key_buffer, etc?


Well, I'm looking for advice on how I should tune MySQL. These are the relevant parts of the my.cnf I used:

[mysqld]
skip-locking
set-variable   = key_buffer=64M
set-variable   = max_allowed_packet=1M
set-variable   = table_cache=256
set-variable   = sort_buffer=4M
set-variable   = net_buffer_length=8K
set-variable   = record_buffer=1M
set-variable   = thread_cache=8
set-variable   = myisam_sort_buffer_size=8M
log-bin
server-id   = 1

[mysqldump]
quick
set-variable   = max_allowed_packet=16M

[mysql]
no-auto-rehash

[isamchk]
set-variable   = key_buffer=20M
set-variable   = sort_buffer=20M
set-variable   = read_buffer=2M
set-variable   = write_buffer=2M

[myisamchk]
set-variable   = key_buffer=20M
set-variable   = sort_buffer=20M
set-variable   = read_buffer=2M
set-variable   = write_buffer=2M


If you have any advice on what I could change here to get faster selects, I'm all ears.


Thanks in advance,
Jan Pieter Kunst

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]



Reply via email to