I have quite an annoying problem. I want to use INNODB as my main database engine and give up on MyISAM as I need the former for using galera-cluster for redundancy.
I copied (description follows) the newbb_post table to a new table called newbb_innopost and changed that to InnoDB. The tables currently hold 5,390,146 entries each.
Running these selects on a freshly started database (so no caching is involved at this point!) the database yields the following results (omitting the complete output, please note that I do not even ask the database to sort the results):
0.13 seconds to 86.19 seconds (!)
I am wondering why this is happening. I did read some answers here on Stackexchange involving InnoDB and some are suggesting increasing innodb_buffer_pool size to 80% of installed RAM. This won't solve the problem, that the initial query to a particular ID will take at least 50x longer and stalling the whole websever, queueing up connections and queries for the database. Afterwards the cache/buffer might kick in, but there are over 100.000 threads in this database, so it is very likely that the cache will never hold all the relevant queries to be served.
The queries above are simple (no joins), and all keys are used:
This is the MyISAM-Table:
and this is the InnoDB Table (it's exactly the same):
Server, with 32GB RAM:
If you need all of the innodb_ variables setting, I can attach that to this post.
Update:
I dropped ALL indexes apart from the primary index, afterwards the result looked like this:
After this I just added one index back to the mix, threadid, the results were the following:
Strange it is, that without any relevant indexes, the full scan only took 29 seconds compared to the 88 seconds using indexes (!).
With only one perfectly tailored index it is still taking 11 seconds by to complete - still far too slow for any real world usage.
Update 2:
I setup MySQL (5.5.38-0ubuntu0.14.04.1 (Ubuntu)) on another server with the exact same hardware configuration and exactly the same database/tables.
The results are nearly the same, first the MyISAM Table:
And this is the result of the InnoDB table
UPDATE 3:
the contents of my.cnf
And the contents of the inno variables:
The number of cores of the machine is 8, it's a
Intel(R) Xeon(R) CPU E3-1246 v3 @ 3.50GHz as of /proc/cpuinfo
One last note: Ran the queries with the indexes suggested by RolandoMYSQLDBA, and the queries took about 11-20s each.
I do want to point out that it is crucial for me (this is the main table of a bulletin board) that the first query about a threadid returns in less than a second, as there are more than 60.000 threads and google-bots constantly crawl these threads.