MySQL memroy/disk./processor limits on Win32

Where can I find documentation detailing
platform-specific limitiatons, such as the maximum
memory utilized, DB size, maximum number of processors
supported, etc. It'd be nice to see platform
comparison regarding these limitations.

I'd also like to know if MySQL has any performance or
scalability problems on the Win32 platform.

Thanks,

Homam




__________________________________
Do you Yahoo!?
Read only the mail you want - Yahoo! Mail SpamGuard.
http://promotions.yahoo.com/new_mail

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: MySQL memroy/disk./processor limits on Win32

Hi Homam,

First, go read this:
http://dev.mysql.com/tech-resources/articles/mysql_platform_ selection.html

At 07:21 PM 12/20/2004, Homam S.A. wrote:
>Where can I find documentation detailing
>platform-specific limitiatons, such as the maximum
>memory utilized

In my experience with smaller (<2GB) databases, it seems that MySQL's
memory utilitization is mostly controlled by the size of your query cache
and other cache parameters. Generally, you want MySQL to use as much
machine memory as practical (on a dedicated system) because memory caches
help the database run faster, but you should also play with your caches on
shared application systems to keep the system from swapping MySQL memory to
disk. (By default, the old pre-4.1 installations set up a "small memory"
footprint; the new 4.1+ installations seem to ask the user about some
general memory parameters during the install, but I haven't personally
played with these yet.)

>, DB size

Try this:
http://dev.mysql.com/doc/mysql/en/Table_size.html

>, maximum number of processors
>supported, etc.

Personally, I've only ever worked with up through 4 CPU machines.

See also ("innodb_thread_concurrency "):
http://dev.mysql.com/doc/mysql/en/InnoDB_start.html

>It'd be nice to see platform
>comparison regarding these limitations.

Do you mean whether to run MySQL on Windows, Linux or MacOS?
If so, check out these pages:
http://dev.mysql.com/doc/mysql/en/Windows_vs_Unix.html
http://dev.mysql.com/doc/mysql/en/Operating_System_Specific_ Notes.html

It kind of sounds like you're fishing for background material for an
internal presentation, so let me also suggest that you check out some of
the case studies like this one:
http://www.mysql.com/news-and-events/success-stories/istockp hoto.com.html

Regards,
-jgl

******************* 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=gcdmw-win32@m.gmane.org

Controlling memory usage under Win32: query_cache_size or innodb_buffer_pool_size?

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 ...

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=gcdmw-win32@m.gmane.org

Re: Controlling memory usage under Win32: query_cache_size or innodb_buffer_pool_size?

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@stdnet.com

- Jonathan Lampe
- jonathan.lampe@standardnetworks.com

******************* 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=gcdmw-win32@m.gmane.org

Re: MySQL memroy/disk./processor limits on Win32

Thank you so much Jonathan! The info and links were
awesome. Actually I'm planning a deployemnt of MySQL
for some very heavy load application, and I wanted to
know the maximum limits in terms of hardware (scaling
up) for the Win32 platform.



--- "Jonathan G. Lampe"
wrote:

> Hi Homam,
>
> First, go read this:
>
http://dev.mysql.com/tech-resources/articles/mysql_platform_ selection.html
>
> At 07:21 PM 12/20/2004, Homam S.A. wrote:
> >Where can I find documentation detailing
> >platform-specific limitiatons, such as the maximum
> >memory utilized
>
> In my experience with smaller (<2GB) databases, it
> seems that MySQL's
> memory utilitization is mostly controlled by the
> size of your query cache
> and other cache parameters. Generally, you want
> MySQL to use as much
> machine memory as practical (on a dedicated system)
> because memory caches
> help the database run faster, but you should also
> play with your caches on
> shared application systems to keep the system from
> swapping MySQL memory to
> disk. (By default, the old pre-4.1 installations
> set up a "small memory"
> footprint; the new 4.1+ installations seem to ask
> the user about some
> general memory parameters during the install, but I
> haven't personally
> played with these yet.)
>
> >, DB size
>
> Try this:
> http://dev.mysql.com/doc/mysql/en/Table_size.html
>
> >, maximum number of processors
> >supported, etc.
>
> Personally, I've only ever worked with up through 4
> CPU machines.
>
> See also ("innodb_thread_concurrency "):
> http://dev.mysql.com/doc/mysql/en/InnoDB_start.html
>
> >It'd be nice to see platform
> >comparison regarding these limitations.
>
> Do you mean whether to run MySQL on Windows, Linux
> or MacOS?
> If so, check out these pages:
>
http://dev.mysql.com/doc/mysql/en/Windows_vs_Unix.html
>
http://dev.mysql.com/doc/mysql/en/Operating_System_Specific_ Notes.html
>
> It kind of sounds like you're fishing for background
> material for an
> internal presentation, so let me also suggest that
> you check out some of
> the case studies like this one:
>
http://www.mysql.com/news-and-events/success-stories/istockp hoto.com.html
>
> Regards,
> -jgl
>
> ******************* 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.
>
>
>
>




__________________________________
Do you Yahoo!?
Yahoo! Mail - 250MB free storage. Do more. Manage less.
http://info.mail.yahoo.com/mail_250

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org