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

Displaying posts with tag: innodb (reset)

InnoDB Primary Key versus Secondary Index: An Interesting Lesson from EXPLAIN
+1 Vote Up -0Vote Down

I ran into an interesting issue today, while examining some EXPLAIN outputs, and wanted to share the findings, as some of this is undocumented.

Basically, you can start with a very simple InnoDB table - 2 INT columns, Primary Key (PK) on the 1st column, regular index on the 2nd:

CREATE TABLE `t1` (
  `id1` int(10) unsigned NOT NULL,
  `id2` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id1`),
  KEY `id2` (`id2`)
) ENGINE=InnoDB;

The query is:

SELECT id1 FROM t1;

This is a straight-forward query with no WHERE clause.

read more

InnoDB Primary Key versus Secondary Index: An Interesting Lesson from EXPLAIN
+0 Vote Up -0Vote Down

I ran into an interesting issue today, while examining some EXPLAIN outputs, and wanted to share the findings, as some of this is undocumented.

Basically, you can start with a very simple InnoDB table – 2 INT columns, Primary Key (PK) on the 1st column, regular index on the 2nd:

CREATE TABLE `t1` (
  `id1` int(10) unsigned NOT NULL,
  `id2` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id1`),
  KEY `id2` (`id2`)
) ENGINE=InnoDB;

The query is:

SELECT id1 FROM t1;

This is a straight-forward query with no WHERE clause.

Given no WHERE clause, we know there will be a full table or index scan. Let’s look at EXPLAIN:

mysql> EXPLAIN SELECT id1 FROM t1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: NULL
          key: id2
      key_len:
  [Read more...]
Presenting MySQL 5.7 Performance & Benchmarks at Percona Live
Employee_Team +2 Vote Up -0Vote Down

I’ll speak about MySQL 5.7 Performance & Benchmarks during the incoming Percona Live and will be happy to share with you all our latest finding, improvements, benchmark results, open issues and many other stuff keeping our brains in constant activity ) As you know, there is no a “silver bullet” solution for MySQL Performance tuning.. – only by a good understanding of what is going inside of MySQL and InnoDB you may configure your MySQL server in the most optimal way for your workloads. And this topic is endless, and progressing every month with new solutions and features – all parties are working hard here, and I’m very

  [Read more...]
innodb_flush_logs_on_trx_commit and Galera Cluster
+0 Vote Up -0Vote Down

We deploy Galera Cluster (in MariaDB) for some clients, and innodb_flush_logs_on_trx_commit is one of the settings we’ve been playing with. The options according to the manual:

  • =0 don’t write or flush at commit, write and flush once per second
  • =1 write and flush at trx commit
  • =2 write log, but only flush once per second

The flush (fsync) refers to the mechanism the filesystem uses to try and guarantee that written data is actually on the physical medium/device and not just in a buffer (of course cached RAID controllers, SANs and other devices use some different logic there, but it’s definitely written beyond the OS space).

In a non-cluster setup, you’d always want it to be =1 in order to be ACID compliant and that’s also InnoDB’s default. So far so good. For cluster setups,

  [Read more...]
Presenting MySQL/InnoDB at Percona Live 2014
Employee_Team +1 Vote Up -0Vote Down

I will be presenting at Percona Live 2014 and I’m excited to share and discuss the latest and greatest features and improvements that we have made to MySQL/InnoDB in 5.7. Great performance improvements, there are some new exciting compression features that we are working on,  GIS support,  temporary table performance etc.. There is a long list. Also, we are always interested to hear about user issues and priorities so that we can address them and/or work them into our plan. Your feedback is very important for us, if you want to influence the direction of InnoDB development then you need to talk to me .

MariaDB 10.0.9 now available
+0 Vote Up -0Vote Down

The MariaDB project is pleased to announce the immediate availability of MariaDB 10.0.9. This is a Release Candidate release.

Among other changes, XtraDB is now the default InnoDB implementation, Oracle’s InnoDB is included as a plugin and can be dynamically loaded if desired. Packages for Ubuntu 14.04 “trusty” and Debian “Sid” have also been added to the MariaDB Ubuntu and Debian repositories.

See the Release Notes and Changelog for detailed information on this release and the

  [Read more...]
Engineer duo from Google, LinkedIn join again for InnoDB talks
+2 Vote Up -0Vote Down

Google senior systems engineer Jeremy Cole is once again teaming with LinkedIn senior software engineer Davi Arnaut for two InnoDB-focused sessions at the upcoming Percona Live MySQL Conference and Expo 2014 this April 1-4 in Santa Clara, California.

The duo will present “InnoDB: A journey to the core II” on April 2 and “InnoDB: A  hands-on exploration of on-disk storage with

  [Read more...]
Optimizing InnoDB Transactions
Employee +2 Vote Up -0Vote Down

(This is a cross-post from the Engine Yard blog. The original article appears here.)

Here is a question I've actually been asked a few times:

"I am writing a batch processing script which modifies data as part of an ongoing process that is scheduled by cron. I have the ability to group a number of modifications together into a transaction, but I'm not sure what the correct number is?"

First off, I think that this question is interesting not just in the context of batch processing, but it equally applies to all parts of the application. If you are designing a high throughput system for MySQL, there are actually some potential pain points that you can design your way around.

Potential Pain Points

Here are the situations where the

  [Read more...]
Fun with Bugs #30 - quick review of my reports in February, 2014
+0 Vote Up -0Vote Down
I've got only one comment to my previous post about deadlock, and it was more like a hint based on a different use case, not a real explanation. So far there is nobody who wants to get free beer... Maybe this is even good, as I do not go to the conference and BOF I've submitted will be supervised by my colleague Przemysław Malkowski. But you still have entire month till the conference to get a chance for a beer from him (we'll arrange this somehow).

In the meantime I'd like to review bug reports for MySQL server (few) and fine manual (many) that I've submitted in February, 2014. 22 in total, one

  [Read more...]
The MySQL ARCHIVE storage engine – Alternatives
+0 Vote Up -0Vote Down

In my previous post I pointed out that the existing ARCHIVE storage engine in MySQL may not be the one that will satisfy your needs when it comes to effectively storing large and/or old data. But are there any good alternatives? As the primary purpose of this engine is to store rarely accessed data in disk space efficient way, I will focus here on data compression abilities rather then on performance.

The InnoDB engine provides compressed row format, but is it’s efficiency even close to the one from that available in archive engine? You can also compress MyISAM tables by using myisampack tool, but that also means a table will be read only after such operation.

Moreover, I don’t trust MyISAM nor Archive when it comes to data

  [Read more...]
10 Newer Entries Showing entries 61 to 70 of 778 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.