MYSQL :: Query Cache Issues

Query Cache Issues
I have a MySQL server running 4.0.12 on a 12 CPU Sun U4500 with 12GB of
memory.

With the query cache running, this machine would noticeably "hiccup"
(just stop responding to requests) every so often and wouldn't squeeze
more then 1000 queries per second or so as a result (heavily mixed OLTP
work, with a lot of SELECT's and almost as many UPDATE's).

Without the Query Cache (and no other tweaking), I'm now at over 2100
at the same time of day.

Is there a problem with the query cache that causes MySQL to be far
less scalable then just straight MySQL/InnoDB?

Are there any thoughts on this? Is there a resource specifically for
high performance/high load MySQL implementation/usage?

MySQL Query Cache: Should I Cache Small, Simple Queries Or Only Complex Ones?
Query cache works great for long, complex queries, but should I also be caching the simplest of select queries.

For example let's say I had a table with 100 records and I needed to select something using a unique key:

SELECT name FROM products WHERE id = 3
Is caching the above pointless, especially in terms of wasted cache memory, considering how basic it is?

Query Cache
I have some problems with i suppose is the "Query Cache".
I have mysql server version 5 installed.

I have an applicattion with works with odbc 3.51 driver.

The problem is... the effect of query cache when i work with 2 or 3 concurrent sessions in my application.

I'm doing this with a session (session 1):

select * from table...
update table...

In the other session (session 2) i do the SAME select:

select * from table...

I can't see at session 2 the changes of sentence update of the 1st session!! Only i can do this it's work the session and begin.

I supossed this a query cache, i dessactivate it i have tried. Know i have:

query_cache_type = OFF
query_cache_size = 0

Query Cache
I was playing around with the query cache. However, after I set global query_cache_size = 20480; and show variables like 'query_cache_size'; the size shown is still 0. Also tried to change the my.ini file. but still 0. The query_cache_type is set to 1.

Query Cache Help
I have a VB front end that works against a MySQL backend. There is a search module that displays matching records and allows you to edit and delete records. If records are deleted and the user than does another search, the results still display the deleted records even though they are no longer in the table. I am assuming this is a cache issue. I don't want to eliminate the cache in fear of performance degrade but would like to give the user the option to refresh which would clear the query cache. Has anyone done this before or does anyone have a better solution?

Query Cache?
So I'm fairly new to mySQL. I've developed a site in php, and I was wondering how I could speed up the queries. Essentially, every time a page is loaded, it has to run through a database of around 2,500 rows, and it has a bit of a lag every time that occurs. Is there any way to cache the database to speed up performance after the initial load?

Query Cache
Is there any way to see which queries are cached?

Should I Use MySQL Query Cache?
The majority of my website's bottlenecks occur due to the database overhead. This is because I run a user community website, where users can send messages, post comments, read/write forum topics, and view profiles.

I have been studying to solve how to speed up the http and the website, however I was also thinking about using caching techniques for the database as well (as opposed to only PHP and HTTP).

Therefore, since MySQL has it's own Query Cache system, would this be effective. I understand that it would work in a system that has a lot of SELECT queries with little UPDATE and INSERT procedures, but for a user community website which consists of lots of changing data, would it be effective?

When and where should Query Caching be used? My Server has alot of RAM and space, so what else should I look out for?

MySQL Query Cache
Is there a way to save or export the query cache in MySQL? I would like to keep the queries to run again when I change the data in my tables.

Query Cache Adjustments
The query cache is brilliant (in theory), but I find in practice it rarely does much.

The problem is that the cache is flushed after every table insert/update. The benefits of this are obvious (keep query results up to date), but I feel having the ability to adjust how often the cache is updated would be immeasurably beneficial.

If only I could have it updated every 5/10/50 inserts/updates (depending on level of table access).

This could lead to a boon to performance with little problem of data getting stale.

Is there anyway way to do this?

Query Cache - What Gets Pruned?
When the Query Cache is full and something has to be pruned, how is the decision to prune a query made? Is it first in-first out, or least recently used-first out? Can I manually set a priorty on what is removed from the cache first? Is there a "Never Remove From Cache" flag?

Understanding Query Cache
when using the search on one of our sites, it takes 4-5 seconds to return a result but if I immediately search for the same thing again it returns instantly due to the Query cache which is great. What seems strange is that 15-30 seconds later it ignores the cache and runs the same query again for a result. Is that normal? It seems like the Query Cache should maintain the query and result until the query cache runs out of memory and starts dumping the oldest records.

Redirecting Query Cache
I am trying to find out how to redirect the mysql query cache to a hard drive.

My goal is to make it usefull again after a restart.

ON DUPLICATE KEY UPDATE And Query Cache
After bludgeoning my head against the table for a bit, I figures out what the problem was that had been plaguing me.

When using a INSERT INTO ... ON DUPLICATE KEY UPDATE ...
statemtent, it seemed like the update was not taking place.
After viewing the webpage in question, (and doing the manual select statement), the old data was being returned.

I couldn't figure out why, until FINALLY, I added 'RESET QUERY CACHE' manually after the statement. Then the correct updated info was returned.

Apparently the query cache is not flushed after using the above ON DUPLICATE KEY UPDATE statement, like it does with other insert or updates.

What do I do now. Just not use that statement? Id like to avoid locking tables if possible. And I cant have the website showing the outdated info.

Query Cache: Simple Question
I just have a simple question about MySQL QQ.

What I want to know is: Is the query cache only setup to apply to queries that stay the same?

PHP

"SELECT one,two,three FROM table LIMIT 1"

(Standard query, no WHERE clause, works the same for all visitors)

But a Query like this then doesn't stay the same

PHP

"SELECT one,two,three FROM table WHERE id='".$id."' LIMIT 1"

Since the ID value depends on a php variable, thus is different each time, then the query is in fact not the same.

Is the use of the query cache still beneficial in a case like this?

Weird Query Cache Numbers
I'm running 4.0.18, and the queries in cache variable grew from about 2K
to 30K in a matter of hours when we hardly used the database at all.
Has anyone seen something similar? (The cache size was set to 100M and
cache limit to 5M -

Benchmark Command And Query Cache
I'm trying to optimize my database, and have read about the Benchmark command.

Here is one that I am playing with:
SELECT BENCHMARK(100,'SELECT COUNT(*) FROM table');

I assume this syntax is correct because I don't get any errors. However, this also doesn't return any errors:
SELECT BENCHMARK(100,'Any Invalid SQL Statement');

Also, I am having a hard time understanding how BENCHMARK can be effective regardless of syntax. I'm guessing the first time a query is ran, it will be stored in the QUERY_CACHE. Subsequent iterations of the benchmark command would basically be pointless (because the query is cached and not actually ran against the DB).

Thoughts?

Testing Speed And Query Cache
I'm trying to test performance on some queries with a million of registers on my table, using various keys.

The problem is that the first time i run the query, it takes about 4/5 seconds, but after that it takes about 0.02 seconds. I know that query cache can be flushed with reset query cache, flush query cache, flush tables also modifing some caps at the sql query, but i'll never take 4/5 seconds again.

for example:
select * from table where id=2 order by index_field desc; 4/5 seconds.
select * from table where id=2 order by index_field desc; 0.02 seconds.

select * from table where id=3 order by index_field desc; 4/5 seconds.
select * from table where id=3 order by index_field desc; 0.02 seconds.

etc, etc...

Skip-new Disables Query Cache?
skip-new is explained as follows in the manual: "Don't use new,
possibly wrong routines." (4.1.1 mysqld Command-line Options). Is
there a list of the routines that will be disabled when using this
command-line option?

The manual also states the following: "concurrent_inserts If ON (the
default), MySQL will allow you to use INSERT on MyISAM tables at the
same time as you run SELECT queries on them. You can turn this option
off by starting mysqld with --safe or --skip-new." (4.5.7.4 SHOW
VARIABLES).

So when i was trying to turn off concurrent insterts I used -skip-new
which also disabled query caching. It took me a little while to figure
this out. I'm using skip-concurrent-insert now. Should this section of
the manual be updated?

System Info:
Windows 2000 SP3
mysqld-nt.exe Ver 4.0.13-max-debug for Win

The Query Cache Does Not Return Stale
I am reading up on query cache feature and would like to have the following question clarified.

From mysql 5.0 manual, section 5.14, it says "The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed."

I would like to know what is the definition of "when tables are modified".

does it mean?

1. new records inserted into table?
2. records gets updated?
3. table structure is modified by using alter table command?

Query Cache Not Working: MySQL 5 / Windows XP
I just installed MySQL 5, and its running great except that the query cache is not working, despite the configuration excerpt from my "my.ini"
below:

query_cache_size=5M
query_cache_limit=2M
query_cache_min_res_unit=4096k
query_cache_type=1

From the MySQL command line, if I enter: SHOW VARIABLES LIKE
'have_query_cache';

I get:
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+

Also, doing a: SHOW STATUS LIKE 'Qcache%'; gives me:

+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 5234168 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 450 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+---------+

I figure the "Qcache_free_blocks" and "Qcache_total_blocks" values of 1
are creating the problem; it's basically telling me that there are no
(okay, 1) free blocks to put my query. However, these values never
change.

The query cache is not vital to me, but it does improve performance, and
I'd like to get it going....

SQL Query Issues
**********QUESTION CHANGED*****************
Error

SQL query: Documentation

SELECT sum( subtotal ) AS total_count
FROM (

SELECT 'male' AS source, id, thumbnail, name, height, chest, waist, collar, NULL , shoe, eyes, race, hair
FROM male_models
WHERE concat( id, thumbnail, name, height, chest, waist, collar, shoe, eyes, race, hair ) LIKE '%a%'
UNION ALL SELECT 'female' AS source, id, thumbnail, name, height, bust, waist, dress, hips, shoe, eyes, race, hair
FROM female_models
WHERE concat( id, thumbnail, name, height, bust, waist, dress, hips, shoe, eyes, race, hair ) LIKE '%a%'
UNION ALL SELECT 'talent' AS source, id, thumbnail, name, NULL , NULL , NULL , NULL , NULL , NULL , NULL , race, NULL
FROM character_models
WHERE concat( id, thumbnail, name, race ) LIKE '%a%'
)

MySQL said: Documentation
#1248 - Every derived table must have its own alias

Can I Cache My Whole Db?
I would like to know if I can cache my whole database. I have a couple of databases. All of them are very small, less then 5MB. Is there a way to cache the whole database in memory? What other ways are there to improve performance?

Cache
My web site is pulling from several tables that don't change all that often
and seems to be an ideal candidate for query caching. I tried to enable it
by copying the my-medium.cnf to /etc/my.cnf and restarted the service. From
my query editor I typed SHOW VARIABLES LIKE 'QUERY_CACHE_TYPE' and it
returns ON which is exactly what I want.

I then executed several queries from my site but none of them are being
cached. They're just plain SELECTs in most cases, any ideas why the DB won't
cache them? Do they have to be executed several times first?

When I try and check Qcache_queries_in_cache I get an empty set, likewise
for Qcache_not_cached.

Splitting Key Cache
In mysql 4.1, there are new ways to save different index files into
different key_cache segments. The idea is to remove problems with
key_cache locking if another process is accessing it.

As this involves maintenance, etc....

Question: Has anyone seen any measurable improvement in doing this,
vs. just creating a huge key_cache and being done with it.

Key Cache Buffer
It grows and grows and causes the mySQL client to throw an Out of Memory error. The only recourse I have is to reboot.
There must be a way to flush this without a reboot and I can't find it in the manuals.

MySQL Cache
I'm developing a Java application using MySQL v.3.23
via JDBC. When I change something in the database
from one computer, I cannot see the modification from
different computer. Even if I exit the application
changing the data, the second application is still
unable to see the changes.

Does Mysql Cache Joins?
will mysql still cache the first... umm... "quert" where it's selecting the forum name in this queru

SELECT
forums.forumname,
threads.threadname
FROM
forums, threads
WHERE
forums.forumid = 1
AND
threads.threadid = 7

or would it be better doing two queries, and knowing it'll cache the forums one?

SELECT
forum_name
FROM
forums
WHERE
forumid = 1
^^ Will get cached

SELECT
topic
FROM
threads
WHERE
threadid = 7
^^ might get cached after a few times the query has been called?

I'm quite new to "query cache"
I didn't know mysql did this
and now I'm trying to improve some of my apps, so I thought I'd askl =]

Connection Seems To Cache Data
I open MySQL Manager and connect to my database.

I then insert one record in to an InnoDB table in MySQL (4.0 and 4.1
alpha hosted on XP running mysqld --console).

I then retrieve the record successfully in MySQL Manager using the
existing connection. This indicates that commit is running
successfully, (I have seen this on the database trace too).

I then try to retrieve the record (through a small MySQL/TomCat
application). Sometimes it is retrieved successfully, and sometimes an
empty record set is returned.

If I reduce connection pooling (on the connection script (in
components.xconf))to min 1 max 1 connections, the record is retrieved
successfully every time.

This seems to me to indicate that existing connections created for my
TomCat app are searching on an old (maybe cached?) version of the
database. However, when by chance I get the same connection I used to
insert the record, the record is retrieved successfully.

However, the record is retrieved more often than not even when there
are 10 connections pooled, although I understand that connections are
not round-robbined and some are used more commonly than others,
supporting my suspision.

This is the only insert that uses transactions in my app, and it works
fine with Oracle. All other inserts (not using transactions) work fine
on both database servers (and both MySQL 4.1.1-alpha and 4.0.16).

Does Mysql Cache Results?
I'm doing some web development and have mysql (3.23.x) installed
to test with (win2k, but I don't think this question is particularly
platform specific).

I have a reasonably complex query which I am trying to optimise.
When I first startup mysql, and perform the query, it can take
10 or so seconds. However, further subsequent queries (sorting
by the same column) return in a fraction of a second. This is true
even with new WHERE or LIMIT clauses.

Is mysql creating a 'temporary' index and caching it? Is the original
10 second query the actual time for my query to execute? I'm finding
it difficuly to optimise my query as I'm not sure exactly which times
are correct.

Connection Seems To Cache Data
I then insert one record in to an InnoDB table in MySQL (4.0 and 4.1
alpha hosted on XP running mysqld --console).

I then retrieve the record successfully in MySQL Manager using the
existing connection. This indicates that commit is running
successfully, (I have seen this on the database trace too).

I then try to retrieve the record (through a small MySQL/TomCat
application). Sometimes it is retrieved successfully, and sometimes an
empty record set is returned.

If I reduce connection pooling (on the connection script (in
components.xconf))to min 1 max 1 connections, the record is retrieved
successfully every time.

This seems to me to indicate that existing connections created for my
TomCat app are searching on an old (maybe cached?) version of the
database. However, when by chance I get the same connection I used to
insert the record, the record is retrieved successfully.

However, the record is retrieved more often than not even when there
are 10 connections pooled, although I understand that connections are
not round-robbined and some are used more commonly than others,
supporting my suspision.

This is the only insert that uses transactions in my app, and it works
fine with Oracle. All other inserts (not using transactions) work fine
on both database servers (and both MySQL 4.1.1-alpha and 4.0.16).

Mysql Cache While HotCopy Runs
What I am looking for is if MySQL has the ability to cache Transactions while the database is locked (i.e. doing a Mysqlhotcopy or possible starting replication) whether in a buffer or possibly temporary table and then to commit this data to the database once the tables are unlocked.

Obviously it would be better if the buffers were able to be defined at differing sizes.

Log In Issues
I just started my MySql account from my Yahoo Small Business Webhosting account. I'm confused now, becuase I installed the PhpAdmin tool that they told me to, but now I can't sign into it. The PhpAdmin site says this is a MySql issue. It appears that my username and password aren't correct.

I am positive that I'm giving the username and password that I created when I created my MySql database a few minutes ago. I keep getting this error message: #1045 - Access denied for user 'root'@'localhost' (using password: YES). I tried resetting my password (to the same password that I gave when I started the account and also tried setting it to an entirely new one). The Yahoo help files said: "If your PHP or Perl configuration files require a host name, you may have a problem with your host name. Make sure that you are using the host name mysql, not the default host name localhost." I'm confused as to what username I should be typing, in this case.

Issues With IF()
Has anyone noticed issues with IF() or IF(expr1, CONCAT(exp1, exp2), CONCAT(exp1, exp2)) after upgrading from MySQL 4.0 to 4.1? A query in our system seems to be hanging and eating up some serious CPU usage (around 99%), yet on 4.0, the system seems to breeze through this query.

Match Against Issues
I have a query that is searching a fulltext field in the database using match against. I get 0 returns, however i believe this might be caused by the keyword being in to many rows and is being ignored by default in mysql. I've posted the query in case i am wrong. However if i'm not wrong how would i create a fulltext search? ....

Access Issues
I just got mySQL today on my fasthosts hosting account.

I want to use the administrator and migration tools which seem very good. I can connect fine to the Fasthosts server, but when I try to view users or add a table I get 'access denied for user xx'@'%' to database 'mysql' 1044.

I was only given one username/pass by my hosting company which I assumed would be the root?

p.s. the migration tools seems great. I managed to migrate my access db to my local machine in 1min!

4.1.14 Upgrade Issues
I had 4.0 running on my local machine but my ISP upgraded to 4.1.14 so I had to as well.

However, the upgrade didn't go as smoothly for me... I removed the service - before I realized I didn't have to. I then installed 4.1.14 without issue But now I can't get the new service to start.

DB Speed Issues?
I am having the oddest issue that I can't put my finger on. It's probably something simple..

I have two DB [prod & stage] on the same RedHat 9 server. These 2 databases share a copy of Apache 1.3.31 with same dbd/dbi drivers. This application is 100% perl with dbd/dbi.

Issue: When I point the code to the staging DB a particular page (Large calendar page, with lots of selects) takes 3 seconds to load. I then point the same code to the prod DB and the page takes 12 seconds to load.

What parameters could cause something this drastic? I will add one more thing here... the volume of data is the same (I copied the prod DB back to staging to rule that possibility out as well.)

Memory Issues
We have recently installed beta version of our application on IBM - Pentium Xeon - dual processor, 1 GB RAM, 80 GB HDD on Fedora Core 3 OS. The kernel version which I currently have is: 2.6.9-1.667 and Mysql Server version: 4.0.20-standard.

I have noticed that mysql processes (using 'top' command) take up substantial amount of memory over period of time. Please take a look at mysql processes output of 'top' command below: Code:

MySQL Issues
I have a few issues related to MySQL. Here are they:

1. As MySQL is acquired by Sun it is not clear how much effort Sun will provide to develop MySQL.

2. The cost might change after the acquisition from Sun which might affect the popularity of MySQL.

3. MySQL might not be suitable for large organisation.

4. Absence of stored procedures, sub-selects and triggers. (Check)

5. Failure to meet the ACID test - it is perfect though for storing/tracking non-critical data. (Check)

Does MySQL 5 corrects these issues? How many of these are sorted in the latest version?

Speed Issues
I have 15,000,000 bank records that are querried against 60,000 census records.

There is a primary index on the census table(concatonation of state/msa and tract) that corresponds to an index on the bank table(not unique on the bank table). I need these indexs to perform timely INNER JOIN querries.

NOTE - all querries that I describe are make temporary tables. They have to be for my app - unless there is an alternate way similar to make tables that I can use. Maybe w/ views?

The proscess works if I filter the bank table down to the state and county levels. If I just filter at the state level the querries drag - Illinois can be 5-10 minutes. I tried to add indexs on the STATE in both the bank and census table but it didn't seem to affect the peformance.

Is Mysql not made to handle 10s of millions of records? Is 10 minutes not long on this query? Am I properly indexing?

If I remove the census table from the equation and just do a straight WHERE things still slog at a slow pace

BigINT Issues..?
I have a PHP script which adds money to a user's bank, the bank feild uses bigInt with a length of 40. bigINT(40).

However, if the bank of a user surpasses 1trillion (1,000,000,000,000) then it resets to 0. I thought bigINT supported far larger numbers? Why is it happening??

Joining Issues
I av a table "code_desc" with the columns: code, desc. an example of a row from it is 01, Industrial Designs respectively.
I have another table with the name q_lists with a column referencing from the code_desc table in something similar to this:

select q.id, cd.desc from q_lists as q, code_desc as cd where q.department=cd.code
//department colums in the q_list table contains int values
//which are fully represented with original values in the code_desc table.
//this saves space

Lately i found the need of adding 1 more column (ministry) to the q_list table referencing from code_desc just the same way as the department column

The problem now is how to retrieve the real values of both from the code_desc table in the same query such that i av something something like:

id | department | ministry
---+------------------+---------
24 | Industrial Designs | Education

Utf8 Issues
I am making a full system backup to make sure if one computer goes down the other will be up and nothing will happen, but when I try to put the phpMyAdmin database on the backup it gives me this error

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT CHARACTER SET utf8 COLLATE utf8_bin' at line 1

this is the only line I put in to focus on the problem, but I do not know what it is
CREATE DATABASE `phpmyadmin` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

Mysql & Xp Issues
I'm not exactly a novice to computing or developing but I am a total novice with mySQL and anything that stems from it and after a trying several tutorials out and searching the forums, I am still stumped. SO any help would be appreciated.I am trying to install a 4.1 release of win32 mySQL and am having major problems getting a server to run at all. (it doesn't have to be 4.1)I get as far as installing it and then an auto configurator pops up and tries to set it up and then fails miserably.

I need this for OpenCMS (OpenCMS.org). Any advice welcome. Though if that advice is to remove xp or dual boot I need to look at other options.

Innodb Issues
I've inherited a database server that is bad shape. The database is very slow to spite having ample hardware. Symptoms:

1. From the mysql user agent the tables (innodb) work as normal although, slowely.

2. Looking at the file system, the files that store the table data are not being updated. Instead a very large files called ibdata1 is being updated regularly.

We think that the innodb engine in this version of mysql (4.0.18) is in some kind of unstable situation. What do you think? What do you recommend we do to fix this?

MySQL 5 Issues
I upgraded from 4.1 to 5.0 just to see if there where any changes that would break my application. After a lot of troubleshooting trying to track down the issue I was able to pin it down.I am using php 5 and a MySQL based session layer. After successfull login the login script redirects the user to the members area.
It seems the INSERT used to save the user information for the session is not being completed before the members page loads so the login fails.
I can fix this by doing 2 diffrent things
1. Backing down to MySQL 4.1 and everything works
2. Pausing the script for 1 sec prior to forwarding ( therefore giving mysql time to update ) works as well.