Changes to database queries in 1.2.3?

[thedarksyde]

Have there been any major changes to database queries or database handling in 1.2.3?  Hostgator just quarantined my database for the time being saying my queries were taking up significant use on the server.  I was taking up for than 50% server allocation on queries.  They pulled these down in the last hour.    I have asked for specific queries that were causing the load but I have not gotten a response back yet.

Are the open connections supposed to be open after the query is completed? I am trying to figure out the issue.

Running Queries:
*************************** 1. row ***************************

STATE: Copying to tmp table
TIME: 0
COMMAND: Query
INFO: SELECT DISTINCT p.manufacturer_id, m.name
FROM products p
LEFT JOIN manufacturers m ON p.manufacturer_id = m.manufacturer_id
WHERE p.product_id IN (SELECT DISTINCT p2c.product_id
FROM products_to_categories p2c
INNER JOIN products p ON p.product_id = p2c.product_id
WHERE p.status = '1' AND p2c.category_id IN (1, 2, 67))
*************************** 2. row ***************************

STATE: Copying to tmp table
TIME: 0
COMMAND: Query
INFO: SELECT DISTINCT p.manufacturer_id, m.name
FROM products p
LEFT JOIN manufacturers m ON p.manufacturer_id = m.manufacturer_id
WHERE p.product_id IN (SELECT DISTINCT p2c.product_id
FROM products_to_categories p2c
INNER JOIN products p ON p.product_id = p2c.product_id
WHERE p.status = '1' AND p2c.category_id IN (1, 2, 140))
*************************** 3. row ***************************

STATE: Copying to tmp table
TIME: 0
COMMAND: Query
INFO: SELECT DISTINCT p.manufacturer_id, m.name
FROM products p
LEFT JOIN manufacturers m ON p.manufacturer_id = m.manufacturer_id
WHERE p.product_id IN (SELECT DISTINCT p2c.product_id
FROM products_to_categories p2c
INNER JOIN products p ON p.product_id = p2c.product_id
WHERE p.status = '1' AND p2c.category_id IN (1, 105))
*************************** 4. row ***************************

STATE: Copying to tmp table
TIME: 0
COMMAND: Query
INFO: SELECT DISTINCT p.manufacturer_id, m.name
FROM products p
LEFT JOIN manufacturers m ON p.manufacturer_id = m.manufacturer_id
WHERE p.product_id IN (SELECT DISTINCT p2c.product_id
FROM products_to_categories p2c
INNER JOIN products p ON p.product_id = p2c.product_id
WHERE p.status = '1' AND p2c.category_id IN (98, 99))
*************************** 5. row ***************************
USER: darksyde_dbuser
DB: darksyde_localhost
STATE: Sending data
TIME: 0
COMMAND: Query
INFO: SELECT COUNT(DISTINCT p2c.product_id) AS total
FROM products_to_categories p2c
INNER JOIN products p ON p.product_id = p2c.product_id
WHERE p.status = '1' AND p2c.category_id IN (5, 48)
*************************** 6. row ***************************

STATE: Sending data
TIME: 0
COMMAND: Query
INFO: SELECT COUNT(DISTINCT p2c.product_id) AS total
FROM products_to_categories p2c
INNER JOIN products p ON p.product_id = p2c.product_id
WHERE p.status = '1' AND p2c.category_id IN (129, 270)
*************************** 7. row ***************************

STATE: Copying to tmp table
TIME: 0
COMMAND: Query
INFO: SELECT DISTINCT p.manufacturer_id, m.name
FROM products p
LEFT JOIN manufacturers m ON p.manufacturer_id = m.manufacturer_id
WHERE p.product_id IN (SELECT DISTINCT p2c.product_id
FROM products_to_categories p2c
INNER JOIN products p ON p.product_id = p2c.product_id
WHERE p.status = '1' AND p2c.category_id IN (118, 186))
*************************** 8. row ***************************

STATE: Copying to tmp table
TIME: 1
COMMAND: Query
INFO: SELECT DISTINCT p.manufacturer_id, m.name
FROM products p
LEFT JOIN manufacturers m ON p.manufacturer_id = m.manufacturer_id
WHERE p.product_id IN (SELECT DISTINCT p2c.product_id
FROM products_to_categories p2c
INNER JOIN products p ON p.product_id = p2c.product_id
WHERE p.status = '1' AND p2c.category_id IN (118, 186))
*************************** 9. row ***************************

STATE: Sending data
TIME: 0
COMMAND: Query
INFO: SELECT COUNT(DISTINCT p2c.product_id) AS total
FROM products_to_categories p2c
INNER JOIN products p ON p.product_id = p2c.product_id
WHERE p.status = '1' AND p2c.category_id IN (108, 109)
*************************** 10. row ***************************

STATE: Copying to tmp table
TIME: 1
COMMAND: Query
INFO: SELECT DISTINCT p.manufacturer_id, m.name
FROM products p
LEFT JOIN manufacturers m ON p.manufacturer_id = m.manufacturer_id
WHERE p.product_id IN (SELECT DISTINCT p2c.product_id
FROM products_to_categories p2c
INNER JOIN products p ON p.product_id = p2c.product_id
WHERE p.status = '1' AND p2c.category_id IN (230))
*************************** 11. row ***************************

STATE: Copying to tmp table
TIME: 1
COMMAND: Query
INFO: SELECT DISTINCT p.manufacturer_id, m.name
FROM products p
LEFT JOIN manufacturers m ON p.manufacturer_id = m.manufacturer_id
WHERE p.product_id IN (SELECT DISTINCT p2c.product_id
FROM products_to_categories p2c
INNER JOIN products p ON p.product_id = p2c.product_id
WHERE p.status = '1' AND p2c.category_id IN (42, 43))
*************************** 12. row ***************************

STATE: Sending data
TIME: 0
COMMAND: Query
INFO: SELECT COUNT(DISTINCT p2c.product_id) AS total
FROM products_to_categories p2c
INNER JOIN products p ON p.product_id = p2c.product_id
WHERE p.status = '1' AND p2c.category_id IN (189, 190)
*************************** 13. row ***************************

STATE: Copying to tmp table
TIME: 0
COMMAND: Query
INFO: SELECT DISTINCT p.manufacturer_id, m.name
FROM products p
LEFT JOIN manufacturers m ON p.manufacturer_id = m.manufacturer_id
WHERE p.product_id IN (SELECT DISTINCT p2c.product_id
FROM products_to_categories p2c
INNER JOIN products p ON p.product_id = p2c.product_id
WHERE p.status = '1' AND p2c.category_id IN (189))
*************************** 14. row ***************************

STATE: Sending data
TIME: 0
COMMAND: Query
INFO: SELECT COUNT(DISTINCT p2c.product_id) AS total
FROM products_to_categories p2c
INNER JOIN products p ON p.product_id = p2c.product_id
WHERE p.status = '1' AND p2c.category_id IN (70, 274)
*************************** 15. row ***************************

STATE: Copying to tmp table
TIME: 0
COMMAND: Query
INFO: SELECT DISTINCT p.manufacturer_id, m.name
FROM products p
LEFT JOIN manufacturers m ON p.manufacturer_id = m.manufacturer_id
WHERE p.product_id IN (SELECT DISTINCT p2c.product_id
FROM products_to_categories p2c
INNER JOIN products p ON p.product_id = p2c.product_id
WHERE p.status = '1' AND p2c.category_id IN (70, 272))

Open connections:

Recent Site Requests:
157.55.39.101   openblindboxes.com   GET   /le-merde-bangal-price-2011-dunny?keyword=2011%20Series
157.55.39.97   openblindboxes.com   GET   /aaron-meshon-dunny-2tone-series?keyword=Red%20Tail
157.55.39.97   openblindboxes.com   GET   /index.php?rt=product/search&keyword=Rob%20Zombie
157.55.39.97   openblindboxes.com   GET   /index.php?rt=product/search&keyword=Squid%20Kids%20Ink
157.55.39.97   openblindboxes.com   GET   /index.php?rt=product/search&keyword=flame
157.55.39.97   openblindboxes.com   GET   /montana-fatcap-series-1
157.55.39.98   openblindboxes.com   GET   /butters-south-park-kidrobot?keyword=South%20Park
157.55.39.98   openblindboxes.com   GET   /deranged-country-kid?keyword=Open%20Blind%20Boxes
157.55.39.98   openblindboxes.com   GET   /ryu-grey-street-fighter-series-1-kidrobot?keyword=World%20
173.35.126.96   openblindboxes.com   GET   /
173.35.126.96   openblindboxes.com   GET   /dunny-series-3?sort=date_modified-DESC
173.35.126.96   openblindboxes.com   GET   /dunny-series-3?sort=date_modified-DESC
173.35.126.96   openblindboxes.com   GET   /dunny-series-3?sort=date_modified-DESC
173.35.126.96   openblindboxes.com   GET   /dunny-series-3?sort=date_modified-DESC
173.35.126.96   openblindboxes.com   GET   /french?sort=date_modified-DESC
173.35.126.96   openblindboxes.com   GET   /french?sort=date_modified-DESC
173.35.126.96   openblindboxes.com   GET   /index.php?rt=checkout/cart
173.35.126.96   openblindboxes.com   GET   /index.php?rt=checkout/cart
173.35.126.96   openblindboxes.com   GET   /index.php?rt=checkout/cart
180.76.15.138   openblindboxes.com   GET   /marvel-frenzies
207.46.13.126   openblindboxes.com   GET   /index.php?rt=product/search&keyword=Big%20Bottle
207.46.13.34   openblindboxes.com   GET   /bruttino-tokidoki-cactus-friends?keyword=Kitty
207.46.13.34   openblindboxes.com   GET   /game-of-thrones-funko-mystery-minis
207.46.13.34   openblindboxes.com   GET   /mc-boooya-by-mad?keyword=Mad
220.181.108.81   openblindboxes.com   GET   /game-of-thrones-funko-mystery-minis
66.249.75.94   openblindboxes.com   GET   /scribe-bunny-costume-dunny-mardivale
76.28.36.114   openblindboxes.com   GET   /
99.135.189.220   openblindboxes.com   GET   /
99.135.189.220   openblindboxes.com   GET   /

[thedarksyde]

Here is show status like '%onn%' against my db. 


Variable_name
Value
Aborted_connects
5257957
Connections
87119605
Max_used_connections
451
Ssl_client_connects
0
Ssl_connect_renegotiates
0
Ssl_finished_connects
0
Threads_connected
12

[thedarksyde]

Issue:
Too many clients are aborted.

Recommendation:
Clients are usually aborted when they did not close their connection to MySQL properly. This can be due to network issues or code not closing a database handler properly. Check your network and code.

Justification:
Aborted client rate is at 40.08 per hour, this value should be less than 1 per hour

Used variable / formula:
Aborted_clients / Uptime

Test:
value * 60 * 60 > 1

[eCommerce Core]

Have there been any major changes to database queries or database handling in 1.2.3?  Hostgator just quarantined my database for the time being saying my queries were taking up significant use on the server.  I was taking up for than 50% server allocation on queries.  They pulled these down in the last hour.    I have asked for specific queries that were causing the load but I have not gotten a response back yet.

There were no major changes on databased for 1.2.3
Here are the changes:
https://github.com/abantecart/abantecart-src/blob/master/public_html/install/abantecart_upgrade.php
https://github.com/abantecart/abantecart-src/blob/master/public_html/install/abantecart_database_upgrade.sql

[eCommerce Core]

Are the open connections supposed to be open after the query is completed? I am trying to figure out the issue.

Mysql connections are closed after each request (page request). Not after every query. 

[thedarksyde]

Are the open connections supposed to be open after the query is completed? I am trying to figure out the issue.

Mysql connections are closed after each request (page request). Not after every query.

What is your current aborted rate per hour?   Obviously 40 per hour when it is supposed to be less than 1 seems like a large issue.