Questions tagged [myisam]
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.
510
questions
0
votes
2
answers
366
views
in Mariadb - Can you cache the indexes and data for Aria Storage engine tables?
I have a table that is a read-only table (real-time view), I used Aria based on the recommendation for read-heavy workload.
Based on this What are the main differences between InnoDB and MyISAM?
When ...
CommunityBot
- 1
1
vote
2
answers
5k
views
How to configure my.cnf for Mysql 5.1, which is using too much CPU?
I am new Database engineer. I am having trouble in my server which is using too much CPU on database query(simple count statement). I have googled about it, so i found out it depends on system ...
0
votes
1
answer
284
views
Changing storage engine to InnoDb from MyIsam is dropping records from table
Currently our Database all tables are using MyIsam as a storage engine. We tried changing storage engine of our mysql tables from MyISAM to InnoDB from PHPMyAdmin operations tab. However during the ...
CommunityBot
- 1
0
votes
1
answer
599
views
How to repair MyISAM table?
I'm trying to recover a corrupted MyISAM table from a legacy system. The table is corrupted when there is a sudden power loss. It has little over 500 million rows and is 23GB is size (.MYD file)
...
- 21
0
votes
0
answers
21
views
MyISAM table repair stuck in loop
my MyISAM table is broken (I know it's better to use Innodb), but now I need to fix this large table. 200GB
I have already tried the following options, but the table repair seems endless and the TMD ...
- 21
1
vote
0
answers
32
views
Wrong query optimization plan with InnoDB
After switching DB engine from MyIsam to InnoDB on MariaDB 10.3.39 we noticed endless queries and huge disk space usage.
The problem are some queries that are wrong optimized with the new engine.
...
- 11
3
votes
1
answer
2k
views
MySQL: error 1118 (42000) row size too large
storage_engine = MyISAM
table_type = MyISAM
version = 5.1.25-rc
database_name.sql dump size is 3.3G:
mysqldump -u user -p database_name > database_name.sql
When I try to restore the database (...
CommunityBot
- 1
0
votes
1
answer
835
views
MySQL MyISAM REPAIR TABLE lost connection to MySQL server during query
So I've been building up over time a database, and unfortunately I was using MyISAM which didn't have any problems until the database became huge.
After massive CPU spikes, I created a script to ...
CommunityBot
- 1
2
votes
1
answer
414
views
Finding temporary table name in MariaDB
As I reported some days ago to MariaDB bug tracker the following bug: https://jira.mariadb.org/browse/MDEV-17420, slave replicas with version 10.2.x are leaking temporary tables on MyISAM files.
Our ...
CommunityBot
- 1
0
votes
1
answer
3k
views
How to change default engine in WAMP to InnoDB
Can someone tell me how to change my default storage engine to InnoDB using wamp? Currently it is MyISAM.
CommunityBot
- 1
1
vote
4
answers
376
views
Load index to cache is not using memory available in the key_buffer_size variable
Good afternoon folks,
I have a problem when trying to load the indexes from the MyISAM tables into MySQL memory, even though there is space available in the key_buffer_size variable, it stops loading ...
CommunityBot
- 1
1
vote
1
answer
630
views
MySQL 5.6 row format changes when changing storage engine from MyISAM to InnoDB
I am testing an upgrade of all existing MySQL 5.6 tables from MyISAM to InnoDB. I converted all row formats to dynamic first for all the tables to be on Barracuda then running alter table <...
CommunityBot
- 1
0
votes
2
answers
277
views
MySQL's maximum memory usage is dangerously high and CPU run 100%
Hi we faced an issue on our server and it go verry slow for more tan 1 month and it go worst every day.
I am really not server administrator,
This is mysqltuner.pl result,
-------- Storage Engine ...
CommunityBot
- 1
1
vote
1
answer
12k
views
MariaDB crashed: Unknown/unsupported storage engine: InnoDB
I've a Debian GNU/Linux 9 (4GB, 2 CPUs) on Digitalocean. Tonight (I've done nothing) my DB (mariaDB) crashed with this errors. I ran a wordpress with InnoDB and myISAM tables:
2020-10-17 0:51:18 ...
CommunityBot
- 1
0
votes
1
answer
266
views
MYSQL Multiple table optimization for advanced search
I have a users table where there is the basic information of each user:
CREATE TABLE `users` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`account_type` varchar(10) NOT NULL,
`email` varchar(...
CommunityBot
- 1
0
votes
1
answer
349
views
MySQL MyIsam Compressing tables
Im packing big MyISAM tables with myisampack and came up with the following issue. (MariaDB 10.1.14 64bit windows)
Indexes are rebuilt after packing the tablewhile running myisamchk -rq, so, it doesnt ...
CommunityBot
- 1
0
votes
1
answer
292
views
Change Storage Engine on Referenced Tables
i need to change storage engine from 'InnoDB' to 'MyISAM' on some tables of different databases.
So i made a script file to do so:
#!/bin/bash
DATABASES=$(mysql -u XXXXX -pXXXXX --skip-column-names -...
CommunityBot
- 1
0
votes
1
answer
1k
views
Repair by Sort not working
I am trying to import a .sql file which has MyISAM engine, about 51 million rows with 18 columns. The file has 1 primary and 1 unique key. I have after lurking around this site and reading mysql ...
CommunityBot
- 1
1
vote
1
answer
319
views
Much slower replication on Innodb compared to myisam
Good morning. I wanted to test how InnoDB will work on my servers and for this I made a slave and connected it to my master with myisam.
The problem is that when the slave works on innodb, replication ...
CommunityBot
- 1
0
votes
1
answer
667
views
Safe method to rename a crashed myisam table
I have a large MyISAM table which has crashed. Repairing the table will take some time. The table only INSERTed to and SELECTed from, never updated. To allow the application to continue working, ...
CommunityBot
- 1
7
votes
2
answers
11k
views
using Aria vs InnoDB vs MyISAM
I was wondering (if anyone knows) if Aria database is crash safe . I had some tables with MyISAM which were getting corrupted very often and switch them to InnoDB . However innodb is a bit slower and ...
- 111
1
vote
1
answer
267
views
C API hangs in mysql_query() [closed]
I have this very strange issue and I cannot reproduce it - it occurs once in ~12 hours only for this table:
CREATE TABLE `ReferenceWaves` (
`ReferenceWave` datetime NOT NULL COMMENT 'DateTime of ...
CommunityBot
- 1
0
votes
1
answer
24
views
Mysql MYISAM force operating on a crashed database
Is theres a way to forcibly operate on a database that crashed (even if it's not possible through mysql cli at least a way to remove the dirty bit that marks it as crashed by editing with an editor).
...
- 76.2k
117
votes
5
answers
231k
views
How to safely change MySQL innodb variable 'innodb_log_file_size'?
So I'm fairly new to tuning InnoDB. I'm slowly changing tables (where necessary) from MyIsam to InnoDB. I've got about 100MB in innodb, so I increased the innodb_buffer_pool_size variable to 128MB:
...
- 76.2k
6
votes
1
answer
897
views
how to cache a subset for cascading select queries in mysql
Here is another database problem I stumbled upon.
I have a date-range partitioned MyIsam look-up table with 200M records and ~150 columns. On this Table I need to perform cascading SELECT-Statements ...
CommunityBot
- 1
3
votes
1
answer
13k
views
mysqldump with --no-tablespaces on InnoDB with index?
Does mysqldump --no-tablespaces dumps index files of InnoDB?
I know that MyIsam uses separate index files *.MYI for index. So if one uses mysqldump with --no-tablespaces, does that mean index is not ...
CommunityBot
- 1
1
vote
1
answer
101
views
Migrate MyISAM tables to InnoDB using replication to avoid a long down time
We have to migrate MyISAM tables to InnoDB but we are afread of a long down time.
In our database we have 10 MyISAM tables where 2 of them are the biggest: ~26GB data + ~10GB indexes.
My question is:
...
- 76.2k
1
vote
0
answers
1k
views
Suddenly high CPU usage
I've read a lot about MySQL optimisation and I already optimised MySQL about a year ago. Reason for that was a high system load average. I tuned many queries and edited the my.cnf file. Since then ...
2
votes
1
answer
35
views
MariaDB counting matching rows in table ~50m rows
Running MariaDB 10.9.4 on Debian 11, I have a table of flights with approximately 50 million rows
My structure looks like:
id int(11) auto increment
flight_number varchar(16)
aircraft char(6)
...
- 13k
1
vote
1
answer
320
views
MySQL InnoDB Insertion Faster Than MyISAM
I wrote code that inserts about 15 millions rows into tables in MyISAM and InnoDB for comparsion.
MyISAM and InnoDB are set up with no optimization configuration in /etc/mysql/my.cnf.
And tables ...
CommunityBot
- 1
0
votes
1
answer
138
views
MySQL update with inner join taking too long
I have the following two tables
CREATE TABLE `tbl_products_temp` (
`cd_id` INT(6) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
`cd_structure` MEDIUMBLOB NOT NULL,
`cd_smiles` TEXT NULL ...
- 181k
0
votes
1
answer
89
views
LVM snapshot of MyISAM tables requires repair every time
I'm attempting to setup slave replication of a large MyISAM table. I login to the master server and perform FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS and that returns right away with the ...
- 21
0
votes
2
answers
2k
views
Mysql SHOW OPEN TABLES shows In_use but nothing in SHOW PROCESSLIST
I'm looking for advice for a MySQL database used for my development. I have a table is locked, although no process is active.
Here's some context: I am a developer on this MySQL development database ...
CommunityBot
- 1
1
vote
1
answer
61
views
How to change the engline type from InnoDb to MyISAM of an existing db?
I have a MariaDb database which consists of 2 tables and which is around 10Gb of the size. One is dependendant on the other via a foreign key.
How can I change the engine type of that particular db ...
- 181k
1
vote
3
answers
9k
views
Disk full of mysql .mad and .mai files
On a (owned) shared server sometimes mysql writes alott of .mad and .mai.
This happens since i think 2 or 3 months, completely random.
It may happen 3 times a day, then it does not happen for a ...
0
votes
1
answer
135
views
Will 'sleeping' connections open files other than the socket in MySQL?
Recently, in our MySQL replication setup, the replication slave crashed due to 'too many open files' error. We brought it backup, but trying to figure out what caused the unexpected hike in the number ...
CommunityBot
- 1
0
votes
1
answer
42
views
wrong index in composite PRIMARY KEY in MyISAM
I use MyISAM for processing tables, as it is much faster to read large data from MyISAM as compared with InnoDB for JOINs. I create map tables for bulk INSERT,
CREATE TABLE tag_map
(
article_id int(11)...
- 4,479
4
votes
1
answer
9k
views
Can converting MyISAM to InnoDB cause problems?
I am changing an old MySQL database table which is still in MyISAM. I'd like to change it to InnoDB, but I don't know whether it can cause problems and it's a pretty important table so I don't want to ...
- 115
4
votes
1
answer
15k
views
MySQL hangs in killed state
I have a strange situation where a mysql OPTIMIZE query will never end. After killinh it, it remains in the "killed" state.
Scenario to reproduce:
OS: CentOS 6.5
MySQL: 5.6
Engine: MyISAM (with at ...
- 24.8k
0
votes
2
answers
159
views
Can I extrapolate from the progress reporting in MyISAM ALTER?
I'm migrating a 300 million row MyISAM table to add some indexes.
Just because it might be relevant, here's my ALTER statement:
ALTER TABLE my_table
ADD UNIQUE INDEX my_table_abcd (a, b, c, d),
...
CommunityBot
- 1
0
votes
1
answer
464
views
Mariadb concurrent insert not working for MYISAM tables
I am using mariadb 10.1.13. I have a large MYISAM table ( > 2000000 rows ) which used to have frequent 'INSERT' operations from multiple scripts.
Recently I had to add a new 'SELECT' query to the ...
CommunityBot
- 1
31
votes
5
answers
55k
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 ...
- 11
3
votes
3
answers
15k
views
Strange MySQL replication error 1146 (Table doesn't exist)
I'm getting a very weird MySQL replication error No. 1146 for a REPLACE INTO query on a slave host replicating all tables in all databases from a master, and I'm having a bit of a hard time ...
- 101
11
votes
6
answers
35k
views
Mysql: Insert performance INNODB vs MYISAM
I am inserting into a simple small table with 5 attributes and 1000 rows.
I observed when the engine is INNODB, each insert is taking 0.03 - 0.05 seconds.
I changed the engine to MYISAM, then the ...
- 107
1
vote
2
answers
157
views
In MySQL, is neccesary or recommended a LOCK TABLE before a REPAIR TABLE
I need to know if is neccesary or recommended to try a LOCK TABLE before a REPAIR TABLE, because sometimes we need to make REPAIR of tables when users are working on the application.
As I have tested, ...
- 13k
38
votes
5
answers
57k
views
Why are simple SELECTs on InnoDB 100x slower than on MyISAM?
I have quite an annoying problem. I want to use INNODB as my main database engine and give up on MyISAM as I need the former for using galera-cluster for redundancy.
I copied (description follows) ...
- 181k
13
votes
3
answers
25k
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 ...
- 181k
6
votes
2
answers
14k
views
Performance settings for MyISAM tables - keep everything in memory
I have a 8-core MySQL server with 32 GB of RAM, holding 40 MyISAM tables (total: ~4GB of data).
Since the databases size is several times smaller than the available memory, how can I make full usage ...
- 181k
10
votes
7
answers
35k
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,004
0
votes
1
answer
974
views
WordPress MySQL writes during "Flush Tables With Read Lock" are rejected instead of being queued
I'm running into an odd issue when setting MySQL into a brief read-only mode for backup.
Reads continue fine during that "read only" minute, but writes are just rejected instead of being ...
- 698