New answers tagged myisam
2
Right now, you are in a very fortunate position. I noticed you have big-tables defined. This is preventing you from experiencing "Table is Full" errors. Why is this good?
Whenever you get "Repair With Keycache" as a status, you have no free space to do file sorting. Making sort_buffer_size bigger isn't necessarily the answer since temp tables become disk ...
1
Finally I've been able to figure out the problem, yet I still don't know why it happenned!
I've found this answer.
Doing ALTER TABLE acs_spectotechnologies_com_enerconcept_0005.measuresHistory ENABLE KEYS; fixed the usage of keys for this table, now every requests are lightning fast!
0
But the link also recommends XtraBackup from Percona, which is faster, no locks on tables that are already in use and gains no time for restoration. For 500GB of data I prefer Percona XtraBackup to be quick and efficient unless you want to convert them to innodb_file_per_table model if it wasn't from the existing DB server. Below link explains on how to ...
1
According to the MySQL Documentation on myisam_repair_threads
If this value is greater than 1, MyISAM table indexes are created in
parallel (each index in its own thread) during the Repair by sorting
process. The default value is 1.
Note Multi-threaded repair is still beta-quality code.
As it says, it is in beta. Allowing for setting the ...
1
You mentioned before you are running
MySQL 5.5.29
16 GB RAM
Here are some good settings to start with
innodb_buffer_pool_size=4G
innodb_buffer_pool_instance=2
innodb_read_io_threads=12
innodb_write_io_threads=12
innodb_io_capacity=300
innodb_log_file_size=128M
Don't do the conversion yet !!!
Put there settings in my.cnf, then do the following
...
0
No, not at all.
Every time, I see a temp table materialize on disk, I see something like this:
-rw-rw---- 1 mysql mysql 0 May 8 17:44 #sql_22d5_2.MYD
-rw-rw---- 1 mysql mysql 1024 May 8 17:44 #sql_22d5_2.MYI
.MYI is always the default size.
On disk, they must be using the MEMORY storage engine. Again, there are no indexes created in conjunction ...
1
SUGGESTION
Your key_buffer_size is only 16MB, but your MyISAM indexes total 59MB. In a highly-trafficked website, I can see index pages getting kicked out of and pushed into the MyISAM keycache frequently. This could have an affect on indexes being updated and overall write performance on the MyISAM tables.
You could just bump up key_buffer_size to 64 MB.
...
1
What concerns me fact that fieldC is not the lead column in the PRIMARY KEY.
What would be preferable is to reverse the order to the primary key columns
CREATE TABLE `my_table` (
`id` int(10) unsigned NOT NULL,
`fieldA` char(40) NOT NULL,
`fieldB` char(40) NOT NULL,
`fieldC` char(32) DEFAULT NULL,
-- some other fields
PRIMARY KEY (`fieldC`,`id`)
-- ...
1
At present, only MyISAM permits auto_increment values in a composite key. I wrote about this over a year ago : How can you have two auto-incremental columns in one table?
According to the Book
Section 5.7 Page 89 Paragraph 1 states the following:
The MyISAM Storage Engine supports composite indexes that include an AUTO_INCREMENT column. This allows ...
0
Antivirus or "Non-Conventional" Backup software has also often caused corruption.
Your first step is to try to find out what is changing the MySQL files.
Countinuous table repairing can only be a temporary workaround.
0
Since it is a MyISAM table you are loading, please be aware of three(3) aspects of bulk loading:
ASPECT #1 : Bulk Insert Buffer
What is the bulk insert buffer? According to the MySQL Documentation
MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when ...
2
OPTIMIZE TABLE basically does three(3) things
Shrinks the data pages
Shrinks index pages
Computes Fresh Index Statistics
Conceptually, OPTIMIZE TABLE operates something like this on mydb.mytable
USE mydb
CREATE TABLE mytabletmp LIKE mytable;
INSERT INTO mytabletmp SELECT * FROM mytable;
ALTER TABLE mytable RENAME mytablezap;
ALTER TABLE mytabletmp ...
1
When you issue service mysql stop, a lot more happens than just cutting off DB Connectivity. The link in the comment from @ethrbunny already explains what things happens.
I would like to focus on one particular aspect: The InnoDB Buffer Pool. InnoDB has to flush the InnoDB Buffer Pool's dirty pages. If you want to know how much, run this before shutdown:
...
Top 50 recent answers are included
