Very Poor Api Performance

Since I cannot post a topic in the 'Issues & Troubleshooting' forum (I have no clue as to why), I will just post it here.

I noticed CS-Cart API has very poor performance. Simultaneously sending about 20 API calls (which are actually 2 x 20 because in our situation, because we need to lookup the product ID by product code first, as I posted earlier) will raise our server (which is top notch) load to > 20. Handling just one call then takes CS-Cart > 20 seconds.

Please investigate these issues thoroughly and fix them in the next release. The current performance is not future (or even present) proof

The current performance is not future (or even present) proof

Hello,

Well, performance depends not only on application's code. You should understand that server configuration also matters. Keep in mind that it might be very important. I ask you not to blame CS-Cart and answer these questions:

1. What is your server configuration (CPU, RAM, I/O)?

2. How many products and categories do you have?

3. What PHP/MySQL version do you use? How they configured?

Hello,

Thank you for your reply. However, I can assure you that our hardware is just fine. You can see for yourself that the store itself is pretty fast: please visit shop4hoesjes.nl.

The server specs are:

2x Intel Xeon 2.6 GHz

4GB RAM

Running PHP 5.6 on Apache 2.4 with MySQL 5.7

We have about 30,000 products and about 500 categories spread over 4 stores.

Hello,

Thank you for your reply. However, I can assure you that our hardware is just fine. You can see for yourself that the store itself is pretty fast: please visit shop4hoesjes.nl.

The server specs are:

2x Intel Xeon 2.6 GHz

4GB RAM

Running PHP 5.6 on Apache 2.4 with MySQL 5.7

We have about 30,000 products and about 500 categories spread over 4 stores.

That is very little ram for such a big store. Nice store though!

That is very little ram for such a big store. Nice store though!

Thank you :grin:

Still though, the performance of the store is excellent with the 4GB RAM we have and I don't see why we would need more RAM for handling simple API requests, which should use much less resources than the store itself? Apparently, it doesn't, but that's exactly my point... The API should be way more efficient and use way less resources.

Thank you :grin:

Still though, the performance of the store is excellent with the 4GB RAM we have and I don't see why we would need more RAM for handling simple API requests, which should use much less resources than the store itself? Apparently, it doesn't, but that's exactly my point... The API should be way more efficient and use way less resources.

I don't know much about the api but haven't seen other complaints. Do keep in mind the frontend of the store is cached so it's pretty fast and doesn't use resources like the back-end.

I don't know much about the api but haven't seen other complaints. Do keep in mind the frontend of the store is cached so it's pretty fast and doesn't use resources like the back-end.

Thank you for your response. Frankly, I don't really care whether or not any people have complained about it yet.

Anyway, please note that I'm trying to address a major issue here. Although a large part of CS-Cart users may not use the API (intensively) yet, but I think more people will need it in the near future, and they will be very, very disappointed.

To illustrate the severity of this issue, I added some logging in our central API handler. In the results below, only 2 requests were sent simultaniously. This already caused a server load of > 3. As you can see, a get request to lookup the product ID by product code takes between 0.7 and 1.2 seconds, and even worse: a simple PUT request (for updating stock only) takes 1.5 to 3 seconds, which is amazingly slow.

			
07-09-2016 14:20:32
CsCartApiWorker Debug sendRequest (GET /api.php?_d=products&product_code=Y&q=8718923202846) execution time: 0.947 s
07-09-2016 14:20:30
CsCartApiWorker Debug sendRequest (PUT /products/26929) execution time: 2.283 s
07-09-2016 14:20:28
CsCartApiWorker Debug sendRequest (GET /api.php?_d=products&product_code=Y&q=8718923202839) execution time: 0.777 s
07-09-2016 14:20:27
CsCartApiWorker Debug sendRequest (PUT /products/26930) execution time: 2.74 s
07-09-2016 14:20:25
CsCartApiWorker Debug sendRequest (GET /api.php?_d=products&product_code=Y&q=8718923202822) execution time: 0.725 s
07-09-2016 14:20:24
CsCartApiWorker Debug sendRequest (PUT /products/26931) execution time: 2.84 s
07-09-2016 14:20:22
CsCartApiWorker Debug sendRequest (GET /api.php?_d=products&product_code=Y&q=8718923202815) execution time: 0.808 s
07-09-2016 14:20:21
CsCartApiWorker Debug sendRequest (PUT /products/26932) execution time: 2.111 s
07-09-2016 14:20:19
CsCartApiWorker Debug sendRequest (GET /api.php?_d=products&product_code=Y&q=8718923202808) execution time: 0.800 s
07-09-2016 14:20:17
CsCartApiWorker Debug sendRequest (PUT /products/26933) execution time: 1.982 s
07-09-2016 14:20:15
CsCartApiWorker Debug sendRequest (GET /api.php?_d=products&product_code=Y&q=8718923202792) execution time: 1.91 s
07-09-2016 14:20:13
CsCartApiWorker Debug sendRequest (PUT /products/26934) execution time: 1.821 s
07-09-2016 14:20:12
CsCartApiWorker Debug sendRequest (GET /api.php?_d=products&product_code=Y&q=8718923202785) execution time: 0.889 s
07-09-2016 14:20:11
CsCartApiWorker Debug sendRequest (PUT /products/26935) execution time: 2.97 s
07-09-2016 14:20:08
CsCartApiWorker Debug sendRequest (GET /api.php?_d=products&product_code=Y&q=8718923202778) execution time: 0.826 s
07-09-2016 14:20:07
CsCartApiWorker Debug sendRequest (PUT /products/26936) execution time: 2.225 s
07-09-2016 14:20:04
CsCartApiWorker Debug sendRequest (GET /api.php?_d=products&product_code=Y&q=8718923202761) execution time: 0.856 s
07-09-2016 14:20:03
CsCartApiWorker Debug sendRequest (PUT /products/26937) execution time: 1.985 s
07-09-2016 14:20:01
CsCartApiWorker Debug sendRequest (GET /api.php?_d=products&product_code=Y&q=8718923202754) execution time: 0.768 s
07-09-2016 14:20:00
CsCartApiWorker Debug sendRequest (PUT /products/26938) execution time: 2.166 s
07-09-2016 14:19:58
CsCartApiWorker Debug sendRequest (GET /api.php?_d=products&product_code=Y&q=8718923202747) execution time: 0.760 s
07-09-2016 14:19:57
CsCartApiWorker Debug sendRequest (PUT /products/26939) execution time: 1.893 s
07-09-2016 14:19:55
CsCartApiWorker Debug sendRequest (GET /api.php?_d=products&product_code=Y&q=8718923202730) execution time: 0.725 s
07-09-2016 14:19:54
CsCartApiWorker Debug sendRequest (PUT /products/26940) execution time: 2.10 s
07-09-2016 14:19:52
CsCartApiWorker Debug sendRequest (GET /api.php?_d=products&product_code=Y&q=8718923202723) execution time: 0.873 s
07-09-2016 14:19:51
CsCartApiWorker Debug sendRequest (PUT /products/26941) execution time: 2.0 s
07-09-2016 14:19:49
CsCartApiWorker Debug sendRequest (GET /api.php?_d=products&product_code=Y&q=8718923202716) execution time: 0.808 s
07-09-2016 14:19:48
CsCartApiWorker Debug sendRequest (PUT /products/26942) execution time: 2.19 s
07-09-2016 14:19:46
CsCartApiWorker Debug sendRequest (GET /api.php?_d=products&product_code=Y&q=8718923202709) execution time: 0.861 s
07-09-2016 14:19:45
CsCartApiWorker Debug sendRequest (PUT /products/26943) execution time: 1.616 s
07-09-2016 14:19:43
CsCartApiWorker Debug sendRequest (GET /api.php?_d=products&product_code=Y&q=8718923202693) execution time: 1.139 s

I wrote some code that uses the API to import the shipment tracking number, change the order status to Shipped and email the customer the tracking information. We haven't seen a performance impact using the API (although we do run the script at night). But I have definitely seen how slow an API call is. It does take about 2 seconds for each call.

David

To illustrate the severity of this issue, I added some logging in our central API handler. In the results below, only 2 requests were sent simultaniously. This already caused a server load of > 3. As you can see, a get request to lookup the product ID by product code takes between 0.7 and 1.2 seconds, and even worse: a simple PUT request (for updating stock only) takes 1.5 to 3 seconds, which is amazingly slow.


Does a tracert to your site look reasonable? What is TTFB for your site from same location that your API is running from?

That does seem rather slow especially since none of the template code is loaded for API calls. I'm wondering if the issue is more environmental than the cart itself. Given that nothing seems to run in under a second, it could be network related. I'm assuming your server config is appropriate for the size of your store.

You might consider posting something in bugtracker with additional environmental and anecdotal data. Might have a better chance of getting to the right person than on this side of the forums.

Does a tracert to your site look reasonable? What is TTFB for your site from same location that your API is running from?

That does seem rather slow especially since none of the template code is loaded for API calls. I'm wondering if the issue is more environmental than the cart itself. Given that nothing seems to run in under a second, it could be network related. I'm assuming your server config is appropriate for the size of your store.

You might consider posting something in bugtracker with additional environmental and anecdotal data. Might have a better chance of getting to the right person than on this side of the forums.

Thank you for your response. The TTFB is just fine from the same location that the API is running from:

bart@voorraad:~/shop4raad$ curl -w "\nConnect: %{time_connect} TTFB: %{time_starttransfer} Total time: %{time_total} \n" https://www.shop4.nl/api.php

{"message":"Unauthorized","status":401}
Connect: 0.006 TTFB: 0.142 Total time: 0.142

Traceroute output also looks good:

traceroute to www.shop4.nl (89.30.194.53), 30 hops max, 60 byte packets

1 v330.switch1.pool20.transip.nl (37.97.185.220) 0.530 ms 0.589 ms 0.659 ms
2 v660.coreswitch3.ams0.transip.net (87.253.147.169) 0.863 ms v660.coreswitch4.ams0.transip.net (87.253.147.170) 1.247 ms 1.246 ms
3 v633.router2.dcga.ams.transip.net (87.253.159.165) 0.353 ms 0.396 ms 0.395 ms
4 transip.nikhef.openpeering.nl (82.150.159.225) 1.906 ms ibgp.router2.dcga.ams.transip.net (87.253.141.250) 0.425 ms transip.nikhef.openpeering.nl (82.150.159.225) 1.895 ms
5 82.150.151.11 (82.150.151.11) 2.882 ms 2.837 ms transip.nikhef.openpeering.nl (82.150.159.225) 1.878 ms
6 89.30.192.42 (89.30.192.42) 1.823 ms 82.150.151.11 (82.150.151.11) 2.698 ms 89.30.192.29 (89.30.192.29) 1.484 ms
7 * 89.30.192.42 (89.30.192.42) 1.789 ms 89.30.192.29 (89.30.192.29) 2.470 ms
8 89.30.192.42 (89.30.192.42) 1.952 ms * 1.941 ms

Also, as you can see in the logs that I previously posted, some calls (PUT on the product endpoint) are about 3 times slower than other calls (GET on product search), which I assume must be caused by the inefficiency of the API itself:

07-09-2016 14:20:27 CsCartApiWorker Debug sendRequest (PUT /products/26930) execution time: 2.74 s
07-09-2016 14:20:25 CsCartApiWorker Debug sendRequest (GET /api.php?_d=products&product_code=Y&q=8718923202822) execution time: 0.725 s

I'm out of guesses then. I would post it to the bugtracker so it gets to an internal developer who can check/validate and respond.

There are many things that work great on small data sets but don't scale well at all. However, the API is pretty much a single transaction model so I'm a bit surprised at the delay....

Do note that a TTFB on an error page doesn't tell you anything about how long the application takes to initialize nor for the "environmental data" to be loaded from the file system and the DB. It only tells you how long it takes your webserver to respond to an error.

From here (Portland OR, USA - gmetrix server in Vancouver BC, CAN), a gtmetrix shows a page load time from http://www.shop4.nl/of 2.9 seconds for 26 requests and a total size of 1.14MB. Good page speed but not too far off from your API requests either! The API should be much faster than a page load considering it's one request and the payload should be a lot less (hundreds of bytes versus megabytes).

Thanks again for your quick response and thank you for thinking along. I will indeed post this issue in the helpdesk and bug tracker. I hope I've provided enough details in this topic for the CS-Cart staff to further look into it.

Do note that a TTFB on an error page doesn't tell you anything about how long the application takes to initialize nor for the "environmental data" to be loaded from the file system and the DB. It only tells you how long it takes your webserver to respond to an error.

I know, but it does tell that the issue is not caused by the network configuration, as 0.14 seconds is a pretty fast response - even if very little environmental data has to be loaded.

From here (Portland OR, USA - gmetrix server in Vancouver BC, CAN), a gtmetrix shows a page load time from http://www.shop4.nl/of 2.9 seconds for 26 requests and a total size of 1.14MB. Good page speed but not too far off from your API requests either! The API should be much faster than a page load considering it's one request and the payload should be a lot less (hundreds of bytes versus megabytes).

The page load time is indeed just fine (in The Netherlands, our target market, where our servers are hosted, it's faster than the results you got), and one would indeed expect the API requests to be much faster.

It's late here and my head is fuzzy. So I'm off to bed. But I love digging into this kind of puzzle. Unfortunately, it does take quite a bit of time to investigate and rarely is anyone every interested in paying for it. So, bugtracker is probably your best bet. Note that bugtracker is dealt with by helpdesk so it gets there automatically. It takes a helpdesk person to refer it to a developer from bugtracker.

I have posted the issue to the bug tracker: http://forum.cs-cart.com/tracker/issue-6488-very-poor-api-performance/

REST the server response format or the output format is JSON/XML which can be easily understood or followed. In case of SOAP, server response is in the form of WSDL, an advanced form of XML.
REST API can be deployed for functions like browsing through the app, adding to cart, chat etc while SOAP can be used for those which involve top-of-the-line security like payment transactions (payment gateway) etc.

Find out which category to use the API

Well thank you for your input but what exactly do you want to say and how is this relevant to the issue I'm trying to address?

We managed to vastly decrease the execution time of the MySQL UPDATE query by changing the storage engine of the product table from MyISAM to InnoDB. However we still consider the performance unacceptable with 500 - 1000 ms for a simple stock update. Please also refer to http://forum.cs-cart.com/tracker/issue-6488-very-poor-api-performance/for detailed information and an updated report from our performance analysis tool.