Merge lp:~hrvojem/percona-server/bug860922-5.5 into lp:percona-server/5.5

Proposed by Hrvoje Matijakovic on 2012-02-23
Status: Merged
Merged at revision: 221
Proposed branch: lp:~hrvojem/percona-server/bug860922-5.5
Merge into: lp:percona-server/5.5
Diff against target: 220 lines (+99/-67)
6 files modified
doc/source/compatibility.rst (+27/-0)
doc/source/conf.py (+1/-1)
doc/source/diagnostics/slow_extended_55.rst (+2/-3)
doc/source/index.rst (+1/-0)
doc/source/scalability/innodb_insert_buffer.rst (+68/-0)
doc/source/scalability/innodb_io_55.rst (+0/-63)
To merge this branch: bzr merge lp:~hrvojem/percona-server/bug860922-5.5
Reviewer Review Type Date Requested Status
Percona core 2012-02-23 Pending
Review via email: mp+94363@code.launchpad.net
To post a comment you must log in.
Stewart Smith (stewart) wrote :

On Thu, 23 Feb 2012 13:00:23 -0000, Hrvoje Matijakovic <email address hidden> wrote:
> +Expanded undo slots
> +===================
> +
> +Enabling :variable:`innodb_extra_undoslots` breaks compatibility with
> other programs. Specifically, it makes the datafiles unusable for
> ibbackup or for a MySQL server that is not run with this option.

We should be clear about it being Percona Server, and that MySQL doesn't
have this option. They did expand things in 5.6 IIRC (or was it 5.5, I
totally forget) - but I think they ended up implementing it in a
different way.

> +Fast checksums
> +==============
> +
> +Enabling :variable:`innodb_fast_checksum` will use more CPU-efficient
> algorithm, based on 4-byte words which can be beneficial for some
> workloads. Once enabled, turning it off will require table to be
> dump/imported again, since it will fail to start on data files created
> when :variable:`innodb_fast_checksums` was enabled.

s/it/Percona Server/

We should also document behaviour of MySQL (probably a "corrupted
checksum" message).

> +Page sizes other than 16KiB
> +===========================
> +
> +This is controlled by variable :variable:`innodb_page_size`. Changing
> the page size for an existing database is not supported. Table will
> need to be dumped/imported again if compatibility with |MySQL| is
> required.

We should mention that we have not yet tested about compatibility with
the same option in MySQL 5.6

--
Stewart Smith

Alexey Kopytov (akopytov) wrote :

On 24.02.12 2:22, Stewart Smith wrote:
> On Thu, 23 Feb 2012 13:00:23 -0000, Hrvoje Matijakovic <email address hidden> wrote:
>> +Expanded undo slots
>> +===================
>> +
>> +Enabling :variable:`innodb_extra_undoslots` breaks compatibility with
>> other programs. Specifically, it makes the datafiles unusable for
>> ibbackup or for a MySQL server that is not run with this option.
>
> We should be clear about it being Percona Server, and that MySQL doesn't
> have this option. They did expand things in 5.6 IIRC (or was it 5.5, I
> totally forget) - but I think they ended up implementing it in a
> different way.
>

It was 5.5 and it was implemented in the absolutely the same way as far
as on-disk compatibility is concerned.

In fact, innodb_extra_undoslots doesn't exist in Percona Server 5.5,
i.e. it just uses the additional segments created by the upstream code.
All innodb_extra_rseg.patch adds to 5.5 is an I_S status table.

Stewart Smith (stewart) wrote :

On Fri, 24 Feb 2012 07:10:21 -0000, Alexey Kopytov <email address hidden> wrote:
> On 24.02.12 2:22, Stewart Smith wrote:
> > On Thu, 23 Feb 2012 13:00:23 -0000, Hrvoje Matijakovic <email address hidden> wrote:
> >> +Expanded undo slots
> >> +===================
> >> +
> >> +Enabling :variable:`innodb_extra_undoslots` breaks compatibility with
> >> other programs. Specifically, it makes the datafiles unusable for
> >> ibbackup or for a MySQL server that is not run with this option.
> >
> > We should be clear about it being Percona Server, and that MySQL doesn't
> > have this option. They did expand things in 5.6 IIRC (or was it 5.5, I
> > totally forget) - but I think they ended up implementing it in a
> > different way.
> >
>
> It was 5.5 and it was implemented in the absolutely the same way as far
> as on-disk compatibility is concerned.

Ahh excellent. I was trying to recall a conversation from several years
ago regarding it, and I think that was from before there was the InnoDB
fix, so it may have just been them thinking about it.

> In fact, innodb_extra_undoslots doesn't exist in Percona Server 5.5,
> i.e. it just uses the additional segments created by the upstream code.
> All innodb_extra_rseg.patch adds to 5.5 is an I_S status table.

awesome.

(some days I feel I really should have just gone and read the code
rather than attempting to remember a conversation from 4 years ago or
something).
--
Stewart Smith

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== added file 'doc/source/compatibility.rst'
2--- doc/source/compatibility.rst 1970-01-01 00:00:00 +0000
3+++ doc/source/compatibility.rst 2012-02-24 09:55:21 +0000
4@@ -0,0 +1,27 @@
5+.. _compatibility:
6+
7+==============================================================
8+Options that make XtraDB tablespaces not compatible with MySQL
9+==============================================================
10+
11+Fast checksums
12+==============
13+
14+Enabling :variable:`innodb_fast_checksum` will use more CPU-efficient algorithm, based on 4-byte words which can be beneficial for some workloads. Once enabled, turning it off will require table to be dump/imported again, since |Percona Server| will fail to start on data files created when :variable:`innodb_fast_checksums` was enabled.
15+
16+In case you've migrated from |Percona Server| to |MySQL| you could get the "corrupted checksum" error message. In order to recover that table you'll need to:
17+
18+ 1) Reinstall Percona Server to read your tables that were created with fast checksums.
19+ 2) Dump the tables (or temporarily convert them to MyISAM).
20+ 3) Install stock MySQL (or at least disable fast checksums).
21+ 4) Restore the InnoDB tables (or convert back from MyISAM).
22+
23+Page sizes other than 16KiB
24+===========================
25+
26+This is controlled by variable :variable:`innodb_page_size`. Changing the page size for an existing database is not supported. Table will need to be dumped/imported again if compatibility with |MySQL| is required.
27+
28+Relocation of the doublewrite buffer
29+====================================
30+
31+Variable :variable:`innodb_doublewrite_file` provides an option to put the buffer on a dedicated disk in order to parallelize I/O activity on the buffer and on the tablespace. Only in case of crash recovery this variable cannot be changed, in all other cases it can be turned on/off without breaking the compatibility.
32
33=== modified file 'doc/source/conf.py'
34--- doc/source/conf.py 2012-02-13 08:49:21 +0000
35+++ doc/source/conf.py 2012-02-24 09:55:21 +0000
36@@ -264,5 +264,5 @@
37 intersphinx_mapping = {
38 'ps51' : ('http://www.percona.com/doc/percona-server/5.1/', None),
39 'xtrabackup' : ('http://www.percona.com/doc/percona-xtrabackup', None),
40- 'ptoolkit' : ('http://www.percona.com/doc/percona-toolkit', None)
41+ 'ptoolkit' : ('http://www.percona.com/doc/percona-toolkit/2.0/', None)
42 }
43
44=== modified file 'doc/source/diagnostics/slow_extended_55.rst'
45--- doc/source/diagnostics/slow_extended_55.rst 2011-12-17 17:34:06 +0000
46+++ doc/source/diagnostics/slow_extended_55.rst 2012-02-24 09:55:21 +0000
47@@ -91,12 +91,11 @@
48 Specifies that only a fraction of ``session/query`` should be logged. Logging is enabled for every nth ``session/query``. By default, n is 1, so logging is enabled for every ``session/query``. Please note: when :variable:`log_slow_rate_type` is ``session`` rate limiting is disabled for the replication thread.
49
50 Logging all queries might consume I/O bandwidth and cause the log file to grow large.
51- * When :variable:`log_slow_rate_type` is ``session``, this option lets you log full sessions, so you have complete records of sessions for later analysis; but you can rate-limit the number of sessions that are logged. Note that this feature will not work well if your application uses any type of connection pooling or persistent connections.
52- Note that you change :variable:`log_slow_rate_limit` in ``session`` mode, you should reconnect for get effect.
53+ * When :variable:`log_slow_rate_type` is ``session``, this option lets you log full sessions, so you have complete records of sessions for later analysis; but you can rate-limit the number of sessions that are logged. Note that this feature will not work well if your application uses any type of connection pooling or persistent connections. Note that you change :variable:`log_slow_rate_limit` in ``session`` mode, you should reconnect for get effect.
54
55 * When :variable:`log_slow_rate_type` is ``query``, this option lets you log just some queries for later analysis. For example, if you set the value to 100, then one percona of queryies will logged.
56
57-Note that every query has global unique ``query_id`` and every connection can has it own (session) :variable:``log_slow_rate_limit`.
58+Note that every query has global unique ``query_id`` and every connection can has it own (session) :variable:``log_slow_rate_limit``.
59 Decision "log or no" calculated in following manner:
60
61 * if ``log_slow_rate_limit`` is 0 - log every query
62
63=== modified file 'doc/source/index.rst'
64--- doc/source/index.rst 2012-02-22 06:27:01 +0000
65+++ doc/source/index.rst 2012-02-24 09:55:21 +0000
66@@ -58,6 +58,7 @@
67 :glob:
68
69 scalability/innodb_split_buf_pool_mutex
70+ scalability/innodb_insert_buffer
71 scalability/innodb_io_55
72 scalability/innodb_expand_undo_slots
73 scalability/innodb_adaptive_hash_index_partitions
74
75=== added file 'doc/source/scalability/innodb_insert_buffer.rst'
76--- doc/source/scalability/innodb_insert_buffer.rst 1970-01-01 00:00:00 +0000
77+++ doc/source/scalability/innodb_insert_buffer.rst 2012-02-24 09:55:21 +0000
78@@ -0,0 +1,68 @@
79+.. _innodb_insert_buffer:
80+
81+==========================
82+Configurable Insert Buffer
83+==========================
84+
85+Percona has implemented several changes related to MySQL's InnoDB Insert Buffer. These features enable adjusting the insert buffer to the different workloads and hardware configurations.
86+
87+System variables:
88+=================
89+
90+.. variable:: innodb_ibuf_active_merge
91+
92+ :version 5.5.8-20.0: Introduced
93+ :cli: Yes
94+ :conf: Yes
95+ :scope: Global
96+ :dyn: Yes
97+ :vartype: Numeric
98+ :default: 0 (~1.0.5), 1 (1.0.6~)
99+ :range: 0 - 1
100+
101+This variable specifies whether the insert buffer can be processed before it reaches its maximum size. The following values are allowed:
102+
103+ * 0:
104+ the insert buffer is not processed until it is full. This is the standard |InnoDB| behavior.
105+
106+ * 1:
107+ the insert buffer can be processed even it is not full.
108+
109+.. variable:: innodb_ibuf_merge_rate
110+
111+ :version 5.5.8-20.0: Introduced
112+ :cli: Yes
113+ :conf: Yes
114+ :scope: Global
115+ :dyn: Yes
116+ :default: 100
117+ :range: 100 - 999999999
118+
119+This variable allows better control of the background thread processing the insert buffer. Each time the thread is called, its activity is altered by the value of both ``innodb_io_capacity`` and ``innodb_ibuf_merge_rate`` this way: ::
120+
121+ [real activity] = [default activity] * (innodb_io_capacity/100) * (innodb_ibuf_merge_rate/100)
122+
123+By increasing the value of ``innodb_ibuf_merge_rate``, you will increase the insert buffer activity.
124+
125+.. variable:: innodb_ibuf_max_size
126+
127+ :cli: Yes
128+ :conf: Yes
129+ :scope: Global
130+ :dyn: No
131+ :vartype: Numeric
132+ :default: Half the size of the |InnoDB| buffer pool
133+ :range: 0 - Half the size of the |InnoDB| buffer pool
134+ :unit: Bytes
135+
136+This variable specifies the maximum size of the insert buffer. By default the insert buffer is half the size of the buffer pool so if you have a very large buffer pool, the insert buffer will be very large too and you may want to restrict its size with this variable.
137+
138+Setting this variable to 0 is equivalent to disabling the insert buffer. But then all changes to secondary indexes will be performed synchronously which will probably cause performance degradation. Likewise a too small value can hurt performance.
139+
140+If you have very fast storage (ie storage with RAM-level speed, not just a RAID with fast disks), a value of a few MB may be the best choice for maximum performance.
141+
142+Other Reading
143+=============
144+
145+* `Some little known facts about InnoDB Insert Buffer <http://www.mysqlperformanceblog.com/2009/01/13/some-little-known-facts-about-innodb-insert-buffer/>`_
146+* `5.0.75-build12 Percona binaries <http://www.mysqlperformanceblog.com/2009/01/23/5075-build12-percona-binaries/>`_
147
148=== modified file 'doc/source/scalability/innodb_io_55.rst'
149--- doc/source/scalability/innodb_io_55.rst 2012-01-12 09:52:26 +0000
150+++ doc/source/scalability/innodb_io_55.rst 2012-02-24 09:55:21 +0000
151@@ -172,69 +172,6 @@
152 in a sequential I/O operation as opposed to several random I/Os if
153 ``area`` is used.
154
155-.. variable:: innodb_ibuf_active_merge
156-
157- :version 5.5.8-20.0: Introduced
158- :cli: Yes
159- :conf: Yes
160- :scope: Global
161- :dyn: Yes
162- :vartype: Numeric
163- :default: 0 (~1.0.5), 1 (1.0.6~)
164- :range: 0 - 1
165-
166-This variable specifies whether the insert buffer can be processed before it reaches its maximum size. The following values are allowed:
167-
168- * 0:
169- the insert buffer is not processed until it is full. This is the standard |InnoDB| behavior.
170-
171- * 1:
172- the insert buffer can be processed even it is not full.
173-
174-.. variable:: innodb_ibuf_max_size
175-
176- :cli: Yes
177- :conf: Yes
178- :scope: Global
179- :dyn: No
180- :vartype: Numeric
181- :default: Half the size of the |InnoDB| buffer pool
182- :range: 0 - Half the size of the |InnoDB| buffer pool
183-
184-This variable specifies the maximum size of the insert buffer. By default the insert buffer is half the size of the buffer pool so if you have a very large buffer pool, the insert buffer will be very large too and you may want to restrict its size with this variable.
185-
186-Setting this variable to 0 is equivalent to disabling the insert buffer. But then all changes to secondary indexes will be performed synchronously which will probably cause performance degradation. Likewise a too small value can hurt performance.
187-
188-If you have very fast storage (ie storage with RAM-level speed, not just a RAID with fast disks), a value of a few MB may be the best choice for maximum performance.
189-
190-.. variable:: innodb_ibuf_merge_rate
191-
192- :version 5.5.8-20.0: Introduced
193- :cli: Yes
194- :conf: Yes
195- :scope: Global
196- :dyn: Yes
197- :default: 100
198- :range: 100 - 999999999
199-
200-This variable allows better control of the background thread processing the insert buffer. Each time the thread is called, its activity is altered by the value of both ``innodb_io_capacity`` and ``innodb_ibuf_merge_rate`` this way: ::
201-
202- [real activity] = [default activity] * (innodb_io_capacity/100) * (innodb_ibuf_merge_rate/100)
203-
204-By increasing the value of ``innodb_ibuf_merge_rate``, you will increase the insert buffer activity.
205-
206-.. variable:: innodb_log_block_size
207-
208- :cli: Yes
209- :conf: Yes
210- :scope: Global
211- :dyn: Yes
212- :vartype: Numeric
213- :default: 512
214- :unit: Bytes
215-
216-This variable changes the size of transaction log records. The default size of 512 bytes is good in most situations. However, setting it to 4096 may be a good optimization with SSD cards. While settings other than 512 and 4096 are possible, as a practical matter these are really the only two that it makes sense to use.
217-
218 .. variable:: innodb_read_ahead
219
220 :cli: Yes

Subscribers

People subscribed via source and target branches