Subject | BLOB Restore Speed |
---|---|
Author | andreas_kc |
Post date | 2009-10-20T14:27:05Z |
Please reference the following table definition.
CREATE TABLE "FILE_ATTACHMENTS"
(
"TICKET" INTEGER NOT NULL,
"FILE_ID" INTEGER NOT NULL,
"FILE_NAME" VARCHAR(255),
"FILE_LABEL" VARCHAR(1024),
"MIME_TYPE" VARCHAR(127),
"FILE_DATE" TIMESTAMP NOT NULL,
"FILE_TYPE" VARCHAR(16),
"FILE_MEMO" BLOB SUB_TYPE TEXT SEGMENT SIZE 80,
"FILE_BLOB" BLOB SUB_TYPE 0 SEGMENT SIZE 80,
PRIMARY KEY ("TICKET", "FILE_ID")
);
ALTER TABLE "FILE_ATTACHMENTS" ADD FOREIGN KEY ("TICKET") REFERENCES TICKET ("TICKET") ON UPDATE CASCADE ON DELETE CASCADE;
This table may contain 5-10k files, 100k-200k files, and an occasional 1-2MB file. File size avg is 128k. There are roughly 78,000 records in this table and it takes a good 10 hours to restore on fair hardware. When looking at linux "top" during the restore fbserver and gbak only runs at 1-2%. When other tables of the database restore then fbserver and gbak contend for 40-50% and top out processor use. What is the bottleneck in restoring this blob table? wa% is only around 4%. It almost looks like the computer is not doing much of anything other than slowly restoring this table. Is there anything that can be done to speed this up?
CREATE TABLE "FILE_ATTACHMENTS"
(
"TICKET" INTEGER NOT NULL,
"FILE_ID" INTEGER NOT NULL,
"FILE_NAME" VARCHAR(255),
"FILE_LABEL" VARCHAR(1024),
"MIME_TYPE" VARCHAR(127),
"FILE_DATE" TIMESTAMP NOT NULL,
"FILE_TYPE" VARCHAR(16),
"FILE_MEMO" BLOB SUB_TYPE TEXT SEGMENT SIZE 80,
"FILE_BLOB" BLOB SUB_TYPE 0 SEGMENT SIZE 80,
PRIMARY KEY ("TICKET", "FILE_ID")
);
ALTER TABLE "FILE_ATTACHMENTS" ADD FOREIGN KEY ("TICKET") REFERENCES TICKET ("TICKET") ON UPDATE CASCADE ON DELETE CASCADE;
This table may contain 5-10k files, 100k-200k files, and an occasional 1-2MB file. File size avg is 128k. There are roughly 78,000 records in this table and it takes a good 10 hours to restore on fair hardware. When looking at linux "top" during the restore fbserver and gbak only runs at 1-2%. When other tables of the database restore then fbserver and gbak contend for 40-50% and top out processor use. What is the bottleneck in restoring this blob table? wa% is only around 4%. It almost looks like the computer is not doing much of anything other than slowly restoring this table. Is there anything that can be done to speed this up?