MySQL Forums
Forum List  »  Performance

Re: Too much time in Copy to Tmp Table
Posted by: Rick James
Date: October 08, 2015 07:21PM

> table_open_cache = 175000

No. Unless you really have 175K active tables. Instead use, say, 1000.

> tmp_table_size=2G
> max_heap_table_size=2G

Dangerous. If several connections need large temp tables, you could run out of RAM. Go back to, say, 64M for both.

> I am using a 3GB tempfs for RAM Disk

Dangerous: When a query needs more than that, it will crash. Better to not have a RAM disk, and make the 3GB available for other caching.

> innodb_buffer_pool_size = 36G

Good for 48GB of RAM.

> query_cache_size=0 and quary_cache_type=0

Usually good.

Swapping is really bad for performance; most of the above notes help prevent such.

IN ( SELECT ... ) performs very badly. Try to turn it into a JOIN.

Options: ReplyQuote

Written By
Re: Too much time in Copy to Tmp Table
October 08, 2015 07:21PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.