Can you provide some objective data? Logs, your testing/benchmarking methodology and results, that sort of thing?
–
wombleJul 10 '12 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.
–
Ronn0Jul 10 '12 at 9:52

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 #5

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:

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!
–
Ronn0Jul 10 '12 at 12:20

2

query_cache_size is enormous, too. Every insert will require up to 6GB of cache to be flushed.
–
Aaron BrownJul 10 '12 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.
–
RolandoMySQLDBAJul 10 '12 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.
–
Aaron BrownJul 10 '12 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.
–
Gavin ToweyJul 11 '12 at 6:02

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.