4 ms·

Hi. I'm surprised that you have run this platform for 8 years. That's incredible! There's one question I want to ask you. Do you have any experience handling m

by supz_k 6y ago

Hi. I'm surprised that you have run this platform for 8 years. That's incredible! There's one question I want to ask you. Do you have any experience handling millions of traffic? I saw that you are using MYSQL for storage. I assume each pageview is stored in a row. So, from my experience MYSQL is very slow performing aggregated queries on those datasets (We recently moved to Clickhouse due to this). I'd like to know your experiences on handling millions of rows in MYSQL, if you have any :)

XCSme 6y ago
Hi, Thank you! It was not hard to work on it for so long, time flies, and I only worked on it as a side-project for a long time. Most of the work was responding to support queries and talking to customers. The good thing when you do something over a long period of time is that you have lots of time to get good ideas and change/rethink parts of the product. I personally don't have any site that gets millions of users per month, but there are some customers using userTrack for around 300k sessions/month. That being said, I think of userTrack as a solution for small and medium businesses, not really something for huge enterprises that probably already have dedicated analytics teams and expensive software stacks. > Do you have any experience handling millions of traffic? I do have some experience with handling heavy traffic, not from userTrack, but from working on a multiplayer browser game with 300k+ monthly users. There we used MongoDB, and the servers handled it pretty well without huge focus on performance or scaling. > from my experience MYSQL is very slow performing aggregated queries on those datasets From what I've seen so far, MySQL is really fast if the queries are done right. If everything has an index and most of the results are filtered, then most queries run without any performance issues, even if the database gets bigger (talking about several or tens of GBs, not about terra-bytes). I am curious what was your bottle-neck with those queries and how the aggregation was being done. Did you run the queries with EXPLAIN to see why they were slow?
- supz_k 6y ago
  Hi, Thanks for the answer. In our MYSQL database, we wanted to count number of pageviews for each website. So, the query was like SELECT COUNT(id) FROM page_views WHERE website_id = x AND created_at > (start of the month) For some websites, there are 20 million+ pageviews for each month. MYSQL goes through all of those 20 million rows (according to EXPLAIN) even with index or composite index (It took a few seconds). So, we had to pre-calculate the number of pageviews each hour and show it to the user. Then, things were worst when we had to show analytics. There we had to group by month or day. So, it took more time. That's when we started finding a solution. And, I learned that there's something called "Analytical Databases", which are designed for those analytics purposes. How they work is completely different from MYSQL. And, here's a benchmark on MYSQL vs Clickhouse. http://mafiree.com/blogs.php?ref=Benchmark-::-MySQL-Vs-ColumnStore-Vs-Clickhouse http://mafiree.com/blogs.php?ref=Benchmark-::-MySQL-Vs-Colum... MYSQL does a pretty good job for many things. However, when it comes to analytics, I think it's better to use an analytical database. As each of your users has its own database, there won't be any issues at all. In our case, all of our clients' pageviews are stored in one table, which grows at 35m records per month. We're also new to this analytical databases thing. I'd like to know your thoughts. :)
  - XCSme 6y ago
    Thanks for the extra details! I built userTrack mostly as a cheaper alternative for smaller businesses, so they can still have access to good tools/data without paying enterprise prices, so the goal was never to support 1M+ monthly sessions, thus I never spent too much time looking into heavy-traffic performance or scaling. > For some websites, there are 20 million+ pageviews for each month. MYSQL goes through all of those 20 million rows (according to EXPLAIN) even with index or composite index (It took a few seconds). So, we had to pre-calculate the number of pageviews each hour and show it to the user. Yes, count usually "goes" through all the rows when using more complex conditions, the way to improve this is usually, as you also did, to pre-calculate the counts using databse triggers (whenever a new row is inserted, the trigger will update the total count). I am not familiar with Clickhouse, but I assume for being so fast it provides at lot fewer features/options to store and manipulate data. How hard was the transition to Clickhouse? Where you able to easily convert the DB schema and all quries?

by supz_k 6y ago

XCSme 6y ago

Hi, Thank you! It was not hard to work on it for so long, time flies, and I only worked on it as a side-project for a long time. Most of the work was responding to support queries and talking to customers. The good thing when you do something over a long period of time is that you have lots of time to get good ideas and change/rethink parts of the product. I personally don't have any site that gets millions of users per month, but there are some customers using userTrack for around 300k sessions/month. That being said, I think of userTrack as a solution for small and medium businesses, not really something for huge enterprises that probably already have dedicated analytics teams and expensive software stacks. > Do you have any experience handling millions of traffic? I do have some experience with handling heavy traffic, not from userTrack, but from working on a multiplayer browser game with 300k+ monthly users. There we used MongoDB, and the servers handled it pretty well without huge focus on performance or scaling. > from my experience MYSQL is very slow performing aggregated queries on those datasets From what I've seen so far, MySQL is really fast if the queries are done right. If everything has an index and most of the results are filtered, then most queries run without any performance issues, even if the database gets bigger (talking about several or tens of GBs, not about terra-bytes). I am curious what was your bottle-neck with those queries and how the aggregation was being done. Did you run the queries with EXPLAIN to see why they were slow?

supz_k 6y ago
Hi, Thanks for the answer. In our MYSQL database, we wanted to count number of pageviews for each website. So, the query was like SELECT COUNT(id) FROM page_views WHERE website_id = x AND created_at > (start of the month) For some websites, there are 20 million+ pageviews for each month. MYSQL goes through all of those 20 million rows (according to EXPLAIN) even with index or composite index (It took a few seconds). So, we had to pre-calculate the number of pageviews each hour and show it to the user. Then, things were worst when we had to show analytics. There we had to group by month or day. So, it took more time. That's when we started finding a solution. And, I learned that there's something called "Analytical Databases", which are designed for those analytics purposes. How they work is completely different from MYSQL. And, here's a benchmark on MYSQL vs Clickhouse. http://mafiree.com/blogs.php?ref=Benchmark-::-MySQL-Vs-ColumnStore-Vs-Clickhouse http://mafiree.com/blogs.php?ref=Benchmark-::-MySQL-Vs-Colum... MYSQL does a pretty good job for many things. However, when it comes to analytics, I think it's better to use an analytical database. As each of your users has its own database, there won't be any issues at all. In our case, all of our clients' pageviews are stored in one table, which grows at 35m records per month. We're also new to this analytical databases thing. I'd like to know your thoughts. :)
- XCSme 6y ago
  Thanks for the extra details! I built userTrack mostly as a cheaper alternative for smaller businesses, so they can still have access to good tools/data without paying enterprise prices, so the goal was never to support 1M+ monthly sessions, thus I never spent too much time looking into heavy-traffic performance or scaling. > For some websites, there are 20 million+ pageviews for each month. MYSQL goes through all of those 20 million rows (according to EXPLAIN) even with index or composite index (It took a few seconds). So, we had to pre-calculate the number of pageviews each hour and show it to the user. Yes, count usually "goes" through all the rows when using more complex conditions, the way to improve this is usually, as you also did, to pre-calculate the counts using databse triggers (whenever a new row is inserted, the trigger will update the total count). I am not familiar with Clickhouse, but I assume for being so fast it provides at lot fewer features/options to store and manipulate data. How hard was the transition to Clickhouse? Where you able to easily convert the DB schema and all quries?