Hi Everyone ,
I’m Facing this Issue Since last 2 month on Production URL, When We call REST API for Multiple Purchase Receipt Creation some of the Purchase Receipts gets and then URL gets down or says connection time-out (for 10-15 mins) after it starts properly, this happens every time.
nginx logs:
2019/06/19 21:32:32 [error] 7996#7996: *632478 upstream timed out (110: Connection timed out) while connecting to upstream, client: 13.232.52.71, server: ####.#####.com, request: “POST /api/method/custom_app.custom_app.create_pr HTTP/1.1”, upstream: “http://127.0.0.1:8004/api/method/custom_app.custom_app.create_pr”, host: “####.#####.com”
2019/06/19 21:32:32 [error] 7995#7995: *632480 upstream timed out (110: Connection timed out) while connecting to upstream, client: 13.232.52.71, server: ####.#####.com, request: “POST /api/method/custom_app.custom_app.create_pr HTTP/1.1”, upstream: “http://127.0.0.1:8004/api/method/custom_app.custom_app.create_pr”, host: “####.#####.com”
2019/06/19 21:32:32 [error] 7998#7998: *632481 upstream timed out (110: Connection timed out) while connecting to upstream, client: 13.232.52.71, server: ####.#####.com, request: “POST /api/method/custom_app.custom_app.create_pr HTTP/1.1”, upstream: “http://127.0.0.1:8004/api/method/custom_app.custom_app.create_pr”, host: “####.#####.com”
For that, We applied followings scenarios:
1.nginx Tunning up:
- Increased keep_alive_timeout up to 50%
- Increased keepalive_ requests up to 50%
- Decreased max_fails up to 100 %
- Increased proxy_read_timeout up to 50%
2.Re-Tune Up MySQL Setting:
- Increased max-allowed-packet to 256M to 800M
- Increased max_connections to 1000 to 10000 connections
- Increased open_files_limit to 65535 to 1048576
- Also, Optimize All Database tables with optimization method MySQL
**3. Re-Indexing Done **:
- Customized and Custom Doctype
Using the above scenrios we reduce connection time out span from 10-15, but still URL gets down multiple PR API calls to server.
After Checking all logs we found in the slow query log :
Time: 190620 0:07:54
User@Host: [] @ localhost []
Thread_id: 15711 Schema: *********** QC_hit: No
Query_time: 189.036801 Lock_time: 0.000018 Rows_sent: 132640 Rows_examined: 132640
Rows_affected: 0
SET timestamp=1560969474;
select , timestamp(posting_date, posting_time) as “timestamp” from tabStock Ledger Entry
where item_code = ‘### ###’
and warehouse = ‘#######’
and ifnull(is_cancelled, ‘No’)=‘No’
and timestamp(posting_date, posting_time) > timestamp(‘2019-06-17’, ‘21:43:49’) and name!='*******’
order by timestamp(posting_date, posting_time) asc, name asc
for update
Currently Production Database Size 4.1GB and from Tables Size Calculation Stock Ledger Entry having Size 1273.99 MB
i’m not sure, is this query taking time respond to UI or WEB or is there any issue or reason to connection - time out.
Please Help out on this