Hi,
On Thu, 9 Aug 2001, Fournier Jocelyn [Presence-PC] wrote:
> > set-variable = join_buffer=2048M
> > set-variable = key_buffer=2048M
> > set-variable = table_cache=1024
> > set-variable = record_buffer=2048M
> > set-variable = sort_buffer=2048M
> > set-variable = tmp_table_size=2048M
>
> Your record buffer and sort buffer setting seems to be too high :)
>
> Memory usage of MySQL is organised like this :
>
> All the thread share up to key_buffer (in your case 2048 Mo).
> Each MySQL connections will also use up to (sort_buffer + record_buffer).
> In your case, it means MySQL could eat up to (2048 Mo + 2048 Mo)*200
> (max_connections) = 819200 Mo :))
OK, I've re-read the manual section on that and it makes more sense now :)
> I suggest you set key_buffer to 2048 Mo, and sort_buffer and record_buffer
> to 5 Mo.
I've been benchmarking my data using various settings. I'm using a table
with 6.5 million rows which is packed and uses compressed keys. The data
is 69M and the index is 123M.
I was trying to identify the effect of the record_buffer, so I performed a
sequential scan on it with:
SELECT COUNT(*) FROM table WHERE char_field LIKE '%foo%'
I found no difference in query time with a record_buffer of 4M, 2M, 1M,
512K, 16K and 1K (!) - any ideas on the cause of this?
I have also set tmp_table_size to 1024M, which according to the manual
should mean that temporary tables will be created in RAM unless they're
going to be bigger than that. When I do the following query:
SELECT char_field,COUNT(*) AS c FROM table GROUP BY char_field
HAVING c > 3 ORDER BY char_field
It creates a temporary table on disk even though it was only about 200MBs
- huh?
> The memory usage of MySQL thread will grow up with the time (each new key
> scanned will stay in memory).
Thanks for your help :)
Corin
/------------------------+-------------------------------------\
| Corin Hartland-Swann | Tel: +44 (0) 20 7491 2000 |
| Commerce Internet Ltd | Fax: +44 (0) 20 7491 2010 |
| 22 Cavendish Buildings | Mobile: +44 (0) 79 5854 0027 |
| Gilbert Street | |
| Mayfair | Web: http://www.commerce.uk.net/ |
| London W1K 5HJ | E-Mail: [EMAIL PROTECTED] |
\------------------------+-------------------------------------/
---------------------------------------------------------------------
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
