|
I've prototyped a way to do pattern discovery using SQL, but I still have a poor understanding of where this method fits in the data mining vernacular.
Being set-based, I'm not building a tree, although a functional tree does arise in the result set.
The steps:
1) Build a "look up" table, by doing a cross join, yielding a combinatorial "dictionary" (a rainbow table) of n-gram "words."
2) Get COUNT(*) > n , using SQL GROUP BY, matched against a large table of items
3) Further look for equivalent longer self-matches within the result set.
My seed table is 177 items, allowed to cross-join itself 3x, for a final table 2.8 million 3-gram words (takes about 35 seconds to build this table in Postgres).
The actual itemset table is 10 million rows in series (serially numbered), although the actual number of itemsets might be considered smaller.
I've recorded 35** seconds on the join between the two original tables, yielding all the simple repeating 3-grams meeting group by's count(*) > x (that's the dictionary joined to the itemsets.
That's the Q&D discovery step, and then subsequent steps simply apply a self-join for longer-chained repeating series. These have been pretty quick, in the 50 millisecond range.
My questions are:
1) What's the best way to describe this algorithm? Frequent pattern? Motif?
2) It's a simple enough method, but is it fast enough for general use? I.e. other data mining apps where performance requirements are different from my own?
3) I've wondered if SQL could be convinced to pattern-match like an LCS dynamic programming algorithm, by matching across gaps in the sequence, with maybe a lookup table of allowable variances & distance between matching values?
**Right now I'm seeing 50 seconds after the buffers load, but I reinstalled Postgres & my postgres.conf file apparently is all defaults now (the postgres process back to only 16 mb getting buffered, so it's suffering more I/O to the SSD drive).
Thanks in advance,
-- Lee
-- modified 5-Sep-14 0:17am.
|
|
|
|
|
Member 11060173 wrote: seed table is 177 items, allowed to cross-join itself 3x, for a final table 2.8 million 3-gram words
...
actual itemset table is 10 million rows
Ehm, 2.8 million is about half of 177*177*177 - that's quite a lot, but does still fit into memory (RAM). With 10 million rows, your trigram table will have some 10^20 rows, and that's beyond the memory of any machine nowadays, even beyond the capacity of any hard disk.
It won't work (already that factor of 10^14 applied to the present 35 seconds should tell you that).
|
|
|
|
|
Oh, I forgot to mention that the trigram dictionary is trimmed by abs(val1+val2+val3) <= 88 (it's a vector dataset of small int). But the 5.5m trigram dictionary might not slow things much given the use of covering indices (the access is all via b-tree indices, obviating the need for as much memory).
I looked into using a 4-gram dictionary but it presented a very large table, much larger than the 3-gram dictionary, and worse it made for more overlapping duplicates in building the equivalent of an FP-tree (at least 1 extra overlap, whereas w/ the 3-gram matches I'm always overlapping by n+2). Also I sense a trigram-based tree innately reflects the smallest useful vector of from-and-to applicable.
One problem might be that in high-frequency datasets I could see an explosion of 3-gram noise that doesn't always support better (longer) matches, bloating the output. I understand that in FP-Tree algo's there's a minimum support criterion that perhaps works around this. There may be a way to ameliorate this in SQL, such as checking for matching adjacencies in a manner that'll optimize via a correlated subquery, using ANY or [NOT] IN.
I haven't had enough time to fully experiment with various datasets, I've been going through a application language selection process** & am contemplating looking into PostGIS' geometric data & index features (R-Tree indices) as a way to get better, longer string matches, even perhaps supporting approximate or intermittent matches akin to the ability of LCS/cosine match algorithms (but on larger sets with more expressive syntax).
I'm prepared to start coding in C or Julia, but I'll avoid it if Postgres proves "fast enough." That's b/c as new data are imported to the DBMS I'll want to rerun pattern discoveries in the background against the main data store. My current 10 million rows are an exorbitant sample, out-scaling anything I expect to encounter in the actual data (MIDI note vectors).
[Edit:]
Just found this:
https://www.academia.edu/5184801/SQL_Based_Frequent_Pattern_Mining_with_FP-growth[^]
It's a very old paper (circa 2001?), but I'll probably be following their methodology. Maybe they gave up b/c DB/2 was too slow vs. algorithmic FP-Growth in C++.
Also, from a 2006 paper: http://webdocs.cs.ualberta.ca/~zaiane/postscript/adma05.pdf
"...In this work we presented COFI-Closed, an algorithm for mining frequent
closed patterns. This novel algorithm is based on existing data structures FP-tree
and COFI-tree. Our contribution is a new way to mine those existing structures
using a novel traversal approach. Using this algorithm, we mine extremely large
datasets, our performance studies showed that the COFI-Closed was able to
mine efficiently 100 million transactions in less than 3500 seconds on a small
desktop while other known approaches failed..."
I know that's old hat by now (my 2008-era Thinkpad T400 Celeron laptop w/ its 4GB RAM & SSD drive vs. his "small desktop"), but I'm in the ballpark.
**( As for fast application langs, esp. for other algorithmic jobs not best modeled in a SQL DBMS, I think I just found a winner: http://www.julialang.org[^] & http://juliawebstack.org/[^] )
modified 5-Sep-14 10:57am.
|
|
|
|
|
I've been playing around with binary search algorithms and have created a novel new variant that appears to be significantly faster than any traditional implementations I've seen.
int boundless_binary_search(int *array, int array_size, int key)
{
register int mid, i;
mid = i = array_size - 1;
while (mid > 7)
{
mid = mid / 2;
if (key < array[i - mid]) i -= mid;
}
while (i && key < array[i]) --i;
if (key == array[i])
{
return i;
}
return -1;
}
I'm wondering if this is a novel binary search implementation, or has this approach been used by someone before?
Some variants and benchmark graphs:
https://sites.google.com/site/binarysearchcube/binary-search[^]
|
|
|
|
|
Perhaps I'm not thinking about it right (I have a cold so I'm not particularly sharp right now), but it seems to me that if the item is in the second half of the array, it will essentially devolve into a linear search.
|
|
|
|
|
It switches to a linear search when roughly 8 elements are left. The i != 0 check is there in case the key is smaller than the value at index 0.
|
|
|
|
|
Yes ok, I guess the cold got to me. I was thinking something weird based around the assumption that mid started in the middle, which it obviously doesn't.
So it works, that's good. It seems closely related to the variant which keeps a "midpoint" and a "span" (here it's the next span (mid), and the midpoint plus that next span (i)). Same pros and cons too (needs fixup at the end, but inner loop is simple), the "midpoint/span" variant is usually seen (when seen at all) in its "more complicated math in the inner loop"-form which doesn't need fixup, but then what's the point.
|
|
|
|
|
Using a midpoint and a span is slower because it requires 2 assignments per loop opposed to 1.5 (on average) in my implementation.
I assume it's the fixup and assignment issue that left academics stumped for the past 60 years. There are also caching issues for larger arrays, for which I've created a variant that is mindful in that regard.
|
|
|
|
|
Gregorius van den Hoven wrote: requires 2 assignments per loop opposed to 1.5 (on average) in my implementation.
Well you realize it'll be a conditional move, right? But only one, whereas regular binary search would have two (or an unpredictable branch, yuck).
So you'd get something like (from GCC output)
.L4:
sar edx
mov ecx, eax
sub ecx, edx
cmp [esi+ecx*4], ebx
cmovg eax, ecx
cmp edx, 7
jg .L4
In a quick test, GCC didn't feel like using cmovs for plain old "left and right bounds" binary search. You can easily measure a huge difference due to that sort of thing, and I'm not sure that's 100% fair, after all you could implement ye olde binary search with cmovs.
|
|
|
|
|
I'm not well versed in that area.
Looks like the compiler is doing something differently when you free up a registry space, but it's still slower.
int tailing_binary_search(int *array, int array_size, int key)
{
register int bot, mid, i;
if (key < array[0])
{
return -1;
}
bot = 0;
i = array_size - 1;
mid = i / 2;
while (bot + 7 < i)
{
if (key < array[mid])
{
i = mid - 1;
mid -= (i - bot) / 2;
}
else
{
bot = mid;
mid += (i - bot) / 2;
}
}
while (key < array[i]) --i;
if (key == array[i])
{
return i;
}
return -1;
}
|
|
|
|
|
Yea GCC at least makes some branches there in the loop, and it's using more complicated divisions by 2 (that work when negative).
|
|
|
|
|
This approach has been done before, e.g. the implementation of qsort in the C runtime library transitions from quicksort to a (linear) insertion sort when the partition size gets below a certain threshold.
|
|
|
|
|
I want to calculate the surface of an area on a sphere including projection effect.
For this I have a scanned image (which results in a 2D disc). On this image I can highlight an area and I have the index of each pixel of that area relative to the center of that disc (x, y coordinates). I also have the radius (in pixels and meters)
How can I calculate the correction needed for each pixel in order to take in account the projection effect (larger at the edges of the disc)?
Thanks.
[SOLUTION]
It's a pretty long solution, so I'll try to be as brief as possible.
1. You need to scale down the sphere to a unit sphere (radius=1)
2. With that you know that x²+y²+z² = 1 and thus z = sqrt(1-x²-y²) and the surface (of a full sphere) is 4*pi*r² = 4*pi, but you only have half the sphere so S=2*pi
3. What you need is the cosine of the angle of the pixel with the Z-axis. This is the z calculated in step 2 (the sqrt) divided by the radius which you reduced to one.
4. In the end you get this formula for the area per pixel: (1/r² * 1/(2*pi*sqrt(1-x²-y²)) ). If you loop through the pixels of the complete area you just need to to sum up all these pixels.
5. This results in a fraction of the surface compared to the sphere surface which you can then convert to any unit you like.
On first sight this "looks" correct 
[/SOLUTION]
modified 8-Aug-14 7:50am.
|
|
|
|
|
open-source GIS libraries have solved this kind of problems in much more general form, iff u get the time to decipher them  
|
|
|
|
|
I've been studying lock free algorithms, specifically the circular lock free ring buffer. I fully understand what they're doing, but am having trouble applying that to some of my code. It seems that the lock free ring buffer is meant for situations where the queue is neither starved nor saturated for any "significant" (in CPU terms) period of time.
For example, I recently had some code with multiple producers and a single consumer. When the server was running at a normal load, the lock free ring buffer would have been an excellent solution. However, when the server load was low it seems the consumer would just be spinning, using quite a bit of CPU doing nothing. Contra-wise, when the server load was high, the producers would be spinning (in the current code, if this happens, the producers have a wait of a certain period at which point, they discard their data and grab the next set [losing data periodically was permissible since the next set would allow a full reconstruction, albeit with stutter]), but how long do they spin? Can't use just a count since time is important in this situation, but grabbing clock is expensive and simply looping the thread consumes a lot of CPU, depriving other producers of their quanta.
Am I missing something?
|
|
|
|
|
I posted a lock-free stack a few days ago. There would be no such issues of spinning/locking up the cpu. The only "problem" with it compared to the circular buffer you're using now is that the stack is FILO instead of FIFO.
A Fundamental Lock-Free Building Block - The Lock-Free Stack[^]
Hope it helps, lemme know if you have any questions.
|
|
|
|
|
Michael Gazonda wrote: There would be no such issues of spinning/locking up the cpu
This is the part I struggle with. If the stack is full, how does the CPU not spin? Likewise, if the stack is empty, pop returns false. Then what?
|
|
|
|
|
With a stack, there is no "full". It's like a single-linked list. Each item points to the next, and carries with it the space required for doing so.
If the stack is empty, and returns false, then that's up to you to handle in whatever way is appropriate.
For myself, I wrote this to handle memory allocation (where I would manually allocate on false), or as a messaging system where false just meant there was no work to do and I would wait on a condition_variable.
|
|
|
|
|
Why not use one of the many open source message queues out there? This is exactly what they do. I'm assuming, of course, that you are trying to implement a circular buffer to solve a problem and not the other way around .
* a message queue solves the producer / consumer problem for you
* a message queue solves the distributed workload problem for you
* a message queue can be configured to use either memory / database / files / etc. as a backing store, so you never lose a work request or response
* clustering / scaling / fail over, etc. is all built in for you
I used Apache ActiveMQ on a project with an arbitrary number of producers and about 400 - 500 consumers (VMWares) and it worked great. Producers would submit work requests and the 400 - 500 consumers would just cycle through the various queues looking for work they knew how to do.
The cool thing about ActiveMQ is that it supports push notifications so you don't use any CPU time during idle time.
|
|
|
|
|
Because I'm trying to understand lock free algorithms.
(I former colleague evaluated ActiveMQ and found it didn't scale well. Neither did RabbitMQ. For the previous app I worked on, both would have been completely inadequate.)
|
|
|
|
|
Yeah, that's why I was asking if you were trying to reinvent the wheel .
Just as an aside with ActiveMQ, it actually scales EXTREMELY well, you just need to configure it exactly right and use it exactly right. They have a TON of configuration options which are pretty poorly documented IMO. Just mis-setting one of them can kill your performance.
One complaint I do have about it is that it doesn't handle a heavy onslaught of large messages very well. My original implementation tried to send 1MB to 2MB messages and it would crash often (probably a configuration issue as we wanted to slam messages through and didn't care if they got lost). I then started compressing the messages with LZ4 and they went down to about 400k to 500k and the system got pretty damn stable for only having one server and 400 to 500 clients and performance was much better. They are actually tweaking the system right now to send really small messages that point to file shares to pick up the work load.
And nothing against you or your ability, but I'd be surprised if you are able to create a production system that can top one of the widely used message queues out there. At least not in a reasonable amount of time. I don't think I could either. That's all those guys do.
If you are doing it as a learning process, well, that's a different story .
On the other hand, if you have to deliver a system that's going to scale to 1000 clients and millions of messages / day in a few weeks, well... .
|
|
|
|
|
SledgeHammer01 wrote: One complaint I do have about it is that it doesn't handle a heavy onslaught of large messages very well
Yup.
SledgeHammer01 wrote: On the other hand, if you have to deliver a system that's going to scale to 1000 clients and millions of messages / day in a few weeks, well...
What, again, was your definition of scalable?
SledgeHammer01 wrote: And nothing against you or your ability, but I'd be surprised if you are able to create a production system that can top one of the widely used message queues out there. At least not in a reasonable amount of time. I don't think I could either. That's all those guys do.
Actually, I have, though the use was very narrow and specialized. Again, our testing found that all the commercial solutions had performance bottlenecks or just plain fell apart when trying to use them with massive amounts of data.
BTW, in one app, switching from ZLib to LZ4 was a significant performance gain that overwhelmed any decrease due to slightly larger message sizes.
|
|
|
|
|
Joe Woodbury wrote: What, again, was your definition of scalable? Smile |
I meant if your task was to deliver that kind of scale from a home grown solution in a few weeks, that would be tough. If you managed to do that, that's very impressive.
Joe Woodbury wrote: Actually, I have, though the use was very narrow and specialized. Again, our testing found that all the commercial solutions had performance bottlenecks or just plain fell apart when trying to use them with massive amounts of data.
BTW, in one app, switching from ZLib to LZ4 was a significant performance gain that overwhelmed any decrease due to slightly larger message sizes.
Yes, there are performance penalties due to all the configuration options.
A slight aside on this topic... we found with our testing that sending large payloads through a message queue (and we originally had a home grown solution as well) is very inefficient.
Say for example, your payload is 1MB files.
Your producer needs to:
1) load the file / deserialize it (or just load it as a binary blob)
2) possibly serialize it back to a binary blob, compress it
3) post it to a queue (whether your own or a commercial one)
4) possibly do some preprocessing on the blob before sending it over the wire
The client needs to:
5) get the large blob
6) decompress it
7) deserialize it
8) do the work
It is more efficient to put the payload on a file share and in the payload just send the UNC path to the file share. This way you get rid of the whole compression / decompression step / multiple serialize / de-serialization steps.
In our project, we have to process 4 BILLION 1MB payloads, so...
ANYWAYS... back to your original question...
Whatever you use, the producer "send" should be instant fire-and-forget and this includes if it needs to get a response. It should do that in an async callback (push notification). There should be pretty much 0% CPU usage when the producer is idle and it should never be blocked. If it has something to submit, it should be able to at any time. The server needs to be able to deal with a flood. Unless your server has a ton of memory and you are guaranteed the clients can keep up at any load, you'd need to have a backing store of some kind when the memory gets filled up.
As far as the client side goes, it sounds like you are polling your circular queue for work. NOTHING in your design should be polling. Polling is a big no-no. Everything should be async push notifications. Sleep / poll is not a good design. Should be push / WaitForSingleObject. WaitForSingleObject does not consume any CPU resources.
|
|
|
|
|
SledgeHammer01 wrote: As far as the client side goes, it sounds like you are polling your circular queue for work. NOTHING in your design should be polling. Polling is a big no-no. Everything should be async push notifications. Sleep / poll is not a good design. Should be push / WaitForSingleObject. WaitForSingleObject does not consume any CPU resources.
That's the point of my question. When doing a lock-free algorithm, how do you avoid polling (due to starvation or saturation)? In the discussions of the well-known circular queue, this is never actually answered--it assumes that a natural balance will always be present.
For the record, my various solutions have used locks.
|
|
|
|
|
Joe Woodbury wrote: That's the point of my question. When doing a lock-free algorithm, how do you
avoid polling (due to starvation or saturation)? In the discussions of the
well-known circular queue, this is never actually answered--it assumes that a
natural balance will always be present.
For the record, my
various solutions have used locks.
I'm not familiar with your requirements, but a circular buffer wouldn't be my first choice for this (unless your requirement is to discard data when the buffer is full). I would use something more like a queue. But right now your code probably looks like this:
while (true)
{
someClass theTask;
if (buffer.GetWork(out theTask))
{
DoWork(theTask);
}
}
or something of that nature. That is a *very* tight loop when no work is available and will kill the CPU. A hack in the polling design is to add a sleep to alleviate the pressure on the CPU. You'll still have some CPU usage, but it'll be much less. A drawback would be, of course, that your client couldn't do any work during the sleep time which is a huge waste of CPU time. I.e. if you slept for 1000ms, and you got a message at 10ms in, your client wouldn't do anything for another 990ms.
A push / no polling design would be something like where your buffer class exposes a waitable handle such as a mutex. Now your client code looks like:
while (true)
{
WaitForSingleObject(buffer.theMutex);
DoWork(buffer.GetWork());
}
or something like that. The WaitForSingleObject() will block with 0% CPU usage. It will only return when the mutex is set.
So...
Your buffer class would set the mutex whenever data is inserted into the queue (in the insert method). The GetWork() method would reset the mutex when there are no remaining items (idle time).
Not sure how you are sending over the network, but sockets support async push notifications, so you'd have to make sure you use that as well. In the socket async callback, you'd insert into the queue which would set the mutex.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
