Re: Mysql cache issues???
Shawn, I can't help but wonder wether that first paragraph means there are concrete plans to redo the qc? shawn green shawn.l.gr...@oracle.com wrote: Hello Egoitz, On 7/15/2013 1:35 PM, Egoitz Aurrekoetxea wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 15/07/13 17:27, Reindl Harald wrote: ... snip... i would say my caches are working perfectly (not only the mysql cache, also opcache etc.) since whe have generate times down to 0.006 seconds for a typical CMS page here which runs in more than 200 installations on the main machine, at high load mysqld is never the problem without the query cache the overall performance drops by 30-40% Hi, The query cache hit rate is near 90% so I assume it's doing all properly... now I'm using 1GB as cache but... I will do some tries... till I see some significant behavior either due to success or failure... I was basically wondering what did you though about performance penalty due to the mysql cache... just that... Thank you very much then ... signature snipped ... Until we redesign the query cache, those stalls will remain. It is unwize to keep so many sets of query results around if they are not actually being used. As has been covered already, the freeze required to perform the purge of all results associated with a specific table can at times be extended (durations of 20-30 minutes are not unusual with cache sizes around 1GB). What you may find is that even if some of your results are reused frequently for a short period of time, they are not reused at all beyond a certain moment. This means you have hundreds or thousands of sets of query results sitting idle in your cache. Reduce the size of your cache until you start to see your reuse rate or efficiency rate decline significantly. You may be surprised how small that is for your workload. To achieve scalability: customize your cache structures to your workload (this may mean caching the results somewhere other than MySQL), optimize your tables for efficient storage and retrieval, and optimize your queries to be as efficient as practical. There are other scalability options such as replication and sharding that can also be introduced into your production environment to reduce the cost of computation on each copy (or portion) of your data. However, this is a topic best handled in a separate thread. -- Sent from Kaiten Mail. Please excuse my brevity. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
User-defined variables not working ONLY on first query in chained replication
I've noticed a weird issue in our chained replication environment where when setting user-defined variables, the first time the variable is used in a session the value is NULL, but all uses thereafter work correctly. The environment is such: we have a master (master1), which has a slave which is also a master (master3), which itself has slaves (master3-slave1), i.e.: master1 - master3 - master3-slave1 I can replicate my issue with a very simple setup. I simply create a test table with one TEXT column, and I set a user-defined variable: CREATE TABLE test_table (id INT(10) PRIMARY KEY AUTO_INCREMENT, result TEXT) ENGINE=InnoDB; SET @mynewvariable = testvalue And then insert the variable into the test table: INSERT INTO test_table VALUES (NULL, @mynewvariable); The first time I run this insert, the value is correctly inserted in to master1 and its slave, master3 (as you'd expect). However, a NULL value is inserted into master3-slave1. However, if I run the INSERT a second time (just the insert, no re-declaration of the user-defined variable), the value is correctly inserted in to all three servers, so that the contents of test_table on the three servers looks as follows: master1 master3 master3-slave1 - - -- testvalue testvalue NULL testvalue testvalue testvalue I don't believe this is related to replication delay, because even if I leave a while between setting the variable and running the first INSERT, the result is always the same. The problem is agnostic of table format or how complex the table is, we can reproduce it exactly like this all of the time. Is this a known issue in MySQL with chained replication like this, or have I discovered a bug? Server version information: master1: Percona Server 5.5.28-29.1 master3: Percona Server 5.5.28-29.3 master3-slave1: Percona Server 5.5.20-55 -- Global Personals is a limited company registered in England and Wales. Registered number: 04880697. Registered office: Minton Place, Victoria Street, Windsor, Berkshire, SL4 1EG, United Kingdom.
Re: User-defined variables not working ONLY on first query in chained replication
Hi Matthew, On 16/07/2013 21:21, Matthew Ward wrote: I've noticed a weird issue in our chained replication environment where when setting user-defined variables, the first time the variable is used in a session the value is NULL, but all uses thereafter work correctly. snip The first time I run this insert, the value is correctly inserted in to master1 and its slave, master3 (as you'd expect). However, a NULL value is inserted into master3-slave1. However, if I run the INSERT a second time (just the insert, no re-declaration of the user-defined variable), the value is correctly inserted in to all three servers, so that the contents of test_table on the three servers looks as follows: snip Is this a known issue in MySQL with chained replication like this, or have I discovered a bug? Do you happen to have any table level replication filters? If so it sounds like you are affected by a bug that was fixed in 5.5.32 (https://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-32.html): *Replication:*Running the server with both the|--log-slave-updates| http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_log-slave-updatesand|--replicate-wild-ignore-table| http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_replicate-wild-ignore-tableoptions in some cases caused updates to user variables not to be logged. (Note: at least replicate-ignore-table is also triggering that bug). Best regards, Jesper Krogh MySQL Support
