i am running Moodle 1.6 (Xampp for windows) and installed mysql as service
I want to edit MYSQL cache size variable (in order to improve the performance) but cant find the my.cnf or my.ini file in my moodle folder
i have tried to do it with mysql admin as well but i can only see the variables and their settings dont know how to edit these variables
Can anybody help?
fouzia
Thanks Ken
I found it but its a speeddial type file and I cannot open it in Text file
Not sure how to edit this file or is there any other place where i can edit these variables?
Currently my "Show Mysql system variables" link shows query cache type is set to ON and query cache size is set to 0 that presumebly means it is not using cache at all. Is it alright if i set query cache size to 20M (i have upgraded to 2GB RAM recently and my C drive has total of 20 GB where D drive has roughly 54 GB available)
Thanks for your help
Fouzia
Hi
I have finally opened my.cnf file in text format but cant find query_cache _size variable in there
i have tried to run the following command on my SQL interface (SET GLOBAL query_cache_size = 40000;), it says successfully executed but the system variables are still showing its value as null
Any hints?
Hi Fouzia
In MySQL when a variable is not listed in the my.cnf file, the server uses the defaults. To set the variable, you have to explicitly add it in the my.cnf server section [mysqld] and restart the mysql service - then it will take the value you have set.
Ken
Hi Ken
I have tried above as per your recommendation but apparently it doesnt work
I have attached my.cnf file for your further advice
Thanks for your help
Regards
Hmm... can you add the extra line:
query_cache_limit = 20M
Restart mysqld, and then take a look at the query_cache variables with this command and the MySQL client:
mysql>SHOW VARIABLES LIKE '%query_cache%';
Check that have_query_cache = Yes and query_cache_type = On. Next, while moodle is being accessed, monitor the query cache usage with this command:
mysql>SHOW STATUS LIKE '%Qcache%';
You should see hits and queries in cache changing. If they do then the cache is working fine.
Ken
Thanks for quick response Ken
I have verified the Have_query_cache=yes and query_cache_type=on
however i cant monitor the query cache usage
i am using Apache and MYsql as a service thats mean i accessed them via programs->administrative tools-> services->mysql
Not very sure how to access the mysql prompt to run SHOW STATUS .... query
I have tried the above query in database->moodle->SQL tab but it says (please see the attached file)
Thanks
OK. Try this:
- Start, Run, Cmd.
- Type cd c:\xampplite\mysql\bin (or wherever your mysqld program is located)
- Type mysql -u root -p (it will ask for your database password)
- Type the SHOW STATUS or SHOW VARIABLES queries in this window (press up arrow to recall commands)
- When you're done, exit by typing QUIT
Below is a screenshot of this on our server (note that the 'cd' command is different as we don't use xampplite 
).
Does this help?
Ken
Ya, worked for me but the cache size is still set to 0
please see the attached file for your reference
Thanks for your help
Regards
OK - for some reason the mysql service is not picking-up the settings in c:\moodle\mysql\bin\my.cnf for query_cache_size. A few things to try:
1. Stop and start the mysql program, then run the SHOW VARIABLES LIKE '%query_cache%' command again.
2. Can you double-check that the mysql server uses the my.cnf file in c:\moodle\mysql\bin by looking in the mysqld-start.bat file for the --defaults-file= part.
3. Can you remember if mysql is installed as a service? AFAIK, if the xampp mysql server is running as a service, the my.cnf file may be located in c:\. So do a search on your C: drive for the file my.cnf.
HTH
Ken
Yes, you are right it was using some other cnf file
Thanks a million- it is working for me now
Ken, I have one more question
I want to implement it on my production server now which is using 2.40 GHz processor, 2 GB RAM with windows 2000 server (servicer pack 4)
i have roughly 350 users who are using moodle for enrollment and assignment module (MAX upload size 2MB)
is it alright if i set Query_cache as 32M? I am really not sure what exactly needs to be monitored when it comes to performance?
Thanks for your help
Fouzia
This is a difficult question to answer 
. The best way is to look in task manager in the perfromance tab at the amount of available physical memory and adjust up. We have 2Gb RAM on our server with these settings:
query_cache_size = 256M
tmp_table_size = 256M
The values seem a little high, and we may need to adjust. To monitor mysql, use MySQLreport (http://hackmysql.com/mysqlreport). It offers a good snapshot of how much query cache you are using and how efficient your system is. The documentation is also very good. We set a secheduled task to run every hour and output the results to a text file for anaylsis in our moodle technical meetings.
To be sure that you can cope with 350 users, run a simulation with Apache JMeter and test for concurrent access to login/index.php, as well as users browsing.
Ken
Thanks Ken but i really dont know how to use the mysqlreport file
i have browsed through the links, downloaded the MYSQLreport folder but unable to find how to incorporate mysqlreport in my environment
For JMETER also i only find binary or source file to be downlaodable? am I supposed to download them??
Any hints
Thanks
Fouzia
