6 messages in com.mysql.lists.clusterRe: upgraded from 5.0 to 5.1 but data...| From | Sent On | Attachments |
|---|---|---|
| Paul Maunders | 11 Sep 2006 06:41 | |
| Anatoly Pidruchny | 11 Sep 2006 06:53 | |
| Paul Maunders | 11 Sep 2006 07:20 | |
| Mikael Ronström | 11 Sep 2006 07:24 | |
| Anatoly Pidruchny | 11 Sep 2006 08:00 | |
| Paul Maunders | 11 Sep 2006 08:20 |
| Subject: | Re: upgraded from 5.0 to 5.1 but database size the same |
|---|---|
| From: | Paul Maunders (pa...@fubra.com) |
| Date: | 09/11/2006 08:20:32 AM |
| List: | com.mysql.lists.cluster |
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
The table structure is as follows...
CREATE TABLE `user` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `displayname` varchar(70) DEFAULT NULL, `password` varchar(32) NOT NULL DEFAULT '', `password_plaintext` varchar(70) NOT NULL DEFAULT '', `title` varchar(10) NOT NULL DEFAULT '', `first_name` varchar(30) NOT NULL DEFAULT '', `last_name` varchar(30) NOT NULL DEFAULT '', `email` varchar(70) NOT NULL DEFAULT '', `email_valid` enum('y','n','b') NOT NULL DEFAULT 'n', `confirmed_date` date NOT NULL DEFAULT '0000-00-00', `email_reverse` varchar(70) NOT NULL DEFAULT '', `address1` varchar(90) NOT NULL DEFAULT '', `address2` varchar(30) NOT NULL DEFAULT '', `city` varchar(50) NOT NULL DEFAULT '', `postcode` varchar(10) NOT NULL DEFAULT '', `county` varchar(30) NOT NULL DEFAULT '', `add_confirmed` enum('y','n') NOT NULL DEFAULT 'n', `active` enum('y','n') NOT NULL DEFAULT 'y', `activation_reminder_date` date NOT NULL DEFAULT '0000-00-00', `activation_reminder_stage` tinyint(1) unsigned NOT NULL DEFAULT '0', `banned_sent` enum('y','n') NOT NULL DEFAULT 'n', `borderline_reminder_date` date NOT NULL DEFAULT '0000-00-00', `borderline_reminder_stage` tinyint(1) unsigned NOT NULL DEFAULT '0', `unique_id` varchar(6) NOT NULL DEFAULT '', `where_heard` varchar(100) NOT NULL DEFAULT '', `locked` tinyint(1) unsigned NOT NULL DEFAULT '0', `done` tinyint(1) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `email` (`email`), KEY `add_confirmed` (`add_confirmed`), KEY `email_valid` (`email_valid`), KEY `active` (`active`), KEY `done` (`done`), KEY `email_reverse` (`email_reverse`), KEY `postcode` (`postcode`) )
As you can see there are a lot of varchars, so I would have expected to get a significant saving with 5.1
In fact, I have just dropped the whole database, and I am re-importing the whole thing but this time using SELECT and INSERT from the original source. After 40,000 records the average row size is now 87 Bytes.... almost 1/10th of the size in 5.0
Rows 42,196 Row length ø 60 Row size ø 87 Bytes Next Autoindex 42,197
So it does seem like there is a bug when using the ndb_restore tool from a 5.0 backup.
Paul
Anatoly Pidruchny wrote:
Well, first of all, may be you are right and 5.1 requires more space for this particular table then 5.0. What is the DDL of this table?
The whole database size is controlled by the DataMemory and IndexMemory configuration parameters. The amount of virtual memory taken by the ndbd processes depends mostly on these two parameters. But this database size is really the maximum size. This total database size is then allocated to tables and indexes. To find out how much space is allocated and how much space is still available, you can use the DUMP 1000 command in ndb_mgm. Just run ndb_mgm connecting to your Cluster, then run "ALL DUMP 1000", then look at your Cluster log file. You will find out messages about how many pages of data and index memory are allocated and how many are free. This way you can compare how much memory was actually used by 5.0 and how much memory is used by 5.1.
And, by the way, the restore process rebuilds the tables. There is no other special better way to rebuild tables so that VARCHARS are stored correctly.
Regards,
Anatoly. By database size I mean the data_length from SHOW TABLE STATUS.
I'm not familiar with the DUMP 1000 command, can you please explain to me what it does?
Here is the output of SHOW TABLE STATUS for one of the tables, as you can see there average row length is 800. This is approximately 3 times the size that each row took up when we were running with MyISAM tables.
*************************** 3. row *************************** Name: user Engine: NDBCLUSTER Version: 10 Row_format: Dynamic Rows: 1421973 Avg_row_length: 800 Data_length: 1294336000 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: 1421986 Create_time: NULL Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: number_of_replicas: 2
Regards,
Paul
Anatoly Pidruchny wrote:
Paul,
what do you call the "database size"? Is this the amount of the virtual memory taken up by the ndbd processes? Do you know about the "DUMP 1000" command? Just checking.
Regards, Anatoly. Hi,
I have just upgraded our deployment of MySQL cluster from 5.0 to 5.1 - I did a backup, deleted 5.0, installed 5.1 and then ran ndb_restore to get all the data back.
A lot of the space in the various tables was previously taken up by VARCHARS so I expected the 5.1 version of the database to be significantly smaller, however after restoring the data, it seems that the overall database size has remained the same.
Is this because I restored a backup of a 5.0 database and it somehow maintained the varchar "padding"?
Does anyone know of anyway that I can get MySQL to rebuild the tables so that VARCHARS are stored correctly?
Regards,
ClamAV 0.88.4/1845/Sun Sep 10 20:08:12 2006
-----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.3 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFFBX7XsYtjtAM5Dp4RAicuAKDW/Hs9XcqheYCn3FTPJNRuaukqXQCeOcw5 r6ifq2c2BfjshJN5tP2XXp0= =MvU5 -----END PGP SIGNATURE-----