The Zen of Server Load



  • I'm helping set up an online store for a friend and he insists on using one of the free shopping carts, so I installed a little over 100,000 queries. If you ask about why your cart is slow on their forum, you'll be told that you "need a host capable of e-commerce" or something even stranger. My favorites: "You need to do some image optimization." and "the high number of queries can be knocked down by using indexes".



  • Apparently using thousands of queries is normal for some people, but I guess it's normal in the same way that touching the door knob 400 times is normal. Have you looked into OSCommerce? Not as many queries, but it's even crappier code, it requires register_globals in php. 



  • If your friend is not afraid of using PHPMyAdmin for updating, deleting or adding items, you could just write him his own in maybe half an hour, if you now a bit of PHP at least.



  • Or phpMinAdmin ... a lot less scary for someone non-technical :-)

    It's sad that perlMyAdmin never really took off.



  • <a href="http://www.magentocommerce.com/>http://www.magentocommerce.com/

    Don't know what it's worth, but at least the code should be cleaner, it requires php 5.1.2 + zend framework.



  • I definitely don't want to write a custom one. I've got test installations of both Magento (looks pretty nice) and OSCommerce (not as nice, although I don't think it needs register_globals any more) right now, along with Cube Cart.



  • @Cap'n Steve said:

    I definitely don't want to write a custom one.
     

    I can understand that (it doesn't sound like you're getting payed), but for the love of $DEITY, steer clear of OSCommerce! It is the kind of package that gives PHP a bad name. Zen Cart - which I just downloaded out of sheer curiosity - is actually nothing more than OSCommerce with a template system and some other extensions, and done pretty badly at that. I don't think the slow page loads would be solved tho; I did did some work on an OSCommerce site recently (my eyesight is starting to come back, don't worry) and while the amount of queries isn't exactly excessive (around 20 on most pages) they do include all sorts of counts and sums - things that don't make large sites happy.

    I had a quick look at Magento too, and that looks a lot better - nice and tidy OO-code, coding style reasonable and reasonably consistent, utilises PHP5-only functions etc. Can't vouch for its performance since I don't have time to actually test it, but out of those 3 it looks like it'd run circles around the other two. 



  • Lest just agree that this never would've happened if it was running linux on a xbox



  • At my last job, we built a geospecific CMS to handle advertising and what-not.  It served 11 million pages per day.  Each page took a minimum of 63 queries to assemble, and runs .  We ran MySQL v5, used ISAM table, and implemented caching at a few application layers.  Each request, at peak load times, took ~ 1/4 second full round-trip to complete.  We measured this with a custom in-house app (which was artifically bandwidth-limited to a 512k broadband connection), and also included the time to render the page.  The entire application ran at 0.05 load average, 5% cpu utilization on 6 2.4ghz Xeon cores.  There were two dual-processor web frontends each with 2GB of RAM, and a single dual-processor database backend with 2GB of RAM.  All had the processors listed.

     It's not hard to get fast performance with a lot of queries.  We did it by relying heavily on MySQL's caching.  We stayed pegged at 100% key utilization, and were averaging 99.5% cache hits.  It breaks down when you have a few large, complicated queries with lots of joins and sets.



  • Hmmm...



    foreach ($array_of_all_unique_ids as $item) {

    run SELECT * FROM theTable WHERE id = $item;

    }



    Bonus points if you can make each query do a full table scan and use an on-disk temp table for sorting.



  • @dtech said:

    If your friend is not afraid of using PHPMyAdmin for updating, deleting or adding items, you could just write him his own in maybe half an hour, if you now a bit of PHP at least.

    That is half an hour for what? I'm not deffending ZenCart at all, because I have had work with it and it certainly has a lot of issues, and has gave me more than one headache. But, a full e-commerce with all the features you can get from ZenCart or OScommerce or whatever, is much more than just an online catalogue and a shopping cart... you have to deal with product types, attributes, stock control, different types of customer, processing payments, credit card gateways, contacting warehouse, shipping costs, order control, taxes for different regions-countries, handling returned products, discounts, coupons, referals, wholesaler, group pricing, maybe multilanguage-country (add more work to shipping, payment, etc) and many other stuff.. 

    Of course I'm not saying that OP friend needs all these features, so maybe it's ok to do the ultra fast favour for the friend... but I will not want to be this guy when you die a strange dead, and he nees some help.



  • What would you say would be an OK number of queries for a page? I agree that 214 is far too high; the ideal would be 1 or 0, but this is normally impractical.

     

    Where would you guys say the borderlines are between "nice", "raised eyebrow" and "holy crap"? 



  • @fatdog said:

    a strange dead, and he nees some help.
     

    Holy crap, did you suddenly fall into a coma on the last part of this sentence?



  • @teedyay said:

    What would you say would be an OK number of queries for a page? I agree that 214 is far too high; the ideal would be 1 or 0, but this is normally impractical.

     

    Where would you guys say the borderlines are between "nice", "raised eyebrow" and "holy crap"? 

    I work at a college and I'm working a lot on the pages that display grades, attendance etc. for students and professors. The pages with most queries use at most 12 to load, and that's when they show lots of different kinds of data in a page. The average is about 4 queries/page load without a postback. I don't think there's a single query with more than 5 joins as well.

    Table normalization helps keeping things simple. When done well, it also makes for queries which are less demanding on the processor and disks. Caching can be your best friend sometimes. Someone should also talk about stored procedures, right?



  • @teedyay said:

    What would you say would be an OK number of queries for a page? I agree that 214 is far too high; the ideal would be 1 or 0, but this is normally impractical.

     

    Where would you guys say the borderlines are between "nice", "raised eyebrow" and "holy crap"? 

    It depends completely on what is being done for that page as well as the relative intensity of the queries themselves.  500 queries isn't so bad if there's a lot of processing going on and they all finish within 2 seconds total.  Meanwhile, a single query can literally take hours.  When it comes to web apps a good thing to remember is that a page should not take more than 2-3 seconds to load on a broadband connection.  It's good to get some tools to benchmark this: there are a few plug-ins for firefox that can help you out.  I always aim for page load times of less than one second.  That is from request until the page is fully returned, not counting external content like images, CSS or JS and not counting whatever JS has to run once the DOM is loaded.  Obviously, lower is better but if your pages are frequently taking more than 3 seconds you should really figure out why.  Most pages should only be returning a small amount of data so there shouldn't be megs and megs of stuff being queried from the DB.  You might have some massive updates, deletes or inserts but these can often be forked off to run in the background on the server while the actual page is served up.



  • [quote user="Renan "C#" Sousa"]Table normalization helps keeping things simple. When done well, it also makes for queries which are less demanding on the processor and disks.[/quote]

    Normalization is good practice to follow, but sometimes it hinders peformance and has to be dialed back for performance reasons.  Also remember that it's possible to have hundreds of queries that complete faster than a single, larger query.  Number of queries isn't a very useful metric by itself. 



  • @MasterPlanSoftware said:

    @fatdog said:

    a strange dead, and he nees some help.
     

    Holy crap, did you suddenly fall into a coma on the last part of this sentence?

    I lol'd, even though you left off the "die" part at the beginning.  Really, I can't comprehend what fatdog was saying in that last bit. 



  • @shakin said:

    foreach ($array_of_all_unique_ids as $item) {

    run SELECT * FROM theTable WHERE id = $item;

    }

    You may have been joking, but after looking at OSCommerce (which Zen Cart is based on), it looks like they select every product category, one at a time. I guess this is because you can have nested categories and they wanted the database to deal with it, rather than selecting the whole table and sorting it out in the code.



    Regarding number of queries, yes multiple queries can be faster than a single complicated one, but I'd still start worrying if a web page needed above 30 or so. I can't think of a situation where 200 queries would be acceptable, unless you had 200 tables that you just needed to get the COUNT(*) from, and even then there's probably a better way.



  • @MasterPlanSoftware said:

    Holy crap, did you suddenly fall into a coma on the last part of this sentence?

     

    Ok, I missed a D on "needs".

    Now, besides the typo I think I have not waken from that coma, because I fail to see what the problem is.
    Of course English is not my first language so I'm going to use that as an excuse. 



  • @Cap'n Steve said:

    Regarding number of queries, yes multiple queries can be faster than a single complicated one, but I'd still start worrying if a web page needed above 30 or so. I can't think of a situation where 200 queries would be acceptable...

    There are quite a few times.  For one, a tree data structure would have to be queried recursively and could easily consume many itself.  If each "node" in the tree has access permissions, attributes and a ton of other data that goes along with it, it can add up quickly.  Making blanket statements like "you should never need more than 30 queries" is pretty foolish. 



  • @fatdog said:

    Ok, I missed a D on "needs".

    Now, besides the typo I think I have not waken from that coma, because I fail to see what the problem is.
    Of course English is not my first language so I'm going to use that as an excuse. 

     

    The whole sentence made no sense, but the rest of your post seemed to be perfect. I found that funny.



  • @MasterPlanSoftware said:

    The whole sentence made no sense, but the rest of your post seemed to be perfect. I found that funny.

     

    Ok, it's a new day, I'm off the medication, didn't get drunk or high last night, and still that sentence makes perfect sense to me.  Damn, I used to be pretty good at English... 

    Now reading it again I can see why it makes sense to me, and not anyone else.. it has nothing to do with language barriers, or translation, it is because I'm a genious, and you guys can't comprehend the mind of a genious. 

     

    Now let me try that sentence again in a non-genious way:

    "Of course I'm not saying that the OP friend needs all these features, so maybe it's ok to do the ultra fast favour for the friend...But I will not want to be on this guy's* shoes when he needs some help with the code (For example, doing some modifications, or fixing a bug.) and it just happened that you* just died in a very strange way**.

     * "this guy" will be the OP friend who needs an ecommerce site. and "you" is the dude that I was replying to and who suggested to build an e-commerce site with php in half an hour, and will let his friend (this guy), use phpMyAdmin torun the shop.

    ** Dying in a strange way is pretty common way to die with smalltime php and VB freelance developers.

     



  • @morbiuswilters said:

    I always aim for page load times of less than one second.  That is from request until the page is fully returned, not counting external content like images, CSS or JS and not counting whatever JS has to run once the DOM is loaded.
     

    Wow, I was totally expecting to read about how you use the advanced ColdFusion framework and technology to achieve this. What a let down, you're really slipping as of late. I demand a refund. 



  • @Nandurius said:

    @morbiuswilters said:

    I always aim for page load times of less than one second.  That is from request until the page is fully returned, not counting external content like images, CSS or JS and not counting whatever JS has to run once the DOM is loaded.
     

    Wow, I was totally expecting to read about how you use the advanced ColdFusion framework and technology to achieve this. What a let down, you're really slipping as of late. I demand a refund. 

    I figured the use of ColdFusion (CF as we call it in the 'hood) was implied.  I mean, I said "page load times of less than one second" and not "buffer overflows that let people steal your credit cards", so obviously I wasn't talking about C#, Java, C, C++, assembly, PHP, Python, Perl, Ruby or Prolog.


  • @morbiuswilters said:

    I mean, I said "page load times of less than one second" and not "buffer overflows that let people steal your credit cards", so obviously I wasn't talking about C#, Java, C, C++, assembly, PHP, Python, Perl, Ruby or Prolog.
    I will give you one American quarter if you can accomplish a buffer-overflow in Prolog.


Log in to reply