Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
10 Newer Entries Showing entries 91 to 100 of 783 10 Older Entries

Displaying posts with tag: innodb (reset)

innodb_stats_on_metadata and slow queries on INFORMATION_SCHEMA
+2 Vote Up -0Vote Down

INFORMATION_SCHEMA is usually the place to go when you want to get facts about a system (how many tables do we have? what are the 10 largest tables? What is data size and index size for table t?, etc). However it is also quite common that such queries are very slow and create lots of I/O load. Here is a tip to avoid theses hassles: set innodb_stats_on_metadata to OFF.

This is a topic we already talked about, but given the number of systems suffering from …

  [Read more...]
how MySQL engineering broke the backups
+0 Vote Up -0Vote Down

MySQL has exceptional track of record by introducing minor fixes that cause major breakages. Though usually I could blame naiveté of engineers, who did not really ever have to deal with production implications, but lately I can start sensing various business implications against open-source offerings.

As an original author of mydumper I really cannot get out of my mind that 5.5 and 5.6 metadata locking changes are there to screw with anyone who is building a backup solution using stable snapshot views of …

  [Read more...]
MySQL 5.7 : Over 1M QPS with InnoDB Memcached Plugin
Employee_Team +5 Vote Up -0Vote Down

Or I could place in the title – “Yes, we done it!”

After reaching 500K QPS in Read-Only on SQL queries, it was natural to expect a much higher performance level from InnoDB Memcached Plugin which is by-passing all SQL related layers.. However the story is not simple, and yet far from finished

While for today we have already our first “preview” results showing that we’re able to reach over 1,000,000 Query/sec level with the latest MySQL 5.7 code:

click …

  [Read more...]
InnoDB adaptive flushing in MySQL 5.6: checkpoint age and io capacity
+0 Vote Up -0Vote Down

In MySQL 5.6 InnoDB has a dedicated thread (page_cleaner) that’s responsible for performing flushing operations. Page_cleaner performs flushing of the dirty pages from the buffer pool based on two factors:
- access pattern  -  the least recently used pages will be flushed by LRU flusher from LRU_list when buffer pool has no free pages anymore;
- age – the oldest modified non-flushed pages are part of flush_list structure and will be flushed by flush_list flusher based on several heuristics.

There is a good …



  [Read more...]
Recalculating InnoDB Persistent Statistics - a Story of the Bug Report
+0 Vote Up -0Vote Down

One of the first posts in this blog was about reporting MySQL bugs "properly", in a way that maximizes chances for it to be processed really soon. I had written the following there:
"Ideally, you should provide a complete test case and/or instructions that any reader can use to reproduce your problem"Indeed, if one can just copy/paste something to mysql command line client or run some file attached to see the problem, chances are high for the bug to be processed really soon. We all like to get low hanging fruits from time to …

  [Read more...]
TokuDB configuration variables of interest
+1 Vote Up -0Vote Down

During our experiments I came upon a few TokuDB variables of interest; if you are using TokuDB you might want to look into these:

  • tokudb_analyze_time

This is a boundary on the number of seconds an ANALYZE TABLE will operate on each index on each partition on a TokuDB table.

That is, if tokudb_analyze_time = 5, and your table has 4 indexes (including PRIMARY) and 7 partitions, then the total runtime is limited to 5*4*7 = 140 seconds.

Default in 7.1.0: 5 seconds

…  [Read more...]
FAQ: InnoDB extended secondary keys
Employee +4 Vote Up -0Vote Down

MySQL 5.6 introduced a new feature called extended secondary keys. We get a lot of questions about it and find that most of them come from a few incorrect assumption. In this post I'll try to get rid of the confusion once and for all. Famous last words... here goes:

Q1: Do I need to do anything to enable extended secondary keys?

No, nothing at all. It's on by default and I can't see any sensible reason why you would want to disable it. However, it is possible to disable it by tuning the optimizer_switch: SET optimizer_switch='use_index_extensions={on|off}'.

Q2: Does extended …





  [Read more...]
InnoDB scalability issues due to tables without primary keys
+1 Vote Up -0Vote Down

Each day there is probably work done to improve performance of the InnoDB storage engine and remove bottlenecks and scalability issues. Hence there was another one I wanted to highlight: Scalability issues due to tables without primary keys. This scalability issue is caused by the usage of tables without primary keys. This issue typically shows itself as contention on the InnoDB dict_sys mutex. Now the dict_sys mutex controls access to the data dictionary. This mutex is used at various important places throughout the InnoDB code and …

  [Read more...]
InnoDB scalability issues due to tables without primary keys
+0 Vote Up -0Vote Down

Each day there is probably work done to improve performance of the InnoDB storage engine and remove bottlenecks and scalability issues. Hence there was another one I wanted to highlight:

Scalability issues due to tables without primary keys

This scalability issue is caused by the usage of tables without primary keys. This issue typically shows itself as contention on the InnoDB dict_sys mutex. Now the dict_sys mutex controls access to the data dictionary. This mutex is used at various places. I will only mention a few of them:

  • During operations such as opening and closing table handles, or
  [Read more...]
Converting an OLAP database to TokuDB, part 3: operational stuff
+1 Vote Up -0Vote Down

This is the third post in a series of posts describing our experience in migrating a large DWH server to TokuDB (see 1st and 2nd parts). This post discusses operations; namely ALTER TABLE operations in TokuDB. We ran into quite a few use cases by this time that we can shed light on.

Quick recap: we've altered one of out DWH slaves to TokuDB, with the goal of migrating most of out …

  [Read more...]
10 Newer Entries Showing entries 91 to 100 of 783 10 Older Entries

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.