MySQL Forums - MyISAM

using the myisam engine and am having trouble with swap memory usage even though there is available system memory (no replies)

Seung Hong — Thu, 05 Jun 2025 07:28:16 +0000

Description:
Hi, my os server is currently running only mysql, and mysql is not using innodb, but using myisam engine.

What I'm wondering is why it uses swap memory even though there is available memory, so the swap memory keeps filling up without being released.

I know that the buffer/cache memory contains the query cache, so I wonder if it is normal to use swap memory instead of taking it from there.

The similar issues I found are as follows:

#83047, #84003

Can I prevent swap from filling up by applying the above settings?

The solutions I thought of are to increase the RAM or periodically initialize the swap.
I wonder if there is another way, or if it is a bug, can you help me?

os : Operating System: Rocky Linux 8.10

# free -h
total used free shared buff/cache available
Mem: 62Gi 13Gi 584Mi 1.0Gi 48Gi 46Gi
Swap: 8.0Gi 1.6Gi 6.4Gi

# cat /proc/3661008/status|grep Vm
VmPeak: 21319524 kB
VmSize: 19771240 kB
VmLck: 0 kB
VmPin: 0 kB
VmHWM: 14112756 kB
VmRSS: 12770808 kB
VmData: 17926176 kB
VmStk: 132 kB
VmExe: 21748 kB
VmLib: 11264 kB
VmPTE: 30700 kB
VmSwap: 1529616 kB

mysql> select engine,data,indexes,total
-> from (
-> select
-> ifnull(engine,'TOTALS') as engine,
-> concat(data,' GB') as data,
-> concat(indexes,' GB') as indexes,
-> concat(tot,' GB') as total,
-> if(engine is null,-1,tot) as ord
-> from (
-> select
-> engine,
-> round( sum(data_length)/1024/1024/1024, 2 ) as data,
-> round( sum(index_length)/1024/1024/1024, 2 ) as indexes,
-> round( sum(data_length+index_length)/1024/1024/1024, 2 ) as tot
-> from information_schema.tables
-> where engine is not null and engine not in('information_schema','performance_schema')
-> group by engine with rollup
-> ) sums
-> ) list
-> order by list.ord desc;

+--------+-----------+-----------+-----------+
| engine | data | indexes | total |
+--------+-----------+-----------+-----------+
| MyISAM | 398.18 GB | 154.33 GB | 552.50 GB |
| CSV | 0.00 GB | 0.00 GB | 0.00 GB |
| InnoDB | 0.00 GB | 0.00 GB | 0.00 GB |
| MEMORY | 0.00 GB | 0.00 GB | 0.00 GB |
| TOTALS | 398.18 GB | 154.33 GB | 552.51 GB |
+--------+-----------+-----------+-----------+

my.cnf
[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8

[mysqld]

innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:10G

sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

port = 3306
socket = /tmp/mysql.sock
datadir = /mnt/disk2/db_data
basedir = /usr/local/mysql
skip-external-locking
key_buffer_size = 4096M
max_allowed_packet = 20M
table_open_cache = 512
sort_buffer_size = 20M
read_buffer_size = 20M
read_rnd_buffer_size = 80M
myisam_sort_buffer_size = 256M
thread_cache_size = 8
query_cache_size = 32M

explicit_defaults_for_timestamp
skip-name-resolve

tmp_table_size=100M
max_heap_table_size=100M

ngram_token_size=2
ft_min_word_len=2
innodb_ft_min_token_size=2

max_connections = 1000
max_connect_errors = 1000
wait_timeout = 7200

slow_query_log
long_query_time = 3
slow_query_log_file=slow_query.log

#symbolic-links=0

character-set-client-handshake=FALSE
character-set-server = utf8
collation-server = utf8_unicode_ci

default-storage-engine = MYISAM
bulk_insert_buffer_size = 128M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 2G
myisam_repair_threads = 1

server-id = 1

innodb_file_per_table=1

innodb_buffer_pool_size=8M

innodb_data_file_path = ibdata1:10M:autoextend

innodb_autoextend_increment=100

innodb_additional_mem_pool_size=2M

innodb_flush_log_at_trx_commit=1
sync_binlog=1

innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 8

innodb_log_buffer_size = 8M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

[mysqldump]
quick
max_allowed_packet = 256M
default-character-set = utf8

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
default-character-set = utf8

[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 100M
write_buffer = 100M

[mysqlhotcopy]
interactive-timeout

Unknown version for files .FRM and MYD (no replies)

FRACHEBOUD Remi — Fri, 14 Mar 2025 14:02:50 +0000

Hello,
I've got two files (.MYD and .FRM)
I try to open on Mysql.
I've installed a few version but i've the same error message that the .FRM file is not correct.
CAn you tell me how i can find the good version who can manage these files?
thanks a lot.

Index key prefix behaviour in MyISAM (2 replies)

Hassan Bahrami — Thu, 22 Aug 2024 15:27:09 +0000

I have a VARCHAR(510) column in my table and I'm gonna need a unique index (UNIQUE KEY) on it, since MyISAM index key prefix length limit is 1000 bytes I have to use index prefix like col_name(250).
My question is if this will lead to a duplication problem for the unique key or not?
it seems a posibility since this index will use the first 250 chars in the column which can be not unique although the whole column value is unique.

MySQL 8.0: Still using MyISAM ? It is time to switch to InnoDB ! (no replies)

Edwin Desouza — Tue, 07 Mar 2023 15:34:25 +0000

MySQL 8.0: Still using MyISAM ? It is time to switch to InnoDB !
- https://blogs.oracle.com/mysql/post/still-using-myisam-it-is-time-to-switch-to-innodb

Delete slow as not using index (no replies)

EDGEFREE DU — Fri, 17 Feb 2023 05:30:48 +0000

I have a very big 600G MyISAM table. Its structure is simple as 7 columns, first 5 are indexes, and last two are blob.
After migrated to MySQL 8.0, the deletion runs 20 times slower than 5.0.96. There are about 50% data to purge daily.
The select with same condition works with index well, but deletion not using index.
Can any guru here help me out?

Default storage engine (5 replies)

K B — Tue, 19 Jul 2022 21:18:46 +0000

I am using MySql server 8.0.

I set the default storage engine to MyISAM as follow:

SET default_storage_engine=MyISAM;

But despite that, when I create a new table, it gets the InnoDB as its engine.

How can I fix this problem?

Changing ft_min_word_len without rebuilding old indexes - will that work as described below? (no replies)

Arek M — Tue, 12 Apr 2022 06:19:25 +0000

Hello.

I have tons of myisam indexes with ft_min_word_len=4. Now I want to change it to other value, like 3 but without rebuilding old indexes. For old indexes =4 is simply ok.

Will such change be ok from mysql point of view and everything will work just fine? I mean old indexes would behave like ft_min_word_len=4 while new (or rebuilt) indexes will behave as ft_min_word_len=2?

Documentation doesn't really cover that. It only mentions that " `FULLTEXT` indexes on `MyISAM` tables must be rebuilt after changing this variable" which would be make sense if anyone would want to new setting to work for existing indexes but that isn't my case.

Migrate MyISAM tables from MySQL 5.7 to 8.0 (no replies)

Edwin Desouza — Mon, 02 Aug 2021 16:20:26 +0000

https://lefred.be/content/migrate-myisam-tables-from-mysql-5-7-to-8-0/

DATA DIRECTORY and INDEX DIRECTORY (2 replies)

Евгений Б — Sun, 20 Dec 2020 12:55:07 +0000

I've tried to execute:

CREATE TABLE `sprading`.`myisam_test` (
`idmyisam_test` INT NOT NULL)
ENGINE = MyISAM
DATA DIRECTORY = 'G:\1'
INDEX DIRECTORY = 'G:\1';

Mysql Community Server 8.0, OS Windows.
My DATA DIRECTORY and INDEX DIRECTORY options are ignored by server and the table created in default server data directory.
Why? How to resolve this problem?

MySQL v8 - MYISAM Pack Information (2 replies)

Frank Peacock — Thu, 03 Sep 2020 08:49:06 +0000

Hello,

I have previously used MYISAM tables on v5.7 on Linux Ubuntu without problems. In particular, packing information correctly appears in the "Show Table Status" response.

I have started using Server version: 8.0.21 - MySQL Community Server - GPL with cPanel on Linux Centos 7. Packing the MYISAM tables produces identical compression ratios as v5.7 so the final file sizes are identical. What is not working is that within mysql_client (using show table status) and phpMyAdmin the packed myisam tables are still showing the original average record size and original table size.

Is this possibly due to cPanel use or is this a known issue?

Thanks,

Frank

Deletion Woes (no replies)

Randy Smith — Wed, 15 Jan 2020 17:18:37 +0000

I have a very large table with many fields, among them a "post_date" field established as varchar(50) DEFAULT NULL and "salary" set up as decimal(11,2).

From a LOAD INI, there are some post_date entries with invalid date fields (e.g. one has "201", for instance) and some salary fields that do not have decimals (e.g. "0" instead of "0.0".)

I can find these by select, but when I change the select to a delete, I'll get "incorrect datetime value" or "incorrect decimal value". I tried "delete from table x where id in (select id from table x where year(post_date) is null)", a select that works fine on its own, but from that I get "you can't specify target table x for update in from clause.".

I tried to set salary to 0.0 or NULL with the same "incorrect decimal value: '0' for column '' at row -1" error.

How can I get rid of these bad rows of data?

MySQL 8 Windows Deletion Performance Improvement? (8 replies)

Randy Smith — Wed, 15 Jan 2020 17:44:00 +0000

I'm relatively new to MySQL but not to databases (40+ years). I see there are a number of topics about performance in this forum, and then I also noticed a not-very-clear note on switching to InnoDB.

First, I have a 119M record, three-field table that I will need to update once or twice a month. It's running on a Windows 2012 R2 server, MySQL 8.0, over 100GB hard drive space and 8 GB RAM (and more RAM is NOT in the budget at this time). There is not much else running on this server. I am doing my manual transactions using MySQL Workbench.

I'll first need to delete a number of rows based on their existence in another table, and then I'll need to insert a number of rows based on their non-existence in the existing table.

It's a MyISAM table and the two necessary fields are indexed.

The first time I tried to do the mass deletion, it ran for almost three days before I decided to kill it, thinking it was frozen. It was not, sorry to say.

So, for kicks, I tried another deletion with LIMIT 100. It's still running after an hour.

This is primarily a resource table; it won't otherwise be updated (e.g. record at a time updates based on user or other inputs).

How can I best speed this search up? Seems to me an hour - at the most - should get this done.

MyISAM - Incorrect information in file *.frm (1 reply)

Boraiah Manjunatha — Mon, 01 Jul 2019 23:12:37 +0000

I have table data files table.frm, table.myi and table.myd that are from mysql version 5.6.31 on centos 7. I want to backport these files on centos 6.2 server that has mysql version 5.5.35. When I tried to do this, I am getting "Incorrect information in file: table.frm". Just fyi before I copied, I had sopped the mysql server and re-started after copying files.

Anyone know why I am getting this error? can a table from newer mysql version backport on a older mysql version?

Database Utilities (3 replies)

Scott Krise — Fri, 15 Mar 2019 19:45:02 +0000

Hello,

Are there any utilities that should be run to optimize your databases?

We are running Mysql 5.7...and we have some large tables (1.5 million records) that we feel are slowing down our system, so as a test, I deleted all records older than 5 years, just to see how it affected my performance. To my surprise, on particular report went from running for about 25 seconds to running for over 5 minutes when I did that! So, this lead me down the path where maybe some of our latency is due to tables not being optimized from time to time.

Using other data types, it was necessary to run utilities that would remove deleted records from your tables, or to reindex them...but I always thought that SQL products dealt with that without any user intervention. Am I wrong about that? Any thoughts as to why I just saw such a drastic change in performance?

Performance by Selections (9 replies)

Greg Mundt — Fri, 22 Mar 2019 15:29:18 +0000

Not sure if this is the best place, but I have 2 MyISAM tables due to the fact that there are few writes, but many reads.

Table 1 - Order Header 35,500,000 rows Primary Index ORD_ID which is unique
Table 2 - Order Lines 85,225,000 rows Primary Index ORD_ID, ORD_Line which is unique

SELECT H.ORD_Store, L.ORD_Item, SUM(L.ORD_Qty * L.ORD_Price) / SUM(L.ORD_Qty) AS WEIGHTED_PRICE, SUM(L.ORD_Qty * L.ORD_Price) AS SALES, COUNT(ORD_ITEM) AS TXNS, L.ORD_Item_Class, L.ORD_Desc FROM ord_headers as H LEFT JOIN ord_lines as L ON H.ORD_ID=L.ORD_ID WHERE (H.ORD_Date BETWEEN '2018-01-01' AND '2018-12-31') GROUP BY ORD_Store, ORD_Item ASC

Returns within 10 seconds all of the results, but if I add AND L.ORD_Item='XXXXXX' to the where clause it will time out after 10 minutes. Why does any inclusion of the second file affect performance so drastically?

Record locking (3 replies)

Scott Krise — Wed, 30 Jan 2019 20:44:27 +0000

I am a programmer, and I have taken over the support of a system written in MYSQL. There is a mixture of MYISAM and INNODB tables within the database, and the programs are written in C++.

It appears as though there is no record locking taking place anywhere in the system, and I'd like some advice on the best course of action to start putting the appropriate locks in place.

So, the majority of our tables are MYISAM, and I don't believe record locking is an option, only table locks can be done? Well, unless Im misunderstanding how the table locking works, that isnt an option since we have 25-50 active users.

So my two questions are:

1) What considerations should be given to simply changing over our myisam tables to innodb? Can I simply edit the table and make the change that way, or should I drop the old table and recreate it with the new structure?

2) Once that is done, if for example a person is about to change a customer, would I just do a SELECT...FOR UPDATE on the customer Im interested in, do the update, then in some way, do an unlock (cant seem to find the syntax for that at the moment).

Thanks in advance!!

MyISAM Column / Record Delimiter (no replies)

Alan Halls — Wed, 12 Dec 2018 23:25:19 +0000

I am interested in finding what the HEX delimiter is for columns, and what tells the MySQL storage engine that it has reached the end of a record.

Looking at the DAT / MYD files, the records don't seem to be in a fixed width / fixed length layout which would seem to indicate there are delimiters within the binary data.

Optimize Table not fixing Blocks/Record (no replies)

Jim Staff — Fri, 30 Nov 2018 18:25:06 +0000

Hi,

It seems as though our MyISAM tables are becoming "fragmented". When we run myisamchk -eis TABLE, some tables show that the Blocks/Record value is > 1 (8, 30, and larger). Running OPTIMIZE TABLE has no effect on that value. Besides dumping the table and reading it back in (which we've done), how can the table be defragmented? We were using 5.6 with a weekly OPTIMIZE on all tables and never had a problem. Recently we migrated to 8.0 and are seeing problems with performance.

Thank you

Mysql order by is very slow from 1,000,000 rows, we have nearly 300,000 search results (1 reply)

Ozden Haldun — Fri, 23 Nov 2018 17:50:19 +0000

When we do Natural Search in Mysql from 1,000,000 rows, we have nearly 300,000 search results. This query lasts 7-8 seconds when we want to select the first 2,000 documents with the highest natural score.

This time is too long for us. How can we shorten the duration of the first 2,000 pick by sort by natural rating?

Thank you in advance for your help.

select (match(baslik,detay) against('+borçlu +yetkili +icra +dairesi +tercih +kayyım +borç +daire +borc' in natural LANGUAGE mode)) as n from ictihat where (match(baslik,detay) against('+borçlu +yetkili +icra +dairesi +tercih +kayyım +borç +daire +borc +Deyn +Vecibe +Evam +Liability +Kredi +Ödev +Ödünç +Namus +borcu +Takanak +Medyun +Debitor +Zimemdar +Yükümlü +Verecekli +Bölük +Çember +Çevre +İdare +Menzil +Ofis +Temşiyet +Yürütme +Curator +Kayyum +Yeğleme +Salahiyettar +Salâhiyyetdâr +Salahiyetli +Mezun' in natural LANGUAGE mode)) order by n desc LIMIT 0,2000

Result: 4.703 sec

Explain: 1 SIMPLE ictihat fulltext aramafull aramafull 0 const 1 100.00 Using where; Using filesort

select (match(baslik,detay) against('+borçlu +yetkili +icra +dairesi +tercih +kayyım +borç +daire +borc' in natural LANGUAGE mode)) as n from ictihat where (match(baslik,detay) against('+borçlu +yetkili +icra +dairesi +tercih +kayyım +borç +daire +borc +Deyn +Vecibe +Evam +Liability +Kredi +Ödev +Ödünç +Namus +borcu +Takanak +Medyun +Debitor +Zimemdar +Yükümlü +Verecekli +Bölük +Çember +Çevre +İdare +Menzil +Ofis +Temşiyet +Yürütme +Curator +Kayyum +Yeğleme +Salahiyettar +Salâhiyyetdâr +Salahiyetli +Mezun' in natural LANGUAGE mode)) LIMIT 0,2000

Result: 1.005 sec

Explain: 1 SIMPLE ictihat fulltext aramafull aramafull 0 const 1 100.00 Using where

Error : Too many columns (3 replies)

Ian Konda — Wed, 19 Sep 2018 18:45:17 +0000

I recently upgraded the mySQL database from version 4.xx to 5.7. I recently started working on mySQL (not my area of expertise as oracle or mssql)

I get quarterly updates from a source which has more than 2000 columns in it. It works fine on older version of mySQL when the table gets created with more than 2000 columns. But the same doesn't work on the version 5.6.Yes I know, it's not a good design. Unfortunately We don't have time and budget to re-design the database.

Any help is appreciated

Thanks
Ian

Cannot open MyISAM backup files with MySQL 8 (3 replies)

ferit akgul — Sat, 21 Jul 2018 02:55:06 +0000

Hi,

We are backing up our DB using MyISAM in MySQL (to be stored on S3). We chose this method because of the convenience. It was enough to copy the folder with MySQL 5.7.

We upgraded to MySQL 8 (we continued to take backups using MyISAM) and the problem is we can not restore those backups from S3 right now.

None of the MySQL versions (5.6 or 8) is able to read these MyISAM backups. The files are not detected by MySQL at all.

How can we restore those files in MySQL 8?

Any help is greatly appreciated.

Thanks,
Ferit

ONLINE DDL - Incorrect Key (no replies)

G K — Mon, 09 Jul 2018 19:14:18 +0000

I am implementing MySQL's - Online DDL to add a column to a table using ALGORITHM=INPLACE to implement 'Zero-Downtime upgrade'. The query I execute is,

ALTER TABLE documentbase_copy ADD testColumn INT, ALGORITHM=INPLACE, LOCK=NONE;

the table sizes are very huge and this query works perfectly fine on tables of size 1 Million or less. But when I tried to run it on table size 10Million, I get the following error -

java.sql.SQLException: Incorrect key file for table 'documentbase_copy'; try to repair it
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:127)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:960)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1116)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1066)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1396)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1051)
at com.example.Informatica.Threads.EstablishConnection.executeUpdateQuery(EstablishConnection.java:67)
at com.example.Informatica.Threads.QueryThread.run(QueryThread.java:19)
at com.example.Informatica.Threads.QueryThread.call(QueryThread.java:33)
at com.example.Informatica.Threads.QueryThread.call(QueryThread.java:1)
at java.util.concurrent.FutureTask.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)

I am not sure what MySQL means by "incorrect key file for table {}, try to repair it." After reading some blogs, I tried to repair my table, but this is an InnoDB table and cannot be repaired.

Please help!!

P.S - This MySQL server and database is hosted on Amazon - RDS

Thanks in advance!

MyISAM multi queries slow performance (1 reply)

Kaihao Wang — Tue, 05 Jun 2018 03:58:15 +0000

I am currently using MySQL Workbench of AWS to process data. I created my code as stored procedures with parameters so that I can call it to process. The thought of this function is that it sliced the main tables into multiple sub_tables based on the parameters entered and created their own temp tables to process.

Lets say that without splitting the jobs it takes 312s to complete and when I split it into 7 instances to run simultaneously, it takes 183s for them to complete ( I was expecting to achieve a similar result like 312/7 = 45s around).

I manually open multiple connections in MySQL both and they delivered similar results, which was not as good as I thought.

Can anyone help to explain?

Appreciated

Meltdown Fix: 40% performance regression for MyISAM; recommend switch to InnoDB (no replies)

Edwin Desouza — Tue, 13 Feb 2018 16:18:48 +0000

Meltdown Fix: 40% performance regression for MyISAM; recommend switch to InnoDB

https://mariadb.org/myisam-table-scan-performance-kpti/

Write Buffering / Write Cache to reduce SSD / CFast Flash wearing (no replies)

Michael Uray — Mon, 22 Jan 2018 12:20:33 +0000

I ran a MySQL MyISAM database engine for a measurement data recording system which writes new values every second, sometimes more often.

After a few weeks the CFast (https://en.wikipedia.org/wiki/CompactFlash#CFast) card died due to to many write cycles.
According to the data sheets are 691.2 TBW allowed on this flash card, but only 3000 Erase/Write cycles:
https://www.br-automation.com/en-ca/products/industrial-pcs/panel-pc-3100-multi-touch/cfast-cards/5cfast256g-10/#techdata

There are a lot of measurement values which get recorded, but the recording depends on a change of the measured value and a minimum recording time delay, which is different on each datapoint.

There is one table for each recording value which contains just two columns:
DateTime: doubleunsigned
Value: 2-8 Byte, depending on the datapoint to record

I think there gets a lot of small data recorded very often, what often causes the flash memory to delete flash blocks and rewrite them.

Is there any way to configure a write buffering / write cache, that changes only get written if they are bigger than xxx bytes (which I would configure to the block size of the flash memory) and / or any option that changes get written at least every yyy seconds if I do not exceed the xxx bytes within this time?

Optimum settings for Small DB running on 2G Ram (no replies)

Hans Brost — Mon, 09 Oct 2017 18:20:23 +0000

Hi. All

Was hoping to get some suggestions as to MySql variable settings based on the following:

1. System Ram: 2G, Database Engine: ISAM
2. Tables:
a: 20,000 rows, Size on disk: 250MB
b: 20,000 rows, Size on disk: 13MB

I run an update script daily which adds transactions to Table a, and updates various fields in Table b.

There are only maybe 20 users who access this database during the day.

The access consists of searches of the tables, using indexes on the tables.

There are no actual searches that would look for particular values in the tables, it's all indexed...there are a lot of sequential reads based on the indexs.

On a DS I had earlier, the update script would run in 28 seconds.
On my current one, it runs 2:43 .....almost 3 minutes.

I've been playing around with various settings, but can't get the processing time down.

Here's my current settings in my.cnf:

[mysqld]
performance-schema=0
innodb_file_per_table=1
max_allowed_packet=268435456

# Made it 500. Was 10000
open_files_limit=500

default-storage-engine=MyISAM
# My Adds
bind-address=127.0.0.1
query-cache-type=1
query_cache_size=64M
query_cache_limit=32M

tmp_table_size=64M
max_heap_table_size=64M
key_buffer_size=512M

#Tried these at 64 and ran out of memory
read_buffer_size=8M
read_rnd_buffer_size=8M

sort_buffer_size=32M
thread_cache_size=10

Be forever grateful for any advise/help anybody has

Thank you,
z

Additional Key Caches (no replies)

alan langridge — Fri, 22 Sep 2017 16:53:13 +0000

Hi,

I have set up two additional MyISAM key caches. How are these monitored? There do not appear to be additional "key_*" status variables.

Are the stats for all key caches stored in the default key_* system variables? And if so, how does one determine if a key cache is doing a worthwhile job?

Thanks,
Al

We're a Big MyISAM Shop, But... (4 replies)

Eric Robinson — Fri, 05 May 2017 17:23:49 +0000

We have 600 instances of MySQL using MyISAM and it has worked fine for us for more than a decade, so we're not interested in switching to InnoDB. Well, not much anyway. In our opinion, InnoDB is a bloated, fly-infested warthog sweltering in the hot sun, but it does one thing well: row-level locking. After ten years of using MyISAM, we're finally running into some complex queries (very complex!) that are locking up our databases. Sometimes we get tons of "locked" queries stacked up waiting for one complex one to finish.

We don't want to convert the whole database to InnoDB. There are 2000 tables. We suspect that there are only 15-20 tables that really need to be converted. What's the best way to analyze these super complex multi-multi-join queries to determine which tables are actually causing our problems?

Massive MyISAM query (3 replies)

Dave S — Tue, 02 May 2017 03:05:36 +0000

I need help, I'm way out of my league here.

I have a MyISAM table I inherited. The table is live and being written to 24/7, and quite frequently. It currently holds over 21M rows on a dedicated hosting server.

I need to select out about 4 million rows from it ... somehow ... to get those rows transfered to another physical server that isn't connected in any way. The query to get the rows is very simple:

SELECT * FROM sales WHERE storeid=X

The table has an ID column (autoincrement int) as the PK, and is also indexed on this storeid (int) column.

Is there some "duh, just do this..." type of MySQL feature that I don't know about that would make my life easy? Otherwise I figure I have to break this up into a LOT of smaller queries to prevent locking the table from write, and make things more manageable in terms of saving the data. I figure the way to do this is by (storeid=X) and (id >= Y) and (id <= Z) and use Y and Z as sort of a sliding window of row numbers.

If it matters, each row contains:
- 5 ints
- 3 varchar(64)
- 2 char(36)

Is there some magic to help with this?

How Switching To InnoDB Helped Us Bust Through MyISAM Walls (no replies)

Edwin Desouza — Wed, 22 Feb 2017 06:05:26 +0000

http://dyn.com/blog/switching-to-innodb-mysqls-myisam-engine-walls/