I'm trying to create a new partition (LIST) on an existing table (innodb) with 30million records. the query is running almost 2 hours and it still on "copy to tmp table" state. I have used the percona mysql performance wizard to improve and I don't see any differences. The server is with no traffic at all. Running on Ubuntu server with 16cores, 30GB memory and SSD 300/3000 IO. it looks like the mysql is not using all resources. the memory usage is on 9GB and only 3 cores are running on very low load. Is there a way to improve setting to use more resources and speed up the query?
First of all, PARTITION BY LIST is virtually useless. Why do you think it might be worth doing?
Let's see SHOW CREATE TABLE. If there are a lot of secondary indexes, that could be the issue.
How big is innodb_buffer_pool_size? Sounds like it is not as big as it should be. (Recommend about 70% of available RAM.)
Let's see the SQL that is taking so long. There may be something subtle in it.