4 ms·

Let's say my business is real time analtics---tracking mouse movements on the page. This is tackled by quite a few startups, so it's not out of the realm of th

Let's say my business is real time analtics---tracking mouse movements on the page. This is tackled by quite a few startups, so it's not out of the realm of things we HNers do ourselves. Mouse tracking can generate potentially millions of data points per day for even a small number of users. However, we only store a set amount of data per client. If for example, you think that every point should be tracked individually you can easily run into the case where Postgres will be non-optimal under the read/write load. Something like Redis on the other-hand might be optimal. This is a case where its not a premature optimization, its just the right tool for the right job.

bad_user 15y ago
I once had to track user actions in a pretty active online game - I configured the Apache servers to pipe their logs to a Perl script. The Perl script was parsing the requests, then pushing that data to a buffer (i.e. an array). Then when the buffer reached 5000 requests, it would push the data in PosgreSQL using a COPY FROM STDIN. On committing the transaction, we also specified synchronous_commit == off As far as DB tables go, each day a new table was created with a timestamp in its name, then a cron script would take care of tables older than one week, aggregating data and getting rid of junk. This setup was handling tens of thousands of writes per second without a sweat on a pretty modest server. Of course, it's less than what Redis can do, but then again I trust PostgreSQL more than I trust Redis.
- true_religion 15y ago
  Out of curiosity, were you doing a high number of reads at the same second? And if so, was it the same batch processing method you were using, or was it random reads?
kls 15y ago
I will readily admit that I am biased, A good deal of my experience has been in large high load web systems with a good deal of legacy environments. When it comes to the final end point for my data I like relational structures. Personally, I find them more adaptable to the unforeseen as far as business insight is concerned, and in the environment, that I have worked in, the unforeseen occurs daily. For example, a marketing manager wants to aggregate data set X against Y to see the outcome. The more data I have in structures that support these unforeseen and ad-hoc requirements the more insight my organization obtain. So personally for your situation I would front cache the data in an NoSQL type structure and bulk transfer it into a relational structure at set intervals to avoid having to write logic in an application layer for each use case that comes through the door. I know that there are emerging tools in this space for the NoSQL databases, but I still find analytical and reporting easier to do in the relational world, relational models seems to lend themselves better to discovering links between data sets after the fact.
- th0ma5 15y ago
  Maybe also check out an EAV model, not relational, per se, not nosql per se, but could help with analytic pivots.
johnzabroski 15y ago
Why not store the data points in a Stable Bloom Filter and use the filter to automatically tell you the frequency of mouse movements? Could this work well? What does the data for mouse movements look like? I am curious if your hypothetical question is a reality. :)
- true_religion 15y ago
  Sometimes you'd want to actually re-create the path of mouse movements and clicks across the page. You can see this page for an example of what I mean: http://www.clicktale.com/product/visitor_recordings http://www.clicktale.com/product/visitor_recordings

bad_user 15y ago

I once had to track user actions in a pretty active online game - I configured the Apache servers to pipe their logs to a Perl script. The Perl script was parsing the requests, then pushing that data to a buffer (i.e. an array). Then when the buffer reached 5000 requests, it would push the data in PosgreSQL using a COPY FROM STDIN. On committing the transaction, we also specified synchronous_commit == off As far as DB tables go, each day a new table was created with a timestamp in its name, then a cron script would take care of tables older than one week, aggregating data and getting rid of junk. This setup was handling tens of thousands of writes per second without a sweat on a pretty modest server. Of course, it's less than what Redis can do, but then again I trust PostgreSQL more than I trust Redis.

true_religion 15y ago
Out of curiosity, were you doing a high number of reads at the same second? And if so, was it the same batch processing method you were using, or was it random reads?

kls 15y ago

I will readily admit that I am biased, A good deal of my experience has been in large high load web systems with a good deal of legacy environments. When it comes to the final end point for my data I like relational structures. Personally, I find them more adaptable to the unforeseen as far as business insight is concerned, and in the environment, that I have worked in, the unforeseen occurs daily. For example, a marketing manager wants to aggregate data set X against Y to see the outcome. The more data I have in structures that support these unforeseen and ad-hoc requirements the more insight my organization obtain. So personally for your situation I would front cache the data in an NoSQL type structure and bulk transfer it into a relational structure at set intervals to avoid having to write logic in an application layer for each use case that comes through the door. I know that there are emerging tools in this space for the NoSQL databases, but I still find analytical and reporting easier to do in the relational world, relational models seems to lend themselves better to discovering links between data sets after the fact.

th0ma5 15y ago
Maybe also check out an EAV model, not relational, per se, not nosql per se, but could help with analytic pivots.

johnzabroski 15y ago

Why not store the data points in a Stable Bloom Filter and use the filter to automatically tell you the frequency of mouse movements? Could this work well? What does the data for mouse movements look like? I am curious if your hypothetical question is a reality. :)

true_religion 15y ago
Sometimes you'd want to actually re-create the path of mouse movements and clicks across the page. You can see this page for an example of what I mean: http://www.clicktale.com/product/visitor_recordings http://www.clicktale.com/product/visitor_recordings