Re: Query Cache Crashing
Hi Rick, Thanks for the advise. I have now set my query-cache to zero. (I take your point about query cache too large. I understand that a smaller cache size, and the use of the SQL_NO_CACHE and SQL_CACHE directives can be used to control which queries are cached. Therefore trying to get the advantage without the disadvantage. But this is a lot of work to change every query we ever run!) I am working on reproducing the error, but so far it appears to be random. Ben On 2012-10-09 18:44, Rick James wrote: As for the crash, I don't know. Instead, I recommend either shrinking the size (if you use a big QC) or turning it off. This would make the issue go away. ANY modification to a particular table leads to ALL entries in the Query cache being purged. For that reason, we (Yahoo) almost never use the QC on any of our many servers. Please provide SHOW GLOBAL VARIABLES LIKE 'query%'; SHOW GLOBAL STATUS LIKE 'Qc%'; Because of inefficiencies in 'pruning', having a query_cache_size bigger than 50M may actually degrade performance. In you have a reproducible test case, submit to bugs.mysql.com . -Original Message- From: Ben Clewett [mailto:b...@clewett.org.uk] Sent: Tuesday, October 09, 2012 4:47 AM To: mysql@lists.mysql.com Subject: Query Cache Crashing Hi MySql, Since upgrading to 5.5.27 (5.5.27-ndb-7.2.8-cluster-gpl-log to be exact) I have experienced problems with the query cache. I am wondering if I am the only one? Twice I have had a core-dump (show at end of mail) on two separate servers running this version. Now I had a complete lock, where 'SHOW PROCESSLIST' showed this for every connection: Waiting for query cache lock This resulted in connections building until the limit was hit. I could only cure this with a 'kill -9' on the mysqld process. Are any other users experiencing this? Do any users know of a fix? Thanks! Ben Clewett. Thread pointer: 0x7f6ea014cf90 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 410220e8 thread_stack 0x4 /usr/sbin/mysqld(my_print_stacktrace+0x35)[0x81c7f5] /usr/sbin/mysqld(handle_fatal_signal+0x403)[0x6e9383] /lib64/libpthread.so.0[0x7f6f491b2b30] /usr/sbin/mysqld(_ZN11Query_cache12unlink_tableEP23Query_cache_block_ta ble+0x1b)[0x5bd1ab] /usr/sbin/mysqld(_ZN11Query_cache19free_query_internalEP17Query_cache_b lock+0x7a)[0x5bdb5a] /usr/sbin/mysqld(_ZN11Query_cache27invalidate_query_block_listEP3THDP23 Query_cache_block_table+0x7e)[0x5be8ee] /usr/sbin/mysqld(_ZN11Query_cache12insert_tableEjPcP23Query_cache_block _tablejhPFcP3THDS0_jPyEy+0x91)[0x5bf7e1] /usr/sbin/mysqld(_ZN11Query_cache25register_tables_from_listEP10TABLE_L ISTjP23Query_cache_block_table+0x176)[0x5bfa76] /usr/sbin/mysqld(_ZN11Query_cache19register_all_tablesEP17Query_cache_b lockP10TABLE_LISTj+0x15)[0x5bfbb5] /usr/sbin/mysqld(_ZN11Query_cache11store_queryEP3THDP10TABLE_LIST+0x39e )[0x5bff5e] /usr/sbin/mysqld[0x5eb02d] /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x269b)[0x5f0aeb] /usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x18a)[0x5f2cba ] /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x13 21)[0x5f4861] /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x167)[0x68d807] /usr/sbin/mysqld(handle_one_connection+0x54)[0x68d874] /lib64/libpthread.so.0[0x7f6f491ab040] /lib64/libc.so.6(clone+0x6d)[0x7f6f4847c08d] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Query Cache Crashing
Hi MySql, Since upgrading to 5.5.27 (5.5.27-ndb-7.2.8-cluster-gpl-log to be exact) I have experienced problems with the query cache. I am wondering if I am the only one? Twice I have had a core-dump (show at end of mail) on two separate servers running this version. Now I had a complete lock, where 'SHOW PROCESSLIST' showed this for every connection: Waiting for query cache lock This resulted in connections building until the limit was hit. I could only cure this with a 'kill -9' on the mysqld process. Are any other users experiencing this? Do any users know of a fix? Thanks! Ben Clewett. Thread pointer: 0x7f6ea014cf90 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 410220e8 thread_stack 0x4 /usr/sbin/mysqld(my_print_stacktrace+0x35)[0x81c7f5] /usr/sbin/mysqld(handle_fatal_signal+0x403)[0x6e9383] /lib64/libpthread.so.0[0x7f6f491b2b30] /usr/sbin/mysqld(_ZN11Query_cache12unlink_tableEP23Query_cache_block_table+0x1b)[0x5bd1ab] /usr/sbin/mysqld(_ZN11Query_cache19free_query_internalEP17Query_cache_block+0x7a)[0x5bdb5a] /usr/sbin/mysqld(_ZN11Query_cache27invalidate_query_block_listEP3THDP23Query_cache_block_table+0x7e)[0x5be8ee] /usr/sbin/mysqld(_ZN11Query_cache12insert_tableEjPcP23Query_cache_block_tablejhPFcP3THDS0_jPyEy+0x91)[0x5bf7e1] /usr/sbin/mysqld(_ZN11Query_cache25register_tables_from_listEP10TABLE_LISTjP23Query_cache_block_table+0x176)[0x5bfa76] /usr/sbin/mysqld(_ZN11Query_cache19register_all_tablesEP17Query_cache_blockP10TABLE_LISTj+0x15)[0x5bfbb5] /usr/sbin/mysqld(_ZN11Query_cache11store_queryEP3THDP10TABLE_LIST+0x39e)[0x5bff5e] /usr/sbin/mysqld[0x5eb02d] /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x269b)[0x5f0aeb] /usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x18a)[0x5f2cba] /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1321)[0x5f4861] /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x167)[0x68d807] /usr/sbin/mysqld(handle_one_connection+0x54)[0x68d874] /lib64/libpthread.so.0[0x7f6f491ab040] /lib64/libc.so.6(clone+0x6d)[0x7f6f4847c08d] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Query Cache Crashing
As for the crash, I don't know. Instead, I recommend either shrinking the size (if you use a big QC) or turning it off. This would make the issue go away. ANY modification to a particular table leads to ALL entries in the Query cache being purged. For that reason, we (Yahoo) almost never use the QC on any of our many servers. Please provide SHOW GLOBAL VARIABLES LIKE 'query%'; SHOW GLOBAL STATUS LIKE 'Qc%'; Because of inefficiencies in 'pruning', having a query_cache_size bigger than 50M may actually degrade performance. In you have a reproducible test case, submit to bugs.mysql.com . -Original Message- From: Ben Clewett [mailto:b...@clewett.org.uk] Sent: Tuesday, October 09, 2012 4:47 AM To: mysql@lists.mysql.com Subject: Query Cache Crashing Hi MySql, Since upgrading to 5.5.27 (5.5.27-ndb-7.2.8-cluster-gpl-log to be exact) I have experienced problems with the query cache. I am wondering if I am the only one? Twice I have had a core-dump (show at end of mail) on two separate servers running this version. Now I had a complete lock, where 'SHOW PROCESSLIST' showed this for every connection: Waiting for query cache lock This resulted in connections building until the limit was hit. I could only cure this with a 'kill -9' on the mysqld process. Are any other users experiencing this? Do any users know of a fix? Thanks! Ben Clewett. Thread pointer: 0x7f6ea014cf90 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 410220e8 thread_stack 0x4 /usr/sbin/mysqld(my_print_stacktrace+0x35)[0x81c7f5] /usr/sbin/mysqld(handle_fatal_signal+0x403)[0x6e9383] /lib64/libpthread.so.0[0x7f6f491b2b30] /usr/sbin/mysqld(_ZN11Query_cache12unlink_tableEP23Query_cache_block_ta ble+0x1b)[0x5bd1ab] /usr/sbin/mysqld(_ZN11Query_cache19free_query_internalEP17Query_cache_b lock+0x7a)[0x5bdb5a] /usr/sbin/mysqld(_ZN11Query_cache27invalidate_query_block_listEP3THDP23 Query_cache_block_table+0x7e)[0x5be8ee] /usr/sbin/mysqld(_ZN11Query_cache12insert_tableEjPcP23Query_cache_block _tablejhPFcP3THDS0_jPyEy+0x91)[0x5bf7e1] /usr/sbin/mysqld(_ZN11Query_cache25register_tables_from_listEP10TABLE_L ISTjP23Query_cache_block_table+0x176)[0x5bfa76] /usr/sbin/mysqld(_ZN11Query_cache19register_all_tablesEP17Query_cache_b lockP10TABLE_LISTj+0x15)[0x5bfbb5] /usr/sbin/mysqld(_ZN11Query_cache11store_queryEP3THDP10TABLE_LIST+0x39e )[0x5bff5e] /usr/sbin/mysqld[0x5eb02d] /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x269b)[0x5f0aeb] /usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x18a)[0x5f2cba ] /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x13 21)[0x5f4861] /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x167)[0x68d807] /usr/sbin/mysqld(handle_one_connection+0x54)[0x68d874] /lib64/libpthread.so.0[0x7f6f491ab040] /lib64/libc.so.6(clone+0x6d)[0x7f6f4847c08d] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Questions regarding Query cache usage
On Tue, Jun 8, 2010 at 10:57 PM, Machiel Richards machi...@rdc.co.za wrote: Good morning all I would like to try and find out how you can see what is using the query cache. My reason for asking is the following: On one of our client databases, the query cache is set to 128Mb and the usage always varied between 5% and 53% and basically never went above that. However, this morning I noticed that the query cache usage is at 99.98% which is very odd for the database. How are you determining the cache usage? I don't think 99.98% utilitzation is a bad thing. It would be preferable to wasting memory on a cache that is under-utilized. Does anybody have an idea on how to determine why this usage is suddenly this high and if we should look at increasing the query cache size or not? Has a new workload been introduced to the server? The cache utilization may be indicative of a lot of small repeated queries being introduced. You can monitor the Qcache_lowmem_prunes and Qcache_free_blocks to determine if you can benefit from increased query cache size. http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/ I also have a second question relating to a previous post I sent through but never really received a definitive answer. The client database is setup with a master slave replication, the master Innodb buffer pool usage is at 4Gb at present (no more system memory available to increase this) We are starting to receive errors on the slave server however relating to the innodb buffer pool size being used up and there is no place to add more locks. This was found to be related to the slave server's innodb buffer pool size that is currently still set to 8mb. I would like to know whether it will be worth changing the value on the slave server to match that of the master server or will this cause more problems? If the memory is available, why not use it? It seems like the default buffer pool size out of the box was just never changed. Kyong -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Questions regarding Query cache usage
On Wed, Jun 9, 2010 at 8:04 PM, Kyong Kim kykim...@gmail.com wrote: If the memory is available, why not use it? It seems like the default buffer pool size out of the box was just never changed. Agreed, of course, but if something happens on a system that is out of the ordinary, it's very good practice to hunt the cause down before it makes more undesireable things happen. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Questions regarding Query cache usage
Absolutely. You don't want to obscure the cause by just throwing more hardware at things. That approach just buys you time until a bigger pile hits the fan if the underlying issue remains unresolved. At the same time, though, 8 MB production innodb buffer pool allocation should be fairly high on the list of things to scrutinize. Kyong On Wed, Jun 9, 2010 at 12:12 PM, Johan De Meersman vegiv...@tuxera.be wrote: On Wed, Jun 9, 2010 at 8:04 PM, Kyong Kim kykim...@gmail.com wrote: If the memory is available, why not use it? It seems like the default buffer pool size out of the box was just never changed. Agreed, of course, but if something happens on a system that is out of the ordinary, it's very good practice to hunt the cause down before it makes more undesireable things happen. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Questions regarding Query cache usage
Good morning all I would like to try and find out how you can see what is using the query cache. My reason for asking is the following: On one of our client databases, the query cache is set to 128Mb and the usage always varied between 5% and 53% and basically never went above that. However, this morning I noticed that the query cache usage is at 99.98% which is very odd for the database. Does anybody have an idea on how to determine why this usage is suddenly this high and if we should look at increasing the query cache size or not? I also have a second question relating to a previous post I sent through but never really received a definitive answer. The client database is setup with a master slave replication, the master Innodb buffer pool usage is at 4Gb at present (no more system memory available to increase this) We are starting to receive errors on the slave server however relating to the innodb buffer pool size being used up and there is no place to add more locks. This was found to be related to the slave server's innodb buffer pool size that is currently still set to 8mb. I would like to know whether it will be worth changing the value on the slave server to match that of the master server or will this cause more problems? Really hoping someone can assist here and all help is appreciated. Machiel
Innodb Buffer Pool vs Query Cache
I am using the innodb storage engine for a table that is used for a lot of SELECT's on columns that are defined as indexes. I have not enabled the query cache as of now since the innodb buffer pool already caches data and index information for InnoDB tables. So my question is - is the query cache used primarily for MyISAM tables or is it also useful for InnoDB tables (considering that the InnoDB has a buffer pool in place)? If I do enable the query cache, is there an additional overhead of maintaining two buffers that essentially contain the same data? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Innodb Buffer Pool vs Query Cache
The query cache lays at the server level, above individual engine implementations, and thus affects all of them. It (case-sensitively!) compares the current query with the queries in the query cache, and if there's an EXACT match (including all parameter positions, wheres, and whatnot), it returns the data immediately, thus saving you the parse and execute steps. That's a lot of benefit, given that you run the same identical query lots of times. There will of course be some extra overhead when you enable it, but unless you have some sort of data mining application that only executes every individual query once and only once, you'll reap the benefits of it. On Thu, Nov 12, 2009 at 10:00 AM, Aveek Misra ave...@yahoo-inc.com wrote: I am using the innodb storage engine for a table that is used for a lot of SELECT's on columns that are defined as indexes. I have not enabled the query cache as of now since the innodb buffer pool already caches data and index information for InnoDB tables. So my question is - is the query cache used primarily for MyISAM tables or is it also useful for InnoDB tables (considering that the InnoDB has a buffer pool in place)? If I do enable the query cache, is there an additional overhead of maintaining two buffers that essentially contain the same data? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
query cache extension
Hi, I'm thinking of extending the query cache to deal with following scenarios, 1. stop invalidating the cache entries when updates take place for columns which are not in the select list of the cached queries 2. when a second query contains a subset of columns from a first query (which has cached results) obtain the results from the cache, this will not happen due to the difference in the query, but since the select list of the second query is a subset of the select list of the first query (and the where clause should be identical) we can obtain the results from the cache. 3. identify the queries with identical select lists but having column names in different orders and process them as identical queries and take the advantage of query cache. Still this is in idea level and I haven't done any coding yet. I just wanted to try out this as an experiment and check the performance on a database where the updates are relatively less. I know that this needs some more memory for the query cache, and need some additional query parsing. Since I'm a beginner in this area I would like to know whether this is doable and any problems that may arise Thanks, Amila -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Query cache question
Hey, Currently, afaik, when you update a table, MySQL will flush all query cache entries for that table. I've heard that work was ongoing to change this behavior to only the affected rows, but I can't seem to find much information on it. Does anyone know the status of this ? Thx, Johan -- That which does not kill you was simply not permitted to do so for the purposes of the plot. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Why can't I kill the query cache?
I had heard of that trick but never looked it up. 'man proc' tells me /proc/sys/vm/drop_caches (since Linux 2.6.16) Writing to this file causes the kernel to drop clean caches, dentries and inodes from memory, causing that memory to become free. To free pagecache, use echo 1/proc/sys/vm/drop_caches; to free dentries and inodes, use echo 2 /proc/sys/vm/drop_caches; to free pagecache, dentries and inodes, use echo 3 /proc/sys/vm/drop_caches. Because this is a non-destructive operation and dirty objects are not freeable, the user should run sync(8) first. I should read the whole man page... On Fri, May 29, 2009 at 5:59 PM, Eric Bergen eric.ber...@gmail.com wrote: You can also flush the cache with echo 1 /proc/sys/vm/drop_caches if you have a new enough kernel. On Fri, May 29, 2009 at 2:16 PM, Dan Nelson dnel...@allantgroup.com wrote: In the last episode (May 29), Gerald L. Clark said: Little, Timothy wrote: Also titled, I want this to run slow ALL the time... I have a group of dreadful queries that I have to optimize. Some take 20-30 seconds each -- the first time that I run them. But then they never seem to take that long after the first time (taking less than a second then). If I change the keywords searched for in the where clauses, then they take a long time again... so it's the query-cache or something just like it. BUT, I am doing this each time : flush tables; reset query cache; set global query_cache_size=0; SELECT SQL_NO_CACHE DISTINCT ca.conceptid AS headingid, And still it's not avoiding the cache. Is there a cache I'm missing? Tim... Disk cache, but I don't know how to clear it. Create a file 2x the size of your RAM (for a 2gb system, dd if=/dev/zero of=bigfile bs=1024k count=4096), then dd it to /dev/null (dd if=bigfile of=/dev/null bs=1024k). That should flush your OS cache. The guaranteed way would be to dismount then remount your filesystem, but that could be difficult depending on how many other processes are using it.. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=eric.ber...@gmail.com -- Eric Bergen eric.ber...@gmail.com http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ba...@xaprb.com -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Why can't I kill the query cache?
Also titled, I want this to run slow ALL the time... I have a group of dreadful queries that I have to optimize. Some take 20-30 seconds each -- the first time that I run them. But then they never seem to take that long after the first time (taking less than a second then). If I change the keywords searched for in the where clauses, then they take a long time again... so it's the query-cache or something just like it. BUT, I am doing this each time : flush tables; reset query cache; set global query_cache_size=0; SELECT SQL_NO_CACHE DISTINCT ca.conceptid AS headingid, And still it's not avoiding the cache. Is there a cache I'm missing? Tim... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Why can't I kill the query cache?
Little, Timothy wrote: Also titled, I want this to run slow ALL the time... I have a group of dreadful queries that I have to optimize. Some take 20-30 seconds each -- the first time that I run them. But then they never seem to take that long after the first time (taking less than a second then). If I change the keywords searched for in the where clauses, then they take a long time again... so it's the query-cache or something just like it. BUT, I am doing this each time : flush tables; reset query cache; set global query_cache_size=0; SELECT SQL_NO_CACHE DISTINCT ca.conceptid AS headingid, And still it's not avoiding the cache. Is there a cache I'm missing? Tim... Disk cache, but I don't know how to clear it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Why can't I kill the query cache?
In the last episode (May 29), Gerald L. Clark said: Little, Timothy wrote: Also titled, I want this to run slow ALL the time... I have a group of dreadful queries that I have to optimize. Some take 20-30 seconds each -- the first time that I run them. But then they never seem to take that long after the first time (taking less than a second then). If I change the keywords searched for in the where clauses, then they take a long time again... so it's the query-cache or something just like it. BUT, I am doing this each time : flush tables; reset query cache; set global query_cache_size=0; SELECT SQL_NO_CACHE DISTINCT ca.conceptid AS headingid, And still it's not avoiding the cache. Is there a cache I'm missing? Tim... Disk cache, but I don't know how to clear it. Create a file 2x the size of your RAM (for a 2gb system, dd if=/dev/zero of=bigfile bs=1024k count=4096), then dd it to /dev/null (dd if=bigfile of=/dev/null bs=1024k). That should flush your OS cache. The guaranteed way would be to dismount then remount your filesystem, but that could be difficult depending on how many other processes are using it.. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Why can't I kill the query cache?
You can also flush the cache with echo 1 /proc/sys/vm/drop_caches if you have a new enough kernel. On Fri, May 29, 2009 at 2:16 PM, Dan Nelson dnel...@allantgroup.com wrote: In the last episode (May 29), Gerald L. Clark said: Little, Timothy wrote: Also titled, I want this to run slow ALL the time... I have a group of dreadful queries that I have to optimize. Some take 20-30 seconds each -- the first time that I run them. But then they never seem to take that long after the first time (taking less than a second then). If I change the keywords searched for in the where clauses, then they take a long time again... so it's the query-cache or something just like it. BUT, I am doing this each time : flush tables; reset query cache; set global query_cache_size=0; SELECT SQL_NO_CACHE DISTINCT ca.conceptid AS headingid, And still it's not avoiding the cache. Is there a cache I'm missing? Tim... Disk cache, but I don't know how to clear it. Create a file 2x the size of your RAM (for a 2gb system, dd if=/dev/zero of=bigfile bs=1024k count=4096), then dd it to /dev/null (dd if=bigfile of=/dev/null bs=1024k). That should flush your OS cache. The guaranteed way would be to dismount then remount your filesystem, but that could be difficult depending on how many other processes are using it.. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=eric.ber...@gmail.com -- Eric Bergen eric.ber...@gmail.com http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysqladministrator and differents query cache hits graphs
I wonder why with tre different MySQL administrator istances I have 3 different QueryCache Hitrate on the same database, in the same time :( I'm trying with: - MySQL administrator v.1.2.12 (Ubuntu 8.10): the query hitcache is always at 100% (average 100) - MySQL administrator v.1.2.5rc (Debian etc): average 86% - MySQL administrator v.1.2.12 (Max OS X 10.4.11): average 40% NB: the graph formula is always the same (the default one), then: (^[Qcache_hits]/(^[Qcache_hits]+^[QCache_inserts]+^ [Qcache_not_cached]))*100 which one is correct? and why I have so different results?! thank you in advance bye MAS! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Query Cache questions
Hi, I have two questions regarding the query cache: 1. What is the difference between query_cache_limit and query_cache_size? mysql show variables like '%query_cache%'; have_query_cache | YES | query_cache_limit| 1048576 | query_cache_min_res_unit | 4096 | query_cache_size | 20971520 | query_cache_type | ON | query_cache_wlock_invalidate | OFF | 2. Would an increase of the query cache size bring a positive effect to the performance? mysqlshow status like '%Qcache%'; Qcache_free_blocks | 414 | Qcache_free_memory | 17000952 | Qcache_hits | 228827 | Qcache_inserts | 731980 | Qcache_lowmem_prunes| 0| Qcache_not_cached | 70644| Qcache_queries_in_cache | 3042 | Qcache_total_blocks | 6584 | 3. How can I calculate a query cache hit rate? Thanks, Uwe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Cache questions
On Wed, Sep 17, 2008 at 11:37 AM, Uwe Kiewel [EMAIL PROTECTED] wrote: Hi, I have two questions regarding the query cache: 1. What is the difference between query_cache_limit and query_cache_size? query_cache_limit is the maximum size of a single resultset in the cache. If your query results in a bigger resultset it will never get into the cache even if its room for it in the cache itself. query_cache_size is the maximum amount of data that may be stored in the cache. I don't know if I managed to explain this in a understandable way, but it's worth a shot... -- mvh base (Bård Aase) MSN: [EMAIL PROTECTED] http://blog.elzapp.com :wq -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Cache questions
Bard Aase schrieb: On Wed, Sep 17, 2008 at 11:37 AM, Uwe Kiewel [EMAIL PROTECTED] wrote: Hi, I have two questions regarding the query cache: 1. What is the difference between query_cache_limit and query_cache_size? query_cache_limit is the maximum size of a single resultset in the cache. If your query results in a bigger resultset it will never get into the cache even if its room for it in the cache itself. query_cache_size is the maximum amount of data that may be stored in the cache. Do you talk about all usable cache memory for all resultsets? Thanks, Uwe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Cache questions
On Wed, Sep 17, 2008 at 11:54 AM, Uwe Kiewel [EMAIL PROTECTED] wrote: Bard Aase schrieb: On Wed, Sep 17, 2008 at 11:37 AM, Uwe Kiewel [EMAIL PROTECTED] wrote: Hi, I have two questions regarding the query cache: 1. What is the difference between query_cache_limit and query_cache_size? query_cache_limit is the maximum size of a single resultset in the cache. If your query results in a bigger resultset it will never get into the cache even if its room for it in the cache itself. query_cache_size is the maximum amount of data that may be stored in the cache. Do you talk about all usable cache memory for all resultsets? Yes -- mvh base (Bård Aase) MSN: [EMAIL PROTECTED] http://blog.elzapp.com :wq -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Cache questions
query_cache_limit is the maximum size of a single resultset in the cache. If your query results in a bigger resultset it will never get into the cache even if its room for it in the cache itself. Even if there is room why will it not fit into the cache. On 9/17/08, Bard Aase [EMAIL PROTECTED] wrote: On Wed, Sep 17, 2008 at 11:54 AM, Uwe Kiewel [EMAIL PROTECTED] wrote: Bard Aase schrieb: On Wed, Sep 17, 2008 at 11:37 AM, Uwe Kiewel [EMAIL PROTECTED] wrote: Hi, I have two questions regarding the query cache: 1. What is the difference between query_cache_limit and query_cache_size? query_cache_limit is the maximum size of a single resultset in the cache. If your query results in a bigger resultset it will never get into the cache even if its room for it in the cache itself. query_cache_size is the maximum amount of data that may be stored in the cache. Do you talk about all usable cache memory for all resultsets? Yes -- mvh base (Bård Aase) MSN: [EMAIL PROTECTED] http://blog.elzapp.com :wq -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Cache questions
On Wed, Sep 17, 2008 at 12:42 PM, Ananda Kumar [EMAIL PROTECTED] wrote: query_cache_limit is the maximum size of a single resultset in the cache. If your query results in a bigger resultset it will never get into the cache even if its room for it in the cache itself. Even if there is room why will it not fit into the cache. Because the query_cache_limit prevents it. Why you'd want to limit that is another question. It's probably to get a more even distribution of whats in the cache, so not one single query makes mysql flush out the whole rest of the cache. Lets say you make 3 queries, where the second one is a query with a huge resultset. SMALL, HEAVY QUERY HUGE, SIMPLE QUERY SMALL, HEAVY QUERY again (the same as the first one) And the two others are the another query, wich is slow to perform. A query_cache_limit might prevent the first query from beeing flushed, which will make you able to use the cache for the third query. On 9/17/08, Bard Aase [EMAIL PROTECTED] wrote: On Wed, Sep 17, 2008 at 11:54 AM, Uwe Kiewel [EMAIL PROTECTED] wrote: Bard Aase schrieb: On Wed, Sep 17, 2008 at 11:37 AM, Uwe Kiewel [EMAIL PROTECTED] wrote: Hi, I have two questions regarding the query cache: 1. What is the difference between query_cache_limit and query_cache_size? query_cache_limit is the maximum size of a single resultset in the cache. If your query results in a bigger resultset it will never get into the cache even if its room for it in the cache itself. query_cache_size is the maximum amount of data that may be stored in the cache. Do you talk about all usable cache memory for all resultsets? Yes -- mvh base (Bård Aase) MSN: [EMAIL PROTECTED] http://blog.elzapp.com :wq -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- mvh base (Bård Aase) MSN: [EMAIL PROTECTED] http://blog.elzapp.com :wq -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Cache questions
okie..got u On 9/17/08, Bard Aase [EMAIL PROTECTED] wrote: On Wed, Sep 17, 2008 at 12:42 PM, Ananda Kumar [EMAIL PROTECTED] wrote: query_cache_limit is the maximum size of a single resultset in the cache. If your query results in a bigger resultset it will never get into the cache even if its room for it in the cache itself. Even if there is room why will it not fit into the cache. Because the query_cache_limit prevents it. Why you'd want to limit that is another question. It's probably to get a more even distribution of whats in the cache, so not one single query makes mysql flush out the whole rest of the cache. Lets say you make 3 queries, where the second one is a query with a huge resultset. SMALL, HEAVY QUERY HUGE, SIMPLE QUERY SMALL, HEAVY QUERY again (the same as the first one) And the two others are the another query, wich is slow to perform. A query_cache_limit might prevent the first query from beeing flushed, which will make you able to use the cache for the third query. On 9/17/08, Bard Aase [EMAIL PROTECTED] wrote: On Wed, Sep 17, 2008 at 11:54 AM, Uwe Kiewel [EMAIL PROTECTED] wrote: Bard Aase schrieb: On Wed, Sep 17, 2008 at 11:37 AM, Uwe Kiewel [EMAIL PROTECTED] wrote: Hi, I have two questions regarding the query cache: 1. What is the difference between query_cache_limit and query_cache_size? query_cache_limit is the maximum size of a single resultset in the cache. If your query results in a bigger resultset it will never get into the cache even if its room for it in the cache itself. query_cache_size is the maximum amount of data that may be stored in the cache. Do you talk about all usable cache memory for all resultsets? Yes -- mvh base (Bård Aase) MSN: [EMAIL PROTECTED] http://blog.elzapp.com :wq -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- mvh base (Bård Aase) MSN: [EMAIL PROTECTED] http://blog.elzapp.com :wq
Query cache contents
Hey, Guys Is there a way to know what queries are in the query cache? thanks
Re: Query cache contents
http://rpbouman.blogspot.com/2008/07/inspect-query-cahce-using-mysql.html - parvesh On Wed, Sep 17, 2008 at 1:15 AM, Edson Noboru Yamada [EMAIL PROTECTED] wrote: Hey, Guys Is there a way to know what queries are in the query cache? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: corrupted query cache?
Hi Max, Max Thayer wrote: What's your question? Heh, yah that might be important... Has anyone experienced this? Does it look like anything in the my.cnf could be altered to alleviate or assist me in diagnosing the problem? I do not get an indication of any problems in the log files or the bin-logs. How might I efficiently trouble shooting this, where might I begin? I can't figure out how to replicate it, it seems to be quite sporadic. What would corrupt query results, temporarily fixed with a mysqld restart? If it's the query cache, there are two relatively simple things you can do: when you get a corrupt result, change the query slightly so it's not a cache hit, and retry. You can also flush or disable the query cache. I don't see anything wrong with the config, though I'm curious about SERIALIZABLE isolation level; it's not something I see often. It shouldn't have anything to do with this. Baron Max Thayer wrote: We are experiencing a certain anomaly here on our db server. The problem had occurred about 3 weeks ago. We ran diagnostics on the hardware over a 48+hour period with no failures or indications of problems with hardware. ( at least the memory.) We rebooted the server, and had not experienced anymore issues until yesterday, it repeated itself. The issue is as follows We run a series of views, sp's and user defined functions for a period of time. During application development we run these from command line, from MySQL query browser and from a series of PHP and Perl Scripts (of which some are executed from web apps others from cli ). At some point, the data returned to the user appears corrupted. Bogus data is returned, whether it be from a query to the actual table or to a view. Upon rebooting the server the data is returned to normal (ie. the data tables written to hdd are fine, it appears only the cached results are fubar). I can point out also if it matters that all my tables are INNODB We are currently developing on a semi-production DB( i know...hands need to be slapped ) We are using Apache 2.2 on a similar BSD server. Server specs follow... * FreeBSD 6.1 Release #0 mysql-server-5.0.45_1 mysql-client-5.0.45_1 * Hardware: Copyright (c) 1992-2006 The FreeBSD Project. Copyright (c) 1979, 1980, 1983, 1986, 1988, 1989, 1991, 1992, 1993, 1994 The Regents of the University of California. All rights reserved. FreeBSD 6.1-RELEASE #0: Sun May 7 04:42:56 UTC 2006 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/SMP Timecounter i8254 frequency 1193182 Hz quality 0 CPU: Intel(R) Xeon(TM) CPU 3.06GHz (3065.81-MHz 686-class CPU) Origin = GenuineIntel Id = 0xf29 Stepping = 9 Features=0xbfebfbffFPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE ,MCA ,CMOV,PAT,PSE36,CLFLUSH,DTS,ACPI,MMX,FXSR,SSE,SSE2,SS,HTT,TM,PBE Features2=0x4400CNTX-ID,b14 Logical CPUs per core: 2 real memory = 2146959360 (2047 MB) avail memory = 2095874048 (1998 MB) ACPI APIC Table: PTLTD APIC FreeBSD/SMP: Multiprocessor System Detected: 4 CPUs cpu0 (BSP): APIC ID: 0 cpu1 (AP): APIC ID: 1 cpu2 (AP): APIC ID: 6 cpu3 (AP): APIC ID: 7 ioapic0 Version 2.0 irqs 0-23 on motherboard ioapic1 Version 2.0 irqs 24-47 on motherboard ioapic2 Version 2.0 irqs 48-71 on motherboard ioapic3 Version 2.0 irqs 72-95 on motherboard ioapic4 Version 2.0 irqs 96-119 on motherboard kbd1 at kbdmux0 acpi0: PTLTD RSDT on motherboard acpi0: Power Button (fixed) Timecounter ACPI-fast frequency 3579545 Hz quality 1000 acpi_timer0: 24-bit timer at 3.579545MHz port 0x1008-0x100b on acpi0 cpu0: ACPI CPU on acpi0 cpu1: ACPI CPU on acpi0 cpu2: ACPI CPU on acpi0 cpu3: ACPI CPU on acpi0 pcib0: ACPI Host-PCI bridge port 0xcf8-0xcff on acpi0 pci0: ACPI PCI bus on pcib0 pci0: unknown at device 0.1 (no driver attached) pcib1: ACPI PCI-PCI bridge at device 2.0 on pci0 pci1: ACPI PCI bus on pcib1 pci1: base peripheral, interrupt controller at device 28.0 (no driver attached) pcib2: ACPI PCI-PCI bridge at device 29.0 on pci1 pci2: ACPI PCI bus on pcib2 pci1: base peripheral, interrupt controller at device 30.0 (no driver attached) pcib3: ACPI PCI-PCI bridge at device 31.0 on pci1 pci3: ACPI PCI bus on pcib3 em0: Intel(R) PRO/1000 Network Connection Version - 3.2.18 port 0x3000-0x303f mem 0xf020-0xf021 irq 28 at device 2.0 on pci3 em0: Ethernet address: 00:30:48:2f:13:7c em1: Intel(R) PRO/1000 Network Connection Version - 3.2.18 port 0x3040-0x307f mem 0xf022-0xf023 irq 29 at device 2.1 on pci3 em1: Ethernet address: 00:30:48:2f:13:7d pcib4: ACPI PCI-PCI bridge at device 3.0 on pci0 pci4: ACPI PCI bus on pcib4 pci4: base peripheral, interrupt controller at device 28.0 (no driver attached) pcib5: ACPI PCI-PCI bridge at device 29.0 on pci4 pci5: ACPI PCI bus on pcib5 pci4: base
corrupted query cache?
We are experiencing a certain anomaly here on our db server. The problem had occurred about 3 weeks ago. We ran diagnostics on the hardware over a 48+hour period with no failures or indications of problems with hardware. ( at least the memory.) We rebooted the server, and had not experienced anymore issues until yesterday, it repeated itself. The issue is as follows We run a series of views, sp's and user defined functions for a period of time. During application development we run these from command line, from MySQL query browser and from a series of PHP and Perl Scripts (of which some are executed from web apps others from cli ). At some point, the data returned to the user appears corrupted. Bogus data is returned, whether it be from a query to the actual table or to a view. Upon rebooting the server the data is returned to normal (ie. the data tables written to hdd are fine, it appears only the cached results are fubar). I can point out also if it matters that all my tables are INNODB We are currently developing on a semi-production DB( i know...hands need to be slapped ) We are using Apache 2.2 on a similar BSD server. Server specs follow... * FreeBSD 6.1 Release #0 mysql-server-5.0.45_1 mysql-client-5.0.45_1 * Hardware: Copyright (c) 1992-2006 The FreeBSD Project. Copyright (c) 1979, 1980, 1983, 1986, 1988, 1989, 1991, 1992, 1993, 1994 The Regents of the University of California. All rights reserved. FreeBSD 6.1-RELEASE #0: Sun May 7 04:42:56 UTC 2006 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/SMP Timecounter i8254 frequency 1193182 Hz quality 0 CPU: Intel(R) Xeon(TM) CPU 3.06GHz (3065.81-MHz 686-class CPU) Origin = GenuineIntel Id = 0xf29 Stepping = 9 Features=0xbfebfbffFPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE ,MCA ,CMOV,PAT,PSE36,CLFLUSH,DTS,ACPI,MMX,FXSR,SSE,SSE2,SS,HTT,TM,PBE Features2=0x4400CNTX-ID,b14 Logical CPUs per core: 2 real memory = 2146959360 (2047 MB) avail memory = 2095874048 (1998 MB) ACPI APIC Table: PTLTD APIC FreeBSD/SMP: Multiprocessor System Detected: 4 CPUs cpu0 (BSP): APIC ID: 0 cpu1 (AP): APIC ID: 1 cpu2 (AP): APIC ID: 6 cpu3 (AP): APIC ID: 7 ioapic0 Version 2.0 irqs 0-23 on motherboard ioapic1 Version 2.0 irqs 24-47 on motherboard ioapic2 Version 2.0 irqs 48-71 on motherboard ioapic3 Version 2.0 irqs 72-95 on motherboard ioapic4 Version 2.0 irqs 96-119 on motherboard kbd1 at kbdmux0 acpi0: PTLTD RSDT on motherboard acpi0: Power Button (fixed) Timecounter ACPI-fast frequency 3579545 Hz quality 1000 acpi_timer0: 24-bit timer at 3.579545MHz port 0x1008-0x100b on acpi0 cpu0: ACPI CPU on acpi0 cpu1: ACPI CPU on acpi0 cpu2: ACPI CPU on acpi0 cpu3: ACPI CPU on acpi0 pcib0: ACPI Host-PCI bridge port 0xcf8-0xcff on acpi0 pci0: ACPI PCI bus on pcib0 pci0: unknown at device 0.1 (no driver attached) pcib1: ACPI PCI-PCI bridge at device 2.0 on pci0 pci1: ACPI PCI bus on pcib1 pci1: base peripheral, interrupt controller at device 28.0 (no driver attached) pcib2: ACPI PCI-PCI bridge at device 29.0 on pci1 pci2: ACPI PCI bus on pcib2 pci1: base peripheral, interrupt controller at device 30.0 (no driver attached) pcib3: ACPI PCI-PCI bridge at device 31.0 on pci1 pci3: ACPI PCI bus on pcib3 em0: Intel(R) PRO/1000 Network Connection Version - 3.2.18 port 0x3000-0x303f mem 0xf020-0xf021 irq 28 at device 2.0 on pci3 em0: Ethernet address: 00:30:48:2f:13:7c em1: Intel(R) PRO/1000 Network Connection Version - 3.2.18 port 0x3040-0x307f mem 0xf022-0xf023 irq 29 at device 2.1 on pci3 em1: Ethernet address: 00:30:48:2f:13:7d pcib4: ACPI PCI-PCI bridge at device 3.0 on pci0 pci4: ACPI PCI bus on pcib4 pci4: base peripheral, interrupt controller at device 28.0 (no driver attached) pcib5: ACPI PCI-PCI bridge at device 29.0 on pci4 pci5: ACPI PCI bus on pcib5 pci4: base peripheral, interrupt controller at device 30.0 (no driver attached) pcib6: ACPI PCI-PCI bridge at device 31.0 on pci4 pci6: ACPI PCI bus on pcib6 aac0: Adaptec SCSI RAID 2020ZCR mem 0xf100-0xf1ff,0xf040-0xf05f,0xf800-0xfbff irq 72 at device 1.0 on pci6 aac0: New comm. interface enabled aac0: Adaptec Raid Controller 2.0.0-1 aacp0: SCSI Passthrough Bus on aac0 aacp1: SCSI Passthrough Bus on aac0 uhci0: Intel 82801CA/CAM (ICH3) USB controller USB-A port 0x2000-0x201f irq 16 at device 29.0 on pci0 uhci0: [GIANT-LOCKED] usb0: Intel 82801CA/CAM (ICH3) USB controller USB-A on uhci0 usb0: USB revision 1.0 uhub0: Intel UHCI root hub, class 9/0, rev 1.00/1.00, addr 1 uhub0: 2 ports with 2 removable, self powered uhci1: Intel 82801CA/CAM (ICH3) USB controller USB-B port 0x2020-0x203f irq 19 at device 29.1 on pci0 uhci1: [GIANT-LOCKED] usb1: Intel 82801CA/CAM (ICH3) USB controller USB-B on uhci1 usb1: USB revision 1.0 uhub1: Intel UHCI root hub, class 9/0, rev
Re: corrupted query cache?
What's your question? Max Thayer wrote: We are experiencing a certain anomaly here on our db server. The problem had occurred about 3 weeks ago. We ran diagnostics on the hardware over a 48+hour period with no failures or indications of problems with hardware. ( at least the memory.) We rebooted the server, and had not experienced anymore issues until yesterday, it repeated itself. The issue is as follows We run a series of views, sp's and user defined functions for a period of time. During application development we run these from command line, from MySQL query browser and from a series of PHP and Perl Scripts (of which some are executed from web apps others from cli ). At some point, the data returned to the user appears corrupted. Bogus data is returned, whether it be from a query to the actual table or to a view. Upon rebooting the server the data is returned to normal (ie. the data tables written to hdd are fine, it appears only the cached results are fubar). I can point out also if it matters that all my tables are INNODB We are currently developing on a semi-production DB( i know...hands need to be slapped ) We are using Apache 2.2 on a similar BSD server. Server specs follow... * FreeBSD 6.1 Release #0 mysql-server-5.0.45_1 mysql-client-5.0.45_1 * Hardware: Copyright (c) 1992-2006 The FreeBSD Project. Copyright (c) 1979, 1980, 1983, 1986, 1988, 1989, 1991, 1992, 1993, 1994 The Regents of the University of California. All rights reserved. FreeBSD 6.1-RELEASE #0: Sun May 7 04:42:56 UTC 2006 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/SMP Timecounter i8254 frequency 1193182 Hz quality 0 CPU: Intel(R) Xeon(TM) CPU 3.06GHz (3065.81-MHz 686-class CPU) Origin = GenuineIntel Id = 0xf29 Stepping = 9 Features=0xbfebfbffFPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE ,MCA ,CMOV,PAT,PSE36,CLFLUSH,DTS,ACPI,MMX,FXSR,SSE,SSE2,SS,HTT,TM,PBE Features2=0x4400CNTX-ID,b14 Logical CPUs per core: 2 real memory = 2146959360 (2047 MB) avail memory = 2095874048 (1998 MB) ACPI APIC Table: PTLTD APIC FreeBSD/SMP: Multiprocessor System Detected: 4 CPUs cpu0 (BSP): APIC ID: 0 cpu1 (AP): APIC ID: 1 cpu2 (AP): APIC ID: 6 cpu3 (AP): APIC ID: 7 ioapic0 Version 2.0 irqs 0-23 on motherboard ioapic1 Version 2.0 irqs 24-47 on motherboard ioapic2 Version 2.0 irqs 48-71 on motherboard ioapic3 Version 2.0 irqs 72-95 on motherboard ioapic4 Version 2.0 irqs 96-119 on motherboard kbd1 at kbdmux0 acpi0: PTLTD RSDT on motherboard acpi0: Power Button (fixed) Timecounter ACPI-fast frequency 3579545 Hz quality 1000 acpi_timer0: 24-bit timer at 3.579545MHz port 0x1008-0x100b on acpi0 cpu0: ACPI CPU on acpi0 cpu1: ACPI CPU on acpi0 cpu2: ACPI CPU on acpi0 cpu3: ACPI CPU on acpi0 pcib0: ACPI Host-PCI bridge port 0xcf8-0xcff on acpi0 pci0: ACPI PCI bus on pcib0 pci0: unknown at device 0.1 (no driver attached) pcib1: ACPI PCI-PCI bridge at device 2.0 on pci0 pci1: ACPI PCI bus on pcib1 pci1: base peripheral, interrupt controller at device 28.0 (no driver attached) pcib2: ACPI PCI-PCI bridge at device 29.0 on pci1 pci2: ACPI PCI bus on pcib2 pci1: base peripheral, interrupt controller at device 30.0 (no driver attached) pcib3: ACPI PCI-PCI bridge at device 31.0 on pci1 pci3: ACPI PCI bus on pcib3 em0: Intel(R) PRO/1000 Network Connection Version - 3.2.18 port 0x3000-0x303f mem 0xf020-0xf021 irq 28 at device 2.0 on pci3 em0: Ethernet address: 00:30:48:2f:13:7c em1: Intel(R) PRO/1000 Network Connection Version - 3.2.18 port 0x3040-0x307f mem 0xf022-0xf023 irq 29 at device 2.1 on pci3 em1: Ethernet address: 00:30:48:2f:13:7d pcib4: ACPI PCI-PCI bridge at device 3.0 on pci0 pci4: ACPI PCI bus on pcib4 pci4: base peripheral, interrupt controller at device 28.0 (no driver attached) pcib5: ACPI PCI-PCI bridge at device 29.0 on pci4 pci5: ACPI PCI bus on pcib5 pci4: base peripheral, interrupt controller at device 30.0 (no driver attached) pcib6: ACPI PCI-PCI bridge at device 31.0 on pci4 pci6: ACPI PCI bus on pcib6 aac0: Adaptec SCSI RAID 2020ZCR mem 0xf100-0xf1ff,0xf040-0xf05f,0xf800-0xfbff irq 72 at device 1.0 on pci6 aac0: New comm. interface enabled aac0: Adaptec Raid Controller 2.0.0-1 aacp0: SCSI Passthrough Bus on aac0 aacp1: SCSI Passthrough Bus on aac0 uhci0: Intel 82801CA/CAM (ICH3) USB controller USB-A port 0x2000-0x201f irq 16 at device 29.0 on pci0 uhci0: [GIANT-LOCKED] usb0: Intel 82801CA/CAM (ICH3) USB controller USB-A on uhci0 usb0: USB revision 1.0 uhub0: Intel UHCI root hub, class 9/0, rev 1.00/1.00, addr 1 uhub0: 2 ports with 2 removable, self powered uhci1: Intel 82801CA/CAM (ICH3) USB controller USB-B port 0x2020-0x203f irq 19 at device 29.1 on pci0 uhci1: [GIANT-LOCKED] usb1: Intel 82801CA/CAM (ICH3) USB controller USB-B on uhci1 usb1: USB revision 1.0
RE: corrupted query cache?
What's your question? Heh, yah that might be important... Has anyone experienced this? Does it look like anything in the my.cnf could be altered to alleviate or assist me in diagnosing the problem? I do not get an indication of any problems in the log files or the bin-logs. How might I efficiently trouble shooting this, where might I begin? I can't figure out how to replicate it, it seems to be quite sporadic. What would corrupt query results, temporarily fixed with a mysqld restart? Max Thayer wrote: We are experiencing a certain anomaly here on our db server. The problem had occurred about 3 weeks ago. We ran diagnostics on the hardware over a 48+hour period with no failures or indications of problems with hardware. ( at least the memory.) We rebooted the server, and had not experienced anymore issues until yesterday, it repeated itself. The issue is as follows We run a series of views, sp's and user defined functions for a period of time. During application development we run these from command line, from MySQL query browser and from a series of PHP and Perl Scripts (of which some are executed from web apps others from cli ). At some point, the data returned to the user appears corrupted. Bogus data is returned, whether it be from a query to the actual table or to a view. Upon rebooting the server the data is returned to normal (ie. the data tables written to hdd are fine, it appears only the cached results are fubar). I can point out also if it matters that all my tables are INNODB We are currently developing on a semi-production DB( i know...hands need to be slapped ) We are using Apache 2.2 on a similar BSD server. Server specs follow... * FreeBSD 6.1 Release #0 mysql-server-5.0.45_1 mysql-client-5.0.45_1 * Hardware: Copyright (c) 1992-2006 The FreeBSD Project. Copyright (c) 1979, 1980, 1983, 1986, 1988, 1989, 1991, 1992, 1993, 1994 The Regents of the University of California. All rights reserved. FreeBSD 6.1-RELEASE #0: Sun May 7 04:42:56 UTC 2006 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/SMP Timecounter i8254 frequency 1193182 Hz quality 0 CPU: Intel(R) Xeon(TM) CPU 3.06GHz (3065.81-MHz 686-class CPU) Origin = GenuineIntel Id = 0xf29 Stepping = 9 Features=0xbfebfbffFPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE ,MCA ,CMOV,PAT,PSE36,CLFLUSH,DTS,ACPI,MMX,FXSR,SSE,SSE2,SS,HTT,TM,PBE Features2=0x4400CNTX-ID,b14 Logical CPUs per core: 2 real memory = 2146959360 (2047 MB) avail memory = 2095874048 (1998 MB) ACPI APIC Table: PTLTD APIC FreeBSD/SMP: Multiprocessor System Detected: 4 CPUs cpu0 (BSP): APIC ID: 0 cpu1 (AP): APIC ID: 1 cpu2 (AP): APIC ID: 6 cpu3 (AP): APIC ID: 7 ioapic0 Version 2.0 irqs 0-23 on motherboard ioapic1 Version 2.0 irqs 24-47 on motherboard ioapic2 Version 2.0 irqs 48-71 on motherboard ioapic3 Version 2.0 irqs 72-95 on motherboard ioapic4 Version 2.0 irqs 96-119 on motherboard kbd1 at kbdmux0 acpi0: PTLTD RSDT on motherboard acpi0: Power Button (fixed) Timecounter ACPI-fast frequency 3579545 Hz quality 1000 acpi_timer0: 24-bit timer at 3.579545MHz port 0x1008-0x100b on acpi0 cpu0: ACPI CPU on acpi0 cpu1: ACPI CPU on acpi0 cpu2: ACPI CPU on acpi0 cpu3: ACPI CPU on acpi0 pcib0: ACPI Host-PCI bridge port 0xcf8-0xcff on acpi0 pci0: ACPI PCI bus on pcib0 pci0: unknown at device 0.1 (no driver attached) pcib1: ACPI PCI-PCI bridge at device 2.0 on pci0 pci1: ACPI PCI bus on pcib1 pci1: base peripheral, interrupt controller at device 28.0 (no driver attached) pcib2: ACPI PCI-PCI bridge at device 29.0 on pci1 pci2: ACPI PCI bus on pcib2 pci1: base peripheral, interrupt controller at device 30.0 (no driver attached) pcib3: ACPI PCI-PCI bridge at device 31.0 on pci1 pci3: ACPI PCI bus on pcib3 em0: Intel(R) PRO/1000 Network Connection Version - 3.2.18 port 0x3000-0x303f mem 0xf020-0xf021 irq 28 at device 2.0 on pci3 em0: Ethernet address: 00:30:48:2f:13:7c em1: Intel(R) PRO/1000 Network Connection Version - 3.2.18 port 0x3040-0x307f mem 0xf022-0xf023 irq 29 at device 2.1 on pci3 em1: Ethernet address: 00:30:48:2f:13:7d pcib4: ACPI PCI-PCI bridge at device 3.0 on pci0 pci4: ACPI PCI bus on pcib4 pci4: base peripheral, interrupt controller at device 28.0 (no driver attached) pcib5: ACPI PCI-PCI bridge at device 29.0 on pci4 pci5: ACPI PCI bus on pcib5 pci4: base peripheral, interrupt controller at device 30.0 (no driver attached) pcib6: ACPI PCI-PCI bridge at device 31.0 on pci4 pci6: ACPI PCI bus on pcib6 aac0: Adaptec SCSI RAID 2020ZCR mem 0xf100-0xf1ff,0xf040-0xf05f,0xf800-0xfbff irq 72 at device 1.0 on pci6 aac0: New comm. interface enabled aac0: Adaptec Raid Controller 2.0.0-1 aacp0: SCSI
Query cache problem with stored procedures
Hi all, I'm benchmarking performance improvement with MySQL Query Cache turned on but I'm facing some problem with queries inside stored procedures when they contains variable parameters I just created this stored procedure to identify the problem CREATE PROCEDURE `proc_test_qcache`(IN mailbox_number VARCHAR(64)) READS SQL DATA DETERMINISTIC BEGIN SELECT password FROM users WHERE mailbox = mailbox_number; END I see, looking at Qcache status, that calls to that stored procedure are not cached. Is it a known limitation ? I think that MySQL should evaluate caching after variable substitution but I'm not aware of MySQL internals to judge if this is the correct behaviour. P.S: I noticed that MySQL caches without problems queries contained in stored procedures which does not contains variables. Tnx in advance for help Regards Edoardo Serra WeBRainstorm S.r.l. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query cache problem with stored procedures
Hi, Your questions are answered in the manual: http://dev.mysql.com/doc/refman/5.0/en/query-cache-how.html It is a known limitation. Edoardo Serra wrote: Hi all, I'm benchmarking performance improvement with MySQL Query Cache turned on but I'm facing some problem with queries inside stored procedures when they contains variable parameters I just created this stored procedure to identify the problem CREATE PROCEDURE `proc_test_qcache`(IN mailbox_number VARCHAR(64)) READS SQL DATA DETERMINISTIC BEGIN SELECT password FROM users WHERE mailbox = mailbox_number; END I see, looking at Qcache status, that calls to that stored procedure are not cached. Is it a known limitation ? I think that MySQL should evaluate caching after variable substitution but I'm not aware of MySQL internals to judge if this is the correct behaviour. P.S: I noticed that MySQL caches without problems queries contained in stored procedures which does not contains variables. Tnx in advance for help Regards Edoardo Serra WeBRainstorm S.r.l. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query cache question when using HANDLER
Hi, I have an application that is using the MySQL HANDLER heavily. The application was ported from a legacy ISAM database to use MySQL. The upshot is that the application works by index walking - i.e., HANDLER tablename OPEN HANDLER tablename OPEN as indexname HANDLER indexname READ indexname = (key1, key2, ...) HANDLER indexname READ indexname NEXT LIMIT 1 HANDLER indexname READ indexname NEXT LIMIT 1 HANDLER indexname READ indexname NEXT LIMIT 1 ... (it works very well - although we are also migrating the applications to use selects and prepares - which are causing their own problems). We run in 2 scenarios - 1. Machines with lots of databases and few users (ie. internal testing machines) and 2. Machines with only one database and many users (i.e. customer production machines). My questions... Are HANDLER queries cached in the query cache? If so, is it worth using a query cache when using so many HANDLER .. NEXT calls. Again if so, is it recommended to set a small query_cache_limit. -- Regards, Ian Collins Systems Manager KIWIPLAN Group Tel: +64 (0)9 2727622 Mob: +64 (0)21 761144 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query cache question when using HANDLER
On Wed, Jun 27, 2007 at 01:27:24PM +1200, Ian Collins wrote: Are HANDLER queries cached in the query cache? No, they are not. The query cache only caches the results of SELECT statements. Jim Winstead MySQL Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Cache Behavior
Hello, I am hoping to get pointed in the right direction/save some time... I have a db in which some web services are constantly inserting/updating data. However, when I run selects from the command line mysql app, I do not see the changes that these services have made unless I do a flush tables. This is even true if I log out and log back in. So, I am guessing that it has to do with caching. Any idea why this is happening? Thanks, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Cache Behavior
Hi, Paul J. Boyes wrote: Hello, I am hoping to get pointed in the right direction/save some time... I have a db in which some web services are constantly inserting/updating data. However, when I run selects from the command line mysql app, I do not see the changes that these services have made unless I do a flush tables. This is even true if I log out and log back in. So, I am guessing that it has to do with caching. Any idea why this is happening? It's almost certainly not the MySQL query cache, because updates invalidate the cache. You can test by adding SQL_NO_CACHE to your SELECT, thusly: SELECT SQL_NO_CACHE If this still doesn't return the results you expect, something else is happening. Are the web services not committing their transactions? That's the only other thing that comes to my mind. I'm sure I'm missing something and someone else will be able to help more. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Cache Behavior
This is our problem: http://bugs.mysql.com/bug.php?id=27210 Thanks, Paul Baron Schwartz wrote: Hi, Paul J. Boyes wrote: Hello, I am hoping to get pointed in the right direction/save some time... I have a db in which some web services are constantly inserting/updating data. However, when I run selects from the command line mysql app, I do not see the changes that these services have made unless I do a flush tables. This is even true if I log out and log back in. So, I am guessing that it has to do with caching. Any idea why this is happening? It's almost certainly not the MySQL query cache, because updates invalidate the cache. You can test by adding SQL_NO_CACHE to your SELECT, thusly: SELECT SQL_NO_CACHE If this still doesn't return the results you expect, something else is happening. Are the web services not committing their transactions? That's the only other thing that comes to my mind. I'm sure I'm missing something and someone else will be able to help more. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Increasing the Query Cache Size has performance ?
Not sure what is going on, but the version of MySQL you are using is ancient. The current version of MySQL is 5.0.37. Even MySQL 4.1 (which has been end-of-lifed) has a latest version of 4.1.22. If it is a bug you are seeing, it likely has been fixed in a later version. Cheers, Jay Kishore Jalleda wrote: Hello Everybody, I increased the query_cache_size on one of our main servers from 100 MB to 250 MB, since I was seeing a very high rate lot of Qcache Low Mem prunes. The server was fine for 15 minutes and the Low mem prunes went down to almost zero, but then started getting too many connections errors and the queries were taking too long to execute, and only after the roll backed the change the server started behaving normally. This could not be a co-incidence as the server has been running fine for months even under heavy traffic conditions. Has anybody ever experienced such a thing or know what could be the cause .. Server info 4.1.11-Debian_4sarge3-log 32GB RAM Max_connections : 400 Thanks Kishore Jalleda -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Increasing the Query Cache Size has performance ?
Hello Everybody, I increased the query_cache_size on one of our main servers from 100 MB to 250 MB, since I was seeing a very high rate lot of Qcache Low Mem prunes. The server was fine for 15 minutes and the Low mem prunes went down to almost zero, but then started getting too many connections errors and the queries were taking too long to execute, and only after the roll backed the change the server started behaving normally. This could not be a co-incidence as the server has been running fine for months even under heavy traffic conditions. Has anybody ever experienced such a thing or know what could be the cause .. Server info 4.1.11-Debian_4sarge3-log 32GB RAM Max_connections : 400 Thanks Kishore Jalleda
Re: Query Cache
Chris, Thanks for clearing that up. You are right, I don't want the general log or the mysql shell history. I'm not happy that I can't get at the Query Cache. I am sure there are tools which MySql developers must use to test the cache. I'll have to look at the source code directories... Regards, Ben Chris wrote: Mohsen Pahlevanzadeh wrote: Ben Clewett wrote: Hi Mohsen, Thanks for the reply. I'm looking in the home directory, as well as the other directories used by mysql and I can't see any files which I do not recognise. (I am UNIX so there are no hidden files.) I am also a little confuses as the Query Cache is entirely a memory structure, does it use a file? You're talking about different things. Mohsen thinks you want to see the the last queries that were run, which are stored in the ~/.mysql_history file. Ben wants to see what queries are stored in the query cache (http://dev.mysql.com/doc/refman/4.1/en/query-cache.html). I don't think you can get a list of queries that mysql has stored in the cache. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Cache
Ben Clewett wrote: Hi Mohsen, Thanks for the reply. I'm looking in the home directory, as well as the other directories used by mysql and I can't see any files which I do not recognise. (I am UNIX so there are no hidden files.) I am also a little confuses as the Query Cache is entirely a memory structure, does it use a file? If you have the name of the Query Cache file, this would be very useful. Regards, Ben. Mohsen Pahlevanzadeh wrote: Ben Clewett wrote: Dear MySql, Can you please tell me if there is a tool to view the queries stored in the Query Cache? Regards, Ben Clewett. Yes,If you see to mysql homeDIR,You see a hidden file that it's contain of your queries. Hidden files are started with a dot in UNIX world. If you see them,You must type ls -a --Mohsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Cache
Hi Mohsen, Thanks for the advise. On my AIX system as root, dot files not hidden. For instance my mysql home directory looks like: drwxr-xr-x 16 mysqlmysql 4096 04 Dec 20:18 . drwxr-xr-x 31 root system 4096 09 Nov 15:12 .. drwxr-xr-x 2 mysqlmysql 4096 13 Oct 2005 bin drwxr-xr-x 3 mysqlmysql 256 04 Dec 20:05 etc drwxr-xr-x 2 mysqlmysql 256 13 Oct 2005 info drwxr-xr-x 3 mysqlmysql 256 07 Oct 2005 lib drwxr-xr-x 2 mysqlmysql 256 13 Oct 2005 libexec drwxr-xr-x 3 mysqlmysql 4096 04 Dec 23:50 logs drwxr-xr-x 2 mysqlmysql 256 07 Oct 2005 lost+found drwxr-xr-x 3 mysqlmysql 256 12 Oct 2005 man -rw-rw 1 mysqlstaff 7 04 Dec 20:18 mysql.pid srwxrwxrwx 1 mysqlstaff 0 04 Dec 20:18 mysql.sock drwxr-xr-x 6 mysqlmysql 4096 13 Oct 2005 mysql-test drwxr-xr-x 3 mysqlmysql 256 07 Oct 2005 share drwxr-xr-x 5 mysqlmysql 4096 13 Oct 2005 sql-bench drwxr-xr-x 2 mysqlmysql 256 05 Dec 10:23 tmp There is no file for the Query Cache. I am sure that since this is a memory structure, there must be a command in MySql or an admin tool which can be used to expose the Queries. If anybody knows this really would be very useful. Regards, Ben. Mohsen Pahlevanzadeh wrote: Ben Clewett wrote: Hi Mohsen, Thanks for the reply. I'm looking in the home directory, as well as the other directories used by mysql and I can't see any files which I do not recognise. (I am UNIX so there are no hidden files.) I am also a little confuses as the Query Cache is entirely a memory structure, does it use a file? If you have the name of the Query Cache file, this would be very useful. Regards, Ben. Mohsen Pahlevanzadeh wrote: Ben Clewett wrote: Dear MySql, Can you please tell me if there is a tool to view the queries stored in the Query Cache? Regards, Ben Clewett. Yes,If you see to mysql homeDIR,You see a hidden file that it's contain of your queries. Hidden files are started with a dot in UNIX world. If you see them,You must type ls -a --Mohsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Cache
Ben Clewett wrote: Hi Mohsen, Thanks for the advise. On my AIX system as root, dot files not hidden. For instance my mysql home directory looks like: drwxr-xr-x 16 mysqlmysql 4096 04 Dec 20:18 . drwxr-xr-x 31 root system 4096 09 Nov 15:12 .. drwxr-xr-x 2 mysqlmysql 4096 13 Oct 2005 bin drwxr-xr-x 3 mysqlmysql 256 04 Dec 20:05 etc drwxr-xr-x 2 mysqlmysql 256 13 Oct 2005 info drwxr-xr-x 3 mysqlmysql 256 07 Oct 2005 lib drwxr-xr-x 2 mysqlmysql 256 13 Oct 2005 libexec drwxr-xr-x 3 mysqlmysql 4096 04 Dec 23:50 logs drwxr-xr-x 2 mysqlmysql 256 07 Oct 2005 lost+found drwxr-xr-x 3 mysqlmysql 256 12 Oct 2005 man -rw-rw 1 mysqlstaff 7 04 Dec 20:18 mysql.pid srwxrwxrwx 1 mysqlstaff 0 04 Dec 20:18 mysql.sock drwxr-xr-x 6 mysqlmysql 4096 13 Oct 2005 mysql-test drwxr-xr-x 3 mysqlmysql 256 07 Oct 2005 share drwxr-xr-x 5 mysqlmysql 4096 13 Oct 2005 sql-bench drwxr-xr-x 2 mysqlmysql 256 05 Dec 10:23 tmp There is no file for the Query Cache. I am sure that since this is a memory structure, there must be a command in MySql or an admin tool which can be used to expose the Queries. If anybody knows this really would be very useful. Regards, Ben. Mohsen Pahlevanzadeh wrote: Ben Clewett wrote: Hi Mohsen, Thanks for the reply. I'm looking in the home directory, as well as the other directories used by mysql and I can't see any files which I do not recognise. (I am UNIX so there are no hidden files.) I am also a little confuses as the Query Cache is entirely a memory structure, does it use a file? If you have the name of the Query Cache file, this would be very useful. Regards, Ben. Mohsen Pahlevanzadeh wrote: Ben Clewett wrote: Dear MySql, Can you please tell me if there is a tool to view the queries stored in the Query Cache? Regards, Ben Clewett. Yes,If you see to mysql homeDIR,You see a hidden file that it's contain of your queries. Hidden files are started with a dot in UNIX world. If you see them,You must type ls -a --Mohsen please send me output of following command : grep mysql /etc/passwd Yours,Mohsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Cache
Hi Mohsen, I see, the difference between the home of mysql and the mysql home... As you asked: mysql:*:204:1::/home/mysql:/usr/bin/ksh # ls -la /home/mysql 4 -rwxr- 1 mysqlstaff 254 14 Feb 2005 .profile Regards, Ben Mohsen Pahlevanzadeh wrote: Ben Clewett wrote: Hi Mohsen, Thanks for the advise. On my AIX system as root, dot files not hidden. For instance my mysql home directory looks like: drwxr-xr-x 16 mysqlmysql 4096 04 Dec 20:18 . drwxr-xr-x 31 root system 4096 09 Nov 15:12 .. drwxr-xr-x 2 mysqlmysql 4096 13 Oct 2005 bin drwxr-xr-x 3 mysqlmysql 256 04 Dec 20:05 etc drwxr-xr-x 2 mysqlmysql 256 13 Oct 2005 info drwxr-xr-x 3 mysqlmysql 256 07 Oct 2005 lib drwxr-xr-x 2 mysqlmysql 256 13 Oct 2005 libexec drwxr-xr-x 3 mysqlmysql 4096 04 Dec 23:50 logs drwxr-xr-x 2 mysqlmysql 256 07 Oct 2005 lost+found drwxr-xr-x 3 mysqlmysql 256 12 Oct 2005 man -rw-rw 1 mysqlstaff 7 04 Dec 20:18 mysql.pid srwxrwxrwx 1 mysqlstaff 0 04 Dec 20:18 mysql.sock drwxr-xr-x 6 mysqlmysql 4096 13 Oct 2005 mysql-test drwxr-xr-x 3 mysqlmysql 256 07 Oct 2005 share drwxr-xr-x 5 mysqlmysql 4096 13 Oct 2005 sql-bench drwxr-xr-x 2 mysqlmysql 256 05 Dec 10:23 tmp There is no file for the Query Cache. I am sure that since this is a memory structure, there must be a command in MySql or an admin tool which can be used to expose the Queries. If anybody knows this really would be very useful. Regards, Ben. Mohsen Pahlevanzadeh wrote: Ben Clewett wrote: Hi Mohsen, Thanks for the reply. I'm looking in the home directory, as well as the other directories used by mysql and I can't see any files which I do not recognise. (I am UNIX so there are no hidden files.) I am also a little confuses as the Query Cache is entirely a memory structure, does it use a file? If you have the name of the Query Cache file, this would be very useful. Regards, Ben. Mohsen Pahlevanzadeh wrote: Ben Clewett wrote: Dear MySql, Can you please tell me if there is a tool to view the queries stored in the Query Cache? Regards, Ben Clewett. Yes,If you see to mysql homeDIR,You see a hidden file that it's contain of your queries. Hidden files are started with a dot in UNIX world. If you see them,You must type ls -a --Mohsen please send me output of following command : grep mysql /etc/passwd Yours,Mohsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Cache
Mohsen Pahlevanzadeh wrote: Ben Clewett wrote: Hi Mohsen, Thanks for the reply. I'm looking in the home directory, as well as the other directories used by mysql and I can't see any files which I do not recognise. (I am UNIX so there are no hidden files.) I am also a little confuses as the Query Cache is entirely a memory structure, does it use a file? You're talking about different things. Mohsen thinks you want to see the the last queries that were run, which are stored in the ~/.mysql_history file. Ben wants to see what queries are stored in the query cache (http://dev.mysql.com/doc/refman/4.1/en/query-cache.html). I don't think you can get a list of queries that mysql has stored in the cache. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Cache
Dear MySql, Can you please tell me if there is a tool to view the queries stored in the Query Cache? Regards, Ben Clewett. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Cache
Hi Mohsen, Thanks for the reply. I'm looking in the home directory, as well as the other directories used by mysql and I can't see any files which I do not recognise. (I am UNIX so there are no hidden files.) I am also a little confuses as the Query Cache is entirely a memory structure, does it use a file? If you have the name of the Query Cache file, this would be very useful. Regards, Ben. Mohsen Pahlevanzadeh wrote: Ben Clewett wrote: Dear MySql, Can you please tell me if there is a tool to view the queries stored in the Query Cache? Regards, Ben Clewett. Yes,If you see to mysql homeDIR,You see a hidden file that it's contain of your queries. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Understanding Query-Cache math...
Hi The query cache uses variable-length blocks and the Qcache_total_blocks and Qcache_free_blocks may indicate query cache memory fragmentation. After FLUSH QUERY CACHE, only a single free block remains. So the variables query_cache_min_res_unit, query_cache_limit, query_prealloc_size, query_alloc_block_size determines the free and available number of blocks. Every cached query requires a minimum of two blocks - for query text and for the query results. Also, every table that is used by a query requires one block. However, if two or more queries use the same table, only one block needs to be allocated. The information provided by the Qcache_lowmem_prunes status variable can help you tune the query cache size. It counts the number of queries that have been removed from the cache to free up memory for caching new queries. The query cache uses a least recently used (LRU) strategy to decide which queries to remove from the cache. If you have query with the size larger than the query_cache_size then the query is not cached. If you have a lot of queries with small results, the default block size may lead to memory fragmentation, as indicated by a large number of free blocks. Fragmentation can force the query cache to prune (delete) queries from the cache due to lack of memory. In this case, you should decrease the value of query_cache_min_res_unit. The number of free blocks and queries removed due to pruning are given by the values of the Qcache_free_blocks and Qcache_lowmem_prunes status variables. Thanks ViSolve DB Team - Original Message - From: Christian Hammers [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, September 01, 2006 1:52 PM Subject: Understanding Query-Cache math... Hello I don't understand why query_cache_size / query_cache_min_res_unit != Qcache_total_blocks and Qcache_free_memory / query_cache_min_res_unit != Qcache_free_blocks Can anybody enlight me so that I know if I have to increase the Query-Cache or not? mysql SHOW status LIKE Qcache_%; +-+---+ | Variable_name | Value | +-+---+ | Qcache_free_blocks | 10382 | | Qcache_free_memory | 247491776 | | Qcache_hits | 119254865 | | Qcache_inserts | 5412923 | | Qcache_lowmem_prunes| 0 | | Qcache_not_cached | 58724815 | | Qcache_queries_in_cache | 16002 | | Qcache_total_blocks | 42464 | +-+---+ mysql SHOW variables LIKE query_cache_%; +--++ | Variable_name| Value | +--++ | query_cache_limit| 8388608| | query_cache_min_res_unit | 4096 | | query_cache_size | 268435456 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF| +--++ thanks, -christian- -- Christian Hammers WESTEND GmbH | Internet-Business-Provider Technik CISCO Systems Partner - Authorized Reseller Lütticher Straße 10 Tel 0241/701333-11 [EMAIL PROTECTED]D-52064 Aachen Fax 0241/911879 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Understanding Query-Cache math...
Hello I don't understand why query_cache_size / query_cache_min_res_unit != Qcache_total_blocks and Qcache_free_memory / query_cache_min_res_unit != Qcache_free_blocks Can anybody enlight me so that I know if I have to increase the Query-Cache or not? mysql SHOW status LIKE Qcache_%; +-+---+ | Variable_name | Value | +-+---+ | Qcache_free_blocks | 10382 | | Qcache_free_memory | 247491776 | | Qcache_hits | 119254865 | | Qcache_inserts | 5412923 | | Qcache_lowmem_prunes| 0 | | Qcache_not_cached | 58724815 | | Qcache_queries_in_cache | 16002 | | Qcache_total_blocks | 42464 | +-+---+ mysql SHOW variables LIKE query_cache_%; +--++ | Variable_name| Value | +--++ | query_cache_limit| 8388608| | query_cache_min_res_unit | 4096 | | query_cache_size | 268435456 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF| +--++ thanks, -christian- -- Christian Hammers WESTEND GmbH | Internet-Business-Provider Technik CISCO Systems Partner - Authorized Reseller Lütticher Straße 10 Tel 0241/701333-11 [EMAIL PROTECTED]D-52064 Aachen Fax 0241/911879 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query cache question
Hi, I just turned on the query cache by modifying its size and limit and restarting the server. However queries aren't being stored to it: Variable_name Value Qcache_free_blocks 1 Qcache_free_memory 10477008 Qcache_hits 0 Qcache_inserts 0 Qcache_lowmem_prunes 0 Qcache_not_cached 3759 Qcache_queries_in_cache 0 Qcache_total_blocks 1 I've found http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html which says the qcache_not_cached variable is the number of queries which were either not SELECT's or have SQL_NO_CACHE specified so I ran several selects (selected records in a few tables using Query Browser and refreshed a few web pages hitting the db). None of the queries have the no cache option specified. What else would prevent queries from getting into the cache? Kathy Mazur Worden Prairie Area Library System
Re: query cache question
Could you post an example of a query you are expecting to be cached? On Wed, 2006-08-30 at 11:43 -0500, Mazur Worden, Kathy wrote: Hi, I just turned on the query cache by modifying its size and limit and restarting the server. However queries aren't being stored to it: Variable_name Value Qcache_free_blocks 1 Qcache_free_memory 10477008 Qcache_hits 0 Qcache_inserts 0 Qcache_lowmem_prunes 0 Qcache_not_cached 3759 Qcache_queries_in_cache 0 Qcache_total_blocks 1 I've found http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html which says the qcache_not_cached variable is the number of queries which were either not SELECT's or have SQL_NO_CACHE specified so I ran several selects (selected records in a few tables using Query Browser and refreshed a few web pages hitting the db). None of the queries have the no cache option specified. What else would prevent queries from getting into the cache? Kathy Mazur Worden Prairie Area Library System -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: query cache question
Maybe that's my issue, I had though the type of query would not matter. I've run simple queries in query browser like this: Select * from table t; And refreshed web pages hit about a hundred times a day containing counting queries like this: select count(BuildingInfo.deliverycode) AS 'Total' from BuildingInfo, AgencyInfo where BuildingInfo.agencyid = AgencyInfo.agencyid AND AgencyInfo.type = 'academic' AND (BuildingInfo.membershipstatus = 'Full' OR BuildingInfo.membershipstatus = 'Developmental') And select queries at least as long as these: SELECT AgencyInfo.Name, AgencyInfo.agencyid from AgencyInfo where AgencyInfo.Type = 'school' AND (AgencyInfo.membershipstatus = 'Full' OR AgencyInfo.membershipstatus = 'Developmental') ORDER BY AgencyInfo.Name SELECT AgencyInfo.Name, BuildingInfo.Name as 'BuildingName', AgencyInfo.agencyid, BuildingInfo.buildingid FROM BuildingInfo INNER JOIN AgencyInfo ON BuildingInfo.agencyid = AgencyInfo.agencyid ORDER BY AgencyInfo.Name, BuildingInfo.Name; There are indexes on the id and name related fields used in the WHERE clauses. Kathy Mazur Worden Prairie Area Library System -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 12:07 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: Re: query cache question Could you post an example of a query you are expecting to be cached? On Wed, 2006-08-30 at 11:43 -0500, Mazur Worden, Kathy wrote: Hi, I just turned on the query cache by modifying its size and limit and restarting the server. However queries aren't being stored to it: Variable_name Value Qcache_free_blocks 1 Qcache_free_memory 10477008 Qcache_hits 0 Qcache_inserts 0 Qcache_lowmem_prunes 0 Qcache_not_cached 3759 Qcache_queries_in_cache 0 Qcache_total_blocks 1 I've found http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html which says the qcache_not_cached variable is the number of queries which were either not SELECT's or have SQL_NO_CACHE specified so I ran several selects (selected records in a few tables using Query Browser and refreshed a few web pages hitting the db). None of the queries have the no cache option specified. What else would prevent queries from getting into the cache? Kathy Mazur Worden Prairie Area Library System -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query cache question
What else would prevent queries from getting into the cache? Anything with NOW() in it or any of it's related functions... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: query cache question
Those queries look just fine to me. Could you output the result of the following: SELECT @@global.query_cache_size; On Wed, 2006-08-30 at 12:27 -0500, Mazur Worden, Kathy wrote: Maybe that's my issue, I had though the type of query would not matter. I've run simple queries in query browser like this: Select * from table t; And refreshed web pages hit about a hundred times a day containing counting queries like this: select count(BuildingInfo.deliverycode) AS 'Total' from BuildingInfo, AgencyInfo where BuildingInfo.agencyid = AgencyInfo.agencyid AND AgencyInfo.type = 'academic' AND (BuildingInfo.membershipstatus = 'Full' OR BuildingInfo.membershipstatus = 'Developmental') And select queries at least as long as these: SELECT AgencyInfo.Name, AgencyInfo.agencyid from AgencyInfo where AgencyInfo.Type = 'school' AND (AgencyInfo.membershipstatus = 'Full' OR AgencyInfo.membershipstatus = 'Developmental') ORDER BY AgencyInfo.Name SELECT AgencyInfo.Name, BuildingInfo.Name as 'BuildingName', AgencyInfo.agencyid, BuildingInfo.buildingid FROM BuildingInfo INNER JOIN AgencyInfo ON BuildingInfo.agencyid = AgencyInfo.agencyid ORDER BY AgencyInfo.Name, BuildingInfo.Name; There are indexes on the id and name related fields used in the WHERE clauses. Kathy Mazur Worden Prairie Area Library System -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 12:07 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: Re: query cache question Could you post an example of a query you are expecting to be cached? On Wed, 2006-08-30 at 11:43 -0500, Mazur Worden, Kathy wrote: Hi, I just turned on the query cache by modifying its size and limit and restarting the server. However queries aren't being stored to it: Variable_name Value Qcache_free_blocks 1 Qcache_free_memory 10477008 Qcache_hits 0 Qcache_inserts 0 Qcache_lowmem_prunes 0 Qcache_not_cached 3759 Qcache_queries_in_cache 0 Qcache_total_blocks 1 I've found http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html which says the qcache_not_cached variable is the number of queries which were either not SELECT's or have SQL_NO_CACHE specified so I ran several selects (selected records in a few tables using Query Browser and refreshed a few web pages hitting the db). None of the queries have the no cache option specified. What else would prevent queries from getting into the cache? Kathy Mazur Worden Prairie Area Library System -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: query cache question
The output of that query is: @@global.query_cache_size 10485760 Kathy Mazur Worden Prairie Area Library System -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 12:42 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: RE: query cache question Those queries look just fine to me. Could you output the result of the following: SELECT @@global.query_cache_size; On Wed, 2006-08-30 at 12:27 -0500, Mazur Worden, Kathy wrote: Maybe that's my issue, I had though the type of query would not matter. I've run simple queries in query browser like this: Select * from table t; And refreshed web pages hit about a hundred times a day containing counting queries like this: select count(BuildingInfo.deliverycode) AS 'Total' from BuildingInfo, AgencyInfo where BuildingInfo.agencyid = AgencyInfo.agencyid AND AgencyInfo.type = 'academic' AND (BuildingInfo.membershipstatus = 'Full' OR BuildingInfo.membershipstatus = 'Developmental') And select queries at least as long as these: SELECT AgencyInfo.Name, AgencyInfo.agencyid from AgencyInfo where AgencyInfo.Type = 'school' AND (AgencyInfo.membershipstatus = 'Full' OR AgencyInfo.membershipstatus = 'Developmental') ORDER BY AgencyInfo.Name SELECT AgencyInfo.Name, BuildingInfo.Name as 'BuildingName', AgencyInfo.agencyid, BuildingInfo.buildingid FROM BuildingInfo INNER JOIN AgencyInfo ON BuildingInfo.agencyid = AgencyInfo.agencyid ORDER BY AgencyInfo.Name, BuildingInfo.Name; There are indexes on the id and name related fields used in the WHERE clauses. Kathy Mazur Worden Prairie Area Library System -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 12:07 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: Re: query cache question Could you post an example of a query you are expecting to be cached? On Wed, 2006-08-30 at 11:43 -0500, Mazur Worden, Kathy wrote: Hi, I just turned on the query cache by modifying its size and limit and restarting the server. However queries aren't being stored to it: Variable_name Value Qcache_free_blocks 1 Qcache_free_memory 10477008 Qcache_hits 0 Qcache_inserts 0 Qcache_lowmem_prunes 0 Qcache_not_cached 3759 Qcache_queries_in_cache 0 Qcache_total_blocks 1 I've found http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html which says the qcache_not_cached variable is the number of queries which were either not SELECT's or have SQL_NO_CACHE specified so I ran several selects (selected records in a few tables using Query Browser and refreshed a few web pages hitting the db). None of the queries have the no cache option specified. What else would prevent queries from getting into the cache? Kathy Mazur Worden Prairie Area Library System -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: query cache question
Please show the output of: SELECT @@global.query_cache_type; and then: SHOW GLOBAL STATUS LIKE 'Qc%'; Thanks! On Wed, 2006-08-30 at 13:43 -0500, Mazur Worden, Kathy wrote: The output of that query is: @@global.query_cache_size 10485760 Kathy Mazur Worden Prairie Area Library System -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 12:42 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: RE: query cache question Those queries look just fine to me. Could you output the result of the following: SELECT @@global.query_cache_size; On Wed, 2006-08-30 at 12:27 -0500, Mazur Worden, Kathy wrote: Maybe that's my issue, I had though the type of query would not matter. I've run simple queries in query browser like this: Select * from table t; And refreshed web pages hit about a hundred times a day containing counting queries like this: select count(BuildingInfo.deliverycode) AS 'Total' from BuildingInfo, AgencyInfo where BuildingInfo.agencyid = AgencyInfo.agencyid AND AgencyInfo.type = 'academic' AND (BuildingInfo.membershipstatus = 'Full' OR BuildingInfo.membershipstatus = 'Developmental') And select queries at least as long as these: SELECT AgencyInfo.Name, AgencyInfo.agencyid from AgencyInfo where AgencyInfo.Type = 'school' AND (AgencyInfo.membershipstatus = 'Full' OR AgencyInfo.membershipstatus = 'Developmental') ORDER BY AgencyInfo.Name SELECT AgencyInfo.Name, BuildingInfo.Name as 'BuildingName', AgencyInfo.agencyid, BuildingInfo.buildingid FROM BuildingInfo INNER JOIN AgencyInfo ON BuildingInfo.agencyid = AgencyInfo.agencyid ORDER BY AgencyInfo.Name, BuildingInfo.Name; There are indexes on the id and name related fields used in the WHERE clauses. Kathy Mazur Worden Prairie Area Library System -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 12:07 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: Re: query cache question Could you post an example of a query you are expecting to be cached? On Wed, 2006-08-30 at 11:43 -0500, Mazur Worden, Kathy wrote: Hi, I just turned on the query cache by modifying its size and limit and restarting the server. However queries aren't being stored to it: Variable_name Value Qcache_free_blocks 1 Qcache_free_memory 10477008 Qcache_hits 0 Qcache_inserts 0 Qcache_lowmem_prunes 0 Qcache_not_cached 3759 Qcache_queries_in_cache 0 Qcache_total_blocks 1 I've found http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html which says the qcache_not_cached variable is the number of queries which were either not SELECT's or have SQL_NO_CACHE specified so I ran several selects (selected records in a few tables using Query Browser and refreshed a few web pages hitting the db). None of the queries have the no cache option specified. What else would prevent queries from getting into the cache? Kathy Mazur Worden Prairie Area Library System -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: query cache question
@@global.query_cache_type ON Variable_name Value Qcache_free_blocks 1 Qcache_free_memory 10477008 Qcache_hits 0 Qcache_inserts 0 Qcache_lowmem_prunes0 Qcache_not_cached 20318 Qcache_queries_in_cache 0 Qcache_total_blocks 1 Thanks -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 2:12 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: RE: query cache question Please show the output of: SELECT @@global.query_cache_type; and then: SHOW GLOBAL STATUS LIKE 'Qc%'; Thanks! On Wed, 2006-08-30 at 13:43 -0500, Mazur Worden, Kathy wrote: The output of that query is: @@global.query_cache_size 10485760 Kathy Mazur Worden Prairie Area Library System -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 12:42 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: RE: query cache question Those queries look just fine to me. Could you output the result of the following: SELECT @@global.query_cache_size; On Wed, 2006-08-30 at 12:27 -0500, Mazur Worden, Kathy wrote: Maybe that's my issue, I had though the type of query would not matter. I've run simple queries in query browser like this: Select * from table t; And refreshed web pages hit about a hundred times a day containing counting queries like this: select count(BuildingInfo.deliverycode) AS 'Total' from BuildingInfo, AgencyInfo where BuildingInfo.agencyid = AgencyInfo.agencyid AND AgencyInfo.type = 'academic' AND (BuildingInfo.membershipstatus = 'Full' OR BuildingInfo.membershipstatus = 'Developmental') And select queries at least as long as these: SELECT AgencyInfo.Name, AgencyInfo.agencyid from AgencyInfo where AgencyInfo.Type = 'school' AND (AgencyInfo.membershipstatus = 'Full' OR AgencyInfo.membershipstatus = 'Developmental') ORDER BY AgencyInfo.Name SELECT AgencyInfo.Name, BuildingInfo.Name as 'BuildingName', AgencyInfo.agencyid, BuildingInfo.buildingid FROM BuildingInfo INNER JOIN AgencyInfo ON BuildingInfo.agencyid = AgencyInfo.agencyid ORDER BY AgencyInfo.Name, BuildingInfo.Name; There are indexes on the id and name related fields used in the WHERE clauses. Kathy Mazur Worden Prairie Area Library System -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 12:07 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: Re: query cache question Could you post an example of a query you are expecting to be cached? On Wed, 2006-08-30 at 11:43 -0500, Mazur Worden, Kathy wrote: Hi, I just turned on the query cache by modifying its size and limit and restarting the server. However queries aren't being stored to it: Variable_name Value Qcache_free_blocks 1 Qcache_free_memory 10477008 Qcache_hits 0 Qcache_inserts 0 Qcache_lowmem_prunes 0 Qcache_not_cached 3759 Qcache_queries_in_cache 0 Qcache_total_blocks 1 I've found http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html which says the qcache_not_cached variable is the number of queries which were either not SELECT's or have SQL_NO_CACHE specified so I ran several selects (selected records in a few tables using Query Browser and refreshed a few web pages hitting the db). None of the queries have the no cache option specified. What else would prevent queries from getting into the cache? Kathy Mazur Worden Prairie Area Library System -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: query cache question
Ah, I think I may have figured it out... Are you using prepared statements? If so, doing that prevents the query cache from caching the resultset entirely. On Wed, 2006-08-30 at 14:17 -0500, Mazur Worden, Kathy wrote: @@global.query_cache_type ON Variable_name Value Qcache_free_blocks1 Qcache_free_memory10477008 Qcache_hits 0 Qcache_inserts0 Qcache_lowmem_prunes 0 Qcache_not_cached 20318 Qcache_queries_in_cache 0 Qcache_total_blocks 1 Thanks -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 2:12 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: RE: query cache question Please show the output of: SELECT @@global.query_cache_type; and then: SHOW GLOBAL STATUS LIKE 'Qc%'; Thanks! On Wed, 2006-08-30 at 13:43 -0500, Mazur Worden, Kathy wrote: The output of that query is: @@global.query_cache_size 10485760 Kathy Mazur Worden Prairie Area Library System -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 12:42 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: RE: query cache question Those queries look just fine to me. Could you output the result of the following: SELECT @@global.query_cache_size; On Wed, 2006-08-30 at 12:27 -0500, Mazur Worden, Kathy wrote: Maybe that's my issue, I had though the type of query would not matter. I've run simple queries in query browser like this: Select * from table t; And refreshed web pages hit about a hundred times a day containing counting queries like this: select count(BuildingInfo.deliverycode) AS 'Total' from BuildingInfo, AgencyInfo where BuildingInfo.agencyid = AgencyInfo.agencyid AND AgencyInfo.type = 'academic' AND (BuildingInfo.membershipstatus = 'Full' OR BuildingInfo.membershipstatus = 'Developmental') And select queries at least as long as these: SELECT AgencyInfo.Name, AgencyInfo.agencyid from AgencyInfo where AgencyInfo.Type = 'school' AND (AgencyInfo.membershipstatus = 'Full' OR AgencyInfo.membershipstatus = 'Developmental') ORDER BY AgencyInfo.Name SELECT AgencyInfo.Name, BuildingInfo.Name as 'BuildingName', AgencyInfo.agencyid, BuildingInfo.buildingid FROM BuildingInfo INNER JOIN AgencyInfo ON BuildingInfo.agencyid = AgencyInfo.agencyid ORDER BY AgencyInfo.Name, BuildingInfo.Name; There are indexes on the id and name related fields used in the WHERE clauses. Kathy Mazur Worden Prairie Area Library System -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 12:07 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: Re: query cache question Could you post an example of a query you are expecting to be cached? On Wed, 2006-08-30 at 11:43 -0500, Mazur Worden, Kathy wrote: Hi, I just turned on the query cache by modifying its size and limit and restarting the server. However queries aren't being stored to it: Variable_name Value Qcache_free_blocks 1 Qcache_free_memory 10477008 Qcache_hits 0 Qcache_inserts 0 Qcache_lowmem_prunes 0 Qcache_not_cached 3759 Qcache_queries_in_cache 0 Qcache_total_blocks 1 I've found http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html which says the qcache_not_cached variable is the number of queries which were either not SELECT's or have SQL_NO_CACHE specified so I ran several selects (selected records in a few tables using Query Browser and refreshed a few web pages hitting the db). None of the queries have the no cache option specified. What else would prevent queries from getting into the cache? Kathy Mazur Worden Prairie Area Library System -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: query cache question
Actually no, I'm not using any prepared statements. But thanks for the pointer on that. -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 2:24 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: RE: query cache question Ah, I think I may have figured it out... Are you using prepared statements? If so, doing that prevents the query cache from caching the resultset entirely. On Wed, 2006-08-30 at 14:17 -0500, Mazur Worden, Kathy wrote: @@global.query_cache_type ON Variable_name Value Qcache_free_blocks1 Qcache_free_memory10477008 Qcache_hits 0 Qcache_inserts0 Qcache_lowmem_prunes 0 Qcache_not_cached 20318 Qcache_queries_in_cache 0 Qcache_total_blocks 1 Thanks -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 2:12 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: RE: query cache question Please show the output of: SELECT @@global.query_cache_type; and then: SHOW GLOBAL STATUS LIKE 'Qc%'; Thanks! On Wed, 2006-08-30 at 13:43 -0500, Mazur Worden, Kathy wrote: The output of that query is: @@global.query_cache_size 10485760 Kathy Mazur Worden Prairie Area Library System -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 12:42 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: RE: query cache question Those queries look just fine to me. Could you output the result of the following: SELECT @@global.query_cache_size; On Wed, 2006-08-30 at 12:27 -0500, Mazur Worden, Kathy wrote: Maybe that's my issue, I had though the type of query would not matter. I've run simple queries in query browser like this: Select * from table t; And refreshed web pages hit about a hundred times a day containing counting queries like this: select count(BuildingInfo.deliverycode) AS 'Total' from BuildingInfo, AgencyInfo where BuildingInfo.agencyid = AgencyInfo.agencyid AND AgencyInfo.type = 'academic' AND (BuildingInfo.membershipstatus = 'Full' OR BuildingInfo.membershipstatus = 'Developmental') And select queries at least as long as these: SELECT AgencyInfo.Name, AgencyInfo.agencyid from AgencyInfo where AgencyInfo.Type = 'school' AND (AgencyInfo.membershipstatus = 'Full' OR AgencyInfo.membershipstatus = 'Developmental') ORDER BY AgencyInfo.Name SELECT AgencyInfo.Name, BuildingInfo.Name as 'BuildingName', AgencyInfo.agencyid, BuildingInfo.buildingid FROM BuildingInfo INNER JOIN AgencyInfo ON BuildingInfo.agencyid = AgencyInfo.agencyid ORDER BY AgencyInfo.Name, BuildingInfo.Name; There are indexes on the id and name related fields used in the WHERE clauses. Kathy Mazur Worden Prairie Area Library System -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 12:07 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: Re: query cache question Could you post an example of a query you are expecting to be cached? On Wed, 2006-08-30 at 11:43 -0500, Mazur Worden, Kathy wrote: Hi, I just turned on the query cache by modifying its size and limit and restarting the server. However queries aren't being stored to it: Variable_name Value Qcache_free_blocks 1 Qcache_free_memory 10477008 Qcache_hits 0 Qcache_inserts 0 Qcache_lowmem_prunes 0 Qcache_not_cached 3759 Qcache_queries_in_cache 0 Qcache_total_blocks 1 I've found http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html which says the qcache_not_cached variable is the number of queries which were either not SELECT's or have SQL_NO_CACHE specified so I ran several selects (selected records in a few tables using Query Browser and refreshed a few web pages hitting the db). None of the queries have the no cache option specified. What else would prevent queries from getting into the cache? Kathy Mazur Worden Prairie Area Library System -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query cache about the federated engine
It bring great effect in performance if federated engine can't use the cache. If mysql intend to support it in the future? - Original Message - From: Michael Loftis [EMAIL PROTECTED] To: wangxu [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Thursday, August 03, 2006 1:27 AM Subject: Re: query cache about the federated engine --On August 2, 2006 5:25:51 PM +0800 wangxu [EMAIL PROTECTED] wrote: I have a problem about the performance of federated engine. The mysql5.0 reference manual says that the FEDERATED tables do not work with the query cache, aren't they? How about the query cache used by the federated engine in the mysql5.1? If the query cache cann't be used with the federated engine tables still, how to enhance the query performance against to the federated tables effectively? Well for one if the machine isn't on the same LAN you'll never have good DB performance -- not sure if this is the case or not, just a helpful hint. For two, the reason why federated doesn't use the query cache is then you have to somehow invalidate the remote cache(s) which would require some form of protocol extension, either in the SQL or the over the wire protocol so that the server with the federated table could ask if it could cache a result, and the server that's serving to the federated client could inform that server when it's cache isn't valid. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query cache about the federated engine
I have a problem about the performance of federated engine. The mysql5.0 reference manual says that the FEDERATED tables do not work with the query cache, aren't they? How about the query cache used by the federated engine in the mysql5.1? If the query cache cann't be used with the federated engine tables still, how to enhance the query performance against to the federated tables effectively?
Re: query cache about the federated engine
--On August 2, 2006 5:25:51 PM +0800 wangxu [EMAIL PROTECTED] wrote: I have a problem about the performance of federated engine. The mysql5.0 reference manual says that the FEDERATED tables do not work with the query cache, aren't they? How about the query cache used by the federated engine in the mysql5.1? If the query cache cann't be used with the federated engine tables still, how to enhance the query performance against to the federated tables effectively? Well for one if the machine isn't on the same LAN you'll never have good DB performance -- not sure if this is the case or not, just a helpful hint. For two, the reason why federated doesn't use the query cache is then you have to somehow invalidate the remote cache(s) which would require some form of protocol extension, either in the SQL or the over the wire protocol so that the server with the federated table could ask if it could cache a result, and the server that's serving to the federated client could inform that server when it's cache isn't valid. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Cache configuration
Hi friends, I need your help. I want to configure the Query Cache, but it dosn't work. I have installed MySQL 5.0.19 in Mandrake Linux. The server has 2GB memory ram, 150 gb hard disk, exclusive for dbms. I configure my system how say the home page. I follow step by step the instructions. (http://dev.mysql.com/doc/refman/5.0/en/query-cache-how.html) If I execute *show variable like 'have_query_cache'*, the result is YES. The follow image show you my status into the global varaibles. mysql show global variables like 'query%'; +--+--+ | Variable_name| Value| +--+--+ | query_alloc_block_size | 8192 | | query_cache_limit| 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 52428800 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | +--+--+ 7 rows in set (0.00 sec) After execute the same query (select * from recursos) and run the command show status like 'Qcache%' I obtain the next result (second image): mysql show status like 'qc%'; +-+--+ | Variable_name | Value| +-+--+ | Qcache_free_blocks | 1| | Qcache_free_memory | 52419952 | | Qcache_hits | 0| | Qcache_inserts | 0| | Qcache_lowmem_prunes| 0| | Qcache_not_cached | 4| | Qcache_queries_in_cache | 0| | Qcache_total_blocks | 1| +-+--+ 8 rows in set (0.00 sec) Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: QUERY CACHE TUNING QUESTION
Hello. My $0.05 :) If you find out the unique queries (they often contain date and time), add SQL_NO_CACHE to SELECT statement - this will decrease the number of inserts of unique queries. Brent Baisley [EMAIL PROTECTED] wrote: You tripled the amount of memory assigned to the cache, but your Qcache_lowmem_prunes value went up 10x and Qcache_not_cached went up 6x. I would say that you have enough queries that are unique, that most of your queries are getting cached then flushed before the same query comes up again. The number of queries being cached went up 13x, so you are caching a lot more queries, but it's not doing all that much good. You're getting diminishing returns as you increase memory. The query cache works best when you have the same exact queries being run over and over. You know your code, so you need to think how many unique queries you have. Perhaps you're getting close to the number of unique queries you run and increasing your cache just a bit more will suddenly send your hit rate way up, perhaps not. Test in big increments. If you suddenly get a big boost in hit ratio, work your way back down. But it may be that the query cache just can't be used optimally with your system. On Sep 15, 2005, at 1:10 PM, Anil wrote: _ From: Anil [mailto:[EMAIL PROTECTED] Sent: Thursday, September 15, 2005 10:36 PM To: 'mysql@lists.mysql.com' Subject: QUERY CACHE TUNING QUESTION Hi list, We are using mysql 4.0.24 on RHEL 3.0 on dell 2650 machine with 2 gb RAM. We tried to fine tune query cache . please find below the analysis PARAMETER old modified == == query_cache_size 10 MB 32 MB Qcache_queries_in_cache 187 2444 Qcache_inserts 122668213560221 Qcache_hits 1510151 7380756 Qcache_lowmem_prunes 8501288096499 Qcache_not_cached2725692 16361318 Qcache_free_blocks 6512 Qcache_free_memory9.26 MB 28.79 MB qCACHE HIT RATE27.64% 19.78% QCACHE HIT RATE IS CALUCULATED WITH FORMULA = (Qcache_hits/ (Qcache_inserts +Qcache_not_cached+Qcache_hits)) * 100 QCACHE HIT RATE IS CALUCULATED WITH FORMULA = (Qcache_hits/ (Qcache_inserts +Qcache_not_cached+Qcache_hits)) * 100 As per the above analysis we observed that qcache hit rate came down after increasing query_cache_size from 10 MB to 32 MB and qcache_hits got increased but the remaining parameters Qcache_inserts, Qcache_not_cached, Qcache_lowmem_prunes also increased because of that hit rate came down drastically. We didn't understand the reason behind hit . Is there any recommended procedure to fine tune query cache . awaiting early reply from list. Thanks in advance Anil DBA -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
QUERY CACHE TUNING QUESTION
Hi list, We are using mysql 4.0.24 on RHEL 3.0 on dell 2650 machine with 2 gb RAM. We tried to fine tune query cache . please find below the analysis PARAMETER old modified == == query_cache_size 10 MB 32 MB Qcache_queries_in_cache 187 2444 Qcache_inserts 1226682 13560221 Qcache_hits 1510151 7380756 Qcache_lowmem_prunes850128 8096499 Qcache_not_cached 2725692 16361318 Qcache_free_blocks 65 12 Qcache_free_memory9.26 MB 28.79 MB qCACHE HIT RATE27.64% 19.78% QCACHE HIT RATE IS CALUCULATED WITH FORMULA = (Qcache_hits/(Qcache_inserts +Qcache_not_cached+Qcache_hits)) * 100 As per the above analysis we observed that qcache hit rate came down after increasing query_cache_size from 10 MB to 32 MB and qcache_hits got increased but the remaining parameters Qcache_inserts, Qcache_not_cached, Qcache_lowmem_prunes also increased because of that hit rate came down drastically. We didn't understand the reason behind hit . Is there any recommended procedure to fine tune query cache . awaiting early reply from list. Thanks in advance Anil DBA
QUERY CACHE TUNING QUESTION
_ From: Anil [mailto:[EMAIL PROTECTED] Sent: Thursday, September 15, 2005 10:36 PM To: 'mysql@lists.mysql.com' Subject: QUERY CACHE TUNING QUESTION Hi list, We are using mysql 4.0.24 on RHEL 3.0 on dell 2650 machine with 2 gb RAM. We tried to fine tune query cache . please find below the analysis PARAMETER old modified == == query_cache_size 10 MB 32 MB Qcache_queries_in_cache 187 2444 Qcache_inserts 122668213560221 Qcache_hits 1510151 7380756 Qcache_lowmem_prunes 8501288096499 Qcache_not_cached2725692 16361318 Qcache_free_blocks 6512 Qcache_free_memory9.26 MB 28.79 MB qCACHE HIT RATE27.64% 19.78% QCACHE HIT RATE IS CALUCULATED WITH FORMULA = (Qcache_hits/(Qcache_inserts +Qcache_not_cached+Qcache_hits)) * 100 QCACHE HIT RATE IS CALUCULATED WITH FORMULA = (Qcache_hits/(Qcache_inserts +Qcache_not_cached+Qcache_hits)) * 100 As per the above analysis we observed that qcache hit rate came down after increasing query_cache_size from 10 MB to 32 MB and qcache_hits got increased but the remaining parameters Qcache_inserts, Qcache_not_cached, Qcache_lowmem_prunes also increased because of that hit rate came down drastically. We didn't understand the reason behind hit . Is there any recommended procedure to fine tune query cache . awaiting early reply from list. Thanks in advance Anil DBA
Re: QUERY CACHE TUNING QUESTION
You tripled the amount of memory assigned to the cache, but your Qcache_lowmem_prunes value went up 10x and Qcache_not_cached went up 6x. I would say that you have enough queries that are unique, that most of your queries are getting cached then flushed before the same query comes up again. The number of queries being cached went up 13x, so you are caching a lot more queries, but it's not doing all that much good. You're getting diminishing returns as you increase memory. The query cache works best when you have the same exact queries being run over and over. You know your code, so you need to think how many unique queries you have. Perhaps you're getting close to the number of unique queries you run and increasing your cache just a bit more will suddenly send your hit rate way up, perhaps not. Test in big increments. If you suddenly get a big boost in hit ratio, work your way back down. But it may be that the query cache just can't be used optimally with your system. On Sep 15, 2005, at 1:10 PM, Anil wrote: _ From: Anil [mailto:[EMAIL PROTECTED] Sent: Thursday, September 15, 2005 10:36 PM To: 'mysql@lists.mysql.com' Subject: QUERY CACHE TUNING QUESTION Hi list, We are using mysql 4.0.24 on RHEL 3.0 on dell 2650 machine with 2 gb RAM. We tried to fine tune query cache . please find below the analysis PARAMETER old modified == == query_cache_size 10 MB 32 MB Qcache_queries_in_cache 187 2444 Qcache_inserts 122668213560221 Qcache_hits 1510151 7380756 Qcache_lowmem_prunes 8501288096499 Qcache_not_cached2725692 16361318 Qcache_free_blocks 6512 Qcache_free_memory9.26 MB 28.79 MB qCACHE HIT RATE27.64% 19.78% QCACHE HIT RATE IS CALUCULATED WITH FORMULA = (Qcache_hits/ (Qcache_inserts +Qcache_not_cached+Qcache_hits)) * 100 QCACHE HIT RATE IS CALUCULATED WITH FORMULA = (Qcache_hits/ (Qcache_inserts +Qcache_not_cached+Qcache_hits)) * 100 As per the above analysis we observed that qcache hit rate came down after increasing query_cache_size from 10 MB to 32 MB and qcache_hits got increased but the remaining parameters Qcache_inserts, Qcache_not_cached, Qcache_lowmem_prunes also increased because of that hit rate came down drastically. We didn't understand the reason behind hit . Is there any recommended procedure to fine tune query cache . awaiting early reply from list. Thanks in advance Anil DBA -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A little help with mysql's Query Cache configuration
Mysql's query cache has two option's [query_cache_limit] and [query_cache_size]. The documentation is not clear (for me) on their purpose and correct usage tactics. What does the [query_cache_size] sets, the maximum memory space a single query can utilize ? Is it wise to set it to the same size as [query_cache_limit] ? How much memory would you recommend setting the caching to, if I have 1GB on the system, and it runs httpd/php mysql all together ? I've also included a dump from our live server, for which I have configured the query cache. for reference (or criticism) : mysql SHOW STATUS LIKE 'Qcache%'; +-+-+ | Variable_name | Value | +-+-+ | Qcache_queries_in_cache | 1007| | Qcache_inserts | 98199 | | Qcache_hits | 97128 | | Qcache_lowmem_prunes| 49330 | | Qcache_not_cached | 671 | | Qcache_free_memory | 1063112 | | Qcache_free_blocks | 340 | | Qcache_total_blocks | 2374| +-+-+ 8 rows in set (0.00 sec) mysql SHOW global VARIABLES like 'query_cache%'; +---+-+ | Variable_name | Value | +---+-+ | query_cache_limit | 5242880 | | query_cache_size | 3145728 | | query_cache_type | ON | +---+-+ 3 rows in set (0.00 sec) Thank you. -- Cheers, Maxim Vexler (hq4ever). Do u GNU ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A little help with mysql's Query Cache configuration
query_cache_size - a total size of memory that server utilizes for query caching. query_cache_limit - maximum size of result set that can be cached. I'd suggest leaving query_cache_limit at 1 mb (the default value), and tune query_cache_size until you have reasonably low qcache_lowmem_prunes value. On 8/15/05, Maxim Vexler [EMAIL PROTECTED] wrote: Mysql's query cache has two option's [query_cache_limit] and [query_cache_size]. The documentation is not clear (for me) on their purpose and correct usage tactics. What does the [query_cache_size] sets, the maximum memory space a single query can utilize ? Is it wise to set it to the same size as [query_cache_limit] ? How much memory would you recommend setting the caching to, if I have 1GB on the system, and it runs httpd/php mysql all together ? I've also included a dump from our live server, for which I have configured the query cache. for reference (or criticism) : mysql SHOW STATUS LIKE 'Qcache%'; +-+-+ | Variable_name | Value | +-+-+ | Qcache_queries_in_cache | 1007| | Qcache_inserts | 98199 | | Qcache_hits | 97128 | | Qcache_lowmem_prunes| 49330 | | Qcache_not_cached | 671 | | Qcache_free_memory | 1063112 | | Qcache_free_blocks | 340 | | Qcache_total_blocks | 2374| +-+-+ 8 rows in set (0.00 sec) mysql SHOW global VARIABLES like 'query_cache%'; +---+-+ | Variable_name | Value | +---+-+ | query_cache_limit | 5242880 | | query_cache_size | 3145728 | | query_cache_type | ON | +---+-+ 3 rows in set (0.00 sec) Thank you. -- Cheers, Maxim Vexler (hq4ever). Do u GNU ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Will myisam lock if the query can be resolved from disk cache/query cache?
OK. Lets take a mythical application. The app is spending about 50% of its time inserting into table FOO. The other 50% of the time its spent doing SELECT against the table. The SELECTs can use an index which is already full loaded into the query cache. Not only THAT but it doesn't need to read the disk because the filesystem buffer has the blocks in memory. In this config will the SELECTs block for the INSERTs? I guess they would! This type of scenario would yield dramatic performance imporovements by migrating to INNODB... would it not? Either that or there's the option of using MyISAM with no DELETEd rows (since it can then support concurrent insert.) Kevin -- Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. See irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Will myisam lock if the query can be resolved from disk cache/query cache?
Hi Kevin, On May 8, 2005, at 1:07 PM, Kevin Burton wrote: OK. Lets take a mythical application. The app is spending about 50% of its time inserting into table FOO. The other 50% of the time its spent doing SELECT against the table. The SELECTs can use an index which is already full loaded into the query cache. Indexes aren't loaded into the query cache, they are loaded into the key cache (key_buffer_size). Now assuming that you have the query cache actually being used (the cache of the actual statement), then normally the SELECT won't wait for any locks. So someone can have a WRITE lock on the table, and your SELECT will still run. If you have a query cache miss, then it will need to acquire the READ lock like a normal SELECT. Not only THAT but it doesn't need to read the disk because the filesystem buffer has the blocks in memory. In this config will the SELECTs block for the INSERTs? I guess they would! Yes. If MySQL has to actually read the table or indexes then it will set locks to do so, as appropriate to the storage engine in use. Keep in mind, if everything is coming from cache (key_buffer + disk buffer), the lock will generally be very quick as there isn't any disk i/o to block on. This type of scenario would yield dramatic performance imporovements by migrating to INNODB... would it not? Either that or there's the option of using MyISAM with no DELETEd rows (since it can then support concurrent insert.) Concurrent insert sounds like it would work well with your above mythical application (since you didn't mention any DELETEs). The only locking conflict you might have would be that your INSERT's would lock other INSERT's while it is occuring. Regards, Harrison -- Harrison C. Fisk, Trainer and Consultant MySQL AB, www.mysql.com Get a jumpstart on MySQL Cluster -- http://www.mysql.com/consulting/packaged/cluster.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Will myisam lock if the query can be resolved from disk cache/query cache?
Harrison Fisk wrote: aren't loaded into the query cache, they are loaded into the key cache (key_buffer_size). Yes... you busted me ! :). I meant to say key cache though. Now assuming that you have the query cache actually being used (the cache of the actual statement), then normally the SELECT won't wait for any locks. So someone can have a WRITE lock on the table, and your SELECT will still run. If you have a query cache miss, then it will need to acquire the READ lock like a normal SELECT. Yes... I realize. The issue is though that only a small percentage of our queries are actually using the query cache. Not only THAT but it doesn't need to read the disk because the filesystem buffer has the blocks in memory. In this config will the SELECTs block for the INSERTs? I guess they would! Yes. If MySQL has to actually read the table or indexes then it will set locks to do so, as appropriate to the storage engine in use. Keep in mind, if everything is coming from cache (key_buffer + disk buffer), the lock will generally be very quick as there isn't any disk i/o to block on. Of course... I realize. But what if its blocked by 5-10 INSERTs. Then its going to have to WAIT for these INSERTs to complete even though it can resolve the query without waiting for the table :-/ This type of scenario would yield dramatic performance imporovements by migrating to INNODB... would it not? Either that or there's the option of using MyISAM with no DELETEd rows (since it can then support concurrent insert.) Concurrent insert sounds like it would work well with your above mythical application (since you didn't mention any DELETEs). The only locking conflict you might have would be that your INSERT's would lock other INSERT's while it is occuring. Yes... thats my current thinking. That our INSERTs are blocking SELECTs even if they can complete without hitting disk. Now we need to find out if we can use the concurrent select feature of myisam without migrating to INNODB. Its harder to migrate to innodb right now. Kevin -- Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. See irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Will myisam lock if the query can be resolved from disk cache/query cache?
Hi, On May 8, 2005, at 1:47 PM, Kevin Burton wrote: Harrison Fisk wrote: aren't loaded into the query cache, they are loaded into the key cache (key_buffer_size). Yes... you busted me ! :). I meant to say key cache though. Alright, I assumed a typo or such. Not only THAT but it doesn't need to read the disk because the filesystem buffer has the blocks in memory. In this config will the SELECTs block for the INSERTs? I guess they would! Yes. If MySQL has to actually read the table or indexes then it will set locks to do so, as appropriate to the storage engine in use. Keep in mind, if everything is coming from cache (key_buffer + disk buffer), the lock will generally be very quick as there isn't any disk i/o to block on. Of course... I realize. But what if its blocked by 5-10 INSERTs. Then its going to have to WAIT for these INSERTs to complete even though it can resolve the query without waiting for the table :-/ It is waiting for the table, it isn't a matter of waiting for disk i/o, but instead making sure it doesn't read anything in an inconsistent state. Every operation has to be atomic and make sure not to read half-written information. Also concurrent insert would prevent this from happening as well. This type of scenario would yield dramatic performance imporovements by migrating to INNODB... would it not? Either that or there's the option of using MyISAM with no DELETEd rows (since it can then support concurrent insert.) Concurrent insert sounds like it would work well with your above mythical application (since you didn't mention any DELETEs). The only locking conflict you might have would be that your INSERT's would lock other INSERT's while it is occuring. Yes... thats my current thinking. That our INSERTs are blocking SELECTs even if they can complete without hitting disk. Now we need to find out if we can use the concurrent select feature of myisam without migrating to INNODB. Its harder to migrate to innodb right now. There isn't really any way to use concurrent INSERT. It happens automatically if possible. However there are a few things you can do to help it along, such as OPTIMIZE after you DELETE large portions of the table. Also it does have to enabled in LOAD DATA INFILE manually. However, regular INSERTs and SELECTs should do this automatically. Why do you think this is your bottleneck? Have you measured anything quantitatively? What is your table_locks_immediate vs. your table_locks_waited? What is your rate of INSERTs? Regards, Harrison -- Harrison C. Fisk, Trainer and Consultant MySQL AB, www.mysql.com Get a jumpstart on MySQL Cluster -- http://www.mysql.com/consulting/packaged/cluster.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Will myisam lock if the query can be resolved from disk cache/query cache?
Harrison Fisk wrote: There isn't really any way to use concurrent INSERT. It happens automatically if possible. However there are a few things you can do to help it along, such as OPTIMIZE after you DELETE large portions of the table. Also it does have to enabled in LOAD DATA INFILE manually. However, regular INSERTs and SELECTs should do this automatically. Well... in order to use concurrent insert you need to prevent tables with deleted data. So the developer just needs to make sure they're always in this situation. Why do you think this is your bottleneck? Have you measured anything quantitatively? What is your table_locks_immediate vs. your table_locks_waited? What is your rate of INSERTs? This is a good idea actually. I took a look at these values and only 3.6% of our queries wait for locks. Of course the problem might be that these locks take up a lot of time in critical places. It would be nice to see these values broken down into seconds waited. but this is probably too much to ask for just yet ;) Kevin -- Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. See irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Query-Cache Reset
Hi, Im the administrador of a MySQL Server, it have lots of queries, and now its getting high load of processor, I try to increase the size of MySQL Query Cache, but if I put more than 128 Mb the cache its reseting all the time and the performance is worst. The server have 1,2 Mb of RAM and I can assign 256 or more MB to Query Cache for reduce processor load. My actual my.cnf config is: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 max_connections = 400 key_buffer = 64M myisam_sort_buffer_size = 32M join_buffer_size = 1M read_buffer_size = 4M sort_buffer_size = 4M table_cache = 1024 thread_cache_size = 128 wait_timeout = 1800 connect_timeout = 10 max_allowed_packet = 16M max_connect_errors = 10 query_cache_limit = 1M query_cache_size = 128M query_cache_type = 1 And my show status info: mysql show status; +--++ | Variable_name| Value | +--++ | Aborted_clients | 8378 | | Aborted_connects | 307| | Binlog_cache_disk_use| 0 | | Binlog_cache_use | 0 | | Bytes_received | 114459945 | | Bytes_sent | 1355402553 | | Com_admin_commands | 49554 | | Com_alter_db | 0 | | Com_alter_table | 0 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin| 0 | | Com_change_db| 178087 | | Com_change_master| 0 | | Com_check| 0 | | Com_checksum | 0 | | Com_commit | 0 | | Com_create_db| 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_table | 0 | | Com_dealloc_sql | 0 | | Com_delete | 49499 | | Com_delete_multi | 0 | | Com_do | 0 | | Com_drop_db | 0 | | Com_drop_function| 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_drop_user| 0 | | Com_execute_sql | 0 | | Com_flush| 0 | | Com_grant| 0 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_help | 0 | | Com_insert | 49288 | | Com_insert_select| 0 | | Com_kill | 0 | | Com_load | 3 | | Com_load_master_data | 0 | | Com_load_master_table| 0 | | Com_lock_tables | 96 | | Com_optimize | 0 | | Com_preload_keys | 0 | | Com_prepare_sql | 0 | | Com_purge| 0 | | Com_purge_before_date| 0 | | Com_rename_table | 0 | | Com_repair | 0 | | Com_replace | 61263 | | Com_replace_select | 0 | | Com_reset| 0 | | Com_restore_table| 0 | | Com_revoke | 0 | | Com_revoke_all | 0 | | Com_rollback | 0 | | Com_savepoint| 0 | | Com_select | 250306 | | Com_set_option | 2560 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 0 | | Com_show_charsets| 0 | | Com_show_collations | 0 | | Com_show_column_types| 0 | | Com_show_create_db | 102| | Com_show_create_table| 2550 | | Com_show_databases | 10 | | Com_show_errors | 0 | | Com_show_fields | 3006 | | Com_show_grants | 0 | | Com_show_innodb_status | 0 | | Com_show_keys| 0 | | Com_show_logs| 0 | | Com_show_master_status | 0 | | Com_show_new_master | 0 | | Com_show_open_tables | 0 | | Com_show_privileges | 0 | | Com_show_processlist | 0 | | Com_show_slave_hosts | 0 | | Com_show_slave_status| 0 | | Com_show_status | 10 | | Com_show_storage_engines | 0 | | Com_show_tables | 2664 | | Com_show_variables | 0 | | Com_show_warnings| 0 | | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_truncate | 0 | | Com_unlock_tables| 102| | Com_update | 71170 | | Com_update_multi | 0 | | Connections | 480457 | | Created_tmp_disk_tables | 49082 | | Created_tmp_files
Re: MySQL Query-Cache Reset
Hello. In my opinion, your status variables are not so bad-looking. About a half of your queries are taken from the cache and the Key_reads/Key_read_requests ratio is less than 1%. Check that the system doesn't start swapping with high value of Query Cache size. If you run lots of queries with 'group by' clause, increasing the tmp_table_size variable could help. The high load of processor is suspicious for me. What hardware do you have? Tell the OS and MySQL version as well. Dto. Sistemas de Unitel [EMAIL PROTECTED] wrote: Hi, I=92m the administrador of a MySQL Server, it have lot=92s of queries, = and now it=92s getting high load of processor, I try to increase the size of = MySQL Query Cache, but if I put more than 128 Mb the cache it=92s reseting all = the time=A0and the performance is worst. The server have 1,2 Mb of RAM and I = can assign 256 or more MB to Query Cache for reduce processor load. My actual my.cnf config is: [mysqld] datadir=3D/var/lib/mysql socket=3D/var/lib/mysql/mysql.sock old_passwords=3D1 max_connections =3D 400 key_buffer =3D 64M myisam_sort_buffer_size =3D 32M join_buffer_size =3D 1M read_buffer_size =3D 4M sort_buffer_size =3D 4M table_cache =3D 1024 thread_cache_size =3D 128 wait_timeout =3D 1800=20 connect_timeout =3D 10 max_allowed_packet =3D 16M max_connect_errors =3D 10 query_cache_limit =3D 1M query_cache_size =3D 128M query_cache_type =3D 1 And my show status info: mysql show status; +--++ | Variable_name| Value | +--++ | Aborted_clients | 8378 | | Aborted_connects | 307| | Binlog_cache_disk_use| 0 | | Binlog_cache_use | 0 | | Bytes_received | 114459945 | | Bytes_sent | 1355402553 | | Com_admin_commands | 49554 | | Com_alter_db | 0 | | Com_alter_table | 0 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin| 0 | | Com_change_db| 178087 | | Com_change_master| 0 | | Com_check| 0 | | Com_checksum | 0 | | Com_commit | 0 | | Com_create_db| 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_table | 0 | | Com_dealloc_sql | 0 | | Com_delete | 49499 | | Com_delete_multi | 0 | | Com_do | 0 | | Com_drop_db | 0 | | Com_drop_function| 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_drop_user| 0 | | Com_execute_sql | 0 | | Com_flush| 0 | | Com_grant| 0 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_help | 0 | | Com_insert | 49288 | | Com_insert_select| 0 | | Com_kill | 0 | | Com_load | 3 | | Com_load_master_data | 0 | | Com_load_master_table| 0 | | Com_lock_tables | 96 | | Com_optimize | 0 | | Com_preload_keys | 0 | | Com_prepare_sql | 0 | | Com_purge| 0 | | Com_purge_before_date| 0 | | Com_rename_table | 0 | | Com_repair | 0 | | Com_replace | 61263 | | Com_replace_select | 0 | | Com_reset| 0 | | Com_restore_table| 0 | | Com_revoke | 0 | | Com_revoke_all | 0 | | Com_rollback | 0 | | Com_savepoint| 0 | | Com_select | 250306 | | Com_set_option | 2560 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 0 | | Com_show_charsets| 0 | | Com_show_collations | 0 | | Com_show_column_types| 0 | | Com_show_create_db | 102| | Com_show_create_table| 2550 | | Com_show_databases | 10 | | Com_show_errors | 0 | | Com_show_fields | 3006 | | Com_show_grants | 0 | | Com_show_innodb_status | 0 | | Com_show_keys| 0 | | Com_show_logs| 0 | | Com_show_master_status | 0
RE: Client Side Query cache
Linux does some sort of page caching automatically and its the reason for there never being any Free memory in a Linux system. So if you read your db via XML and php and develop a page from that using a CSS style sheet Linux will cache it as a Page.(Took us forever to catch that one, something to do with CSS and no one really knows the answer that we have seen) As for a query cache edit your My.cnf and query_cache_type 1 2 = Cache only if script says to(my suggested setting, make your coders code for caching instead of catch all settings) This does cause some grief if the data changed you can see a cached result instead.(Even though your not supposed to). Also if your running in PPC feeds you can't cache those as you will hit their timeouts and get no redirect results. Our PPC XML Feed (http://www.firebasesofware.com) only allows the link to be valid for 3 minutes before your visitor will be redirected to our front door. This is not a traffic grab. Just 94% of cached results are typically fraud clicks so we don't allow the caching of results. We are more cautious than most (they use 5 minutes) only because we have a very high paying feed and it attracts the low life's of the internet world. Thanks Donny Lairson President 29 GunMuse Lane P.O. box 166 Lakewood NM 88254 http://www.gunmuse.com 469 228 2183 -Original Message- From: Mister Jack [mailto:[EMAIL PROTECTED] Sent: Thursday, April 14, 2005 4:49 AM To: mysql@lists.mysql.com Subject: Client Side Query cache Hi, I was wondering if there is any query cache code/lib somewhere to cache certains queries ? I'm always doing the same queries, (and the result never change, so I could spare the round-trip to the server), but caching each tine the data for it is a bit of work. Thanks, for your suggestions -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Client Side Query cache
Hi, I was wondering if there is any query cache code/lib somewhere to cache certains queries ? I'm always doing the same queries, (and the result never change, so I could spare the round-trip to the server), but caching each tine the data for it is a bit of work. Thanks, for your suggestions -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Client Side Query cache
Mister Jack wrote: Hi, I was wondering if there is any query cache code/lib somewhere to cache certains queries ? I'm always doing the same queries, (and the result never change, so I could spare the round-trip to the server), but caching each tine the data for it is a bit of work. Thanks, for your suggestions MySQL has a query cache. Check the documentation. Add the lines: query_cache_type= 1 query_cache_size= 16M to the [safe_mysqld] of your /etc/mysql/my.cnf file and restart mysql. It will cache all queries on a *connection*. ie to receive a cached result, you have to issue the query from the same connection as when the query was originally run. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query cache confused when using different client protocols
Hello. Yes. See a long discussion at: http://bugs.mysql.com/bug.php?id=6511 Thomas van Gulick [EMAIL PROTECTED] wrote: Try this: Setup a database server, with MySQL 4.1, with query cache turned on Setup a client machine with MySQL 4.0 Setup a client machine with MySQL 4.1 (libmysqlclient14) Create very simple table : CREATE TABLE woepwoep (CNT int NOT NULL); Insert single row : INSERT INTO woepwoep SET CNT=10; Now, do select on client machine with MySQL 4.0 : SELECT CNT FROM woepwoep; Query gets cached in format suitable for old protocol Now, do select on client machine with MySQL 4.1 : SELECT CNT FROM woepwoep; This returns undesirable results. The other way around (initial query done on MySQL 4.1 client, subsequent query done on MySQL 4.0 client) fails too, but then it at least say 'Malformed packet'. Is this a bug? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query cache confused when using different client protocols
Try this: Setup a database server, with MySQL 4.1, with query cache turned on Setup a client machine with MySQL 4.0 Setup a client machine with MySQL 4.1 (libmysqlclient14) Create very simple table : CREATE TABLE woepwoep (CNT int NOT NULL); Insert single row : INSERT INTO woepwoep SET CNT=10; Now, do select on client machine with MySQL 4.0 : SELECT CNT FROM woepwoep; Query gets cached in format suitable for old protocol Now, do select on client machine with MySQL 4.1 : SELECT CNT FROM woepwoep; This returns undesirable results. The other way around (initial query done on MySQL 4.1 client, subsequent query done on MySQL 4.0 client) fails too, but then it at least say 'Malformed packet'. Is this a bug? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Cache
Hi, Is there a way to access if a Query is returning its record from QUERY_CACHE or not? E.g. $sql=SELECT * FROM TABLE; $result=db_query($sql); print $result; //If query NoT returned from mysql_query_cache() //Would Output something like: Array[status][cached]=0; Array[status][count]=1; Array[data][0][field1]=1; Array[data][0][field2]=2; Etc... //ELSE //Would Output something like: Array[status][cached]=1; Array[status][count]=1; Array[data][0][field1]=1; Array[data][0][field2]=2; Etc... Any ideas would be greatly appreciated... Skex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Cache
Skex, show status like 'Qcache%'; Will give you status of the query cache. There isn't a way to tell if a query result came from the cache or not other than the time it took to return or if it's the only query ran between checks of the show status. Neither of these methods is very reliable. Cache hits from Qcache_hits and Com_select from show status like 'Com_select'; should be enough to tune the cache. Alternatly the mytop utility will show you cache hit miss ratios on the fly. -Eric On Tue, 7 Dec 2004 16:28:38 +, Alexis Cheshire [EMAIL PROTECTED] wrote: Hi, Is there a way to access if a Query is returning its record from QUERY_CACHE or not? E.g. $sql=SELECT * FROM TABLE; $result=db_query($sql); print $result; //If query NoT returned from mysql_query_cache() //Would Output something like: Array[status][cached]=0; Array[status][count]=1; Array[data][0][field1]=1; Array[data][0][field2]=2; Etc... //ELSE //Would Output something like: Array[status][cached]=1; Array[status][count]=1; Array[data][0][field1]=1; Array[data][0][field2]=2; Etc... Any ideas would be greatly appreciated... Skex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] http://www.bleated.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Cache
Do you know if this is available thru the C API .. Could a User Defined Function be written to return this? Any more ideas would be great.. Thanks Skex -Original Message- From: Eric Bergen [mailto:[EMAIL PROTECTED] Sent: 07 December 2004 16:50 To: Alexis Cheshire Cc: [EMAIL PROTECTED] Subject: Re: Query Cache Skex, show status like 'Qcache%'; Will give you status of the query cache. There isn't a way to tell if a query result came from the cache or not other than the time it took to return or if it's the only query ran between checks of the show status. Neither of these methods is very reliable. Cache hits from Qcache_hits and Com_select from show status like 'Com_select'; should be enough to tune the cache. Alternatly the mytop utility will show you cache hit miss ratios on the fly. -Eric On Tue, 7 Dec 2004 16:28:38 +, Alexis Cheshire [EMAIL PROTECTED] wrote: Hi, Is there a way to access if a Query is returning its record from QUERY_CACHE or not? E.g. $sql=SELECT * FROM TABLE; $result=db_query($sql); print $result; //If query NoT returned from mysql_query_cache() //Would Output something like: Array[status][cached]=0; Array[status][count]=1; Array[data][0][field1]=1; Array[data][0][field2]=2; Etc... //ELSE //Would Output something like: Array[status][cached]=1; Array[status][count]=1; Array[data][0][field1]=1; Array[data][0][field2]=2; Etc... Any ideas would be greatly appreciated... Skex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] http://www.bleated.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Cache
'show' is a query just like any select. They both return the same type of result set. As a result they can be issued from the C API or any other. http://dev.mysql.com/doc/mysql/en/SHOW_VARIABLES.html On Tue, 7 Dec 2004 17:12:28 +, Alexis Cheshire [EMAIL PROTECTED] wrote: Do you know if this is available thru the C API .. Could a User Defined Function be written to return this? Any more ideas would be great.. Thanks Skex -Original Message- From: Eric Bergen [mailto:[EMAIL PROTECTED] Sent: 07 December 2004 16:50 To: Alexis Cheshire Cc: [EMAIL PROTECTED] Subject: Re: Query Cache Skex, show status like 'Qcache%'; Will give you status of the query cache. There isn't a way to tell if a query result came from the cache or not other than the time it took to return or if it's the only query ran between checks of the show status. Neither of these methods is very reliable. Cache hits from Qcache_hits and Com_select from show status like 'Com_select'; should be enough to tune the cache. Alternatly the mytop utility will show you cache hit miss ratios on the fly. -Eric On Tue, 7 Dec 2004 16:28:38 +, Alexis Cheshire [EMAIL PROTECTED] wrote: Hi, Is there a way to access if a Query is returning its record from QUERY_CACHE or not? E.g. $sql=SELECT * FROM TABLE; $result=db_query($sql); print $result; //If query NoT returned from mysql_query_cache() //Would Output something like: Array[status][cached]=0; Array[status][count]=1; Array[data][0][field1]=1; Array[data][0][field2]=2; Etc... //ELSE //Would Output something like: Array[status][cached]=1; Array[status][count]=1; Array[data][0][field1]=1; Array[data][0][field2]=2; Etc... Any ideas would be greatly appreciated... Skex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] http://www.bleated.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] http://www.bleated.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Cache
Sorry I think I asked the question in the wrong way... I know I can access the Variables you mentioned via SHOW queries, but was wondering if there is any way by either: 1) Hacking the C Source Code OR 2) Writing a User Defined Function To determine if a particular query result has been returned from the Cache rather than the Direct from Raw Tables. Hope that is a bit clearer Cheers Skex -Original Message- From: Eric Bergen [mailto:[EMAIL PROTECTED] Sent: 07 December 2004 17:30 To: Alexis Cheshire Cc: [EMAIL PROTECTED] Subject: Re: Query Cache 'show' is a query just like any select. They both return the same type of result set. As a result they can be issued from the C API or any other. http://dev.mysql.com/doc/mysql/en/SHOW_VARIABLES.html On Tue, 7 Dec 2004 17:12:28 +, Alexis Cheshire [EMAIL PROTECTED] wrote: Do you know if this is available thru the C API .. Could a User Defined Function be written to return this? Any more ideas would be great.. Thanks Skex -Original Message- From: Eric Bergen [mailto:[EMAIL PROTECTED] Sent: 07 December 2004 16:50 To: Alexis Cheshire Cc: [EMAIL PROTECTED] Subject: Re: Query Cache Skex, show status like 'Qcache%'; Will give you status of the query cache. There isn't a way to tell if a query result came from the cache or not other than the time it took to return or if it's the only query ran between checks of the show status. Neither of these methods is very reliable. Cache hits from Qcache_hits and Com_select from show status like 'Com_select'; should be enough to tune the cache. Alternatly the mytop utility will show you cache hit miss ratios on the fly. -Eric On Tue, 7 Dec 2004 16:28:38 +, Alexis Cheshire [EMAIL PROTECTED] wrote: Hi, Is there a way to access if a Query is returning its record from QUERY_CACHE or not? E.g. $sql=SELECT * FROM TABLE; $result=db_query($sql); print $result; //If query NoT returned from mysql_query_cache() //Would Output something like: Array[status][cached]=0; Array[status][count]=1; Array[data][0][field1]=1; Array[data][0][field2]=2; Etc... //ELSE //Would Output something like: Array[status][cached]=1; Array[status][count]=1; Array[data][0][field1]=1; Array[data][0][field2]=2; Etc... Any ideas would be greatly appreciated... Skex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] http://www.bleated.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] http://www.bleated.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query-cache statistics
Hello, I was wondering whether someone could tell me something about my query-cache statistics (MySQL 4.0.22): Qcache queries in cache 4479 Qcache inserts 37392 Qcache hits 56994 Qcache lowmem prunes 0 Qcache not cached 24 Qcache free memory 9448968 Qcache free blocks 502 Qcache total blocks 9604 It seems to me, that the inserts/hits ratio is a bit off; meaning, I expect a good cache performance to be one where inserts is low vs. a high hits. Is that assessment right? Also, it seems my Qcache free memory is pretty high; but I left it that way because I do not seem to have a whole lot of Qcache free blocks. Would decreasing the allocated qcache memory also decrease the amount of free blocks? Thanks, - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query cache derived table
Based on may observations, derived tables (subselect) are not cacheable: select column1, column2 from (select SQL_CACHE * from MyTable) as b The second part is not inserted to QueryCache nor is it retrieved if already in cache. Also the file comments in sql_cache.cpp - Make derived tables cachable. Can anybody help steer me to the general steps required to add this functionality? Thanks Sorry if I have duplicated my post, not sure if first attempt was delivered due to spam controls. -- Mike McMahon [EMAIL PROTECTED] ActiveWire 410-547-8277 x610 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Cache
Thanks to all who replied. Regards Terry Riley - Original Message - We have the query cache turned on, and it appears to be working well. However, there appears to be no indication in the manual as to the time that a cached query remains in memory. In the absence of this information, is it safe to assume that a cached query remains there indefinitely, unless either (1) one of the tables used in the initial query is modified, or (2) the server is re-started? We are using ColdFusion MX, which has its own query cache (which is very useful for whats called Query-of-query selects), but there the residence time is configurable, and the timer is restarted if the cached query is called before the timeout. Regards Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Cache
We have the query cache turned on, and it appears to be working well. However, there appears to be no indication in the manual as to the time that a cached query remains in memory. In the absence of this information, is it safe to assume that a cached query remains there indefinitely, unless either (1) one of the tables used in the initial query is modified, or (2) the server is re-started? We are using ColdFusion MX, which has its own query cache (which is very useful for whats called Query-of-query selects), but there the residence time is configurable, and the timer is restarted if the cached query is called before the timeout. Regards Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Cache
Yes, a query cache is only removed when one of it's tables is updated or the server is restarted. -Eric On Sat, 7 Aug 2004 23:16 +0100 (BST), Terry Riley [EMAIL PROTECTED] wrote: We have the query cache turned on, and it appears to be working well. However, there appears to be no indication in the manual as to the time that a cached query remains in memory. In the absence of this information, is it safe to assume that a cached query remains there indefinitely, unless either (1) one of the tables used in the initial query is modified, or (2) the server is re-started? We are using ColdFusion MX, which has its own query cache (which is very useful for whats called Query-of-query selects), but there the residence time is configurable, and the timer is restarted if the cached query is called before the timeout. Regards Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Cache
At 23:16 +0100 8/7/04, Terry Riley wrote: We have the query cache turned on, and it appears to be working well. However, there appears to be no indication in the manual as to the time that a cached query remains in memory. In the absence of this information, is it safe to assume that a cached query remains there indefinitely, unless either (1) one of the tables used in the initial query is modified, or (2) the server is re-started? Yes. Why would you remove a result from the cache if it's still current? Actually, there is another condition: (3) you issue a RESET QUERY CACHE statement, which clears the entire cache. We are using ColdFusion MX, which has its own query cache (which is very useful for whats called Query-of-query selects), but there the residence time is configurable, and the timer is restarted if the cached query is called before the timeout. Regards Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Cache
In the last episode (Aug 07), Paul DuBois said: At 23:16 +0100 8/7/04, Terry Riley wrote: However, there appears to be no indication in the manual as to the time that a cached query remains in memory. In the absence of this information, is it safe to assume that a cached query remains there indefinitely, unless either (1) one of the tables used in the initial query is modified, or (2) the server is re-started? Yes. Why would you remove a result from the cache if it's still current? Actually, there is another condition: (3) you issue a RESET QUERY CACHE statement, which clears the entire cache. There's another one: (4) the data in the cache exceeds query_cache_size, in which case old cached results will get purged to make room for new ones. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query-cache questions
Hello, I just upgraded to MySQL 4.0.18. I have a question about the new query-cache. The documentation says: The FLUSH TABLES statement also flushes the query cache. What does that mean exactly? Does it mean FLUSH TABLES, in effect, is equal to RESET QUERY CACHE, in that it purges the cache? Or does it mean FLUSH TABLES just defrags the query-cache, and has the effect of FLUSH QUERY CACHE? I would also be interesting to know whether defragging the query-cache takes any disk I/O. Also, I initially set the query_cache_size to 128M. That is a bit excessive, perhaps. Is there a known, say, 'key_buffer to query_cache_size' ratio? Thanks, - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query cache and queries with non-english characters
G B U [EMAIL PROTECTED] wrote: Recently I've come around that mysql (4.1.0 at least) treats different queries containing non-english characters (in my case characters from cp1251 charset) as the same query and therefore returns wrong results. For example the following queries are regarded as identical while they are not: SELECT FROM table WHERE name LIKE 'ÐÒÉ%' SELECT FROM table WHERE name LIKE 'ÐÒÏ%' Is this a bug or expected behaviour? If you use 4.0.x or 3.23.xx version check that MySQL server uses cp1251 with SHOW VARIABLES LIKE character_set statement. If you use 4.1.x version check that column name has cp1251 character set. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query cache and queries with non-english characters
Just to be sure that this question is not supressed here :) Recently I've come around that mysql (4.1.0 at least) treats different queries containing non-english characters (in my case characters from cp1251 charset) as the same query and therefore returns wrong results. For example the following queries are regarded as identical while they are not: SELECT FROM table WHERE name LIKE '%' SELECT FROM table WHERE name LIKE '%' Is this a bug or expected behaviour? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query cache and queries with non-english characters
Recently I've come around that mysql (4.1.0 at least) treats different queries containing non-english characters (in my case characters from cp1251 charset) as the same query and therefore returns wrong results. For example the following queries are regarded as identical while they are not: SELECT FROM table WHERE name LIKE '%' SELECT FROM table WHERE name LIKE '%' Is this a bug or expected behaviour? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how big can Query Cache get?
MySQL baby [EMAIL PROTECTED] wrote: Anyone using TONS of Query Cache for a high-traffic site? Our search server is really sweating. I was about to jump through a LOT of software loops to program query caching at the app level, when I realized that MySQL had it built-in already. (We're using newest: 4.0.18) Seems we've got plenty of Qcache_free_memory (see below) - so I'm wondering... Has anyone set their query_cache_size= setting really high like 1GB of RAM - to see how it helps? You can set query_cache_size to 1Gb, but this can causes slight performance drop because MySQL will need to manage more memory. You better let MySQL manage memory by using Qcache_lowmem_prunes variable. Any more articles or info on this besides the docs at http://www.mysql.com/doc/en/Query_Cache.html ? | Key_blocks_used | 29625 | | Key_read_requests| 31993562 | | Key_reads| 28450 | | Key_write_requests | 204631 | | Key_writes | 131756 | | Max_used_connections | 140| | Not_flushed_key_blocks | 0 | | Not_flushed_delayed_rows | 0 | | Open_tables | 100| | Open_files | 146| | Open_streams | 0 | | Opened_tables| 106| | Questions| 1337108| | Qcache_queries_in_cache | 11363 | | Qcache_inserts | 530809 | | Qcache_hits | 520845 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached| 1984 | | Qcache_free_memory | 255088000 | | Qcache_free_blocks | 4773 | | Qcache_total_blocks | 27515 | -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how big can Query Cache get?
Anyone using TONS of Query Cache for a high-traffic site? Our search server is really sweating. I was about to jump through a LOT of software loops to program query caching at the app level, when I realized that MySQL had it built-in already. (We're using newest: 4.0.18) Seems we've got plenty of Qcache_free_memory (see below) - so I'm wondering... Has anyone set their query_cache_size= setting really high like 1GB of RAM - to see how it helps? Any more articles or info on this besides the docs at http://www.mysql.com/doc/en/Query_Cache.html ? | Key_blocks_used | 29625 | | Key_read_requests| 31993562 | | Key_reads| 28450 | | Key_write_requests | 204631 | | Key_writes | 131756 | | Max_used_connections | 140| | Not_flushed_key_blocks | 0 | | Not_flushed_delayed_rows | 0 | | Open_tables | 100| | Open_files | 146| | Open_streams | 0 | | Opened_tables| 106| | Questions| 1337108| | Qcache_queries_in_cache | 11363 | | Qcache_inserts | 530809 | | Qcache_hits | 520845 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached| 1984 | | Qcache_free_memory | 255088000 | | Qcache_free_blocks | 4773 | | Qcache_total_blocks | 27515 | | Rpl_status | NULL | | Select_full_join | 1005 | | Select_full_range_join | 0 | | Select_range | 22 | | Select_range_check | 803| | Select_scan | 6271 | | Slave_open_temp_tables | 0 | | Slave_running| ON | | Slow_launch_threads | 0 | | Slow_queries | 138| | Sort_merge_passes| 0 | | Sort_range | 105822 | | Sort_rows| 1479015| | Sort_scan| 5009 | | Table_locks_immediate| 638173 | | Table_locks_waited | 5449 | | Threads_cached | 8 | | Threads_created | 4300 | | Threads_connected| 111| | Threads_running | 2 | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Cache not being used...
Hi Daniel, Yes, query_cache_type is 1 by default if you don't set it. :-) It's not used by default, however, because query_cache_size is 0. You need to set query_cache_size to 16M, 32M, etc. Hope that helps. Matt - Original Message - From: Daniel Kasak Sent: Thursday, September 25, 2003 12:49 AM Subject: Query Cache not being used... Hi all. I've set an option in the my.cnf file ( for a 4.0.14 server ): [mysqld] query_cache_type = 1 and restarted the server. The query cache is supposed to be available. SHOW VARIABLES LIKE 'have_query_cache' returns 'YES' as described in the documentation. But all the qcache_* status indicators are always 0. Is there anything else I'm supposed to do to turn the query cache on? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
