Tag Info

The only way MyISAM can be faster that InnoDB would be under this unique circumstance MyISAM When read, a MyISAM table's indexes can be read once from the .MYI file and loaded in the MyISAM Key Cache (as sized by key_buffer_size). How can you make a MyISAM table's .MYD faster to read? With this: ALTER TABLE mytable ROW_FORMAT=Fixed; I wrote about this ...

Looking at the feature availability list at http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html two possible problems jump out: No transaction of FK support, meaning you will have to manage transactional integrity and referential integrity in your own code were needed (which could end up being a lot less efficient than letting the DB do this ...

There's nothing wrong with using multiple storage engines on the same physical machine, as long as you understand the pros and cons of each. There are performance considerations, feature limitations and use cases for all the plugin storage types. For instance, if you have a small table that's 90% writes, you might choose MyISAM. If the data can be ...

Let me start by saying, I hate ALTER. It's evil, IMHO. Say, this is your current table schema - CREATE TABLE my_table_of_love ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, my_value VARCHAR(40), date_created DATE, PRIMARY KEY(id) ) ENGINE=MyISAM CHARSET=utf8; Here's the path I recommend - Create a new table object that will replace the old one: CREATE ...

1) Loss protection is a function of paranoia. Always make a backup. If you're really paranoid, make a backup then restore from the backup. 2) This page of the MySQL manual has instructions to convert table types. The fastest way to alter a table to InnoDB is to do the inserts directly to an InnoDB table. That is, use ALTER TABLE ... ENGINE=INNODB, or ...

One valid reason to move to another MySQL flavour is performance. The latest MariaDB versions 5.3 and 5.5 (which are drop in replacemants of MySQL 5.1 and 5.5 respectively) included major improvements in query optimization. You can read more on their site: What is MariaDB 5.3 Examples on improvements/features are "Index Condition Pushdown", "Cost-based ...

'page split' and 'heap' should not be used in the same sentence. Do you mean why wasn't the row of size 393+change fit in the free space of the first page of the heap? That is because the PFS free space info has not high enough resolution, see Managing Extent Allocations and Free Space: The PFS has one byte for each page, recording whether the page is ...

In a simple world, MyISAM is faster for reads, InnoDB is faster for writes. Once you start introducing mixed read/writes, InnoDB will be faster for reads as well, thanks to its Row locking mechanism. I wrote a comparison of MySQL storage engines a few years ago, that still holds true to this day, outlining the unique differences between MyISAM and InnoDB. ...

Pros If there is no Sphinx API for the language you are using SphinxSE allows you to do searches through MySQL. If you are doing background processing and storing the search results in your database SphinxSE allows you to move the data from the searchd service directly into MySQL instead of going through an application layer. Cons You have to compile ...

To be honest, with a low traffic environment running on 5.1, you shouldn't have any problems with MyISAM. The biggest downfall of MyISAM is table-locking WRITES, IMO. But with limited data in the tables, this won't be noticeable. MyISAM is probably easier to manage from a DBA standpoint with recovery and repair. And in 5.1, I think InnoDB's benchmarks ...

InnoDB supports compression as of MySQL 5.1 (with InnoDB Plugin), or natively in MySQL 5.5. The level of compression really depends on your table content, but I see 75% reduction in size as common. Reads and writes to compressed tables involve more CPU. InnoDB will support your foreign keys as well as any indexes. I would say it is a fairly good ...

For completeness, whilst Jack's answer is technically true, it is possible to use Data Cartridges to expand on vanilla Oracle offerings. In fact, a company called CopperEye released a new indexing method (patented, now not available) utilizing this functionality. See this old press release. The patent makes for fascinating reading.

You may want to watch how many MEMORY tables you are using. Even with a MEMORY table, there must be an open file handle to the .frm of the MEMORY table. Check your open_files_limit. You may need to increase that as well. You also have a great disparity between read_buffer_size and read_rnd_buffer_size. You have read_buffer_size at 64M and ...

There are plenty of cases not to use the memory storage engine - and when InnoDB will be faster. You just need to think about concurrency and not trivial single threaded tests. If you have a large enough buffer pool, then InnoDB will become entirely memory resident for read operations as well. Databases have caches. They warm themselves up! Also - do ...

Both works with b-tree index, but MyISAM can work with FULLTEXT index also. MySQL 5.6 is supposed to support FULLTEXT indexing as well. Look for it when it goes GA. InnoDB is more strict in data integrity, while MyISAM is loose InnoDB has transactions while MyISAM does not. InnoDB has foreign keys and relationship contraints while MyISAM ...

In general, MariaDB is a drop-in replacement for MySQL. It says so on their home page. :-) Your applications won't notice a difference. For specialized applications and edge cases, there may be difference. But these are going to be tuned to use special features of the newer versions of MySQL. You may also want to look at Percona Server as it tracks the ...

When it comes to InnoDB, you should never use big primary keys. Why? InnoDB has an internal Clustered Index called gen_clust_index. When you are doing INSERTs into an InnoDB table with just a Primary Key, you are building up the gen_clust_index. I would expect this table to bloat very quickly. Your goal should be to record the URL and index it in such a way ...

It is very interesting this question would come up because a similar question was asked back in January 2011 ( When is the right time to use MariaDB instead of MySQL, and Why? ). Giving thought to that 16-month-old question and how I answered it in April 2011, here are my answers to your immediate questions: Why still using MyISAM ? MyISAM still has ...

It's X times easier to optimize whole server (memory configuration, caches, indexes) when you have only one engine used. Mixing myisam with innodb on large databases will always stuck at some point forced by some comprosise for both engine to work good (but not excelent :) I recommend You to interest in some dedicated full text search engines like sphinx, ...

I can't tell you if this is a common practice. I can say about my own experience. I always use the best tool for the job, so I mix engines all the time. Most of my projects use MyISAM as the default engine. When I need special features just available on InnoDB, I go for it. When a table is mostly read-only, I choose Archive engine before I could blink. ...

I have not had much experience switching the myisam_data_pointer_size option from 4 to 6. Simply put, I think you got it right based on the Documentation. Here is why: According to the MySQL Documentation 5.5 on myisam_data_pointer_size: The default pointer size in bytes, to be used by CREATE TABLE for MyISAM tables when no MAX_ROWS option is ...

I think it's time to do some math. What's your system ram vs. the size of table your trying to stuff into it? What is the size of the struct (data storage requirements of each row) you're storing in your memory table? Have you multiplied that * qty rows to see if that exceeds your values? http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html ...

This is just chime-in on @DTest's answer. I agree with his answer concerning pros and cons of Migration From MyISAM to InnoDB I would like add that setting up caching for MyISAM is more simplistic that InnoDB because you have to worry about InnoDB log files in conjunction with the buffer pool usage and sizing objects that can be stored in it. MyISAM is ...

If Default installation/binary installation In My opinion the BLACKHOLE , ARCHIVE are installed and enabled by default when you install the Mysql server. but the FEDERATED is not enabled by default.For that do like this You can enable it manually you have edit your my.cnf file.edit my.cnf and in the [mysqld] section, add the line: federated Now ...

Is there a maintenance or tuning headache here? ABSOLUTELY !!! Any benefit to it? I'll pretend I didn't read that. The MyISAM storage engine always performs a full table lock with each DML (INSERT, UPDATE, DELETE). SELECTs block the daylights out of DML Statements. Any transactions that mix MyISAM and InnoDB tables will cause even InnoDB tables to ...

Of the choices, I would update, then convert. InnoDB received some massive performance boosts in 5.5, so by upgrading first you get some features that 5.5 introduces, and then you can convert to InnoDB to get full innodb benefit. 5.0 InnoDB performance was not great, so you might see some strange issues by converting to InnoDB prior to updating to 5.5 As ...

MariaDB developers claim that it's a drop-in replacement, and it's true until version 5.5. MariaDB 5.1-5.2-5.3 can replace MySQL 5.1. MariaDB 5.5 can replace MySQL 5.5 The small "incompatibility" issues usually don't apply, however, they are documented here: https://kb.askmonty.org/en/mariadb-vs-mysql-compatibility/ There are also a lot of bug fixes and ...

I actually wrote a post back in July 2011 ( Modify DEFINER on Many Views ) about how to access views for modification. It is a fact that when the storage engine is NULL, it is always a View. The actual definition of the View is MySQL 5.1/5.5 mysql> desc information_schema.views; +----------------------+--------------+------+-----+---------+-------+ | ...

The biggest downside for ARCHIVE in your environment would be that it won't work. The ARCHIVE storage engine only supports INSERT and SELECT -- not UPDATE or DELETE -- and only supports one index, on the primary key. I would to go with InnoDB, which can, incidentally, also store tables compressed on disk. I can't think of a good reason to go any other ...

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 ...