Jon,
> So how do I optimize memory usage? Where to start ?
If your server is compiled with debug=full, the command
mysqladmin proc stat
shows you total server memory use. Look in the manual at
How MySQL uses Memory
SHOW VARIABLES (for all vars that control server memory use)
Perhaps the following little vignette we worked up may help ...
The size of the non-InnoDB index buffer is set by key_buffer_size, which to
improve performance for even moderately sized databases can be set to a
value up to a quarter or more of available server RAM, but to avoid
thrashing, should be set to not more than half. The FIFO table handler cache
allots up to 64 entries on a per-open-table-per-connection basis. Issuing
mysqladmin flush-tables
closes all tables that are not in use, marks all other tables for closing
when the corresponding thread ends, and frees most allocated memory.
The maximum number of concurrent connections is set by max_connections. Each
connection
- uses a stack of thread_stack bytes, default 64k,
- uses a connection buffer of net_buffer_length bytes, default 16k,
- uses a result buffer (same size), and
- opens each data and index file once, creating for each table a table
buffer, buffers for each column, a row buffer 3 times the maximum row
length, and if the table has a BLOB column then a BLOB buffer up to the size
of the largest BLOB value.
So suppose you expect ...
- 200 maximum connections,
- queries that access 10 tables max + 3 temp tables each,
- average table buffer sizes of 32k,
- average row lengths of 10k,
- no BLOB columns, and
- a quarter of all queries requiring sorts,
then
- the average number of open tables for all threads, (the MySQL variable
table_cache) is 200(10 + 3) = 2600,
- the server needs 2600*(64+16+16+3*10+32)k + 50(200/4)MB = 510 MB RAM
for connection buffering.
PB
---------------------------------------------------------------------
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
