MyISAM is a non-transactional storage engine for MySQL. It provides high-speed storage and retrieval, as well as fulltext searching capabilities. Also, is the default storage engine type for version prior to 5.5.
2
votes
1answer
112 views
+100
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
51 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` (
...
0
votes
2answers
263 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 ...
1
vote
1answer
26 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?
0
votes
1answer
93 views
How to check growth of database in mysql?
I want to know is there any method to check the growth of database on file
EXAMPLES
Database A contains all tables in INNODB storage engine
Database B contains all tables in MyISAM storage engine
...
1
vote
1answer
56 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 ...
0
votes
1answer
14 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 ...
2
votes
1answer
51 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
102 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?
0
votes
2answers
147 views
MySQL Table not repairing
Table info:
Database name: user_motiva
Table name: wp_options.frm wp_options.MYD wp_options.MYI wp_options.TMD
when I do a mysqlcheck -r --all-databases it gets hung on that table even if you ...
0
votes
1answer
56 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 ...
0
votes
1answer
65 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.
...
0
votes
1answer
181 views
Unable to change engine to MyISAM in MySQL
I have installed MySQL 5.6.10 on Mac OS X 10.6.5. My issue is that MySQL is using InnoDB as its default engine.
I have checked following engines are supported in MySQL:
show engines\G
...
1
vote
1answer
48 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`;
...
0
votes
1answer
24 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, ...
2
votes
1answer
75 views
MySQL Tables Require Daily Repairs - Server, Table or HD?
I've been experiencing a weird issue with one of MySQL DBs. Every day, sometimes 2-3 times per day, I'll need to repair the tables. The MySQL DB has 25 tables with 5.6m rows in total.
The bigger ones ...
4
votes
3answers
124 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 ...
5
votes
1answer
96 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 ...
1
vote
1answer
35 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 ...
1
vote
1answer
29 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
2answers
71 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
...
0
votes
0answers
39 views
MySQL “hangs” after every restart.. corrupted tables every now and then.. (5.1.68-cll)
I was seeking an advice in forums about a problem that we face last days. We have a vBulletin forum, we never had any problems in general, but the last couple of days MySQL server hangs and when we ...
0
votes
1answer
76 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
16 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
45 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
42 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 ...
1
vote
1answer
33 views
extra steps after changing storage engine and adding index
Someone told me to look into his website for quick optimization; I'm a programmer and i don't have much experience optimizing databases.
I have a php/MySQL site uses the MyISAM storage engine. It ...
1
vote
1answer
149 views
MySQL my.cnf Optimization
We are an email marketing company and we recently switched our setup to MySQL. We need to configure MySQL (my.cnf) for extreme performance.
We have tried to configure my.cnf but heavy queries got ...
0
votes
1answer
28 views
Reinsert data in new table with new IDs
I have the following two tables:
buynsell
CREATE TABLE `buynsell` (
`id` VARCHAR(20) NULL DEFAULT NULL,
`msg` VARCHAR(255) NULL DEFAULT NULL,
`date` VARCHAR(25) NULL DEFAULT NULL,
...
2
votes
2answers
57 views
Are there implicit indexes in InnoDB like MyISAM?
If you have (id, a, b, c, d) table with primary key (id) and another key (a, b, c), in MyISAM that means that you also have the following implicit keys:
(a)
(a, b)
(a, b, c, id)
Is this valid for ...
3
votes
1answer
100 views
Can I use mysql to serve 100's of millions of small image files?
I need to serve 300,000,000 small image files totaling 1.5TB. Trying to unpack these files to individual files on the file system is next to impossible (1 MByte/sec throughput rates to create the ...
6
votes
2answers
1k views
Why does MyISAM support FULLTEXT Searching and InnoDB does not?
I know the basic differences between the MyISAM and InnoDB engines. In MySQL 5.6 beta, InnoDB will support fulltext search. But, I wonder why previous versions of InnobDB didn't support FTS? My guess ...
2
votes
1answer
963 views
mysql table very large size although it doesn't have a lot of records
I have drupal website, one of the tables is cache_form. I have a cron to
DELETE FROM cache_form where expire < UNIX_TIMESTAMP(NOW());
which works fine.
The size of cache_form.myd is 4.9G ...
1
vote
1answer
76 views
LOAD DATA INFILE on a MyISAM table blocks user read-queries, can we get around this?
I've got a large reference table, 100GB, in a MyISAM table, load it up from scratch goes well (4ish hours) using LOAD DATA INFILE.
But we have 1GB each day we want to update in a batch process using ...
2
votes
3answers
968 views
MySQL LOAD DATA INFILE slows by 80% after a few gigs of input with InnoDB engine
I'm loading a 100GB file via LOAD DATA INFILE. I've had good success with MyISAM, a few hours and done.
I'm trying it now using InnoDB. The load starts fast at over 10MB/sec (watching the table file ...
11
votes
1answer
11k views
How can I change the default storage engine in phpmyadmin?
I use InnoDB almost exclusively in my applications. However, if I'm not careful when setting up the table, I forget to change it and phpmyadmin sticks me with MyISAM. Is there a way to change the ...
0
votes
2answers
72 views
Bypass MyISAM table lock
I have a big MyISAM table with a fulltext index for searching. Problem is that inserting data into that table results in a table lock and in the meantime no other sql query can perform any action ...
0
votes
1answer
61 views
View dropped, now shows as table “in use”, can't drop
I have a view on a MySQL/MyISAM setup, then the underlying table had a column dropped, and made that view invalid.
I tried dropping the view so I could recreate it, then MySQL said it was a table ...
2
votes
1answer
197 views
FLUSH TABLES stays in the processlist even after execution
Here is my code from Stored Procedures in Mysql
FLUSH TABLES;
TRUNCATE TABLE tbl_cdrload;
TRUNCATE TABLE tbl_cdrdetails_temp;
Then i execute a query to load a file in to database as below
LOAD ...
1
vote
2answers
442 views
How do I get spatial index feature if I mainly use innodb?
My current strategy is to create another table in myisam that contains those spacial information. Then if I want to find 20 closest business, I'll just use join.
Is this a good idea?
1
vote
1answer
81 views
How does mysql deal with queries that touches myisam and innodb tables?
In our mysql database, we use both myisam and innodb tables, though there are more myisam tables.
QUESTIONS
If a query involves both myisam and innodb tables, will the query use table lock or row ...
2
votes
2answers
232 views
Need help in restore some rows into mySQL database frm myd myi
I am using one mySQL database for several sections of the website and one of the section is not working, when I go to the link of that section it gives the error "1146 Error @ Line 39: Table ...
1
vote
1answer
56 views
Does MySQL's MyISAM use cache before insert?
I want to test two different Disks, one of them is HDD and another is SSD
I use exactly same information and app for both servers. They are exactly same, just one of them is SSD and another is HARD.
...
0
votes
1answer
392 views
MySQL INSERTs waiting for UPDATEs (MyISAM)?
In show processlist;, MySQL 5.5, we see that INSERTs tend to wait for UPDATEs to complete before they can proceed. This is on MyISAM tables.
We tried INSERT HIGH_PRIORITY and UPDATE LOW_PRIORITY. ...
0
votes
1answer
70 views
Why number of matches in fulltext search get less when we put less strings?
SELECT * FROM tableauxiliary WHERE MATCH (FullTextSearch)
AGAINST ('resta*' IN NATURAL LANGUAGE MODE)
show 5 results
SELECT * FROM tableauxiliary WHERE MATCH (FullTextSearch)
AGAINST ('restaurant*' ...
2
votes
1answer
204 views
Unique insert performance: Primary Key vs Unique Index
I have a table of unique values (domains_unique), with collumn domain varchar(255), with more than 20 mil records.
What's the fastest way to insert into the table, by keeping the domain unique ...
3
votes
2answers
115 views
Choosing between MyISAM & InnoDB - two servers with related tables
I don't want to make a mistake.
I am about to put my website online and I admit I haven't thought about which MySQL engine to choose: MyISAM or InnoDB.
I read here and there that InnoDB should be ...
1
vote
1answer
72 views
Issue with Storage Engine
Just got the below error in one of the slave. I just Googled it and changed the variable myisam_data_pointer_size from 4 to 6.
Somehow, my ALTER TABLE was successful.
Can you please let me know ...
0
votes
0answers
69 views
With MyISAM does delay_key_write=ALL work when key_buffer_size=0
Does the delayed key write in MySQL with MyISAM actually function if there is no key cache? Does it have it's own structures to cache the writes?
This server actually has superior performance when ...
1
vote
2answers
104 views
MySQL: optimal configs / methods for ALTER of large MyISAM table to InnoDB (17gb+)
I have a large MySQL (5.1) MyISAM table (13gb table, 4GB indexes - 40mm+ records)
I'm going to be converting this table to InnoDB in a production environment.
The DB is on a cloud server with ...
