mysql + phpmyadmin => optimization?

Discussion in 'Server Operation' started by Ovidiu, Jan 14, 2007.

  1. Ovidiu

    Ovidiu Active Member


    I recently upgraded to the latest phpmyadmin version and found out, it gives you optimization tips. Now I am curious about some sample tips its giving me:

    Qcache_queries_in_cache  	11 k  	 The number of queries registered in the cache.
    Qcache_inserts 	915 k 	The number of queries added to the cache.
    Qcache_hits 	3,249 k 	The number of cache hits.
    [COLOR="Red"]Qcache_lowmem_prunes 	7,052 	The number of queries that have been removed from the cache to free up memory for caching new queries. This information can help you tune the query cache size. The query cache uses a least recently used (LRU) strategy to decide which queries to remove from the cache.[/COLOR]
    Qcache_not_cached 	11 k 	The number of non-cached queries (not cachable, or not cached due to the query_cache_type setting).
    Qcache_free_memory 	11 M 	The amount of free memory for query cache.
    Qcache_free_blocks 	2,708 	The number of free memory blocks in query cache.
    Qcache_total_blocks 	26 k 	The total number of blocks in the query cache.
    what baffles me is the red part: it says I should optimize my cache size, althoug just below that it says I still have Qcache_free_memory = 11 M so there is plenty of free cache.

    [COLOR="Red"]Created_tmp_disk_tables  	5,643  	The number of temporary tables on disk created automatically by the server while executing statements. If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size value to cause temporary tables to be memory-based instead of disk-based.[/COLOR]
    Created_tmp_tables 	7,553 	The number of in-memory temporary tables created automatically by the server while executing statements.
    Created_tmp_files 	617 	How many temporary files mysqld has created.
    again I am unsure about the red part, as my values are these:

     max tmp tables  	32
    tmp table size  	67,108,864
    tmp table size was 32 M I changed it to 64 M and its still in red....

    what about this:
    Select_full_join  	131  	The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.
    I have clients so I cannot influence their DB usage...
    and this:

    Sort_merge_passes  	257  	The number of merge passes the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable.
    current values:

     myisam sort buffer size  	8,388,608
    sort buffer size  	8,388,600
    I was unsure which value to increase,... by default my values were 8M and 2M so I increased the second value to 8M but phpmyadmin still suggests I increase - is it talking about the myisam value or about the other one?

    and what about his?

    Open_tables  	128  	 The number of tables that are open.
    [COLOR="Red"]Opened_tables 	2,204 	The number of tables that have been opened. If opened tables is big, your table cache value is probably too small.[/COLOR]
    Table_locks_immediate 	1,510 k 	The number of times that a table lock was acquired immediately.
    Table_locks_waited 	1,749 	The number of times that a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication.
    current values:
    table cache  	128
    this was 64 before, I increased it to 128 still the value is in red...

    can someone give me some explanations? or am I worrying too much?
  2. falko

    falko Super Moderator ISPConfig Developer

    Did you restart MySQL after your changes?

    I haven't noticed these optimization tips from phpMyAdmin yet though...
  3. Ovidiu

    Ovidiu Active Member

    of course I did :) you'll find them when you click on the "status" tab.
  4. falko

    falko Super Moderator ISPConfig Developer

    Maybe phpMyAdmin (or your browser) caches the values somewhere?
  5. Ovidiu

    Ovidiu Active Member

    no they do change if I refresh

Share This Page