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.
3
votes
1answer
32 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
votes
2answers
60 views
Help my database isn't performing fast enough! 100M Merge with 6M need < 1 hour!
I have a server right now receiving more raw data files in 1 hour then I can upsert (insert -> merge) in an hour.
I have a table with 100M (rounded up) rows. Table is currently MyISAM. The table has ...
1
vote
0answers
38 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 ...
0
votes
1answer
54 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` (
...
1
vote
1answer
29 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
86 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 ...
6
votes
1answer
194 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
21 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
78 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
59 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
62 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 ...
1
vote
1answer
44 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 ...
5
votes
1answer
98 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
34 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
87 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
...
2
votes
1answer
84 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 ...
0
votes
0answers
48 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
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
49 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
50 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
35 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
207 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,
...
0
votes
1answer
412 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.
...
2
votes
2answers
63 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 ...
4
votes
2answers
143 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 ...
0
votes
1answer
78 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 ...
1
vote
1answer
78 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 ...
3
votes
1answer
101 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 ...
0
votes
2answers
80 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 ...
1
vote
1answer
113 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
206 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
206 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
...
0
votes
1answer
26 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, ...
0
votes
1answer
76 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 ...
1
vote
1answer
104 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 ...
1
vote
1answer
61 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.
...
2
votes
1answer
234 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 ...
1
vote
1answer
75 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
77 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 ...
0
votes
0answers
30 views
MySQL: optimal configs / methods for ALTER of large MyISAM table to InnoDB (17gb+) [duplicate]
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 ...
1
vote
2answers
114 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 ...
1
vote
1answer
57 views
With MyISAM is there any index size savings when using INT vs BIGINT?
There is obviously a four byte per row space savings in a data column when using INT vs a BIGINT type.
But I have been trying to figure out if there is also any space savings for myisam indexes on ...
3
votes
1answer
83 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, ...
4
votes
2answers
130 views
Using MyISAM for reading and InnoDB for writing data
I read many articles about comparisons of MyISAM and InnoDB from performance point of view and I decided to use MyISAM for reading data and InnoDB for writing data.
I want to use two structure like ...
0
votes
2answers
329 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 ...
0
votes
1answer
95 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
...
2
votes
3answers
1k 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 ...
1
vote
1answer
191 views
MySQL: To use MYISAM or INNODB engine? (plot twist enclosed)
We use INNODB for all of our tables, but I have one special table, a 100+ GB reference table (only read-queries against it).
We are loading changes to this table in a nightly batch job using LOAD ...
1
vote
1answer
159 views
Set up replication and migrate to InnoDB with low downtime
Current situation is this:
I have a large legacy DB using only MyISAM tables, the size of whole
DB is 4GiB, one of the tables contains over 10 million rows, so it is
pretty large.
The database is ...
