SitePoint Sponsor

User Tag List

Page 3 of 3 FirstFirst 123
Results 51 to 66 of 66
  1. #51
    SitePoint Member todd's Avatar
    Join Date
    Sep 2003
    Location
    Los Angeles
    Posts
    10
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    quick way to check mysql query quality

    Probably the quickest way to see if your MySQL queries are fairly well put together is to use the following two tools from within MySQL. I think with Cpanel's implementation of phpMyAdmin you can only use one though (depends on your web hosting company). Direct MySQL server access is best.

    With the speed of your queries this may not be too important but really worth checking anyway.

    From the MySQL prompt (or from in the SQL window in phpMyAdmin)

    mysql>show full processlist;

    Look out for "Copying to tmp table" in any of your processes. A query that has to create temporary tables can almost always benefit from some attention. Look out for any queries that use a join without properly using a where statement on the first table (and each table as it is joined in, especially tables with a lot of data). Any "sort by" or "distinct" can be bad news as well.


    The second is just a general indicator that indexing may help - kind of tough on a shared server

    mysql>show status;

    Look for "Handler_read_rnd_next" - compare it with the other "Handler_read_*" - if "Handler_read_rnd_next" is really large, look into your indexing and your where statements. If you have a suspect page, you can try checking the number for "Handler_read_rnd_next" then running your page a bunch of times and checking again right away.

    There can definitely be a lot of things that will run up the load and it can be very difficult to troubleshoot without being able to use command line tools. You might be headed for a good VPS or dedicated server with your needs.

    Well, hope that helps!
    --- Have a good day! ---
    Todd
    Development and Administration Group
    InMotion Hosting, Inc.

  2. #52
    SitePoint Evangelist Daijoubu's Avatar
    Join Date
    Oct 2002
    Location
    Canada QC
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by CrabbyX
    I think I may have solved my problem. I previously assigned a function for output buffering which manually gzencoded and reduced excess whitespace for all HTML files. I will update you with a definite answer to whether it has worked later.
    Striping extra whitespace, especialy with preg_replace, is useless and use quite alot of CPU time
    Increasing the compression level by 1 is faster and saves more bytes (say, from level 1 to 2)

    Btw, do not use anything above level 1 as the saving would be minimal but use quite more processing power
    If you're no longer using your own function but ob_gzhandler(), use ini_set('zlib.output_compression_level', 1) to set it

    If you still have your own function, this is the fastest way to accomplish it:
    PHP Code:
    if (strpos($_SERVER['HTTP_ACCEPT_ENCODING'], 'gzip') !== false)
    {
        
    header('Content-Encoding: gzip');
        echo 
    "\x1f\x8b\x08\x00\x00\x00\x00\x00"gzcompress($content1);
    }
    else
    {
        
    // Code to strip space/tabs/newlines if browser doesn't support gzip
        //echo str_replace("\t", '', $content);

    I've tested douzens of browsers and all of them ignore the ending pack('V', CRC/lenght)

    Btw, using output buffering and echo() is more effective than adding content to an $variable .= 'content'

    Quote Originally Posted by noddy
    is your server using the zend optimiser. I can tell you it makes a massive difference to the speed of the php software.
    The Zend Optimizer usually slow things down! The time it waste on "optimizing" the code outweigh the benefits
    Unless you have bunch of badly written loops, I doubt it will make any difference (except adding to the load...)

    As for mod_rewrite, that thing also consume power, either or not it's in an .htaccess or directly inside httpd.conf
    And aren't you in a shared/virtual environment? How are you going to change Apache settings?

    As for optimizing your queries, run EXPLAIN on your SELECT and see what's mysql doing
    Adding index or/and tweaking your row type can help
    Even if it takes 0.01secs, it doesn't mean they're optimized
    Speed & scalability in mind...
    If you find my reply helpful, fell free to give me a point

  3. #53
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello,

    The load caused by arcadexl has now seem to have leveled out after CrabbyX has modified some of his code. It is usually staying below the 0.60 mark which is still quite high but it should be ok.

    I ran Apache Benchmark on arcadexl and some other sites on the server, here are the results:

    Code:
    root@server [~]# ab -n 100 http://www.arcadexl.com/
    This is ApacheBench, Version 2.0.40-dev <$Revision: 1.121.2.1 $> apache-2.0
    Copyright (c) 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
    Copyright (c) 1998-2002 The Apache Software Foundation, http://www.apache.org/
    
    Benchmarking www.arcadexl.com (be patient).....done
    
    
    Server Software:        Apache/1.3.33
    Server Hostname:        www.arcadexl.com
    Server Port:            80
    
    Document Path:          /
    Document Length:        39641 bytes
    
    Concurrency Level:      1
    Time taken for tests:   29.975571 seconds
    Complete requests:      100
    Failed requests:        99
       (Connect: 0, Length: 99, Exceptions: 0)
    Write errors:           0
    Total transferred:      3996125 bytes
    HTML transferred:       3959725 bytes
    Requests per second:    3.34 [#/sec] (mean)
    Time per request:       299.756 [ms] (mean)
    Time per request:       299.756 [ms] (mean, across all concurrent requests)
    Transfer rate:          130.17 [Kbytes/sec] received
    
    Connection Times (ms)
                  min  mean[+/-sd] median   max
    Connect:        0    0   0.0      0       0
    Processing:   225  299 133.5    247     976
    Waiting:      213  286 132.4    235     958
    Total:        225  299 133.5    247     976
    
    Percentage of the requests served within a certain time (ms)
      50%    247
      66%    259
      75%    291
      80%    331
      90%    440
      95%    661
      98%    812
      99%    976
     100%    976 (longest request)
    Code:
    root@server [~]# ab -n 100 http://www.hotdesign.co.uk/
    This is ApacheBench, Version 2.0.40-dev <$Revision: 1.121.2.1 $> apache-2.0
    Copyright (c) 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
    Copyright (c) 1998-2002 The Apache Software Foundation, http://www.apache.org/
    
    Benchmarking www.hotdesign.co.uk (be patient).....done
    
    
    Server Software:        Apache/1.3.33
    Server Hostname:        www.hotdesign.co.uk
    Server Port:            80
    
    Document Path:          /
    Document Length:        4532 bytes
    
    Concurrency Level:      1
    Time taken for tests:   2.344284 seconds
    Complete requests:      100
    Failed requests:        0
    Write errors:           0
    Total transferred:      480900 bytes
    HTML transferred:       453200 bytes
    Requests per second:    42.66 [#/sec] (mean)
    Time per request:       23.443 [ms] (mean)
    Time per request:       23.443 [ms] (mean, across all concurrent requests)
    Transfer rate:          200.06 [Kbytes/sec] received
    
    Connection Times (ms)
                  min  mean[+/-sd] median   max
    Connect:        0    0   0.0      0       0
    Processing:    18   22  19.8     19     196
    Waiting:       12   21  19.9     19     195
    Total:         18   22  19.8     19     196
    
    Percentage of the requests served within a certain time (ms)
      50%     19
      66%     20
      75%     20
      80%     20
      90%     22
      95%     25
      98%     84
      99%    196
     100%    196 (longest request)
    As you can see arcadexl's average request time is just 300ms, whereas hotdesigns is just under 24ms. It is noticable that arcadexl is slower, could this still be the amount of queries / includes?

    Regards,

    J Fox
    GlookiHost.com - Simple, Fast, Easy
    [ Shared Hosting | Reseller | Domains ]

  4. #54
    SitePoint Zealot
    Join Date
    Feb 2005
    Location
    UK
    Posts
    121
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just adding my 2 cents: Why does your page use 20 queries?

    I ask this because, in my experience, many web and php programmers have no idea how to use a database properly. To me, 20 queries sounds like you are running a query for each table instead of using joins, group by, etc. And looking at your ArcadeXL site I don't see anything that should require so many queries. If you would like to post them here I can see what can be done to improve that part of your code.

  5. #55
    SitePoint Evangelist Daijoubu's Avatar
    Join Date
    Oct 2002
    Location
    Canada QC
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could use IPB DB class and make use of it's debug
    ie: http://ptprophecy.com/forum/?debug=1
    Speed & scalability in mind...
    If you find my reply helpful, fell free to give me a point

  6. #56
    SitePoint Addict CrabbyX's Avatar
    Join Date
    Aug 2004
    Location
    England, UK
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My home page uses about 12 queries max. Most are due to random display of items, latest played games, and users online/sessions.

    I already use a IPB SDK and have modified the DB driver to tell me what queries are being used.
    michael.Crabbe

  7. #57
    SitePoint Evangelist Daijoubu's Avatar
    Join Date
    Oct 2002
    Location
    Canada QC
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by CrabbyX
    My home page uses about 12 queries max. Most are due to random display of items, latest played games, and users online/sessions.

    I already use a IPB SDK and have modified the DB driver to tell me what queries are being used.
    And is it possible for us to take a look at that output?
    Speed & scalability in mind...
    If you find my reply helpful, fell free to give me a point

  8. #58
    SitePoint Addict CrabbyX's Avatar
    Join Date
    Aug 2004
    Location
    England, UK
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    just add explain=1 to the query string on any page:

    http://www.arcadexl.com/?explain=1
    michael.Crabbe

  9. #59
    SitePoint Zealot
    Join Date
    Feb 2005
    Location
    UK
    Posts
    121
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by CrabbyX
    just add explain=1 to the query string on any page:

    http://www.arcadexl.com/?explain=1
    Why should we? It is not us that has the problem. We are asking to see the whole set of queries to see if they cannot be rewritten to require fewer in number and also less processing of the resultsets. Now if you can't be bothered to post them, we can't be bothered to look them up ourselves.

  10. #60
    SitePoint Addict CrabbyX's Avatar
    Join Date
    Aug 2004
    Location
    England, UK
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why have you bothered getting yourself worked up and suggesting I can't be bothered putting the queries here? It's not nice to judge.

    I will update this post in a couple of hours with all queries that are used often enough for them to be logged
    1. 2.5657: DELETE FROM ibf_sessions WHERE running_time < [VALUE] or ip_address='[VALUE]'
    2. 1.8772: SELECT * FROM `ibf_sessions` WHERE `member_name`!='' AND `running_time`>[VALUE] ORDER BY `running_time` DESC
    3. 0.2393: SELECT * FROM `ibf_games_list` ORDER BY RAND() LIMIT 8
    4. 0.2317: SELECT moderator.mid as is_mod, moderator.allow_warn, m.id, m.name, m.mgroup, m.password, m.email, m.restrict_post, m.view_sigs, m.view_avs, m.view_pop, m.view_img, m.auto_track, m.mod_posts, m.language, m.skin, m.new_msg, m.show_popup, m.msg_total, m.time_offset, m.posts, m.joined, m.last_post, m.last_visit, m.last_activity, m.dst_in_use, m.view_prefs, m.org_perm_id, m.temp_ban, m.sub_end, g.* FROM ibf_members m LEFT JOIN ibf_groups g ON (g.g_id=m.mgroup) LEFT JOIN ibf_moderators moderator ON (moderator.member_id=m.id OR moderator.group_id=m.mgroup ) WHERE m.id=[VALUE]
    5. 0.0552: SELECT * FROM `ibf_games_list` LEFT JOIN `ibf_games_champs` ON `ibf_games_champs`.`champ_gid`=`ibf_games_list`.`gid` WHERE `ibf_games_list`.`gcat`='[VALUE]' ORDER BY `ibf_games_list`.`added` DESC LIMIT 25,25
    6. 0.0386: SELECT `score` FROM `ibf_games_scores` WHERE `mid`='[VALUE]' LIMIT 1
    7. 0.0289: SELECT id FROM ibf_members WHERE LOWER(name)='[VALUE]'
    8. 0.0214: SELECT * FROM `ibf_links` ORDER BY `text`
    9. 0.0124: SELECT * FROM `ibf_games_list` ORDER BY `gcount` DESC LIMIT 10
    10. 0.0006: SELECT `avatar`, `avatar_size` AS `size` FROM `ibf_members` WHERE `id` = ''
    11. 0.0006: SELECT MAX(id) as new_id FROM ibf_members
    12. 0.0006: UPDATE ibf_stats SET MEM_COUNT=MEM_COUNT+1, LAST_MEM_NAME='[VALUE]'
    13. 0.0006: SELECT * from ibf_badwords
    14. 0.0006: SELECT `gid`, `highscore_type`, `gtitle` FROM `ibf_games_list` WHERE `gid`='[VALUE]' LIMIT 1
    15. 0.0006: SELECT `s_id` FROM `ibf_games_scores` ORDER BY `s_id` DESC LIMIT 1
    16. 0.0007: SELECT * from ibf_pfields_data WHERE fedit='[VALUE]'
    17. 0.0007: SELECT * FROM `ibf_stats` LIMIT 1
    18. 0.0007: UPDATE ibf_members SET last_activity='[VALUE]' WHERE id=[VALUE]
    There are a few, although I'm not sure how you can shorten them without knowing which pages they're executed on, but whatever you insist!
    Last edited by CrabbyX; Feb 22, 2005 at 11:43.
    michael.Crabbe

  11. #61
    SitePoint Evangelist Daijoubu's Avatar
    Join Date
    Oct 2002
    Location
    Canada QC
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by CrabbyX
    just add explain=1 to the query string on any page:

    http://www.arcadexl.com/?explain=1
    Cool, those numbers doesn't mean much, now if you can run EXPLAIN SELECT * FROM tbl...

    And what are those 2 queries for?
    PHP Code:
    0.0027
    SELECT 
    FROM `ibf_sessionsWHERE `member_name`='' AND `running_time`>1109092767 AND `location`='Arcade,,' ORDER BY `running_timeDESC

    0.0025 
    SELECT 
    FROM `ibf_sessionsWHERE `member_name`!='' AND `running_time`>1109092767 AND `location`='Arcade,,' ORDER BY `running_timeDESC 
    Oh and btw, you shouldn't backtick strings or quote integers in MySQL...

    Looking at http://www.arcadexl.com/games/action.html?explain=1
    You have a query that takes 0.4secs to execute...
    PHP Code:
    SELECT FROM `ibf_games_listLEFT JOIN `ibf_games_champsON `ibf_games_champs`.`champ_gid`=`ibf_games_list`.`gidWHERE `ibf_games_list`.`gcat`='3' ORDER BY `ibf_games_list`.`addedDESC LIMIT 0,25 
    I'm sure this one can be tweaked and sometimes, 2 small queries do a better job than one with bunch of joins and temporary table

    One more thing, it looks like MySQL is caching the results so you may want to add SQL_NO_CACHE after SELECT for more accurate debugging
    Speed & scalability in mind...
    If you find my reply helpful, fell free to give me a point

  12. #62
    SitePoint Addict CrabbyX's Avatar
    Join Date
    Aug 2004
    Location
    England, UK
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Backtick strings? I have not backticked any strings, only the names of fields and tables, which I'm sure is 100% correct.

    Sorting out EXPLAINs now. Sorting out double query now.
    michael.Crabbe

  13. #63
    SitePoint Evangelist Daijoubu's Avatar
    Join Date
    Oct 2002
    Location
    Canada QC
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    as `stuffs`
    Speed & scalability in mind...
    If you find my reply helpful, fell free to give me a point

  14. #64
    SitePoint Addict CrabbyX's Avatar
    Join Date
    Aug 2004
    Location
    England, UK
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Those two queries, one is for selecting all guests (no-names) from the table and the other for selecting active members. I can probably shorten that now.

    Does EXPLAIN actually execute UPDATEs/DELETEs?
    michael.Crabbe

  15. #65
    SitePoint Evangelist Daijoubu's Avatar
    Join Date
    Oct 2002
    Location
    Canada QC
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    EXPLAIN only works with SELECT
    As for those 2 queries to ibf_sessions, I would suggest you to either grab them all and process with PHP (what IPB do on the board index) or run a SELECT for those with a member_id and a COUNT(*) for the guests
    Speed & scalability in mind...
    If you find my reply helpful, fell free to give me a point

  16. #66
    SitePoint Addict CrabbyX's Avatar
    Join Date
    Aug 2004
    Location
    England, UK
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Would a PHP rand() from an array selected from the database be quicker than doing ORDER BY rand()? .. from a table with currently 170 rows, likely to end up about 1000 in the future?

    Something like this:

    $result = mysql_query( "SELECT * FROM table" );
    $num_rows = mysql_num_rows( $result );
    $index = rand( 0, ($num_rows-1) );
    $random_record = mysql_result( $result, $index );
    Last edited by CrabbyX; Feb 24, 2005 at 11:17.
    michael.Crabbe


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •