PBXT tables very large on disk
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
PBXT |
Fix Committed
|
Undecided
|
Unassigned |
Bug Description
PBXT tables seem to be extremely large in comparison to other engines on disk. Even outputting to a .CSV file still produce a filesize that is less than half of PBXT:
mysql> SHOW CREATE TABLE Logger\G
*******
Table: Logger
Create Table: CREATE TABLE `Logger` (
`timestampOcc
`dateOccurred` date NOT NULL,
`session` char(32) DEFAULT NULL,
`host` varchar(255) DEFAULT NULL,
`sslMode` enum('enabled'
`requestURI` varchar(255) DEFAULT NULL,
`referer` varchar(255) DEFAULT NULL,
`userAgent` varchar(255) DEFAULT NULL,
`remoteHost` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> ALTER TABLE Logger ENGINE='PBXT';
Query OK, 945463 rows affected (1 min 21.74 sec)
Records: 945463 Duplicates: 0 Warnings: 0
mysql> SELECT DATA_LENGTH / 1024 / 1024 AS Data, INDEX_LENGTH / 1024 / 1024 AS Indexes FROM information_
+------
| Data | Indexes |
+------
| 0.90166473 | 0.00390625 |
+------
1 row in set (0.00 sec)
office101-221:test root# ls -lh
total 1195560
-rw-rw---- 1 _mysql staff 3.8M Apr 28 11:13 Logger-1.xtr
-rw-rw---- 1 _mysql staff 8.7K Apr 28 11:12 Logger.frm
-rw-rw---- 1 _mysql staff 580M Apr 28 11:13 Logger.xtd
-rw-rw---- 1 _mysql staff 4.0K Apr 28 11:12 Logger.xti
-rw-rw---- 1 _mysql staff 65B Apr 28 11:01 db.opt
mysql> ALTER TABLE Logger ENGINE='Archive';
Query OK, 945463 rows affected (13.85 sec)
Records: 945463 Duplicates: 0 Warnings: 0
mysql> SELECT DATA_LENGTH / 1024 / 1024 AS Data, INDEX_LENGTH / 1024 / 1024 AS Indexes FROM information_
+------
| Data | Indexes |
+------
| 27.35079288 | 0.00000000 |
+------
1 row in set (0.00 sec)
office101-221:test root# ls -lh
total 56048
-rw-rw---- 1 _mysql staff 27M Apr 28 11:08 Logger.ARZ
-rw-rw---- 1 _mysql staff 8.7K Apr 28 11:07 Logger.frm
-rw-rw---- 1 _mysql staff 65B Apr 28 11:01 db.opt
mysql> ALTER TABLE Logger ENGINE='MyISAM';
Query OK, 945463 rows affected (8.29 sec)
Records: 945463 Duplicates: 0 Warnings: 0
mysql> SELECT DATA_LENGTH / 1024 / 1024 AS Data, INDEX_LENGTH / 1024 / 1024 AS Indexes FROM information_
+------
| Data | Indexes |
+------
| 186.35408020 | 0.00097656 |
+------
1 row in set (0.01 sec)
office101-221:test root# ls -lh
total 381696
-rw-rw---- 1 _mysql staff 186M Apr 28 11:14 Logger.MYD
-rw-rw---- 1 _mysql staff 1.0K Apr 28 11:14 Logger.MYI
-rw-rw---- 1 _mysql staff 8.7K Apr 28 11:14 Logger.frm
-rw-rw---- 1 _mysql staff 65B Apr 28 11:01 db.opt
mysql> ALTER TABLE Logger ENGINE='InnoDB';
Query OK, 945463 rows affected (48.27 sec)
Records: 945463 Duplicates: 0 Warnings: 0
mysql> SELECT DATA_LENGTH / 1024 / 1024 AS Data, INDEX_LENGTH / 1024 / 1024 AS Indexes FROM information_
+------
| Data | Indexes |
+------
| 221.75000000 | 0.00000000 |
+------
1 row in set (0.00 sec)
office101-221:test root# ls -lh
total 475168
-rw-rw---- 1 _mysql staff 8.7K Apr 28 11:16 Logger.frm
-rw-rw---- 1 _mysql staff 232M Apr 28 11:17 Logger.ibd
-rw-rw---- 1 _mysql staff 65B Apr 28 11:01 db.opt
mysql> SELECT * FROM Logger INTO OUTFILE '/tmp/logger.csv';
Query OK, 945463 rows affected (8.04 sec)
office101-221:tmp root# ls -lh logger.csv
-rw-rw-rw- 1 _mysql wheel 215M Apr 28 11:20 logger.csv
office101-221:tmp root#
I wasn't sure if this was by design, but it seems like it could cost a lot of disk I/O? I was talking to a colleague (and fellow PBXT enthusiast) and he seems to think it may be an issue with PBXT not packing keys?
Related branches
Changed in pbxt: | |
status: | New → Incomplete |
My apologies! These tests were run on MySQL 5.1.34 with latest PBXT from Bazaar (revision 614). Both MySQL and PBXT were built from source on my Mac running OS X 10.5.6.
Also, though not directly related to each other perhaps, I ran the same tests for bug 368693 as well as 368692.