This sounds very unusual for a table using the ARCHIVE Storage Engine. Why? A duplicate key error is not characteristic for ARCHIVE Storage Engine since
- Engine does not support the creation of indexes
- Engine supports INSERTs and SELECTs
Surprisingly, there can be a key internally present. How?
According to the MySQL Documentation
The ARCHIVE engine supports the AUTO_INCREMENT column attribute. The AUTO_INCREMENT column can have either a unique or nonunique index. Attempting to create an index on any other column results in an error. The ARCHIVE engine also supports the AUTO_INCREMENT table option in CREATE TABLE and ALTER TABLE statements to specify the initial sequence value for a new table or reset the sequence value for an existing table, respectively.
Given this information, look back at the table and the query
insert into test_table (id,arch) values (123,'FILE_CONTENT')
If the id
column has the AUTO_INCREMENT
attribute, you should not specify id
with a value. It would produce a normal 1062 error (Duplicate Key) for other Storage Engines.
SUGGESTIONS
Change the insert
to a format that can handle the AUTO_INCREMENT attribute of id
insert into test_table (id,arch) values (0,'FILE_CONTENT')
or
insert into test_table (arch) values ('FILE_CONTENT')
Give it a Try !!!
UPDATE 2013-08-06 16:57 EST
If you are planning to do queries from the archive table, you need to get away from the ARCHIVE Storage Engine. Why? Again, according to the MySQL Documentation
Retrieval: On retrieval, rows are uncompressed on demand; there is no row cache. A SELECT operation performs a complete table scan: When a SELECT occurs, it finds out how many rows are currently available and reads that number of rows. SELECT is performed as a consistent read. Note that lots of SELECT statements during insertion can deteriorate the compression, unless only bulk or delayed inserts are used
Note that every SELECT against an ARCHIVE table is a full table scan. If you lookup id 123 with 1,000,000 rows, you gotta read 1,000,000 rows every time.
SUGGESTION
Convert the table to MyISAM. Then you can have a proper index on id
plus the ability to create other indexes on other columns as needed.
CREATE TABLE test_table_myisam ENGINE=MyISAM as SELECT * FROM test_table WHERE 1=2;
ALTER TABLE test_table_myisam ADD PRIMARY KEY (id);
INSERT IGNORE INTO test_table_myisam SELECT * FROM test_table;
DROP TABLE test_table;
ALTER TABLE test_table_myisam RENAME test_table;
Looking at the my.ini
, I have two suggestions
SUGGESTION #1
I would bump up the following settings in your my.ini
sort_buffer_size=4M
join_buffer_size=4M
This will make some joins and sort stay in memory. Of course, once a JOIN
or an ORDER BY
needs more than 4M
, it will page to disk as a MyISAM table.
If you cannot login as root@localhost
, then restart mysql with
C:\> net stop mysql
C:\> net start mysql
If you can login as root@localhost, you do not have to restart mysql to use these settings.
Just run this in the MySQL client:
SET @FourMegs = 1024 * 1024 * 4;
SET GLOBAL sort_buffer_size = @FourMegs;
SET GLOBAL join_buffer_size = @FourMegs;
SUGGESTION #2
Since your Data is on Drive D:
, you may have Disk I/O on Drive C:
.
Please run this query:
mysql> show variables like 'tmpdir';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tmpdir | C:\Windows\TEMP |
+---------------+-----------------+
1 row in set (0.00 sec)
Since I run mysql on my Desktop with defaults, my temp tables are being written to Drive C:
. If Drive D is a better disk than Drive C:
, perhaps you can map temp tables to Drive D:
by setting tmpdir in my.ini
as follows:
tmpdir="D:/DBs/"
You will have to restart mysql since tmpdir is not a dynamic variable.
Give it a Try !!!
UPDATE 2013-11-29 10:09 EST
SUGGESTION #3
Given the fact that MySQL is running in Windows and you cannot touch the queries in the core package, I have two ideas tat must be done together.
IDEA #1 : Move the Database to a Linux Machine
You should be able to
- Setup a Linux machine
- Install MySQL on the Linux machine
- Enable Binary Logging for MySQL in Windows
- mysqldump the database to a text SQL file
- Load SQL file to MySQL running in Linux
- Setup replication from MySQL/Windows to MySQL/Linux
IDEA #2 : Reconfigure Moodle to point to the Linux Machine
Moodle was designed for LAMP in the first place. Just change the config files to point to the Linux machine instead of localhost.
Here is a link to an old Moodle 2.3 doc on setting up MySQL : http://docs.moodle.org/23/en/Installing_Moodle#Create_an_empty_database
I am sure the latest docs are available as well.
What is the Point of Moving the Database to Linux ???
How does this help the temp table situation ???
I would then suggestion setting up a RAM disk as the target folder for your temp tables
Temp table creation will still happen, but it will be written to RAM rather than disk. reducing Disk I/O.
UPDATE 2013-11-29 11:24 EST
SUGGESTION #4
I would suggest revisiting SUGGESTION #2 with a fast RAID-0 disk (32+ GB), configuring it as Drive T: (T for Temp). After installing such a disk, add this to my.ini
:
[mysqld]
tmpdir="T:\"
MySQL restart would be required, using
net stop mysql
net start mysql
BTW I said RAID-0 on purpose so that you can get good write performance over a RAID-1, RAID-10. A tmp table disk is not something I would make redundant.
Without optimizing the queries as @RaymondNijland has been commenting on, you cannot reduce the temp table creation count in any way. SUGGESTION #3
and SUGGESTION #4
offer speeding up temp table creation and temp table I/O as the only alternative.
Best Answer
This is a very tricky question because of the internals of the ARCHIVE storage engine.
People have asked this same question in the MySQL Forums
Jan 04, 2014
: Huge and growing archive with ARN extensionJan 28, 2015
: large .arn file filling up whole hard driveWhat needs to be understood is the file layout of an ARCHIVE table:
.frm
: Every table in MySQL has a.frm
regardless of storage engine.ARZ
: Table data.ARM
: Table metadata.ARN
: Optimization FileLet's start with
.ARZ
. What's theZ
stand for ??? zlibWhy zlib ??? MySQL Documentation says
Some compression is happening to data that is being inserted. If your
.ARN
file is growing, it must be doing some compression related work on everyINSERT
in terms of encoding/encryption.Note the
zlib.net
Technical Details says under the subheadingMaximum Compression Factor
:Give the presence of
.ARN
that is growing, it must be a temp table used to determine how row data is compressed and then cached to disk. (ARCHIVE does not cache data in memory).At this point, your problem is at the compression layer of the storage engine.
WHAT TO DO NEXT
Run
FLUSH TABLES;
and copy the.frm
,.ARM
, and.ARZ
to another server that has the same OS server. Do not copy from Linux to Windows. Try runningREPAIR TABLE
there.If you still cannot read the data, you may have to do some deeper diving. You could try downloading archive_reader.c (from Twitter), compile it and try to read your data.
Godspeed, Spiderman !!! (My Disclaimer).
ONCE YOU HAVE RECOVERED YOUR DATA
SUGGESTION #1
Do not use the REPLACE command against an ARCHIVE table. Why ???
DELETE
is not supported for an ARCHIVE tableREPLACE
is supported for an ARCHIVE table, requires INSERT and DELETE privsIf ARCHIVE does not expose the
DELETE
operation, but allowsREPLACE
to secretly do so, this could impact performance greatly.SUGGESTION #2
SELECTs
andINSERTs
can peacefully coexist within an ARCHIVE table. Of course, the one and only exception would be if you insert a new row and SELECT that same row concurrently.SUGGESTION #3
Get a bigger data disk and a lot more RAM. This should give zlib a lot more head room for data compression in RAM before resorting to using the
.ARN
.