Subscribe to RSS feed
posted on 4 Jul 2017  -  40,568 views
Since the drastic move to re-architecture StocksCafe and adding support for two new markets at the same time in April, StocksCafe website have became slower. I am aware of that and have been taking significant amount of time trying to improve it.
To be very honest, I am not 100% sure what is the true cause although I am inclined to think that it is due to a summation of reasons such as poor database configuration, increased data volume etc instead of a single cause. Speaking of which, if anyone is an expert in MySQL or MongoDB among us, please reach out :)
Anyway, over the weekend, in an attempt to speed things up, I have installed
Memcached onto StocksCafe backend. Memcached is a caching system that helps by caching data that are repeatedly used instead of hitting the database every time. It has reduce StocksCafe response time by about 25% as of this writing (based on
Of course, there is no free lunch in this world. Using any caching systems generally come with two type of costs. Increased memory usage and cache invalidation determination. Increased memory usage is not a big problem nowadays since server RAM are inexpensive. Deciding when to update your cache, however, is very tricky. If you update the cache often, it slows you down which is the exact problem you are trying to solve but if you do not update it often enough or rather at the right time, you will then have outdated results in the cache.
In fact, yesterday, a couple of users have already noted "wrong" day change value in their portfolios and other people's in the shared portfolios. It is actually caused by me making a wrong assumption and hence showing outdated results.
As I will continue to cache more contents of StocksCafe, if you notice outdated contents, please reach out and let me know!
- Do you use indexes, especially primary keys and unique keys for your database?
4 Jul 2017 11:15:06
- I am assuming your CPU and memory utilization is still below 50%. Have you check which are the most used queries and well as which queries are taking the longest time to complete?
Also, do you have sufficient 'OPEN' connections allowed for your DB.
P.S. Not an expert, just have some DB experience.
4 Jul 2017 11:22:43
- @Martinlam, "Do you use indexes, especially primary keys and unique keys for your database?" => I log all queries >0.3 seconds and create indexes for all of them. However, some queries take >0.3 seconds even with indexes hence I believe the problem is elsewhere.
@knic, "Have you check which are the most used queries and well as which queries are taking the longest time to complete?" => Yes, I log all slow queries that take >0.3 seconds and create indexes for them. However, some queries take >0.3 seconds even with indexes hence I believe the problem is elsewhere.
"Also, do you have sufficient 'OPEN' connections allowed for your DB." => Hmm... What is sufficient? What is a correct way to monitor?
4 Jul 2017 12:19:38
- show full processlist;
show variables where variable_name = "max_connections";
4 Jul 2017 13:31:03
- I assume that I want "show full processlist;" rows to be close to max_connections? There seems to be only 11 current connections whereas max_connections is 150
4 Jul 2017 14:27:15
- I am not an expert in MySQL.
Usually, my sysadmin/app folk will use APM to identify the bottleneck of system slowness.
4 Jul 2017 22:04:41
- Hey evan, the updates have noticeably got faster. Just want to drop a note and say thanks! Appreciate the effort.
6 Jul 2017 23:03:42
- @hanneng, thanks. It would be great if you could share the specific APM they are currently using.
@seekingprivatereturn, great to know :)
7 Jul 2017 06:36:26
Next Article >
< Previous Article
Portfolio Changes: Current Cost ...
Market Update Emails Fixed
List All Articles
Other articles by evankoh
How Much is a Stock Worth?
Recently, I read a book, A Random Walk Down Wall Street by Burton G. Malkiel. In the book, he discussed two ways to valuate a stock - The firm-foundation theory and the castle-in-the-air theory. The firm-foundation theory assumes that each stock has a true value which is equal to the present value of all its future dividends. It is likely that you have heard something similar because this approach ...
StocksCafe now supports Singapore Savings Bonds!
With the increasing interest rates, Singapore Savings Bonds is becoming more and more popular. Naturally, many users have requested for StocksCafe to support them. As usual, StocksCafe answered your call :) If you do not know what Singapore Savings Bonds is about, you can learn more here and here. I am happy to share that StocksCafe now supports Singapore Savings Bonds! Click here to see the different ...
StocksCafe upgrades to Morningstar data!
StocksCafe x Morningstar StocksCafe recently signed a deal with Morningstar. In case you have never heard of Morningstar, here is a wiki article about them. Basically, Morningstar is an established company that provides a wide array of products and services to both retail and institutional investors. With the recently signed deal, StocksCafe will now be getting financials data directly from Morningstar. ...