Skip to main content
Commonmark migration
Source Link

I would not recommend the MEMORY storage engine

#REASON #1 : No Redundancy

REASON #1 : No Redundancy

Whether you have a server crash or a normal system shutdown, all the data in the MEMORY table are lost. All you would have is the table structure.

#REASON #2 : Mild Disk I/O

REASON #2 : Mild Disk I/O

No matter what Storage Engine you choose, the .frm of a table is always accessed to check for table existence and availability. This will incur some disk I/O for this check.

Please read past posts on the pros and cons of the MEMORY Storage Engine

#RECOMMENDATION

RECOMMENDATION

Given the two reasons for not using the MEMORY Storage Engine, I would recommend the MyISAM Storage Engine over using MEMORY or InnoDB. Why?

Looking back at Reason #1, you can have everything in RAM and have data redundancy on disk if you create the table as follows:

STEP 01) Create the table like this:

CREATE TABLE blacklist
(
    url VARCHAR(20),
    dt DATETIME,
    PRIMARY KEY (url)
) ENGINE=MyISAM ROW_FORMAT=Fixed;

STEP 02) Create a dedicated 16MB MyISAM cache for that table:

cd /var/lib/mysql
echo "SET GLOBAL blacklist_keycache.key_buffer_size = 1024 * 1024 * 16;" > init-file.sql
echo "CACHE INDEX blacklist IN blacklist_keycache; >> init-file.sql
echo "LOAD INDEX INTO CACHE blacklist; >> init-file.sql

STEP 03) Add this to /etc/my.cnf

[mysqld]
init-file=/var/lib/mysql/init-file/sql

STEP 04) Restart MySQL

service mysql restart

That's it.

Going forward, every reload of the table will populate the dedicated key cache. Please note the ROW_FORMAT=Fixed clause. What that does is speed up character search 20-25% (I wrote about this before).

Why not use InnoDB?

  • The data and index pages would have the data twice in RAM.
  • Accessing an InnoDB table introduces additional mild disk I/O via data dictionary checking (See pictorial representation of ibdata1)
  • Index pages to rotate out is the InnoDB Buffer Pool is too small. Contrariwise, an InnoDB Buffer Pool too big wastes RAM.

Using MyISAM, the data remains on disk but is exclusively accessed from the dedicate key cache.

I would not recommend the MEMORY storage engine

#REASON #1 : No Redundancy

Whether you have a server crash or a normal system shutdown, all the data in the MEMORY table are lost. All you would have is the table structure.

#REASON #2 : Mild Disk I/O

No matter what Storage Engine you choose, the .frm of a table is always accessed to check for table existence and availability. This will incur some disk I/O for this check.

Please read past posts on the pros and cons of the MEMORY Storage Engine

#RECOMMENDATION

Given the two reasons for not using the MEMORY Storage Engine, I would recommend the MyISAM Storage Engine over using MEMORY or InnoDB. Why?

Looking back at Reason #1, you can have everything in RAM and have data redundancy on disk if you create the table as follows:

STEP 01) Create the table like this:

CREATE TABLE blacklist
(
    url VARCHAR(20),
    dt DATETIME,
    PRIMARY KEY (url)
) ENGINE=MyISAM ROW_FORMAT=Fixed;

STEP 02) Create a dedicated 16MB MyISAM cache for that table:

cd /var/lib/mysql
echo "SET GLOBAL blacklist_keycache.key_buffer_size = 1024 * 1024 * 16;" > init-file.sql
echo "CACHE INDEX blacklist IN blacklist_keycache; >> init-file.sql
echo "LOAD INDEX INTO CACHE blacklist; >> init-file.sql

STEP 03) Add this to /etc/my.cnf

[mysqld]
init-file=/var/lib/mysql/init-file/sql

STEP 04) Restart MySQL

service mysql restart

That's it.

Going forward, every reload of the table will populate the dedicated key cache. Please note the ROW_FORMAT=Fixed clause. What that does is speed up character search 20-25% (I wrote about this before).

Why not use InnoDB?

  • The data and index pages would have the data twice in RAM.
  • Accessing an InnoDB table introduces additional mild disk I/O via data dictionary checking (See pictorial representation of ibdata1)
  • Index pages to rotate out is the InnoDB Buffer Pool is too small. Contrariwise, an InnoDB Buffer Pool too big wastes RAM.

Using MyISAM, the data remains on disk but is exclusively accessed from the dedicate key cache.

I would not recommend the MEMORY storage engine

REASON #1 : No Redundancy

Whether you have a server crash or a normal system shutdown, all the data in the MEMORY table are lost. All you would have is the table structure.

REASON #2 : Mild Disk I/O

No matter what Storage Engine you choose, the .frm of a table is always accessed to check for table existence and availability. This will incur some disk I/O for this check.

Please read past posts on the pros and cons of the MEMORY Storage Engine

RECOMMENDATION

Given the two reasons for not using the MEMORY Storage Engine, I would recommend the MyISAM Storage Engine over using MEMORY or InnoDB. Why?

Looking back at Reason #1, you can have everything in RAM and have data redundancy on disk if you create the table as follows:

STEP 01) Create the table like this:

CREATE TABLE blacklist
(
    url VARCHAR(20),
    dt DATETIME,
    PRIMARY KEY (url)
) ENGINE=MyISAM ROW_FORMAT=Fixed;

STEP 02) Create a dedicated 16MB MyISAM cache for that table:

cd /var/lib/mysql
echo "SET GLOBAL blacklist_keycache.key_buffer_size = 1024 * 1024 * 16;" > init-file.sql
echo "CACHE INDEX blacklist IN blacklist_keycache; >> init-file.sql
echo "LOAD INDEX INTO CACHE blacklist; >> init-file.sql

STEP 03) Add this to /etc/my.cnf

[mysqld]
init-file=/var/lib/mysql/init-file/sql

STEP 04) Restart MySQL

service mysql restart

That's it.

Going forward, every reload of the table will populate the dedicated key cache. Please note the ROW_FORMAT=Fixed clause. What that does is speed up character search 20-25% (I wrote about this before).

Why not use InnoDB?

  • The data and index pages would have the data twice in RAM.
  • Accessing an InnoDB table introduces additional mild disk I/O via data dictionary checking (See pictorial representation of ibdata1)
  • Index pages to rotate out is the InnoDB Buffer Pool is too small. Contrariwise, an InnoDB Buffer Pool too big wastes RAM.

Using MyISAM, the data remains on disk but is exclusively accessed from the dedicate key cache.

replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
Source Link

Given the two reasons for not using the MEMORY Storage Engine, I would recommend the MyISAM Storage Engine over using MEMORY or InnoDB. Why?

Going forward, every reload of the table will populate the dedicated key cache. Please note the ROW_FORMAT=Fixed clause. What that does is speed up character search 20-25% (I wrote about this before).

Why not use InnoDB?

Using MyISAM, the data remains on disk but is exclusively accessed from the dedicate key cache.

Given the two reasons for not using the MEMORY Storage Engine, I would recommend the MyISAM Storage Engine over using MEMORY or InnoDB. Why?

Going forward, every reload of the table will populate the dedicated key cache. Please note the ROW_FORMAT=Fixed clause. What that does is speed up character search 20-25% (I wrote about this before).

Why not use InnoDB?

Using MyISAM, the data remains on disk but is exclusively accessed from the dedicate key cache.

deleted 9 characters in body
Source Link
RolandoMySQLDBA
  • 184.3k
  • 33
  • 323
  • 531

I would not recommend the MEMORY storage engine

#REASON #1 : No Redundancy

Whether you have a server crash or a normal system shutdown, all the data in the MEMORY table are lost. All you would have is the table structure.

#REASON #2 : Mild Disk I/O

No matter what Storage Engine you choose, the .frm of a table is always accessed to check for table existence and availability. This will incur some disk I/O for this check.

Please read past posts on the pros and cons of the MEMORY Storage Engine

#RECOMMENDATION

Given the two reasons for not using the MEMORY Storage Engine, I would recommend the MyISAM Storage Engine over using MEMORY or InnoDB. Why?

Looking back at Reason #1, you can have everything in RAM and have data redundancy on disk if you create the table as follows:

STEP 01) Create the table like this:

CREATE TABLE blacklist
(
    url VARCHAR(20),
    dt DATETIME,
    PRIMARY KEY (url)
) ENGINE=MyISAM ROW_FORMAT=Fixed;

STEP 02) Create a dedicated 16MB MyISAM cache for that table:

cd /var/lib/mysql
echo "SET GLOBAL blacklist_keycache.key_buffer_size = 1024 * 1024 * 16;" > init-file.sql
echo "CACHE INDEX blacklist IN blacklist_keycache; >> init-file.sql
echo "LOAD INDEX INTO CACHE blacklist_keycache;blacklist; >> init-file.sql

STEP 03) Add this to /etc/my.cnf

[mysqld]
init-file=/var/lib/mysql/init-file/sql

STEP 04) Restart MySQL

service mysql restart

That's it.

Going forward, every reload of the table will populate the dedicated key cache. Please note the ROW_FORMAT=Fixed clause. What that does is speed up character search 20-25% (I wrote about this before).

Why not use InnoDB?

  • The data and index pages would have the data twice in RAM.
  • Accessing an InnoDB table introduces additional mild disk I/O via data dictionary checking (See pictorial representation of ibdata1)
  • Index pages to rotate out is the InnoDB Buffer Pool is too small. Contrariwise, an InnoDB Buffer Pool too big wastes RAM.

Using MyISAM, the data remains on disk but is exclusively accessed from the dedicate key cache.

I would not recommend the MEMORY storage engine

#REASON #1 : No Redundancy

Whether you have a server crash or a normal system shutdown, all the data in the MEMORY table are lost. All you would have is the table structure.

#REASON #2 : Mild Disk I/O

No matter what Storage Engine you choose, the .frm of a table is always accessed to check for table existence and availability. This will incur some disk I/O for this check.

Please read past posts on the pros and cons of the MEMORY Storage Engine

#RECOMMENDATION

Given the two reasons for not using the MEMORY Storage Engine, I would recommend the MyISAM Storage Engine over using MEMORY or InnoDB. Why?

Looking back at Reason #1, you can have everything in RAM and have data redundancy on disk if you create the table as follows:

STEP 01) Create the table like this:

CREATE TABLE blacklist
(
    url VARCHAR(20),
    dt DATETIME,
    PRIMARY KEY (url)
) ENGINE=MyISAM ROW_FORMAT=Fixed;

STEP 02) Create a dedicated 16MB MyISAM cache for that table:

cd /var/lib/mysql
echo "SET GLOBAL blacklist_keycache.key_buffer_size = 1024 * 1024 * 16;" > init-file.sql
echo "CACHE INDEX blacklist IN blacklist_keycache; >> init-file.sql
echo "LOAD INDEX INTO CACHE blacklist_keycache; >> init-file.sql

STEP 03) Add this to /etc/my.cnf

[mysqld]
init-file=/var/lib/mysql/init-file/sql

STEP 04) Restart MySQL

service mysql restart

That's it.

Going forward, every reload of the table will populate the dedicated key cache. Please note the ROW_FORMAT=Fixed clause. What that does is speed up character search 20-25% (I wrote about this before).

Why not use InnoDB?

  • The data and index pages would have the data twice in RAM.
  • Accessing an InnoDB table introduces additional mild disk I/O via data dictionary checking (See pictorial representation of ibdata1)
  • Index pages to rotate out is the InnoDB Buffer Pool is too small. Contrariwise, an InnoDB Buffer Pool too big wastes RAM.

Using MyISAM, the data remains on disk but is exclusively accessed from the dedicate key cache.

I would not recommend the MEMORY storage engine

#REASON #1 : No Redundancy

Whether you have a server crash or a normal system shutdown, all the data in the MEMORY table are lost. All you would have is the table structure.

#REASON #2 : Mild Disk I/O

No matter what Storage Engine you choose, the .frm of a table is always accessed to check for table existence and availability. This will incur some disk I/O for this check.

Please read past posts on the pros and cons of the MEMORY Storage Engine

#RECOMMENDATION

Given the two reasons for not using the MEMORY Storage Engine, I would recommend the MyISAM Storage Engine over using MEMORY or InnoDB. Why?

Looking back at Reason #1, you can have everything in RAM and have data redundancy on disk if you create the table as follows:

STEP 01) Create the table like this:

CREATE TABLE blacklist
(
    url VARCHAR(20),
    dt DATETIME,
    PRIMARY KEY (url)
) ENGINE=MyISAM ROW_FORMAT=Fixed;

STEP 02) Create a dedicated 16MB MyISAM cache for that table:

cd /var/lib/mysql
echo "SET GLOBAL blacklist_keycache.key_buffer_size = 1024 * 1024 * 16;" > init-file.sql
echo "CACHE INDEX blacklist IN blacklist_keycache; >> init-file.sql
echo "LOAD INDEX INTO CACHE blacklist; >> init-file.sql

STEP 03) Add this to /etc/my.cnf

[mysqld]
init-file=/var/lib/mysql/init-file/sql

STEP 04) Restart MySQL

service mysql restart

That's it.

Going forward, every reload of the table will populate the dedicated key cache. Please note the ROW_FORMAT=Fixed clause. What that does is speed up character search 20-25% (I wrote about this before).

Why not use InnoDB?

  • The data and index pages would have the data twice in RAM.
  • Accessing an InnoDB table introduces additional mild disk I/O via data dictionary checking (See pictorial representation of ibdata1)
  • Index pages to rotate out is the InnoDB Buffer Pool is too small. Contrariwise, an InnoDB Buffer Pool too big wastes RAM.

Using MyISAM, the data remains on disk but is exclusively accessed from the dedicate key cache.

added 658 characters in body
Source Link
RolandoMySQLDBA
  • 184.3k
  • 33
  • 323
  • 531
Loading
deleted 2 characters in body
Source Link
RolandoMySQLDBA
  • 184.3k
  • 33
  • 323
  • 531
Loading
Source Link
RolandoMySQLDBA
  • 184.3k
  • 33
  • 323
  • 531
Loading