my.cnf selection in MySQL

enterume

Hi,

I have 1.5GB RAM and dual 1.13GHZ processor machine, running MySQL and Apache+PHP togather.

According to the headline stated in each config file, it seem like my-medium.cnf is suitable for those machine which bundle MySQL and webserver togather. If so, will it under-use the hardware resource offer from my server?

One thing I dont' understand is, in my-medium.cnf, it said:

This is for a system with little memory (32M - 64M) where MySQL plays

a important part and systems up to 128M very MySQL is used together with

other programs (like a web server)

On the other hand, in my-large.cnf it said:

This is for large system with memory = 512M where the system runs mainly

MySQL.

So, for my case, I have 1.5GB RAM, but the machine running both apache and MySQL... which config I shoudl use?

Please advice.

Thanks
LOuis

killerbishop

Be careful with those options. At any one time, MySQL will consume AND hold that much memory for it's SQL cache. So, 512 megabytes might be used as the SQL cache.

The directions are based on use - if MySQL is critical in your environment, then you want to dedicate more resources. If it is less important, then dedicate less.

Most tweaking is done based on use - so change the settings to reflect the actual usage. This means you'll have to watch the systems and change the settings according to what you see.

enterume

Hi Patrick,

For my case, it is sort like a information site to show the latest odds, and the page will reflect every 10 second , get differnet information from different table.. so what do you think which my.cnf should use? because it seem like when there is 130++ user concurrently access to it, the mysql will perform slow.

how to use the mysqld to change the MySQL setting? because it keep on prompt me can't use root to change the setting.. . Please advice.

Thanks
Louis

killerbishop

Well, the speed of the database server won't necessarily be boosted if the queries issued are poor (i.e. using lots of JOIN statements for example). However, increasing memory will make it so the system won't thrash when those kind of queries are made.

If the data will be changing every 10 seconds - MySQL might not be the best choice for your database backend (this would mean INSERT and UPDATE statements are being issued frequently, and MySQL wasn't design to be good with those - just SELECT statements). I would advise switching to a more robust database server if you will be dealing with that issue (or MySQL 4.0.3, which has better support for this).

Is this a fresh install, or your not the administrator? You should have root access (or you won't be able to restart the daemon anyway). If it is a fresh install, access is granted like:

mysql -u root

There is no default password in the default install.

I would begin with a 128/256 megabyte cache. If this is the only application that will running on your system, I would also make your PHP scripts use persistent caching and reduce the number of Apache processes to a reasonable level so that it doesn't consume the server.

enterume

Hi Patrick,

Thanks for your quick response.

The question I ask previously is change the setting via the mysqld .

mm. I thought machine hardward spec enough to select the my-large.conf to be my MySQL config, which having higher cache. Same question again, would it be under-use my harward if i choose the lower .cnf ? This is the only application running on my machine.

Thanks
Louis

killerbishop

Not necessarily, I thought you were running Apache on there as well? Your going to need resources for it as well (not just running, but actually dealing with hundreds or thousands of connections, running PHP scripts, and answering queries).

If your not running anything but a default install of Linux and MySQL, by all means - use all the resources you need. I'm just trying to strike a balance where I think that Apache is going to be more of a memory hog (and the bottleneck), then MySQL. Again, I don't know what queries your issuing to your server, so I can't say for certain.

You can't change the SQL cache settings from within MySQL, it requires a manual restart if you change this setting (MySQL would have no way of deallocating the existing SQL cache to be freed for use with the system, since this is most likely done using something like malloc()).

enterume

Sorry, I misunderstood what you mean 'application', i thought it was refering to the website.

The machine actually running PHP+Apache+MySQL on linux box. The most query I execute is 'select' statement for the front end user. For admin usre, they will always update and add new record.

I have search thru the web for optimising the MySQL. This is the one I found lately.. http://wdvl.internet.com/Authoring/DB/SQL/Optimize/optimize2_4.html .

IT seem like compiling the MySQL with pgcc++ will help the performance of MySQL.