MyISAM is the non-transactional storage engine for MySQL. It provides high-speed storage and retrieval, as well as fulltext searching capabilities. In addition, it is the default storage engine type for versions of MySQL prior to 5.5.

learn more… | top users | synonyms

0
votes
2answers
39 views

MySQL using swap during conversion to InnoDB

I'm trying to convert some MyISAM tables in my database to InnoDB (MySQL 5.1). The server has quite a bit of memory (96G), and the tables that are giving me problems can be on the order of 50 millions ...
1
vote
2answers
151 views

Setting default CHARSET and DB Engine

I recently updated my server's database system from MySQL to MariaDB. After update, everything works well. But, when I went to phpMyAdmin, the default charset and types are changed: At the end of ...
1
vote
1answer
95 views

Mysql Performance tuning for myisam tables.

Folks I have a 4-core MySQL server with 4 GB of RAM, holding 19 MyISAM tables (total: ~164GB of data) and 32 innodb tables ( 5mb ) and we use it to process CDR files( basically call detail records ...
0
votes
1answer
206 views

how to set bulk_insert_buffer_size in mysql?

I want to set bulk_insert_buffer_size variable for insert. I searched from other pages too, all they are saying about my.cnf. But there is no my.cnf in my system. I checked my.ini and there is no such ...
0
votes
0answers
36 views

How to get rid of the table lock in MySQL

I have a database for my website the website have a betting game(using insert and update queries) and the other pages of the website are using (select, insert and update queries). When a user make a ...
1
vote
1answer
252 views

Timeout on ALTER TABLE table ENGINE=InnoDB - is table still converted?

I'm converting tables in an old MYISAM database to InnoDB to increase robustness in case of power loss etc. I'm using Mysql 5.5.x Before doing the production system, I am doing a trial run on a copy ...
0
votes
3answers
182 views

Converting live MyISAM table to InnoDB

I have a table that tracks web traffic. The table is constantly written and read from. Since table is pretty big in size I have to make sure the application will not "freeze" in the process of ...
0
votes
1answer
69 views

Backing up MySQL w/MyISAM on Amazon RDS

So, for various good reasons, we're maintaining a mySQL database using a MyISAM engine on Amazon RDS. I've just taken over this application, and I'm concerned we're not getting good backups, since ...
0
votes
1answer
238 views

MySQL Read Speed and Partitioning on Separate Drives

Introduction Simple project but for an heavy read load -90% reads- over a table(~20Milion Rows ever-growing where I have to boost performance and ensure response low time for read queries) that was ...
2
votes
1answer
243 views

Is it safe to kill ANALYZE TABLE query on a MyISAM table?

I have a huge MyISAM table that is blocking my application. I frequently find ANALYZE TABLE queries running on that table. I guess that's because a join is being performed on the table or at least an ...
4
votes
1answer
2k views

Consistent logical backup of databases that use MyISAM and InnoDB engines

I have a question regarding the logical backup of MySQL databases that use both MyISAM and InnoDB. The mysqldump utility supports these two options: --single-transaction - Creates a consistent ...
0
votes
0answers
154 views

Improve Speed of MySQL Database Import

I'm importing around 44 GB (14 million rows) of information into an InnoDB MySQL database, and I'd like to do so in a reasonable timeframe. Since I'm doing this on a personal computer, I only have 8 ...
4
votes
2answers
4k views

How to increase fast backup and restore of 500GB database using mysqldump?

There is a database A size of 500GB. Tables in database A contains both MyISAM and INNODB tables. MyISAM tables are master tables and Innodb tables are main transaction tables. Backup and restore ...
2
votes
1answer
148 views

Issue moving database between servers (cannot find .frm file even though it's there)

I used MAMP on my local machine to make a little site with a mysql database. I moved all the site files over the to server. Great. The name of my database is roster and it has one table called users. ...
0
votes
0answers
54 views

Performance setting for MyISAM tables - my.cnf file

I have a 4-core MySQL server with 4 GB of RAM, holding 19 MyISAM tables (total: ~164GB of data) and 32 innodb tables ( 5mb ) . QUESTIONS Since the databases size is smaller than the available ...
3
votes
1answer
102 views

What if I want to keep using MyISAM

In my current use of MyISAM the databases are extremely small and kept for a few weeks after which time they are deleted and completely rebuilt with a clean slate. The script uses MYISAM tables and ...
65
votes
8answers
39k views

What are the main differences between InnoDB and MyISAM?

What are the main differences between InnoDB and MyISAM?
1
vote
1answer
42 views

MySQL command querying all MyISAM database

I would appreciate if anyone could help me of what's the proper command in MySQL to query all database for having "MyISAM" engine. I need to convert all DB & tables from MyISAM to InnoDB.
2
votes
2answers
533 views

Repair MySQL table without downtime

We have a corrupt MyISAM table in our production MySQL database. Is there a method for repairing this table without locking it? We were considering using a method similar to Facebook's Online Schema ...
1
vote
1answer
342 views

Spatial Index on an InnoDB table

I have a database which is in InnoDB, because I wanted consistency (cascading delete). Now, I want to use spatial indexes for selecting rows ordered by geometric distance. However, spatial indexes ...
1
vote
1answer
207 views

MyISAM vs InnoDB for Forums [closed]

I want to design a forum script with MySql. Which database engine would have better performance? MyISAM or InnoDB ?
3
votes
2answers
654 views

How to backup a MySQL database without slowing the application?

I'm an application developer and wondering about our database backup strategy. Right now our sysadmins backup our database every 8 hours. I don't really know how the do it, but while the backup is ...
2
votes
1answer
55 views

How to go about modeling the main object of relationship?

I'm using MySQL and MyISAM tables. I have a bunch of objects that I want to group together 1 object belongs to 1 and only 1 group 1 group can have many objects 1 group has 1 required main object ...
1
vote
1answer
303 views

Merge multiple tables in a database

I have a MySQL database with table names like 1,2,3... upto 1000 Each tables have 2000 rows and has the same structure. This is how all my 2000 table's structure look like id,url,title,content id ...
2
votes
2answers
2k views

MySQL 5.5 High CPU Usage

I have been working with a client with high traffic website(500k visitors and 600-800 active users at any given time). This uses wordpress and the DB is using MyISAM engine. The problem we had was ...
0
votes
1answer
42 views

Which text-index I should create for xooops engine to achieve better search results?

In one of projects we use xoops engine to manage content. In mysql slow query log most of queries are following : SELECT p.uid,f.forum_id, p.topic_id, p.poster_name, p.post_time, f.forum_name, ...
3
votes
1answer
122 views

What's the optimal server configuration for fastest inserting in a MySql database in this scenario?

I have a MyISAM table partitioned by range with 90 partitions representing the last 90 days. I expect this table to receive somewhere between 500 million to 1 billion records before i start rotating ...
0
votes
1answer
63 views

MyISAM table is not working as expected in MySQL

mysql>show processlist; +-------+------+------------------------------+---------------------+---------+-------+-------+------------------+ | Id | User | Host | db ...
1
vote
2answers
420 views

Remove InnoDB files (ibdata1 and iblog files) after migration to MyISAM

I changed the only two InnoDB tables on a replication slave to MyISAM, so the slave now only has MyISAM tables. Can I now remove the ibdata1 file completely?
0
votes
0answers
52 views

Multiple mysql tables, JOIN performance

I have multiple tables in a mysql database. Each table has about 10 million rows. I have multiple queries, and often need to join multiple tables in every query, which seems to be causing some ...
2
votes
1answer
120 views

Is the key_buffer_size applicable to myisam tmp tables?

I have a database about 750GB in size. It's all innodb. Larger analytical queries often need to group by several columns or use distinct so it's common that MySQL will have to create tmp tables. The ...
0
votes
2answers
79 views

MySQL database datafiles in windows SystemData directory

When i create a database and some tables in MySQL Server, it makes some data files in Windows SystemData directory in C:\ProgramData\MySQL\MySQL Server 5.5\data\mydb\. Each table has three files with ...
1
vote
1answer
63 views

InnoDB query duration inconsistent

I am running a series of UPDATE commands on a nearly empty InnoDB table, and around 1 out of every 20-30 queries will inexplicably take 10 times as long as the others. For example, the first 20 ...
0
votes
1answer
219 views

Information about Disk Storage MySQL

I am trying to create an application for monitoring MySQL,in my application there's a part of monitor DISK STORAGE, so after more research I have not found how to calculate : used and free size of ...
0
votes
1answer
139 views

Latin1 to UTF8 on large MySQL database, minimal downtime

Let me lay out the basic scenario for the problem I'm up against. I'm running MySQL 5.1 and have a single database with 2 large MyISAM tables (9M and 20M rows respectively). Each of these tables has ...
0
votes
2answers
1k views

High Mysql Load , over 700% CPU

I had high mysql load on server linux 64 bit , 24 G.B ram , Intel(R) Core(TM) i7 CPU 950 @ 3.07GHz , Alot of quiers in sending data mode Here is mysql status ...
1
vote
1answer
170 views

MyISAM key buffer

Good evening, I want to know how can I use informations about MyISAM to calculate : size of buffer, size of buffer used, Write ratio, Read ratio, and MyISAM key cache hit rate and thank you.
1
vote
1answer
277 views

Optimizing disk space for MyISAM tables: what is the benefit of ALTER and ANALYZE if you already do OPTIMIZE?

Per this post, in order to optimize the storage of a MyISAM table, you need to: OPTIMIZE TABLE ; ALTER TABLE ENGINE=MyISAM ; ANALYZE TABLE ; It's obvious what OPTIMIZE does for disk space but what ...
0
votes
3answers
229 views

Convert the table PROCESSLIST from InnoDB to MYISAM

I use MySQL. My database's name is "db", and I have a table called "Test". I want recover the last update, so I used this query: SELECT update_time FROM information_schema.tables WHERE ...
3
votes
1answer
305 views

Getting Random Sample from large database MYSQL (No Auto Inc Field)

You were so helpful with my last question, I thought id throw another one at you thats stumping me. Basically I got a table, requirements from company was it was all supposed to be 1 table so I got ...
1
vote
1answer
665 views

mysql optimize table crash

When I try OPTIMIZE TABLE `table` (MyISAM) on a table which is about 300MB, then it is crashed and must be repaired. What could cause this problem? The same problem occurs on other tables over 300MB. ...
4
votes
2answers
13k views

way to prevent queries from waiting for table level lock

We've encountered a problem after moving the database of our customer to an extra server. This should have had positive effects on the site's performance, but there is a problem with table locking in ...
4
votes
4answers
597 views

TRUNCATE TABLE statement sometimes hangs

Why does the TRUNCATE TABLE statement hang sometimes? What are the reasons for this type of issue? I am migrating from MySQL to MariaDB. This problem doesn't happen with MySQL, only with MariaDB. ...
1
vote
0answers
75 views

MySQL Partitioning Performance

I have a MyISAM table with billions of rows that is still causing me problems because the indexes, even after I shortened them as much as possible, do not fit in my 64GB of RAM. I am unable to index ...
6
votes
1answer
1k views

MySQL Hanging Completely when `ALTER TABLE… ENABLE KEYS`

I know very little about database administration but I have to deal with some very large tables on my site. This server has 64GB of RAM and Intel Core i7-3820 (4 x 3600 MHz). Most of everything it ...
0
votes
1answer
86 views

Two identical MySQL tables, one not using indexes

I have one MySQL Server (5.0.37) on a Linux Server that have many databases. Some of them (12) have each an identical table to log sensors data. Schema of one table CREATE TABLE `measuresHistory` ( ...
2
votes
1answer
167 views

Repair with 1 thread

Why would MySQL be using Repair with 1 thread to rebuild the indexes on a table (1 primary key, 2 billion rows) when I have myisam_repair_threads set to 4?
1
vote
1answer
1k views

Configuring MySQL (my.cnf) for MyISAM and InnoDB

I want to convert some (not all) the MyISAM tables to InnoDB. Here is my current my.cnf: pastebin.com/FW8YXtLQ What would be the best configuration? (need to add InnoDB settings, byecause there are ...
3
votes
1answer
2k views

MySQL high CPU usage (MyISAM table indexes)

I have a problem with an inherited MySQL database. From time to time mysqld uses up to 2300% CPU.. The only solution is to service mysql stop and run an myisamchk -r on a table. After the indexes have ...
1
vote
1answer
227 views

Why do MySQL MyISAM table indexes go out of date?

I have few MyISAM tables, where only the auto_increment primary key is up to date with the number of columns, but not other indexes. Any idea, why this happens?