Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.0(EOL)
-
None
Description
https://lists.launchpad.net/maria-developers/msg13299.html
Sequences will skip all cached value at alter.
|
d77aaa6994b 11.0 |
create sequence s; |
select next value for s; # 1 |
alter sequence s cycle; |
select next value for s; # 1001, because the default cache size is 1000 and reserved_until is 1001 |
drop sequence s; |
It becomes worse when we throw in as <int_type> (Item 1 or MDEV-28152):
create sequence s as tinyint; |
select next value for s; |
alter sequence s maxvalue 63; |
select next value for s; # error: ER_SEQUENCE_RUN_OUT, because default cache size 1000 is out of range for tiny int, and no maxvalue will work. |
drop sequence s; |
This may be related to the documented behaviour[1] of discarding cached values when flushing tables. Presumably this makes server restart also do the same.
create sequence s; |
select next value for s; # 1 |
flush tables;
|
select next value for s; # 1001 |
drop sequence s; |
|
|
create sequence s; |
select next value for s; # 1 |
--source include/restart_mysqld.inc
|
select next value for s; # 1001 |
drop sequence s; |
[1] https://mariadb.com/kb/en/sequence-overview/#notes
Shall we
1. treat the alter-discard behaviour as a bug and fix it, OR
2. treat the alter-discard behaviour as intended, just like flush tables, OR
3. treat all cache discard behaviours as a bug and fix them?
Attachments
Issue Links
- relates to
-
MDEV-34291 Provide a variable for the default cache size for create sequence
-
- Open
-
Activity
The default is 1000 as a compromise between performance (especially with multi user and when using replication), disk usage and convenience.
Asking for a new, not cached value, is a quite slow operation, compared to creating a new row in a table (which is a common operation).
Having a low cache value causes more calls to the storage engine, more writes to the ready/undo logs and to the binary log, for each new not cached value.
Oracle for example, has a minimum value for 'cache' as 2 and a default value if cache is not specified of 20 rows.
The way sequences work, a user should never assume that there will be no holes in a sequence (this is true for any database I am aware of). If the 1000 is too big, one can always adjust it with the cache option or use auto increment.
Here is an example of using the cache of 1000. If replication would be used, things would be about 9% worse without the default cache and the binary log would be 7x
bigger when using a cache of 1 compared to 1000 (tested).
create sequence s1 cache=1 engine=innodb;
create table t1 (a int primary key default nextval(s1), b int) engine=innodb;
insert into t1 (b) select seq from seq_1_to_1000000;
Query OK, 1000000 rows affected (4.078 sec)
drop table if exists t1,s1;
create sequence s1 cache=1000 engine=innodb;
create table t1 (a int primary key default nextval(s1), b int) engine=innodb;
insert into t1 (b) select seq from seq_1_to_1000000;
Query OK, 1000000 rows affected (2.431 sec)
The default cache should rather be set to 1 (the PostgreSQL default), and then solution 2) is probably fine. The default 1000 leads to user confusion.