8

Inserts into the following table are taking up to 70 seconds to complete:

CREATE TABLE IF NOT EXISTS `productsCategories` ( `categoriesId` int(11) NOT NULL, `productsId` int(11) NOT NULL, PRIMARY KEY (`categoriesId`,`productsId`), KEY `categoriesId` (`categoriesId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

There are around 100,000 rows in the table, and it is taking 7MB on disk.

Are there some settings in MySQL which can improve write performance?

My my.cnf file is as follows:

log-slow-queries="/var/log/mysql/slow-query.log" long_query_time=1 log-queries-not-using-indexes innodb_buffer_pool_size=4G innodb_log_buffer_size=4M innodb_flush_log_at_trx_commit=2 innodb_thread_concurrency=8 innodb_flush_method=O_DIRECT query_cache_size = 6G key_buffer_size = 284M query_cache_limit = 1024M thread_cache_size = 128 table_cache = 12800 sort_buffer_size=2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M read_buffer_size=128K open_files_limit = 1000 table_definition_cache = 1024 table_open_cache = 6000 max_heap_table_size=512M tmp_table_size=4096M max_connections=1000 thread_concurrency = 24

Here is the hardware setup:

  • Dell R710
  • RAID10
  • 48G RAM

Given this hardware, I wouldn't expect the problem to be a hardware bottleneck.

Improve this question
4
  • Can you provide some objective data? Logs, your testing/benchmarking methodology and results, that sort of thing? Commented Jul 10, 2012 at 9:50
  • What kind of logs would you like to see? The only thing i see why it takes so long is in de mtop. I'd test things as change the table to a myisam table, higher memory limits, higher threads. Commented Jul 10, 2012 at 9:52
  • The best setting to improve mysql... is to switch to postgres lol Commented Nov 9, 2017 at 18:16
  • thread_concurrency = 24 has no effect... you can dump that line Commented Nov 9, 2017 at 18:19

2 Answers 2

Reset to default
17

OBSERVATION #1

The very first thing that catches my eye is the table structure

CREATE TABLE IF NOT EXISTS `productsCategories` ( `categoriesId` int(11) NOT NULL, `productsId` int(11) NOT NULL, PRIMARY KEY (`categoriesId`,`productsId`), KEY `categoriesId` (`categoriesId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Please notice that the categoriesId index and the PRIMARY KEY start with the same column. It is a redundant index. Since this table is InnoDB, the categoriesId index is redundant for another reason: All secondary indexes contains keys into gen_clust_index (aka Clustered Index; See what is gen_clust_index used for in mysql?)

If you remove the categoriesId index with

ALTER TABLE productsCategories DROP INDEX categoriesId;

this will improve INSERTs dramatically because of not having to do extra Secondary and Clustered index maintenance.

OBSERVATION #2

If you are doing any bulk insert operations, you need a large bulk insert buffer.

Please see my past posts on this:

OBSERVATION #3

Your log file size is way too small !!! It should be 25% of the InnoDB Buffer Pool, which in your case should be 1G. See my post on how to resize InnoDB Log Files.

OBSERVATION #4

Please, do not set innodb_thread_concurrency !!! I learned firsthand at Percona Live NYC to leave that setting alone. It is disabled by default in MySQL 5.5, MySQL 5.1 InnoDB Plugin, and Percona Server 5.1+.

OBSERVATION #5

You need to use innodb_file_per_table. If this is disabled, I make file maintenance on ibdata1 a nightmare. Please read my post on how to cleanup InnoDB to implement this.

OBSERVATION #6

If you are using MySQL 5.5 or Percona Server, you have to set certain options to make InnoDB use mutiple CPUs/multiple cores. Please see my post on those settings.

OBSERVATION #7

You have innodb_log_buffer_size=4M. The default is 8M. That will cause twice as much flushing to the redo logs. That will also counteract your innodb_flush_log_at_trx_commit=2setting. Please set it to 32M. Also, please see MySQL Documentation on innodb_log_buffer_size.

In light of these observations, please add or replace the following settings:

[mysqld] innodb_thread_concurrency = 0 innodb_read_io_threads = 64 innodb_write_io_threads = 64 innodb_io_capacity = 5000 innodb_file_per_table innodb_log_file_size=1G innodb_log_buffer_size=1G bulk_insert_buffer_size = 256M
Improve this answer
6
  • Wow, thanks! I did print all points and working on it now and save it in the brain to help other people in the future. Many thanks! Commented Jul 10, 2012 at 12:20
  • 2
    query_cache_size is enormous, too. Every insert will require up to 6GB of cache to be flushed. Commented Jul 10, 2012 at 12:26
  • @AaronBrown I agree. I read in the High Performance MySQL book that InnoDB does tedious things with transaction ID on InnoDB table in the query cache which make it kind of needless, not to mention slow, to use a query cache. In fact, MySQL 4.1 has the query cache disabled for InnoDB. Commented Jul 10, 2012 at 12:30
  • I think the query cache is completely separate from the storage engine. It basically serializes all write operations by forcing everything to go through and scan the query cache for queries to invalidate. I've never found a use case for it and it has always caused problems. Commented Jul 10, 2012 at 16:05
  • one more thing to add: query_cache_size = 6G <-- this is totally and utterly absurd. Query cache is often better disabled, and for sure shouldn't be lager than 32M or 64M. The overhead of maintaining a 6G query cache is certainly hurting performance. Commented Jul 11, 2012 at 6:02
0

You should check innodb_log_file_size, the default setting is 5M which is quite low for write intensive setups. Consider setting it to 100M. You will have to delete the old ib_logfile* files in order to start the DB with new settings. Please do not delete the log files while the DB server is running, you will have to stop it first. Probably you should backup the old log files first, not just delete them.

Improve this answer
0

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.