Questions tagged [storage-engine]
The storage-engine tag has no usage guidance.
91
questions
64
votes
4
answers
138k
views
Which is faster, InnoDB or MyISAM?
How can MyISAM be "faster" than InnoDB if
MyISAM needs to do disk reads for the data?
InnoDB uses the buffer pool for indexes and data, and MyISAM just for the index?
- 1,999
37
votes
7
answers
53k
views
What are reasons **NOT** to use the MEMORY storage engine in MySQL?
I recently discovered that MySQL has a "memory" engine that I wasn't aware of (most of my database work is for hobby projects so I learn what I need as I go). It seems like this option should give me ...
- 789
26
votes
3
answers
24k
views
MongoDB MMAPv1 vs WiredTiger storage engines
In mongoDB3 appeared a new storage engine: WiredTiger. Yet, MMAPv1 is still the default choice in Mongo.
One might not be better than the other, it's often a matter of use case and choosing the ...
- 365
25
votes
3
answers
18k
views
Is it common practice to mix InnoDB and MyISAM tables on same server?
I've got a single database of about 4.5GB running on a server with 8GB RAM. The vast majority of the tables are MyIsam (about 4.3GB), but I'm soon going to be converting some of them to InnoDB. (It's ...
- 23.4k
15
votes
3
answers
3k
views
Speeding up conversion of MyISAM to InnoDB
I've got a mysql 5.1 server with a database of approximately 450 tables, taking up 4GB . The vast majority of these tables (all but 2) are MyIsam. This has been fine for the most part (don't need ...
- 23.4k
15
votes
1
answer
2k
views
Do databases make a delete and an insert when having to update rows?
So today a professor told us that when the database has to make an update, internally (at low level) it makes a delete and then an insert with the updated fields. He then said that this is something ...
13
votes
1
answer
2k
views
Slot Array and Total Page Size
I continue to read in many forums and on many blogs that a page is comprised as shown below:
Page Size: 16 x 512B = 8192B
Page Header: = 96B
Maximum In_Row Row: = 8060B
This leaves (...
- 1,437
12
votes
2
answers
5k
views
Does AWS RDS support MEMORY storage engine?
Title says it all. Strangely can't find a result on this.
- 223
10
votes
3
answers
3k
views
I am using the MEMORY storage engine but MySQL still writes to my disk...Why?
I am using the MEMORY Engine for all tables associated with a particular MYSQL query because speed of access is paramount to my project.
For some reason, I have noticed that a large amount of disk ...
- 283
9
votes
1
answer
603
views
Understanding IAM Pages: extent intervals
I'm reading Itzik's book "Querying Microsoft SQL Server 2012" as well as reading/watching different educational material on the internet. My intention is to have a useful understanding of how the ...
- 193
8
votes
1
answer
1k
views
Is it Possible to Recover Data from a BLACKHOLE table? [closed]
I had created table with engine BLACKHOLE basically the BLACKHOLE storage engine acts as a “black hole” that accepts data but throws it away and does not store it. Retrievals always return an empty ...
- 1,187
8
votes
1
answer
11k
views
Do you still use MyISAM or prefer Aria storage engine?
If Aria storage engine (previously called Maria) is the "new" MyISAM, which supports transaction and automatic crash recovery:
Why still use MyISAM ?
Should changing storage engine from MyISAM to ...
- 1,999
7
votes
3
answers
733
views
What is the stride of a GAM interval
For interest, I was reading about the internal structures of a Microsoft SQL Server file. It's fairly obvious how IAM pages are connected. However, it is not clear to me where later GAM pages are to ...
- 24.7k
7
votes
3
answers
4k
views
Is MariaDB a suitable drop in replacement for MySQL on a standard LAMP stack?
I've been wondering lately what (if any) are the improvements available in MariaDB over 'conventional' MySQL?
I understand that where platform interoperability and/or backwards compatibility may be ...
- 173
7
votes
5
answers
28k
views
How to Add Federated engine after installing Mysql
I have Mysql 5.5.18
and upon show engines:
show engines;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine ...
- 473
6
votes
2
answers
5k
views
What storage engines work with Oracle?
http://en.wikipedia.org/wiki/Database_engine
mentions about database engines aka storage engines. What all storage engines can be used with the Oracle database?
- 3,351
6
votes
1
answer
5k
views
MongoDB 3.0 : Configure storage engine at collection or database level
I know we can configure the storage engine to be wiredTiger at instance level when we start mongod.
But can we also select the storage engine to be different for different collection or database?
- 163
6
votes
1
answer
869
views
Which MySQL storage engine is the most similar to ARCHIVE when that is not available?
I have a table that contains a log of events that is written to often and read very infrequently by comparison. I would love to use the ARCHIVE engine, because it is basically designed for exactly ...
- 217
6
votes
1
answer
4k
views
Which mysql storage engine to choose?
I'm confused about which mysql engine should I choose (lets talk about the most used : MyISAM and InnoDB).
The theories say:
Both work with BTree indexes, but MyISAM can work with FULLTEXT index ...
- 1,999
6
votes
2
answers
800
views
Optimal engine for small lookup tables in MySQL
I have following question: I am designing web application with several dozens of small lookup tables: These tables usualy contain three columns (ID, Name, Description) and a couple of rows (mostly ...
- 324
5
votes
2
answers
3k
views
How is it possible for mysql storage engine to be NULL?
I'm just reading a book and in one of the examples I get notices:
That the engine in the information_schema.table is null, how is that possible?
Can I create tables without any engine??, is there any ...
- 1,999
5
votes
2
answers
19k
views
MongoDB - Unable to change mongodb storage engine to WiredTiger
I have installed MongoDB in Ubuntu 14.04 but I am unable to change its storage engine to WiredTiger.
I have added the necessary changes to the /etc/mongod.conf file which is namely as the following-:
...
- 153
5
votes
1
answer
816
views
What page type is page 516855552?
SQL Server tracks page allocation in various internal bitmaps. Among these are the Global Allocation Map (GAM) and Page Free Space (PFS) pages. We know that GAM pages occur at set intervals of 511232 ...
- 24.7k
5
votes
1
answer
455
views
What are the pros and cons of using the Sphinx Storage Engine?
What are the pros/cons of using the Sphinx Storage Engine in MySQL (doc) over the normal api?
- 1,377
4
votes
2
answers
3k
views
Question about non-clustered index storage in SQL Server
I have a table people with two non-clustered indexes.
create table people(
id_person int,
first_name varchar(50),
last_name varchar(50),
city varchar(100),
state char(2),
zip_code int
)
CREATE ...
- 9,630
4
votes
2
answers
807
views
Should I move to InnoDB during a planned migration?
I'm shortly migrating a Debian Lenny webserver to another host and Debian Squeeze. The standard MySQL installation even on Squeeze is 5.1 and therefore would default to MyISAM - I'm inclined to stick ...
- 39.7k
4
votes
1
answer
159
views
Why does my heap not fill its pages completely while an identical clustered index can fill to m_freeCnt = 0?
-- Example 1: With Clustered Index
CREATE TABLE tbl5
(
i VARCHAR(900) NOT NULL
) ;
GO
CREATE CLUSTERED INDEX CIX_tbl5
ON tbl5 (i ASC) ;
GO
INSERT INTO tbl5
( i )
VALUES
...
- 1,437
4
votes
1
answer
781
views
MariaDB Spider Engine Out of Memory
I had almost standard MariaDB with InnoDB engine working fine but I wanted to test out the Spider Engine + Sharding/Partitioning. I had a query which was passing fine on the MariaDB InnoDB for 15-...
- 61
4
votes
1
answer
5k
views
Any disadvantages to enabling federated storage engine?
We've encountered a couple use cases where having federated tables prove useful, despite their limitations and caveats.
As best I can tell this must be enabled with a federated declaration in the ....
- 5,253
3
votes
3
answers
12k
views
Migrate Jira to MySql - Unknown system variable 'storage_engine'
I'm migrating JIRA in Windows from HSQL to MySQL and I got stuck with the message Unknown system variable 'storage_engine'. Though I created a backup, installed MySQL and copied J connector to libs ...
- 133
3
votes
1
answer
997
views
Kafka Internal Data structure vs LSM tree
I was going through Database storage engines and found out about LSM trees. I had also read about Kafka architecture and know that Kafka internally is a commit log. I want to know if Kafka internally ...
- 153
3
votes
1
answer
4k
views
How to export mysql databases with different ENGINE?
I'm setting up MySQL Cluster. As it uses ENGINE=NDBCLUSTER, how could I export my databases from MySQL Server specifying the ENGINE?
Is there a way to do this?
All my tables are MYISAM
- 313
3
votes
1
answer
2k
views
How to convert innodb engine tables to tokudb engine
Hi I want to migarate my server newly installed tokudb engine. i want to convert each table of database from innodb to tokudb. How can I do that using sed command ?
- 151
3
votes
1
answer
242
views
WiredTiger Page Size
I'm unable to find out if there is a pagesize in a WiredTiger MongoDB storage engine.
I mean some analog of the innodb_page_size for MySQL.
- 183
3
votes
2
answers
2k
views
MySQL lock on MEMORY storage engine, when does it apply?
I want to use MySQL MEMORY storage engine to handle my PHP session variables (actually I already implemented it but since all my tests are being done by a single user, me, I am unable to notice this).
...
- 31
3
votes
1
answer
1k
views
Which MySQL table to use for read-only, bulk-loaded tables?
So I have a workload on 2 key tables in my application with the following characteristics:
READ-ONLY DATA
The data is bulk-loaded in a fully consistent manner from a single process on a schedule, ...
- 133
3
votes
1
answer
2k
views
MySQL query 'going away' on executing INSERT ON DUPLICATE UPDATE statement with a 12524 character blob
I have a mysql insert on update query like so
insert into table (col1, col2, col3) values (1,2,'huge blob with 12524 chars') on duplicate key update col3 = 'huge blob with 12524 chars';
col1, col2 ...
3
votes
0
answers
141
views
Moving from dynamic disk allocation to direct space disk via storage pool in SQL server
Pardon me if I am not clear in asking the question since I have very less understanding from storage terms used or how it's used with Sql server.
Currently our storage team is testing to move disk ...
- 2,169
2
votes
1
answer
2k
views
Unknown storage engine 'XtraDB'
I installed Percona Server 5.6.12-rc60.4, when I want create table such as :
CREATE TABLE IF NOT EXISTS `mydb`.`table` (
`id` INT NOT NULL AUTO_INCREMENT ,
`title` VARCHAR( 64 ) NOT NULL ,
PRIMARY ...
- 663
2
votes
1
answer
412
views
Disable database tables for writes but report writes as successful
I manage a database where thousands of GBs of useless log data is created daily.
I can't fix the software that is performing all of this (unnecessary) logging. Is there a way to lock (or disable ...
- 188
2
votes
2
answers
1k
views
Any problem will all InnoDB and one MyISAM table?
I have a MySQL 5.5 DB with all InnoDB tables. For some reason, one of the developers decided to create a MyISAM table. It is a log table for user sessions. I don't see why we should mix in a MyISAM ...
- 21
2
votes
1
answer
464
views
How do I create fractal index using tokudb engine?
I install MariaDb and configured it to use TokuDb Engine.
I need to use Fractal-Index Built-in on TokuDb Engine.
I have create my index as follow:
ALTER TABLE `table__name` ADD INDEX ( `column` );
...
- 125
2
votes
2
answers
2k
views
Which first: upgrade mysql version or convert storage engine?
I inherited a production mysql server at 5.0. It uses MyISAM exclusively. It is not a high volume database but it does have some large datasets.
I am planning on:
upgrading to 5.5
converting ...
2
votes
1
answer
798
views
Is the Memory engine adequate for a read intensive/lookup table?
I have an InnoDB table with just two columns, a VARCHAR(20) and a DATETIME, and around 4M rows. This table serves as a blacklist for other tables, and is truncated and recreated from a csv file from ...
- 318
2
votes
1
answer
1k
views
SQL SERVER Database IO performance impact of splitting database tables into FileGroups and multiple Files per filegroup
SAN LUN ambiguity wrt performance
MSDN says to split read/write operations into a drive, sequential writes to another drive. (PRIMARY filegroup use typically data with read, update, write activity ...
- 343
2
votes
1
answer
467
views
Sync two MySQL servers data, while changing the Engine in the slave
I'm running a MySQL server 5.5.44, and I'd need to sync its data to a new server running MySQL 5.7.13 using master-slave. The idea is to bring both DBs synced until we migrate to the second, newer ...
- 165
2
votes
1
answer
83
views
Alternative ways to find tables with specific storage engine?
I have a VPS with three accounts and 5 db's. Recently I added MySQLTuner. All of my tables are MyISAM, but the results MySQLTuner are showing that I have 14 tables that use InnoDB.
[--] Data in ...
- 43
2
votes
1
answer
700
views
SQLyog and MariaDB engine
I am quite new in MariaDB and I use SQLyog Community Edition as the frontend GUI. Whenever I create a new table, on the Engine dropdown, there is no XtraDB option. Is there any additional steps I am ...
- 23
2
votes
1
answer
1k
views
TokuDB database size unknown in phpmyadmin
In mysql server, I installed the TokuDB storage engine. I converted database engine InnoDB to TokuDB, but my table sizes show unknown in phpmyadmin.
- 151
2
votes
1
answer
966
views
Issues converting MyISAM table to InnoDB (auto column issue)
I'm having issues trying to convert a table from MyISAM to InnoDB in MySQL 5.6.
The following is the table dump:
--
-- Table structure for table `companies`
--
DROP TABLE IF EXISTS `companies`;
/*!...
