I am using Windows 2000 Server, IIS and MySQL 4.0.18. I'm not
sure about configuring this for other setups.
If you have access to your my.cnf (typically found in the root),
add these lines to your [mysqld] section (you can change the values
in you want, refer to the link above for more info).
Then, in your code, whereever you want to have the query
results cached, for example,
$result = sql_query("SELECT active FROM yourprefix_modules WHERE title='$module'", $dbi);
change it to:
$result = sql_query("SELECT SQL_CACHE active FROM yourprefix_modules WHERE title='$module'", $dbi);
If you don't have access to your MySQL configurations, ask your host
about the settings. Remember, you will be need to be using a 4.x version
of MySQL.
steven111 Lieutenant
Joined: Dec 30, 2003
Posts: 283
Posted:
Sun May 09, 2004 8:38 pm
I have not delved into it, but I think the cache is automatically on, and modifying the SQL select statement is not required. Any thoughts/benchmarks on that?
On further research, query caching is on by default, and modifying the select statement is not necessary... although setting up proper startup parameters would really help.
Quote:
Assuming that the query cache is enabled, the query_cache_type variable influences how it works. This variable can be set to the following values:
A value of 0 or OFF prevents caching or retrieval of cached results.
A value of 1 or ON allows caching except of those statements that begin with SELECT SQL_NO_CACHE.
hmmm. i just checked 2 different configurations, home and work, both 4.0.18 installs downloaded from mysql.com and i can verify that the query caching was NOT enabled.
the default cnf file had those options rem'd out, however, removing the remarks and restarting the service did enable the cache.
which cnf are you using ? small ? medium ? huge ? i used mysqlfront w/o any cnf loaded and my query cache was set to "off".
oh well, no matter.
steven111 Lieutenant
Joined: Dec 30, 2003
Posts: 283
Posted:
Sun May 09, 2004 11:10 pm
What I am saying that you would not have to modify each and every "select" statement, the startup options of course have to be configured.
Would you test that, i.e. run an sql select statemet without modifying it for caching.
sure - and i may be wrong here, but from what ive read on other sites, groups, etc. is that even if query caching is enabled, your select statements won't take advantage of it unless you modify the select statement.
i enabled the cache, ran some queries, and checked the status via mysql> SHOW STATUS LIKE 'Qcache%'; on the mysql command prompt. nada - zilch - nothing using the cache. i ran the same query several times, still, nothing.
i re-ran the query using the SQL_CACHE statement and bingo, mysql> SHOW STATUS LIKE 'Qcache%'; would return my cache hits, AND, the performance increase was tremendous.
i am by no means an expert on this, hell, the first day i posted my original post was the day i started experimenting.
please post your results and we can compare
steven111 Lieutenant
Joined: Dec 30, 2003
Posts: 283
Posted:
Sun May 09, 2004 11:46 pm
I am glad you actually tested it, as your result do seem to be different from the documentation (or the way I read the documentation).
When I get a chance, I will run some tests myself, and report. I was hoping that every statement does not have to be modified...
when i started playing around with it, i added a variable right after each SELECT statement, real easy search/replace using Dreamweaver. This way, I can set the value in mainfile to either SQL_CACHE or SQL_NO_CACHE.
Plus, this gives me the ability to customize each module later if I want, for example, if for some reason i want to add either of these, SQL_SMALL_RESULT SQL_BIG_RESULT SQL_BUFFER_RESULT statements i can easily add them now, either globally or per module.
I foresee dramatic speed optimization with your approach. I have employed disk caching in the past (cache Lite), but am now looking at using mysql caching instead whenever possible. mysql caching, being memory based, is a lot faster.
Great job. Keep 'em coming.
How about identifying the sql statement that lend themselves to caching? or are you turning on caching for EVERY select statement in your script?
first, i had to evaluate each of my select statements using the EXPLAIN command. this allowed me to see which SELECTs where taking advantage of indexes or not. i found that the default indexes that are created via the nuke sql file blow, they work, just not as optimal as they could be.
once i determined which SELECTs needed fixing, the indexing started. i also took advantage of multiple field indexes instead of multiple indexes. i started very basic, creating indexes on fields that are used in WHERE and ORDER clauses. For example, CREATE INDEX a_b_c ON tablename (a_b_c);
this greatly increased speeds on the site. again, after creating the new indexes i re-ran the EXPLAIN command to ensure that indexes were being used.
now to the SELECT statements. my site uses more SELECTs than INSERTs/UPDATEs/etc. so I started there. if the SELECT was for guests/regged users i sqlcache'd it, if it was for admins i didn't. i'm the only admin and i'm local, so i don't care too much.
the key here is to test test test. don't just settle for a standard SELECT statement. take advantage of correctly created indexes. play with your cache settings, i.e. memory, etc. also, utilize the SQL_SMALL/BIG/HUGE_RESULT statements if you know the size of the query that will be returned.
i was also able to increase the speed by rolling my own mysql, with a very finely tuned compiler for my specific machine (this took a while to nail down all of the compiler switches). i also roll my own php, tuned as well.
i also found that mysql caching was faster for me than using my previous caching, Turck MMCache (which is still awesome btw).
i'll post more finding as i discover em...
steven111 Lieutenant
Joined: Dec 30, 2003
Posts: 283
Posted:
Mon May 10, 2004 3:00 pm
Since you are doing all the hard work, it would be beneficial to identify the select statements that are for "lookup" and don't change often from page to page (or from user to user). For example, determining who the admin is (in ViewForum.php, if I recall) is something that is not changing for a llooonnng period of time, and is an involved multi-table join, so it would be a great candidate.
There are also "low hanging fruits". PHPBB settings are read on each page, and can easily be cached (same for nuke settings!!).
There are a ton of such things. Before, djmaze and I collaborated on using function's static declaration to avoid hitting mysql on multiple calls to the same function --on the same page. This yielded huge performance improvement, but I think your approach would yield a lot more performance since it goes across users/pages.
So as you find those pesky select statements, please share.
I have some custom code that uses rand() to return rows in random from a select statement. That would not be cached, and I need to return the result set, and then select from it in PHP
Otherwise, everything good. Thanks for pointing this great solution out.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
