Skip to main content ↑ ↓
When does Elk use tmp tables? Started by badmonkey · · Read 14034 times 0 Members and 1 Guest are viewing this topic. previous topic - next topic

When does Elk use tmp tables?

Part of my sql tuning strategy includes using a tmpfs ramdisk for mysql tmp tables. These have been increased slowly over time. It seems two things occur regarding tmp tables. One is it isn't yet large enough at 100M. The other is, and this could be erroneous, it appears they are seldom used.

The question is what/where/when does Elk use tmp tables? The answer could confirm the aforementioned erroneous potential. Or it could alter the tuning strategy. If it is indeed rarely used perhaps it's best to recoup the memory and let it go to hard disk instead.

Thoughts, insight, or feelings from the gurus?
 

Re: When does Elk use tmp tables?

Reply #1 –

Hummmm, I'll take a shot.

First up is the deliberate use of a temp table in the code CREATE TEMPORAY TABLE is pretty rare, mostly since not all sites allow (give the db privileges') to create them.  So where they are used, non temporary table code also has to be added so things work everywhere. Which means extra work when doing any debugging or updates.

I can only think of  a few areas where we try to use them.  One is during search (custom and standard only I *think*), there is one in the maintance functions with recounting user posts, and then the "big" one was used in recent posts.

In 1.1.7 the recent posts one was removed (same for 2.0), and in 2.0 additionally the recounting maintance function was changed to avoid this.  That leaves certain search indexes only in 2.0

Next up is the db engine deciding it needs some temporary tables to hold some intermediate data or etc, These are Internal temporary tables .  Those as best as possible are worked around by how query's are constructed or tables / keys are created, but some are unavoidable but we do our best to avoid that, especially in any area under common use.  Other areas, such as the ACP are run once every XX days so not as important.

I know doing things with DISTINCT like COUNT(DISTINCT) will do it, as will ORDER BY with a different GROUP BY clause, its these "sorting" of results that make the db engine go, hey I'll stuff these here and then sort them later.  Being able to do that in memory is better then writing it to disk and you may be able to avoid that with your my.cnf settings.

Re: When does Elk use tmp tables?

Reply #2 –

Hhmmm.....thanks for the explanation @Spuds‍ 


I'll have to give it some thought. Off the cuff it sounds as though retaining the tmpfs is a good idea given sufficient memory. Perhaps I should continue increasing it. Apparently the unread function is popular, as is the search function.

Re: When does Elk use tmp tables?

Reply #3 –

When 1.1.7 is "done" that will get one major use.   As for search consider sphinx instead, its somethign to play with (and if you do this I've recently done some research (RTFM) and improved whats in the 1.1 branch)

Re: When does Elk use tmp tables?

Reply #4 –

Quote from: Spuds – When 1.1.7 is "done" that will get one major use.  As for search consider sphinx instead, its somethign to play with (and if you do this I've recently done some research (RTFM) and improved whats in the 1.1 branch)
 
 Sphinx is an attractive option. It works wonderfully on the whole. It IS tempting to use it again. Does anyone know how to properly configure it to include decimal indexing and results? That was the primary reason for dropping sphinx...

Re: When does Elk use tmp tables?

Reply #5 –

Could you expand a bit on what you mean by ' decimal indexing and results'

Re: When does Elk use tmp tables?

Reply #6 –

Quote from: Spuds – Could you expand a bit on what you mean by ' decimal indexing and results'
 
 Sure! My sites are motorsport related. An example of a frequent search syntax may be something like "2.0 swap" for information based on an engine displacement. The last sphinx version I used didn't like the decimal. It would drop it and search for "20 swap" or simply drop the numbers altogether as it may be too short for a search term. Or maybe I was doing it wrong. ;D

Re: When does Elk use tmp tables?

Reply #7 –

OK ... let me look intot that! ... Sounds like it either an error with our query builder or the conf file .... I'll investigate a bit :D

Re: When does Elk use tmp tables?

Reply #8 –

Quote from: Spuds – OK ... let me look intot that! ... Sounds like it either an error with our query builder or the conf file .... I'll investigate a bit :D
 
Mighty kind offer. If memory serves it's something with sphinx itself. It's worth another look see.

Re: When does Elk use tmp tables?

Reply #9 –

I finally got back around to looking at this and yes it can be done :D

It takes a couple of minor tweaks, so I may actually include them in 2.0 since they seem safe.

The basic issue is that sphinx by default does not index the decimal point, it actively removes it (and other things that are considered punctuation).   So 57 and 5.7 would be indexed the same as far as I could tell.  You can add the '.' to the charset_table  command so it will be indexed.  However doing that leads to other issues, such as words at the end of a sentence would be indexed with the .  and then not found in a  non wildcard search..  And you can do some things about that but at the expense of a less selective search (using short prefix lengths)

The solution that worked the best was to use a regexp_filter.  The filter gets applied to both the index and the search term.   For our purpose using \b(\d+)\pP+(\d+)\b => \1_\2 would convert 5.7 to 5_7 or 123.45 to 123_45 and 1,000.34 to 1_000_34 etc and as '_' is a standard indexable character.   Now a search for 5.7 would be converted to 5_7 which is how 5.7 would be indexed to start with.  The user does not see any of that, so its not confusing to them.

There is one filter in Elk which strips punctuation from search terms so that would need to be tweaked, and then presto it works.
Last Edit: February 10, 2021, 05:48:14 pm by Spuds