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

1
vote
1answer
1k 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 ...
1
vote
1answer
1k 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 ...
1
vote
1answer
1k 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 ...
1
vote
3answers
1k 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
308 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
567 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
568 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
3k 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
260 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 ...
2
votes
1answer
517 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. ...
3
votes
1answer
148 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 ...
1
vote
1answer
146 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
964 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
818 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
323 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
894 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
67 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
3k 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 ...
3
votes
2answers
4k 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
44 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
298 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
78 views

MyISAM table is not working as expected in MySQL

mysql>show processlist; +-------+------+------------------------------+---------------------+---------+-------+-------+------------------+ | Id | User | Host | db ...
1
vote
2answers
1k 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?
2
votes
1answer
226 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 ...
1
vote
1answer
106 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
309 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
313 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 ...
1
vote
2answers
2k 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
407 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
902 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
2answers
371 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
677 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
881 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. ...
6
votes
2answers
25k 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
1k 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. ...
2
votes
0answers
95 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 ...
7
votes
1answer
3k 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
121 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
418 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?
3
votes
1answer
3k 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
355 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?
1
vote
1answer
363 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`; ...
4
votes
2answers
1k views

Index before or after bulk load using load infile?

I have a database with over 1B rows and two columns that are indexed (in addition to the PK). Is it better to have the index pre-defined in the table before the load infile or better to index after ...
7
votes
1answer
184 views

Start/Stop MySQL

I'm seeking help to understand what happens when the following command line is executed: root@prodn$ service mysqld stop Yes, it shuts down the MySQL server so access to it is no longer available ...
2
votes
1answer
1k views

Will OPTIMIZE TABLE have any impact on my data?

I'm having issues with a database that is getting quite slow. The analyzer in phpMyAdmin recommends that I run OPTIMIZE TABLE on my tables. But before doing so, I would (of course) like to know if ...
2
votes
1answer
296 views

High IO when switched to InnoDB

We have query that worked well in MyISAM. When we switched to InnoDB, the writes I/O raised up to 50 times. The query is type of INSERT ... ON DUPLICATE KEY UPDATE I don't know if the problem is ...
0
votes
1answer
207 views

Large queries on MyISAM tables crashing MySQL

I have a PHP website, which is using the third-party software ResourceSpace, that when I installed it, it used my default storage engine to create the tables, which at the time was MyISAM. Now that ...
0
votes
0answers
22 views

Myisam Mysql server crashed

I have a server with linux fedora 11 Operating System. Recently , I discovered that every morning mysql has been crashed, so I have to restart mysql service. Can any body help me?
0
votes
1answer
468 views

MySQL: replicating to a different table engine type

According to the replication documentation from MySQL, it is possible to set up replication from InnoDB source tables to MyISAM destination tables. Unfortunately, the documentation has little to say ...
1
vote
1answer
443 views

MySQL: OPTIMIZE after TRUNCATE?

Using MySQL (either InnoDB or MyISAM tables--I have both), if I TRUNCATE a table and then LOAD DATA INFILE, should I issue an OPTIMIZE TABLE call after the LOAD? It's unclear to me if this is a ...