Corin Hartland-Swann wrote:
>
> Hi there,
>
> I am trying to optimise MySQL for use on a machine with:
>
> Linux 2.4.7-ac9 (only kernel to work with the disk controller)
> Dual Pentium III 1000 MHz
> 4096 MB memory
> RAID-1 Mirror over two 76GB 7200 RPM UDMA disks
> Reiserfs partition for MySQL
>
> As a reference point, I am running the benchmarking tests to measure the
> performance. I plan on measuring the performance with our particular
> queries after this.
>
> After reading the section of the manual dealing with performance tuning, I
> used the following in my.cnf:
>
> [mysqld]
> set-variable = join_buffer=2048M
> set-variable = key_buffer=2048M
> set-variable = table_cache=1024
> set-variable = max_allowed_packet=16M
> set-variable = max_connections=200
> set-variable = record_buffer=2048M
> set-variable = sort_buffer=2048M
> set-variable = tmp_table_size=2048M
>
Take care, record_buffer and sort_buffer are allocated on a per thread
basis. According to your setup, you may need up to
(record_buffer+sort_buffer)*max_connections = 800 Gb RAM !
Your mysqld process will certainly coredump !
Also remember that Linux on a 32 bits machine cannot allocate more
than ~3 Gb per process.
> The problem is that these settings don't seem to have practically any
> effect on the amount of memory that MySQL uses when running the
> benchmarking suite - I expected it to chew up large amounts of memory with
> these settings. MySQL is currently only taking up 25MB of RAM - not the
> 512MB plus I was expecting.
>
Your test is not heavy enough !
MySQL allocates memory only when needed, it does not preallocate it
at startup.
> This machine is going to be a dedicated database server for a number of
> web servers - I expected MySQL to take up about half the memory, and disk
> caching the other half. I want it to run like a beast posessed :)
>
> Does anyone have any real world examples and/or suggestions for how to
> increase the performance? Any other variables I should be looking at?
>
I would suggest that:
- look closely at http://www.mysql.com/doc/S/H/SHOW_VARIABLES.html
and be careful with variables that have effect on a per thread connection
basis.
- search mysql mailing list archives, there were some discussions on
these topics (for instance, key_buffer setup).
> When the machine is operational, the biggest table I expect to be using
> has 55 million rows and takes up about 2G disk space (compressed, packed,
> everything).
>
> Please could you CC: any replies to me, since I am no longer on the list.
>
> Regards,
>
> 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] |
> \------------------------+-------------------------------------/
>
Regards
--
Joseph Bueno
NetClub/Trader.com
---------------------------------------------------------------------
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
