MySQL queries: The XF 1.2 difference?
- Thread starter CTXMedia
- Start date
What I dislike about the "mysql queries" number is it is just a metric. It really does not mean anything at the end. You can have 10 queries vs. 50 and your forum still goes slow.
What people should be optimizing for is "page load time" regardless of how many queries the forum is doing.
Yes, there is a correlation, more queries usually means slower, but provided that one slow query or one bad join can take your forum experience to the ground it is hardly a good indicator. Then, just taking a look at the queries is misleading ("this forum software makes 3x less queries" - hence better - that would be a bad conclusion)
A modern ORM would keep the process in memory and would actually make a query instead of a join in several scenarios, which will benefit from caching the additional table specially on 1-1 joins, I have yet to come across a modern system that does not use a mapping technology and still uses plain queries. And those system give up the crazy joins and big queries mostly.
I see you have a nice panel in there, can you hunt down a graph that does "time spent doing queries" ? that would be incredibly interesting
What people should be optimizing for is "page load time" regardless of how many queries the forum is doing.
Yes, there is a correlation, more queries usually means slower, but provided that one slow query or one bad join can take your forum experience to the ground it is hardly a good indicator. Then, just taking a look at the queries is misleading ("this forum software makes 3x less queries" - hence better - that would be a bad conclusion)
A modern ORM would keep the process in memory and would actually make a query instead of a join in several scenarios, which will benefit from caching the additional table specially on 1-1 joins, I have yet to come across a modern system that does not use a mapping technology and still uses plain queries. And those system give up the crazy joins and big queries mostly.
I see you have a nice panel in there, can you hunt down a graph that does "time spent doing queries" ? that would be incredibly interesting
I disabled a small number, but I wouldn't expect any of them to have such a big impact: RCBD - RecentStatus (homepage sidebar only); Online Status (added a small icon to avatars in the postbit); Create Post on Report (only used for reports); and Multiple Account Detection. Also removed a mobile style that some members used - not many.
I've seen a reduction in other metrics too - which, overall, adds up to a lower load on the server (which is always a good thing, right?!
).I presumed that 1.2 was more efficient and therefore was the key element in the change. Nice work guys, if that's the case.
Really should be optimizing for both fast page load times *and* low query count. As you grow, scaling database queries is one of the hardest things to do (much easier to scale out web servers/HTTP requests).
If your pages take 100+ queries to render a page (for example in vBulletin 5), you are going to bottleneck on your DB server REALLY quickly.
You can also scale a DB, you can have replication, master-slave.
The proper way of doing optimization is doing a profiling of the application, based on the load time, detecting which is the bottleneck, and then optimizing for that bottleneck. If the bottleneck is the DB, then sure, optimize for that, and it can be total query time, or number of queries (since each query is a trip to the server, but not necessarily a new connection).
The problem with getting fixated on the query count is that it actually produces scenarios in which it is harder to scale the application, in XenForo there are a lot of joins done against the visitor Id (read markers, etc), which in turn end up rendering the query as non-cacheable from either the application or the query cache DB side.
vBulletin 5 didn't have it completely wrong, the problem is that they are using a technology that goes against the architecture, PHP by nature does not keep state, it serializes it, it does not have a native application scope. And MySql realy was designed with "establish a new connection every time" mentality. The scenario of multiple idempotent queries is not bad, but you need the proper architecture to support that, and I would argue that PHP+Mysql is hardly that, it makes all the queries again each request or does crazy unserializing. Both bad scenarios.
Yeah... that will scale reads to a certain degree... Not going to scale writes (although hopefully a site isn't doing a ton of writes for every page view). But you will still bottleneck on SQL reads before most other things (like PHP or web server requests).
