Index garbage collection on varchar column causes server to hang
----------------------------------------------------------------
Key: CORE-5419
URL: http://tracker.firebirdsql.org/browse/CORE-5419
Project: Firebird Core
Issue Type: Bug
Affects Versions: 2.5.6
Environment: Windows, CentOs
Reporter: Thomas Kragh
Priority: Critical
Steps to reproduce:
1) Create a table(TEST) with a single varchar(36) column named ID (not null).
2) Load the table with data 4000000 rows using this: (select lower(
uuid_to_char(gen_uuid())) from rdb$database);
3) Create a primary key on the ID column.
4) Delete all rows in "TEST" with delete from test and commit
5) from another connection issue "select count(*) from test"
This will cause the database server to become unstable and hang for minutes at
a time(1-5 minutes), where no new connections is made and no other queries get
processed. Other databases on the server is affected in the same way as well!
To observe this behavior, i wrote a small program that would select
current_timestamp from the server(select current_timestamp from mon$database)
and print how long time it took to the console. Then sleep for 500ms and then
select the time time again. This loop ran during step 4 and 5.
If step 5 is omitted and backup/sweep is executed the same problem occurred
when garbage collection starts on the TEST table.
Servermode is super classic with pagebuffers set to 256 and tested with 1024
without any luck.
On the mailing list Ann Harrison gave this explanation:
https://groups.yahoo.com/neo/groups/firebird-support/conversations/topics/129936
(the first 15 messages is about virtual server might be the cause - skip those)
"Interesting. GUIDs produce really fat indexes. Firebird uses prefix
compression and GUIDs vary in the first bytes so they don't compress well. Keys
generated sequentially will be on different index leaf pages. Since records are
deleted and garbage collected in the order they were stored, index garbage
collection could easily touch a different page for each key. With only 256
buffers, index garbage collection is running pages through the cache wildly -
with luck they're cached by the file system."
The solution to the problem that made me file this bug was to use sequential
generated GUID, however this is not always possible, in the company I work at,
we use varchar index for social security number, login tokens, oauth
access/refresh tokens, guids from third party applications and so on.
Best regards
Thomas Kragh
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, SlashDot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
