2 servers, 2 memory tables, different sizes

Question

I have got two servers both running a MySQL instance. The first one, server1, is running MySQL 5.0.22. The other one, server2, is running MySQL 5.1.58.

When I create a memory table on server1 and I add a row its size is instantly 8,190.0 KiB.

When I create a memory table on server2 and I add a row its size is still only some bytes, though.

Is this caused by the difference in MySQL version or (hopefully) is this due to some setting I can change?

EDIT:

I haven't found the reason for this behaviour yet, but I did found a workaround. So, for future references, this is what fixed it for me:

All my memory tables are made once and are read-only from thereon. When you specify to MySQL the maximum number of rows your table will have, its size will shrink. The following query will do that for you.

ALTER TABLE table_name MAX_ROWS = N

Answer 1

Factor of 2?

OK, the problem likely is caused by the UTF-8 vs latin1
:- http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

You can check the database connection, database default character set for both servers.

here is the testing I have just done :-

mysql> create table test ( name varchar(10) ) engine
    -> =memory;
Query OK, 0 rows affected (0.03 sec)

mysql> show create table test;
+-------+------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                   |
+-------+------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `name` varchar(10) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into test values ( 1 );

mysql> set names utf8;
Query OK, 0 rows affected (0.01 sec)


mysql> create table test2 ( name varchar(10) ) engine =memory default charset = utf8;
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> insert into test2 values ( convert(1 using utf8) );
Query OK, 1 row affected (0.01 sec)

mysql> select table_name, avg_row_length from information_schema.tables where TABLE_NAME in( 'test2', 'test');
+------------+----------------+
| table_name | avg_row_length |
+------------+----------------+
| test       |             12 |
| test2      |             32 |
+------------+----------------+
2 rows in set (0.01 sec)