MySQL in-memory table vs. SQLite in-memory DB
-
I would like to know which of the following options will produce the fastest transactions (and why). Unfortunately, I don't know enough about the low-level "nuts and bolts" of database operation to really have a good intuition on this. My PHP script resides on one server, my MySQL database resides on another. I believe that by performing operations on a regular MyISAM table, I am incurring time costs related to both 1) communicating between the web server and the database server, and 2) performing disk operations because of MyISAM is disk-based storage engine. I do not, however, know how much of the time cost is associated with each of these factors or if both are really significant (in the sense of important). I have some temporary data that I want to manipulate on a per-session basis, and the way I see it I have two options: I can create a local memory database and create a table there.
$wm = sqlite_open(':memory:', 0666);
sqlite_query($wm,"CREATE TABLE patterns (pattern varchar(500))");Or, I can create a table in memory on the existing MySQL database.
mysql_connect("not.localhost.com", "admin", "example");
mysql_select_db("example");
mysql_query("CREATE TEMPORARY TABLE patterns (pattern VARCHAR(500)) ENGINE=MEMORY");Which one of these will be faster to perform queries on? Will there be a noticable difference? My intuition is that the second will be slower because it is a transaction on a non-local database, but I don't know this for sure. When the table is in-memory, which computer's memory is it actually in? How much volume would have to be going on for a difference to actually be noticable? Any thoughts would be very much appreciated.
--Greg
-
I would like to know which of the following options will produce the fastest transactions (and why). Unfortunately, I don't know enough about the low-level "nuts and bolts" of database operation to really have a good intuition on this. My PHP script resides on one server, my MySQL database resides on another. I believe that by performing operations on a regular MyISAM table, I am incurring time costs related to both 1) communicating between the web server and the database server, and 2) performing disk operations because of MyISAM is disk-based storage engine. I do not, however, know how much of the time cost is associated with each of these factors or if both are really significant (in the sense of important). I have some temporary data that I want to manipulate on a per-session basis, and the way I see it I have two options: I can create a local memory database and create a table there.
$wm = sqlite_open(':memory:', 0666);
sqlite_query($wm,"CREATE TABLE patterns (pattern varchar(500))");Or, I can create a table in memory on the existing MySQL database.
mysql_connect("not.localhost.com", "admin", "example");
mysql_select_db("example");
mysql_query("CREATE TEMPORARY TABLE patterns (pattern VARCHAR(500)) ENGINE=MEMORY");Which one of these will be faster to perform queries on? Will there be a noticable difference? My intuition is that the second will be slower because it is a transaction on a non-local database, but I don't know this for sure. When the table is in-memory, which computer's memory is it actually in? How much volume would have to be going on for a difference to actually be noticable? Any thoughts would be very much appreciated.
--Greg
GregStevens wrote:
Any thoughts would be very much appreciated
I think you should take measurements, and note down the version of the database that you tested.
GregStevens wrote:
Which one of these will be faster to perform queries on?
I'm hoping Sqlite, since it could be a fast implementation in native code without much overhead.
GregStevens wrote:
My intuition is that the second will be slower because it is a transaction on a non-local database, but I don't know this for sure.
How about putting it one the same machine? You also could consider Times Ten[^] from Oracle.
GregStevens wrote:
When the table is in-memory, which computer's memory is it actually in?
That depends on the specific implementation; most of the time it will be in memory that's managed by Windows, putting it into the virtual memory area. Others might have optimizations.
GregStevens wrote:
How much volume would have to be going on for a difference to actually be noticable?
You can review the amount of free memory using the Task Manager;
Your computers fysical memory - used memory = free memory
Once the system starts to page out memory, you'll notice delays. That can range from minimal delays (say, served from the 64Mb buffer of memory in your harddisk) to large delays (Windows reshuffling a lot on disk, paging in and out other applications that are also running, your computer nearly grinding to a halt)
Bastard Programmer from Hell :suss: