You are here

Home » Tools

Performance Tuning Key for MySQL

This MySQL Performance Tuning Key should give you a guide how to best tune you MySQL database systematically... It should also work similar for other RDBMS.

Also check our MySQL Performance Monitor

For a database configuration tuning only please look first at our MySQL database health check.

If this MySQL Database Health Check does NOT solve your problem our specialized Performance Tuning and Architecture Consultants can help you for sure!

Caution: Some recommendations are dangerous! Dangerous means you can loose or get inconsistent data in certain cases. Only use them if you know what you are doing!!!

Acknowledgement

Thanks to the following people for hints:
  • Jens Bollmann

Efficiency of Performance Tuning measurements

Before you start tuning you should think about the following graph:

Tuning Efficiency

And see also Relative Impact on Performance (p. 33 ff.)

Start

(last updated 2010-10-03)

000. Do you have performance problems?

  • Yes: --> 001
  • No : Do not tune your system it only can get worse! --> back

001. Have you ever tuned your system?

002. You have already started to tune your system

Chose the area you want to tune (the following order is recommended):

  • Architecture & Design Tuning --> 100
  • SQL Query Tuning --> 200
  • Application Tuning --> 600
  • Server Tuning --> 300
  • Operating System Tuning --> 400
  • Hardware Tuning --> 500

003. You have not yet tuned your system

You can choose now between 3 tuning paths:

  • the more systematical --> 004
  • the more profitable --> 010.
  • the quick shot --> 050.

004. You choose the more systematical path

  • go through 100 ff. and then start from the beginning of this tuning key.

010. You choose the more profitable path.

Have you enabled the slow query log?

011. You did not yet enable the slow query log

Enable the slow query log with a long query time of for example 10 and then restart the server.
Then wait for a appropriate period of time which represents your typical business (for example 1 day, or 1 week or 1 month).

012. You have enabled the slow query log

Did you get any slow queries?

013. You have enabled the slow query log but no queries in it

Check if everything works fine by provoking a slow query (for example cartesian product: SELECT a.*, b.*, c.* FROM table a, table b, table c);
Have you now some queries in it?

014. Slow query log works fine but now queries in it

Decrease long query time to for example 5, then 3, then 2 and then 1 second. Have you now some queries in it?

015. Slow query log works fine but now queries running longer than 1 second in it

Is the system performance acceptable for you now?

020. You got some queries in the slow query log

Is the amount of slow queries huge (for example > 20-50)?

021. You got a huge amount of queries in the slow query log

Treat the slow query log with the following command: # mysqldumpslow -s t slow.log > slow.profile For windows users: You need perl and this script from a MySQL UNIX package. Pick the first query look for its original in the slow.log and take the query from the slow log!!!

050. You choose the quick shot path

Is your system read heavy (for example com_select / (com_insert* + com_replace* + com_update* + com_delete*) > 4)?

051. Your system is read heavy

Enable query cache. Does this help?

  • Yes: --> 000
  • No: Disable query cache again. --> 052

052. Your system is not read heavy

Do you use InnoDB tables only?

053. You use InnoDB tables only

Increase innodb_buffer_pool_size according your memory up to 80% of your RAM. Consider that system is NEVER swapping!!!

054. Do you use MyISAM tables only?

055. You use MyISAM tables only

Increase key_buffer_size according your memory up to 25-33% of your RAM. Consider that system is NEVER swapping!!!

056. You use MyISAM and InnoDB tables mixed

Split up to 80% of you RAM according the amount of your MyISAM and InnoDB data. Consider that system is NEVER swapping!!!

100. Architecture & Design Tuning

  • Index tuning --> 150
  • Data type tuning --> 160
  • Table Design tuning --> 170
  • Storage engines --> 101
  • Character sets
  • Concurrent inserts locking --> MyISAM
  • Replication --> 190
  • Architecture --> 140

101. Do you use MyISAM tables?

102. Do you use InnoDB tables?

103. Do you use other storage engine tables?

110. You use MyISAM tables

Do you use FULLTEXT indexing?

111. You use MyISAM tables and NO FULLTEXT indexing

AVG_ROW_LENGTH, MAX_ROWS, PACK_KEYS, ROW_FORMAT, myisam_data_pointer_size, myisampack

130. You use other storage engines tables

140. General database architecture tuning

Do you think your application can run without the use of a RDBMS?

  • No: --> 141
  • Yes: Try to avoid RDBMS if you do not really need it. There are some much faster solutions around! --> 000

141. Architecture tuning

The following items can help:

  • Application on same/different servers
  • MySQL Partitions (new with 5.1)
  • Replication --> 190
  • Physical partitioning of the data into different nodes.
  • MySQL Cluster
  • Upgrade to newer releases.

150. Indexing

Do you have fully redundant indices?

  • Yes: --> remove them --> 151
  • No: --> 151

151. No: more fully redundant Indexes

Do you have partially redundant indexes?

  • Yes: --> try to remove them --> 152
  • No: --> 152

152. No: more fully redundant Indexes and optimised partially redundant indexes

Do you have indices with attributes in different order?

  • Yes: --> try to reorder attributes and remove the (partial) redundant indices --> 153
  • No: --> 153

153. Indexes cleaned up

Some more hints for indexing

  • WHERE clause
  • Aggregation functions
  • ORDER BY clause
  • JOIN operations
  • High selectivity (> 10)
  • Low selectivity (< 10)
  • small tables
  • covering indexes
  • prefixed indexes
  • pk with myisam
  • cardinality

160. Data type tuning

  • Use smallest INT type (TINYINT < SMALLINT < MEDIUMINT < INT < BIGINT)
  • Use FLOAT instead of DOUBLE
  • Use VARCHAR instead of CHAR (not always better!)
  • Use BIT for status

170. Table design tuning

  • Remove not needed attributes.
  • Remove or move old/not used rows to other tables
  • For MyISAM: AVG_ROW_LENGTH, MAX_ROWS, PACK_KEYS, ROW_FORMAT, myisam_data_pointer_size, myisampack

180. You use MyISAM tables with FULLTEXT index

For large datasets, it is much faster to load your data into a table that has no FULLTEXT index and then create the index after that, than to load data into a table that has an existing FULLTEXT index.

190. Replication tuning

Does the slave fall often/much behind the master?

  • Yes: --> 191
  • No: You probably do not have a Replication Performance problem. --> End

191. Your slave is falling often/much behind the master

Which thread is the slow one. The IO_thread or the SQL_thread?

You can find this out by comparing the File/Position of SHOW MASTER STATUS and the Master_Log_File/Read_Master_Log_Pos (which reflects the Position of the IO_thread) and Relay_Master_Log_File/Exec_Master_Log_Pos (which reflects the Position of the SQL_thread) of SHOW SLAVE STATUS.

Typically it is the SQL_thread who makes you lagging

  • IO_thread --> 192
  • SQL_thread --> 197
  • None of these: Please contact us to get more help. --> End

192. Your slave is falling behind the master because of the IO_thread

This can happen because of several reasons:

  • Too many Slaves (more than 20 - 40 Slaves) --> 193
  • Problems with the Network --> 194
  • Too much data -->195

193. Too many Slaves

If you have too many Slaves (more than 20 - 40 Slaves per Master) this could be the issue. Try to use cascaded replication.

  • Cascaded Replication: Please contact us to get more help. --> End

194. Problems with the Network

Check if you have problems with the network between your Slave and your Master (ifconfig, ping, etc.)

  • Problems found and solved and lagging disappeared? --> End
  • Too much data -->195

195. Too much data

If you transfer too much data this could also be the issue. Are you using Row-Based-Replication (RBR) or Statement-Based-Replication (SBR)?

  • Increase Network throughput --> 196
  • Row-Based-Replication (RBR) --> Try SBR.
  • Statement-Based-Repilcation (SBR): We have to investigate more in detail. Please contact us to get more help. --> End
  • Try to reduce the amount of data: Please contact us to get more help. --> End
  • Try to shared your data: Please contact us to get more help. --> End

197. Your slave is falling behind the master because of the SQL_thread

This is more often the case than the IO_thread is lagging. It comes because of severl reasons:

  • Missing Primary Keys when using Row-Based-Replication --> 000
  • Genrally badly tuned queries --> 000
  • Worse configuration of Slave than Master --> 000
  • Weaker Slave than Master --> 000
  • Single-Threaded nature of Slave --> 000

200. SQL Query Tuning

  • SELECT --> 201
  • ORDER BY --> 240
  • GROUP BY --> 250
  • INSERT/UPDATE/DELETE --> 220
  • When you think your queries are perfect --> 300

201. SELECT

--> Chap. 7.2

220. INSERT/UPDATE/DELETE

240 ORDER BY 7.2.12 Optimisation

  • --> filesort
  • Index
  • increase sort_buffer_size
  • increase read_rnd_buffer_size
  • point tmpdir to a dedicated file system

241. Is the query really needed like this?

242. Change the query

243. Check if index is used for ORDER BY (Using filesort if NOT)

  • --> Index is used (NO filesort) --> 245
  • --> Index is NOT used (filesort) --> 244

244. Try to find a index matching ORDER BY

  • --> Go to 245

245. Try to (regularly) store the rows in sorted order (ALTER TABLE ... ORDER BY ...)

  • --> Go to 246

246. Try to increase sort_buffer_size for this query

  • --> Go to 247

247. Try to increase read_rnd_buffer_size

  • --> Go to 248

248. Move tmpdir to other Disk

250 GROUP BY 7.2.13

251. Is the query really needed like this?

252. Change the query

253. Check if index is used for GROUP BY (all attributes from the same index in order, NO temporary)?

  • --> Index is used (NO temporary) --> 255
  • --> Index is NOT used (Using temporary) --> 254

254. Try to find a index matching GROUP BY or do GROUP BY in the same order than SORT BY

  • --> Go to 255

255. GROUP BY sort according expression. Do you really need a sorting?

  • --> YES --> 257
  • --> NO --> 256

256. Try to add ORDER BY NULL to your statement

257. Try to (regularly) store the rows in sorted order (ALTER TABLE ... ORDER BY ...)

  • --> Go to 258

258. When query is still to slow, try to

  • Optimise table and/or data structure (compression, static row format, exclude columns, exclude old rows, data types)
  • Create your own aggregation table
  • Partition horizontally or vertically

300. Server Tuning

http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html SHOW STATUS analysis

301. Is your MySQL server already running a reasonable amount of time (uptime, 1 period of usual business (for example 1 day or 1 week))?

  • YES --> 302
  • NO --> Wait until then. In the meantime you can enable the slow query log --> Slow query log.

302. What storage engine are you using?

  • Mixed --> 303
  • MyISAM only --> 320
  • InnoDB only --> 330

303. You are using mixed storage engines, general

 parameters

304. Connections

  • Connections
  • Aborted_clients
  • Aborted_connects
  • Threads_cached
  • Threads_connected
  • Threads_created increasing --> increase thread_cache_size
  • Threads_running
  • Max_used_connections

305. Throughput

  • Bytes_received
  • Bytes_sent
  • Questions

306. Slow query log enabled?

  • No: --> Enable slow query log first and then come back.
  • Yes: -->

307. Check for slow queries. Do you have slow queries?

 Slow_queries

  • No: -->
  • Yes: --> Go to analysing and optimising queries and then come back here.

308. IO contention:

  • Table_locks_immediate
  • Table_locks_waited

308a. IO contention:

    Have you binary logging enabled?

  • No: --> 309
  • Yes: --> 308b

308b. IO contention:

    Do you need binary logging?

  • No: --> Disable binary logging. --> 000
  • Yes: --> 308c

308c. IO contention:

    Is Binlog_cache_disk_use greater than 0?

  • No: --> 309
  • Yes: --> Increase binlog_cache_size --> 000

309. Status about temporary resources

    Do you see high created_tmp_disk_tables values (high: Created_tmp_disk_tables / Created_tmp_tables > 5%)?

  • Yes: --> increase tmp_table_size or optimise queries --> 309a
  • No: --> 309a
  • Created_tmp_files

309a. Status about temporary resources (Created_tmp_files)

  • Created_tmp_files

310. Files, tables, streams

  • Open_files
  • Open_streams
  • If Open_tables is close to table_cache or Opened_tables increasing increase table_cache.

310a. Table .frm file

    Can you afford to lose some tables/get corrupt table after instance crash?

  • Yes: --> disable syn_frm -->
  • No: -->

311. select

  • Select_full_join --> Should be zero
  • Select_full_range_join --> only critical when high
  • Select_range
  • Select_range_check
  • Select_scan

312. sort

  • Sort_merge_passes --> high then increase sort_buffer_size
  • Sort_range
  • Sort_rows
  • Sort_scan

313. Are you using the MySQL query cache?

  • Yes: -->
  • No: -->
  • Qcache_free_blocks
  • Qcache_free_memory
  • Qcache_hits
  • Qcache_inserts
  • Qcache_lowmem_prunes

314. Qcache_not_cached high even free_memory available?

  • --> YES 5.14.1 Try to avoid queries which are not cachable...
  • --> NO
  • Qcache_queries_in_cache
  • Qcache_total_blocks
  • --> flush query cache
Qcache_hits / (Qcache_hits + Com_select) * 100 = Percentage of SELECT queries which can be cached. Qcache_not_cached / (Qcache_not_cached + Qcache_inserts) * 100 = Not cachable queries. (Com_select + Qcache_hits)/(Com_insert* + Com_update* + Com_delete* + Com_replace*) = Read/Write ratio Qcache_hits / Qcache_inserts = Cache hit ratio should be large (> 3)

316. delayed inserts

  • Delayed_errors
  • Delayed_insert_threads
  • Delayed_writes
  • Not_flushed_delayed_rows

320. You are using pure MyISAM
  • If you have a small database decrease myisam_data_pointer_size
  • If you do a lot of OUTER JOINS try to change myisam_stats_method
  • If Key_blocks_unused is ALWAYS high decrease key_buffer_size and use resources somewhere else.
  • If Key_blocks_used * key_cache_block_size is close to key_buffer_size increase key_buffer_size.
  • If Key_blocks_unused is near to 0 increase key_buffer_size.
  • If Key_read_requests/Key_reads is less than 100 increase key_buffer_size.

InnoDB tuning

InnoDB application tuning

330. PRIMARY KEY tuning

    Are you updating your PRIMARY KEY's?

  • No: --> 330a
  • Yes: Updating PK's is expensive. Try to avoid this. --> 330a

330a. Data load tuning

    Do you have performance problems with your data load?

  • No: --> 345
  • Yes: --> 331

331. Data load tuning for InnoDB tables

    Are you dropping indexes on InnoDB tables before loading?

  • No: --> 332
  • Yes: Load on InnoDB table is best done on table with indexes (not like MyISAM). --> 332

332. Data load tuning for InnoDB tables

    Are your rows sorted in PRIMARY KEY order?

  • Yes: --> 333
  • No: Importing is fastest if the rows are presorted in the PRIMARY KEY order. Try to sort them before loading either on OS level or dumping with ORDER BY. --> 333

333. Data load transaction tuning

    Have you AUTOCOMMIT enabled or are you committing after each DML statement?

  • No: --> 334
  • Yes: Consider to pool some or all statements into one transaction (one single disk cannot do more than 250 trx/s!) with START TRANSACTION and COMMIT. Disable AUTOCOMMIT. --> 334

334. Data load INSERT tuning

    Are you using multi row INSERT syntax?

  • Yes: --> 335
  • No: Use multi row INSERT syntax if possible. --> 335

335. Data load KEY check tuning

    Do you have UNIQUE KEYs or FOREIGN KEYs on your table?

  • No: --> 336
  • Yes: Disable UNIQUE_CHECKS and/or FOREIGN_KEY_CHECKS during import. --> 336

336. Data load InnoDB variable tuning

    Can you stop and restart your database server before/after data load?

  • No: --> 338
  • Yes: Make innodb_buffer_pool_size and innodb_log_file_size bigger during import. --> 337

337. Data load InnoDB variable tuning

    Can you manually roll back or restart your data load when system crashes during data load?

  • No: --> 338
  • Yes: Consider setting innodb_flush_log_at_trx_commit != 1 during load--> 338

338. Data load with LAST_INSERT_ID

    Are you using LAST_INSERT_ID in your data load transactions?

  • No: --> 339
  • Yes: Try to avoid LAST_INSERT_ID during data load. --> 339

339. Data load ROLLBACK or crash tuning

    Can you afford to wait for hours when transaction must be rolled back or database crashes during data load?

  • Yes: --> 340
  • No: Beware of huge ROLLBACK's (happens also after crash). This can take hours. Make smaller pieces of your transaction. --> 340

340. Delete rows from table during data load

    Do you have to delete all rows from a table?

  • No: --> 345
  • Yes: Use TRUNCATE table instead of DELETE. Disable FOREIGN KEY constraints before if necessary. --> 345

InnoDB Table Design

345. PRIMARY KEY tuning

    Do you have "long" PRIMARY KEY's (PK)?

  • No: --> 346
  • Yes: In InnoDB you should only use short PRIMARY KEY's (rule of thumb: <= 20 byte). Best is AUTO_INCREMENT [TINY|SMALL|MEDIUM]INT --> 346

346. InnoDB index tuning

    Have you indices on long CHAR or VARCHAR attributes?

  • No: --> 347
  • Yes: Try to avoid indices on long CHAR or VARCHAR attributes. Consider prefixed indices. --> 347

347. InnoDB Index tuning

    Have you defined your tables with character set = utf8?

  • No: --> 348
  • Yes: Because utf8 uses more space (3 bytes) for indices and CHAR attributes you should only define your columns with utf8 when you really need it! --> 348

348. CHAR tuning

    Do you use CHAR attributes with varying field length or utf8 character set?

  • No: --> 350
  • Yes: Choose VARCHAR instead of CHAR if you have varying field length or if you use utf8 character set. --> 350

InnoDB parameter tuning

350. Transactions and full ACID compliancy

    Do you need transactions and/or full ACID compliancy?

  • Yes: --> 355
  • No: --> 351

351. Transactions and full ACID compliancy

    Have you already considered to use MyISAM?

  • Yes: --> 352
  • No: --> 100

352. You do NOT need transactions or full ACID compliancy

    Can you afford to loose little amount of data in case of crash?

  • No: --> 355
  • Yes: Set flush_log_at_trx_commit = 2 --> 353

353. InnoDB double write tuning

    Can you risk to get corrupted data after system crash?

  • No: --> 355
  • Yes: disable innodb_doublewrite --> 355

355. You NEED transaction and/or full ACID compliancy

    Do you need distributed transactions (XA) feature (for example for replication)?

  • Yes: --> 356
  • No: set innodb_support_xa = 0 --> 356

356. InnoDB buffer pool tuning

    Do you have a lot of IO activity or is Innodb_buffer_pool_pages_free = 0 and do you have enough free memory (RAM)?

  • No: --> 357
  • Yes: --> Increase innodb_buffer_pool_size up to 50-80% of your memory (less than 1800 Mbyte on 32-bit systems) if innodb_buffer_pool_pages_free is 0. Avoid swapping. --> 357

357. InnoDB additional memory pool tuning

    Have you errors in the MySQL error log concerned with innodb_additional_mem_pool?

  • No: --> Do not touch this parameter --> 358
  • Yes: --> Innodb_additional_mem_pool is increased dynamically by MySQL. Increase this value only moderately if you have error messages in your MySQL error log --> 358

358. InnoDB log file size tuning

    Do you have a lot of I/O activity on your disk system where your InnoDB log file resides?

  • No: --> 359
  • Yes: --> Increase your innodb_log_file_size up to (innodb_buffer_pool_size / innodb_log_files_in_group) but not more than (4 Gbyte / innodb_log_files_in_group). Larger log files means longer recovery time! 359

359. InnoDB thread concurrency tuning

    Do you have more than 4 CPU's/cores (without hyper threading) or many connections?

  • No: --> 359a
  • Yes: Try to change innodb_thread_concurrency. A good point to start is: (#CPU + #Disks). Other recommendations are: 4, (#Disks * #CPU * 2) or (#CPU * 2). This parameter is very system and OS dependent you have to play around with it and benchmark your system! --> 359a

359a. InnoDB max dirty pages tuning

    Do you have several little peaks a day you want to break?

  • No: --> 359b
  • Yes: decrease innodb_max_dirty_pages_pct to 80-50% or even lower during peak time --> 359b

359b. InnoDB log buffer size tuning

    Do you have (high) Innodb_log_waits?

  • No: --> 359c
  • Yes: Increase innodb_log_buffer_size. --> 359c

359c. InnoDB flush log tuning

    Do you have IO contention?

  • No: --> 359f
  • Yes: --> 359d

359d. InnoDB flush log tuning

    Do you have have battery cache disk buffers?

  • No: --> 359e
  • Yes: set innodb_flush_log_at_trx_commit = 2 and test very good if your disk cache batteries really work! -->359e

359e. InnoDB flush log tuning

    Can you afford to lose up to 1 second of your data?

  • No: --> 359f
  • Yes: set innodb_flush_log_at_trx_commit = 0 -->359f

359f. InnoDB checksum tuning

    Can you risk to be not aware of physical data corruption?

  • No: --> 359g
  • Yes: disable innodb_checksums --> 359g

359g. InnoDB flush method tuning

    Have you ever tried to change innodb_flush_method?

  • Yes: --> 359i
  • No: Try innodb_flush_method fsync, O_DSYNC or O_DIRECT for Linux and BSD (Solaris) --> 359i

359i InnoDB binlog tuning

    Can you afford to loose some data in the binlog in case of database crash?

  • No: --> 359j
  • Yes: Disable innodb_safe_binlog and/or sync_binlog --> 359j

InnoDB data storage tuning

359j. Rebuilding InnoDB tables

    Are you doing a lot of DML statements on your InnoDB table?

  • No: --> 359k
  • Yes: --> Rebuilding the table will remove fragmentation and reduces page splits. 359k

359k. Separate InnoDB log files from InnoDB tablespace files

    Do you have high I/O on your disk and several disks in your box?

  • No: --> 359l
  • Yes: Separate your InnoDB tablespace files from your InnoDB log files (on different physical disks) if you do not have "stripe everything everywhere" disk architecture. --> 359l

359l. InnoDB tablespace tuning

    Do you have per-file tablespaces?

  • Yes: --> 359m
  • No: In MySQL 4.1, using per-file tables increases performance. --> 359m

359m. InnoDB tablespace tuning with innodb_file_per_table

    Do you have high I/O on disks where your InnoDB tablespace files reside?

  • No: --> 359n
  • Yes: Try to separate your InnoDB table with the hot spot on separate physical disk if you do not have "stripe everything everywhere" disk architecture --> 359n

InnoDB hardware and OS tuning

359n. I/O system access tuning

    Are your InnoDB tablespace file or log files stored on normal file systems?

  • No: --> 359o
  • Yes: Try to use raw devices. This should only be done as last resort. --> 359o

359o. CPU tuning

    Have your CPU's hyper threading mode?

  • No: --> 359p
  • Yes: Disable hyper threading. It helps sometimes. --> 359p

359p. Battery supplied disk cache

    Has your I/O system battery supplied disk cache?

  • No: --> 100
  • Yes: Consider to set innodb_flush_log_at_trx_commit = 2. --> 100

400. Operating System Tuning
  • Avoid old Windows and old BSD kernels. Use mainstream OS like Linux, Solaris, Windows.
  • Use recent 2.6 Linux kernels.
  • Some file systems do not perform optimal.
  • Use recent file systems.
  • Use NPTL thread library.

File System tuning

410. File System Tuning

    Have you mounted your File System with noatime and nodiratime?

  • Yes:
  • No:

420. I/O Scheduler

    The default I/O scheduler may not be optimal for database workloads. If you are I/O limited, try an other I/O scheduler.

    cat /sys/block/<device>/queue/scheduler

    noop and deadline often give better results than cfq or anticipatory.

  • Yes:
  • No:

500. Hardware Tuning

    Are you using hyper threading on your system?

  • Yes: --> 501
  • No: --> 502

501. You are using hyper threading

    Disable hyper threading. Does it help?

  • Yes: --> 502
  • No: --> 502

502. Disabling hyper threading did not help

    Are you using 64-bit Architecture?

  • Yes: --> End
  • No: --> 503

503. You are using 32-bit Architecture

64-bit architectures sometimes performs better. Try using a 64-bit architecture. End

600. Application tuning

    Do you have data loads which are to slow?

  • Yes: --> 650
  • No: --> 601

601. Application tuning other problems

Do you have backup/restore tuning problems?

602. Application tuning other problems

Not yet implemented. Sorry!

640. Backup/restore tuning

650. Data load tuning

  • General hints --> 651
  • MyISAM tables --> 655
  • FULLTEXT index --> 660
  • InnoDB tables --> 330

651. General hints for data load tuning

  • Use LOAD DATA INFILE (Section 13.2.5, “LOAD DATA INFILE Syntax”)
  • Use multiple-row INSERT syntax (7.2.16. Speed of INSERT Statements)
  • Temporarily turn off the uniqueness checks during the import session
  • Wrap load with table locks
  • Use INSERT DELAYED
  • Avoid SELECT FROM LAST_INSERT_ID

655. Data load tuning for MyISAM tables

  • Increase key_buffer_size

660. Data load tuning for MyISAM tables with FULLTEXT indices

For large datasets, it is much faster to load your data into a table that has no FULLTEXT index and then create the index after that, than to load data into a table that has an existing FULLTEXT index.

FromDual Performance Tuning Consulting

If all those measures did NOT help you to solve your MySQL Performance Tuning problems you really need some external help.

Please get in contact with us and we are glad to help you!

We provide MySQL Consulting services, remote and on-site and we offer you our Remote-DBA services if you do not have your own MySQL DBA or lack of DBA resources...

tags: 
mysql
performance
tuning