brashquido

No, the ones I'll try looking for are the ones that should be able to use the cache, but are not byte exact.

Yeah, I figure if I lower the cache until Qcache_lowmem_prunes starts registering something and then just up it a bit I should have the sweet spot for my server. Not sure what to do about queries not using the cache though.

It's a concern because if you configure MySQL to be able to consume more physical memory than what you have got then at some stage you are going to hit a brick wall. Especially when MySQL is not on a dedicated server you are headed for all sorts of contention issues if you don't do your homework. I've since been looking at Monyog, which although still only in beta uses a different equation for calculating the max; key_buffer + innodb_buffer_pool + max_connections * (read_buffer + sort_buffer + binlog_cache_size) + max_connections * 2MB

Because of the overhead of having to maintain the cache. I haven't done any testing on this yet, but after asking a few questions on the webyog forums I was told that if my query cache had a low hitrate and there was still plenty of room left in the cache (i.e queries were not byte exact, or set not to use the cache) then I might very well see slightly better performance by disabling it. As I use a VPS with very limited RAM resources the memory freed up by disabling the cache is also worth considering. Apart from that my only real choice is examine all the queries in my webapps and optimise them to better utilize the cache. Unfortunately that is well beyond my capabilities at the moment.

My query cache hitrate was only about 65~70%. I've read that to get performance benefits your hitrate needs to be in the high 90's. Is this not correct?

It's in the Drupal snippet; http://drupal.org/node/50291

Hi All, Seems my server is not benefiting from having the query cache enabled and I'm wanting to turn it off. Just wondering how you set have_query_cache to off in my.ini? I've tried just have_query_cache=no but MySQL doesn't restart. Any ideas?

Ok, thanks. When I type in SHOW TABLES should I get a full list of all tables in all the databases?

Hi All, I have around 1100 tables on my MySQL server, and I'm wanting to convert them all so that they are using the MyISAM engine. Is there anyway I can convert all my tables in one statment? something like; ALTER TABLE * ENGINE = MYISAM

Hi All, I'm got several fairly low trafficked sites (nothing over 10,000 page views a month) on a VPS with 512MB RAM running Windows 2003 Server, IIS6, PHP 4.4.4, MySQL 4.1.20 and a handful of other services. So basically I have very limited system resources. I've been trying to tune MySQL over the past few days and am finding I've very limited on the amount of memory I can allocate to it. Just wondering if it would be better in my case to only use MyISAM? My sites are not heavy on writes (not much forum activity, etc) and that way I could free up the memory being used for the InnoDB cache for other services. What would the pros/cons of doing this be?

So obvious, thanks

This is probably a stupid question, but what is the easiest way to find which primary keys and foreign keys are not indexed? I imagine there are no shortcuts here, and it would involve enabling the slow query log with the log-queries-not-using-indexes switch and then examining the log?

Hi All, Trying to size a server for MySQL and looking for equations to use to figure out the minimum amount of RAM MySQL is going to use, and more importantly what the maximum amount of RAM is going to be used. I can't seem to find anything about finding the minimum amount of RAM required, but I found this equation for finding the maximum amount of RAM on the Drupal website; key_buffer + innodb_buffer_pool + innodb_log_buffer + innodb_additional_mem_pool + net_buffer + max_connections * (read_buffer + join_buffer + sort_buffer + myisam_sort_buffer + thread_stack + tmp_table_size + read_rnd_buffer) I'm not sure how accurate that is though, as according to my math (which isn't great) that would mean I need somewhere around 8.8 million gigbytes of RAM. Could someone please point me in the right direction?

Hi All, I manage a VPS for myself and a couple of friends, and it seems that a lot of the PHP apps we use don't use indexes at all. MySQL uses a lot of CPU time, which I'm told can come back to the lack of having/using indexes. 1) Can I add indexes to all tables in all the DB's on my server at once, and if so how? 2) Will just adding indexes and nothing else improve MySQL performance, or do the queries in the PHP code have to be modified to use the new index?

Thanks guys, that's fixed all the PHP specific ones. Anyone know where I can find the ones for FastCGI, such as; PHP_FCGI_MAX_REQUESTS Thanks again

Hi All Is there any one place I can goto to find a complete list of environment variables needing to be set on a Windows server for PHP? I can't seem to find anything about it on the PHP site. In particular I am looking to find what environment variables need or can be set to govern FastCGI in a Windows / IIS environment.