SELECT P, PStr, PFloat, UM, StartTime
FROM Pt
WHERE (StartTime >= 908995600)
AND (StartTime <= 909245578)
AND CId=703
ORDER BY StartTime ASC, CId ASC
LIMIT 0, 1000;
Because of the WHERE clause, this would run faster with
INDEX(CId, StartTime)
(Now we get into tradeoffs among indexes -- it is too costly to simply include all desirable indexes for such a big table.)
That index would speed this up slightly:
WHERE (CId=1199) AND (StartTime BETWEEN <t1> AND <t2>) ORDER BY StartTime DESC LIMIT 0,1
The current PRIMARY KEY (StartTime, CId), works almost as well, _assuming_ there are only a few distinct CIds in the partition.
Because of "PARTITION BY HASH(CId)", I recommend you do EXPLAIN PARTITIONS SELECT on all of your SELECT statements. You may find that some fail to do the desirable pruning.
> we need to maintain new partitions as the time passes
True, that is a nuisance.
> creating new partitions lock the full table for some time (I have seen even minutes)
That depends on how you do the create. I like to
ALTER TABLE x REORGANIZE future into tomorrow..., future...;
and keep "future" empty.
> my benchmarks show performances of these queries run on RANGE schema are worst than on HASH schema
Interesting. Was it BY RANGE(CId)? Which queries were slower?
The critical question (relating to HASH or LIST) -- Do you _always_ include "CId = constant" in the WHERE clause?
> The ideal size seems to be 1 partition per CId...2000
Did you try various numbers of PARTITIONs?
table_open_cache (and others) -- are they well over 2000?
Back to
> Memory usage increasing constantly
If the 172 connections are all opening all 2000 partitions, that may be causing a lot of memory contention.
Suggestion: Monitor memory usage versus Threads_connected. This may 'prove' that more connections leads to more memory usage.
With thread_cache_size = 50, you are keeping up to 50 threads (and their associated memory) around. Changing it won't fix your problem, but it could muddy the suggestion I just made.