ANN: Database Designer for MySQL 2.1.0 Beta available!
This release does not introduce any new functionality. A huge amount of internal and architecture tasks were done instead. New compiler version used as well as new exception tracer implemented. Some little changes, not worthy of mention, are also present. There are two new major features in this release as compared to previous: [!] New compiler used in development [!] New exception tracer tool used Please test v2.1.0-Beta thoroughly. We depend on your bug reports and feedback in order to proceed to final release. The more testing you do, the sooner 2.1.0 will be available. If you have some spare time to help us with testing, please visit MicroOLAP Database Designer for MySQL Download page at http://microolap.com/products/database/mysql-designer/download/. Please don't hesitate to ask any questions or report bugs with our Support Ticketing system available at http://www.microolap.com/support/ Beta releases should never be used in production; they are for testing purposes only.
uncertain future of Oracle MySQL exams [1Z0-871, 1Z0-872, 1Z0-873, 1Z0-874]
Hi I am now responsible for LAMP applications. I worked previously with Oracle 11g. It seems that those exams are an easy win for me. I guess my preparation effort is about 60 hours. 1Z0-871 MySQL 5 Developer Certified Professional Exam, Part I 1Z0-872 MySQL 5 Developer Certified Professional Exam, Part II 1Z0-873 MySQL 5 Database Administrator Certified Professional Exam, Part I 1Z0-874 MySQL 5 Database Administrator Certified Professional Exam, Part II My concern is about the future of MySQL and the MySQL certifications. - it seems the exams didn't change since 2005 - Linux distributions ship MariaDB (not MySQL) - Oracle Press don't publish an official study guide - MySQL OCP exams are simpler than other OCP exams (for example Oracle 11g) what do you think?
InnoDB problem.
Greetings. I've restored an MySQL backup from our MySQL server into another server. The backup includes InnoDB tables. After the import, MySQL recognized the innodb tables fine but when I try to do a check table ir returns that the table doesn't exists. Permission and owner of the table files (.frm files) are ok, since it recognizes MyISAM tables (they have the same permission). Innodb engine is enabled.. Which can cause the tables to appears as non existent, as far as they do really exist? -- Att.* *** Luis H. Forchesatto http://br.linkedin.com/pub/luis-henrique-forchesatto/73/684/a67
Re: InnoDB problem.
- Original Message - From: Luis H. Forchesatto luisforchesa...@gmail.com Subject: InnoDB problem. Permission and owner of the table files (.frm files) are ok, since it recognizes MyISAM tables (they have the same permission). Oops. You should always read the fine manual. You took file-level backups, yes? Did they include the ibdata1 and similar files? Those contain innodb's dictionary - and in default installs also all the actual tables. The database/* files only contain the .frm, for innodb. If I'm right, you haven't got a backup at all. I'm crossing my fingers that I'm wrong... -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: uncertain future of Oracle MySQL exams [1Z0-871, 1Z0-872, 1Z0-873, 1Z0-874]
Hello Lukas, On 7/22/2013 8:16 AM, Lukas Lehner wrote: Hi I am now responsible for LAMP applications. I worked previously with Oracle 11g. It seems that those exams are an easy win for me. I guess my preparation effort is about 60 hours. 1Z0-871 MySQL 5 Developer Certified Professional Exam, Part I 1Z0-872 MySQL 5 Developer Certified Professional Exam, Part II 1Z0-873 MySQL 5 Database Administrator Certified Professional Exam, Part I 1Z0-874 MySQL 5 Database Administrator Certified Professional Exam, Part II My concern is about the future of MySQL and the MySQL certifications. - it seems the exams didn't change since 2005 - Linux distributions ship MariaDB (not MySQL) - Oracle Press don't publish an official study guide - MySQL OCP exams are simpler than other OCP exams (for example Oracle 11g) what do you think? You are correct. However, after being acquired twice in rapid succession and after much internal MySQL reorganization due to each, a few resources are back in place to keep up with this stuff again. For some pretty solid legal reasons Oracle tries to avoid announcing much of anything before it is actually ready to be used. Stay tuned to the publicity channels for any official announcements if or when they are made. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Mysql cache issues???
For most, not all, production servers, these two are the 'right' settings: query_cache_type = OFF query_cache_size = 0 Both are needed to avoid some code paths from being unnecessarily followed. (Maybe someday, that will be fixed, too.) I recommend only 50M as the max for _size. Here are some metrics to look at to see if the QC is worth having. (Of course, you have to run with it ON or DEMAND for a while to get values for these.) Qcache_free_memory / query_cache_size -- good value..bad value: 0%,100% -- Meaning: Pct Query Cache free -- What to do if 'bad': lower query_cache_size Qcache_lowmem_prunes / Uptime -- good value..bad value: 0,15 -- Meaning: Query Cache spilling -- What to do if 'bad': increase query_cache_size Qcache_not_cached / Uptime -- good value..bad value: 0,80 -- Meaning: SQL_CACHE attempted, but ignored -- What to do if 'bad': Rethink caching; tune qcache Qcache_free_blocks * 4096 / query_cache_size -- good value..bad value: 0,1 -- Meaning: Fragmentation in qcache -- What to do if 'bad': decrease query_cache_min_res_unit Qcache_hits / Qcache_inserts -- good value..bad value: 10,1 -- Meaning: Hit to insert ratio -- high is good Qcache_hits / (Qcache_hits + Com_select) -- good value..bad value: 100%,25% -- Meaning: Hit ratio -- What to do if 'bad': Use _type=DEMAND and use SELECT SQL_NO_CACHE more often Qcache_total_blocks * query_cache_min_res_unit / Qcache_queries_in_cache -- Meaning: estimate of query size -- What to do if 'bad': adjust query_cache_min_res_unit Qcache_queries_in_cache -- Meaning: Queries cached (Com_select + Qcache_hits) / (Com_insert + Com_update + Com_delete + Com_replace) -- Meaning: Read to write ratio -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Monday, July 15, 2013 11:53 PM To: shawn green; mysql@lists.mysql.com Subject: 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Mysql cache issues???
And for most, not all, of /my/ production servers since pretty much ever, the right setting has been on. Again, please don't generalize based off your own usecase. I have no idea what the whole world is doing with their servers. Rick James rja...@yahoo-inc.com wrote: For most, not all, production servers, these two are the 'right' settings: query_cache_type = OFF query_cache_size = 0 Both are needed to avoid some code paths from being unnecessarily followed. (Maybe someday, that will be fixed, too.) I recommend only 50M as the max for _size. Here are some metrics to look at to see if the QC is worth having. (Of course, you have to run with it ON or DEMAND for a while to get values for these.) Qcache_free_memory / query_cache_size -- good value..bad value: 0%,100% -- Meaning: Pct Query Cache free -- What to do if 'bad': lower query_cache_size Qcache_lowmem_prunes / Uptime -- good value..bad value: 0,15 -- Meaning: Query Cache spilling -- What to do if 'bad': increase query_cache_size Qcache_not_cached / Uptime -- good value..bad value: 0,80 -- Meaning: SQL_CACHE attempted, but ignored -- What to do if 'bad': Rethink caching; tune qcache Qcache_free_blocks * 4096 / query_cache_size -- good value..bad value: 0,1 -- Meaning: Fragmentation in qcache -- What to do if 'bad': decrease query_cache_min_res_unit Qcache_hits / Qcache_inserts -- good value..bad value: 10,1 -- Meaning: Hit to insert ratio -- high is good Qcache_hits / (Qcache_hits + Com_select) -- good value..bad value: 100%,25% -- Meaning: Hit ratio -- What to do if 'bad': Use _type=DEMAND and use SELECT SQL_NO_CACHE more often Qcache_total_blocks * query_cache_min_res_unit / Qcache_queries_in_cache -- Meaning: estimate of query size -- What to do if 'bad': adjust query_cache_min_res_unit Qcache_queries_in_cache -- Meaning: Queries cached (Com_select + Qcache_hits) / (Com_insert + Com_update + Com_delete + Com_replace) -- Meaning: Read to write ratio -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Monday, July 15, 2013 11:53 PM To: shawn green; mysql@lists.mysql.com Subject: 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 -- 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
