A better solution, is just to increase your innodb buffer size, you will get virtually same performance as the 'memory' table once all the data is in memory. Plus all the data will be persisted.
That is partially what was done.
Other than increasing the buffer size, he did a partition on the data.
Together with the buffer size increase, if he created another table using the "archive" engine and kept pruning the data form the `domain` table to this archive table he would achieve the same thing with persistance.
All in all, it was a very nice solution and use case for the memory table! :)
Thanks for the link. Unfortunately, since there are 30 different columns that can be searched on, there are many indexes and the index size for the domains table itself is 4GB. I run this off a 2GB linode, so unless I add a lot more RAM, Innodb is not going to match the memory table speed.
The Innodb table right now has about 9 million records and is 8GB in size (4GB index size). The memory table has a subset of the same data - 1.2 million records and is 276MB in size.
It makes me curious what an apples-to-apples comparison would look like. What it you put the same subset in a separate innodb table and tune the memory settings so it's likely to stay resident?
We had tried this before switching to the memory table. I cannot remember exact details, but the resulting innodb table was still too large - maybe around 1 GB and we did not want to add additional RAM.
Another issue with using a second Innodb table would be the load time. Every 3 hours we empty the memory table and load the subset from the innodb table (we actually use an MyIsam table in the middle.. but that's not important). Loading the table takes less than 10 seconds. If we had used an innodb table instead of a memory table, this load time would be significant.
Comments
A better solution, is just to increase your innodb buffer size, you will get virtually same performance as the 'memory' table once all the data is in memory. Plus all the data will be persisted.
This is an old, but still very useful script for helping to suggest what settings to tweak: https://github.com/major/MySQLTuner-perl
http://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.ht...
That is partially what was done. Other than increasing the buffer size, he did a partition on the data. Together with the buffer size increase, if he created another table using the "archive" engine and kept pruning the data form the `domain` table to this archive table he would achieve the same thing with persistance.
All in all, it was a very nice solution and use case for the memory table! :)
Thanks for the link. Unfortunately, since there are 30 different columns that can be searched on, there are many indexes and the index size for the domains table itself is 4GB. I run this off a 2GB linode, so unless I add a lot more RAM, Innodb is not going to match the memory table speed.
I'm a little confused... the InnoDB table and memory table had the same data, but the InnoDB table was larger (at least twice as large, I presume)?
The Innodb table right now has about 9 million records and is 8GB in size (4GB index size). The memory table has a subset of the same data - 1.2 million records and is 276MB in size.
It makes me curious what an apples-to-apples comparison would look like. What it you put the same subset in a separate innodb table and tune the memory settings so it's likely to stay resident?
We had tried this before switching to the memory table. I cannot remember exact details, but the resulting innodb table was still too large - maybe around 1 GB and we did not want to add additional RAM.
Another issue with using a second Innodb table would be the load time. Every 3 hours we empty the memory table and load the subset from the innodb table (we actually use an MyIsam table in the middle.. but that's not important). Loading the table takes less than 10 seconds. If we had used an innodb table instead of a memory table, this load time would be significant.