 | | Re: Controlling memory usage under Win32: query_cache_size or
innodb_buffer_p | Re: Controlling memory usage under Win32: query_cache_size or
innodb_buffer_p 2004-12-21 - By Jonathan G. Lampe
Back
At 10:18 AM 12/21/2004, Patrick Questembert wrote:
>When I run large queries, mysqlnt.exe gets to around 150MB of memory usage,
>which brings my WinXP server to a near stand-still and other apps are barely
>able to run ...
Does MySQL memory on your machine go up and down? The query cache should
take up a stable chunk of memory, and I would think the InnoDB would too.
Something else which may be grinding your machine to a halt is plain old
disk swapping - MySQL memory has been swapped to disk as virtual memory and
swapping it back takes some time. To avoid this, you kind of need to avoid
filling the rest of physical memory with your applications or use your
MySQL caches more frequently. You could also disable virtual memory by
taking your swap file down to 0, but doing that it probably beyond the
scope of what we would want to get in to here.
I'd take down both parameters for a while - especially if this is a
development box and you are working with mickey-mouse tables or not doing
many queries. As you move toward production and production loads, then you
will need to retune, of course, but I'd work on avoiding swap for now.
-jgl
>This is on Windows XP, MySQL Server 4.1, 1GB of memory, all tables InnoDB. I
>am running 3 MySQL apps (which are not querying lots of data, just writing)
>and one app querying a large number of rows.
>
>I haven't tuned my MYSQL server performance just yet so would appreciate
>some guidance to avoid tuning the wrong parameter ... which of the size
>parameters do I need to change for this situation?
>
>I assume I need to edit \Program Files\MySQL\MySQL Server 4.1\my.cnf
>The size parameters mentioned there in relation to InnoDB tables are:
>query_cache_size=33M
>tmp_table_size=16M (not sure if this is relevant for apps which don't create
>in-memory tables explicitely)
>innodb_additional_mem_pool_size=2M (recommended not to change this value)
>innodb_log_buffer_size=1M
>innodb_buffer_pool_size=93M
>innodb_log_file_size=19M
>
>At first glance, query_cache_size=33M or innodb_buffer_pool_size=93M seem
>like the parameters I need to tune down. Question is which one? In-line
>documentation of both parameters seem to indicate an overlap in purpose ...
>does innodb_buffer_pool_size supercede query_cache_size for InnoDB tables?
>
>Thanks!
>Patrick Questembert
>
>
>--
>MySQL Windows Mailing List
>For list archives: http://lists.mysql.com/win32
>To unsubscribe: http://lists.mysql.com/win32?unsub=jonathan@(protected)
- Jonathan Lampe
- jonathan.lampe@(protected)
******************* PLEASE NOTE *******************
This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed. If
you are not the named addressee you should not disseminate, distribute or copy
this e-mail. Please delete this e-mail from your system. If you are not the
intended recipient you are notified that disclosing, copying, distributing or
taking any action in reliance on the contents of this information is strictly
prohibited.
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=mysql@(protected)
Earn $52 per hosting referral at Lunarpages.
|
|
|