should have read:Max Number of Concurrent MySQL Queries
Max Number of Concurrent MySQL Connections
Vger
should have read:Max Number of Concurrent MySQL Queries
Max Number of Concurrent MySQL Connections
Vger
I bumped the connections from 250 to 750.
pconnect = false and I am not logging the parse times (but I am displaying them)
Of course I went mad with power. Have you ever tried going mad without power? It's boring. No one listens to you.
Apparently log slow queries was turned off with the last upgrade. I think I turned it back on (not super confident). Here is an example from the last log that we had. All the slow queries are similar.
# User@Host: ***[***] @ localhost []
# Query_time: 68 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
select p.products_id, p.products_image
from orders_products opa, orders_products opb, orders o, products p, hide_categories hc
where opa.products_id = '75170'
and opa.orders_id = opb.orders_id
and opb.products_id != '75170'
and opb.products_id = p.products_id
and opb.orders_id = o.orders_id
and p.products_status = 1
and p.master_categories_id = hc.categories_id
and hc.visibility_status = 0
group by p.products_id
order by o.date_purchased desc
limit 6;
This leads the host to think that Zen is the problem but I wonder if you are right and it is the news site. That is around when the problem started to arise but as I had said, I didn't think it would cause problems since it was shared before. Perhaps that is the next step, to try and move them to a different server.
Of course I went mad with power. Have you ever tried going mad without power? It's boring. No one listens to you.
I believe that particular SQL query is for the 'Also Purchased Products' on the product info page. It was slow in ZC v1.3.8a and sped up in v1.3.9 by the addition of an index. You've switched that option off for the moment so it shouldn't appear in your slow query log any more.
At the moment the only thing to do is to wait and see what happens and see if any more queries appear in the slow query log.
It is hard to tell if it is the other site causing the problem or not. You may never know unless you move it (if only temporarily) to another server.
Regards,
Christian.
If you are displaying Page Parse Times then they have to be monitored (generating sql queries), so turn that off.
If you are logging all slow sql queries on this VPS system then it will cause a signifiant drain on resources, so try turning that off too.
Sometimes too much information can be a bad thing.
Vger
The page parse time and number of SQL queries are recorded whether you display it or not. Displaying the information places no extra load on the server and generates no extra SQL queries. Right now it is useful information to have.
Enabling the slow query log will not place any extra load on the MySQL server. Again, right now it is useful information to have to actually fix the problem.
Regards,
Christian.
I guess we have to disagree, at least on the second point.
Some time ago we had a sudden load problem on one of our servers and enabled MySQL logging (admittedly for all queries) so that we could track down the site responsible for the sudden increase in load. We tracked down the site and got it to move, but the load did not markedly decrease until we stopped logging all MySQL queries - when it dropped back to normal levels.
So I still recommend turning off those queries for now, just to see if it makes a difference to the load. You'll soon know if it does or doesn't and if it doesn't they can be reinstated easily.
Vger
The problem existed prior to enabling the mysql logging (slow queries) and parse times. I enabled them to attempt to figure out the problem so I don't think they will help much in fixing the problem regardless of if they are affecting the load on the server or not.
Of course I went mad with power. Have you ever tried going mad without power? It's boring. No one listens to you.
In which case the other advice, to move the news website - given that the problem only began when you added that site - would seem to be your best option to resolve the problem.
Vger
Bookmarks