The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

High server load, need help with my.cnf & slowquery log

Discussion in 'Workarounds and Optimization' started by howzit, Feb 20, 2012.

  1. howzit

    howzit Member

    Joined:
    Feb 20, 2012
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Howzit! Free Advertising Classifieds
    cPanel Access Level:
    Website Owner
    Hi... My server is a bit bugged with MySql and I am not sure what is the problem...
    According to mysqltuner.pl I don't have much slow queries... (Or I am to dumb to read it as it shoul! :( )
    Can anyone please help me and look at my stats to help me?

    My mysqltuner.pl reads:
    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.92-community-log
    [OK] Operating on 32-bit architecture with less than 2GB RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 441M (Tables: 425)
    [!!] Total fragmented tables: 13
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 2d 20h 8m 39s (25M q [105.023 qps], 575K conn, TX: 1B, RX: 4B)
    [--] Reads / Writes: 84% / 16%
    [--] Total buffers: 74.0M global + 4.4M per thread (500 max threads)
    [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
    [!!] Maximum possible memory usage: 2.2G (113% of installed RAM)
    [OK] Slow queries: 2% (521K/25M)
    [OK] Highest usage of available connections: 6% (34/500)
    [OK] Key buffer size / total MyISAM indexes: 16.0M/201.0M
    [OK] Key buffer hit rate: 100.0% (11B cached / 1M reads)
    [OK] Query cache efficiency: 50.9% (11M cached / 21M selects)
    [!!] Query cache prunes per day: 2683271
    [OK] Sorts requiring temporary tables: 0% (16 temp sorts / 8M sorts)
    [!!] Joins performed without indexes: 216325
    [!!] Temporary tables created on disk: 49% (8M on disk / 16M total)
    [OK] Thread cache hit rate: 99% (34 created / 575K connections)
    [OK] Table cache hit rate: 37% (902 open / 2K opened)
    [OK] Open file limit used: 52% (1K/2K)
    [OK] Table locks acquired immediately: 99% (24M immediate / 24M locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_size (> 32M)
        join_buffer_size (> 1.0M, or always use indexes with joins)
        tmp_table_size (> 32M)
        max_heap_table_size (> 16M)
    
    root@cl-t094-411cl [~]#
    It says I have 13 fragmented tables but all is optimized on a daily basis.
    How can I access the slow-query log so I can trace problem look-ups and where the script is originated from?

    My top runs from Mysql using 1% CPU and next moment to 180% CPU...
    Is this a certain script or look up that's causing this, or a corrupted DB Table?

    my.cnf looks as follow:
    Code:
    root@cl-t094-411cl [~]# cat /etc/my.cnf
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    skip-innodb
    max_connections = 500
    key_buffer = 16M
    myisam_sort_buffer_size = 64M
    join_buffer_size = 1M
    read_buffer_size = 1M
    sort_buffer_size = 2M
    table_cache = 1024
    thread_cache_size = 64
    wait_timeout = 1800
    connect_timeout = 10
    max_allowed_packet = 16M
    max_connect_errors = 10
    query_cache_limit = 1M
    query_cache_size = 32M
    query_cache_type = 1
    long_query_time = 3
    log-queries-not-using-indexe
    log-slow-queries=/var/lib/mysql/slow.log
    
    [mysqld_safe]
    err-log=/var/log/mysqld.log
    open_files_limit = 8192
    
    [mysqldump]
    quick
    max_allowed_packet = 16M
    
    [myisamchk]
    key_buffer = 64M
    sort_buffer = 64M
    read_buffer = 16M
    write_buffer = 16M
    
    [mysql.server]
    user=mysql
    root@cl-t094-411cl [~]#
    

    Regards
    Joggie
     
    #1 howzit, Feb 20, 2012
    Last edited: Feb 20, 2012
  2. srpurdy

    srpurdy Well-Known Member

    Joined:
    Jun 1, 2011
    Messages:
    101
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Start with this
    max_connections = 200
    query_cache_size = 64M
    thread_cache_size = 16 //64 is probably too high.
    join_buffer_size = 256K
    read_buffer_size = 256K
    sort_buffer_size = 256K

    I don't see either of these so you should add them start at 64M
    tmp_table_size = 64M
    max_heap_table_size = 64M

    Also these two above should always be the same. right now your config has one at 32M and the other at 16M

    You seem to be missing this
    key_buffer_size = 220M
    [OK] Key buffer size / total MyISAM indexes: 16.0M/201.0M
    This should be a little higher than your total indexes and you may need to increase it as your databases grow.

    Your main problem is your databases are not using indexes properly. Is this a custom database or databases you have running? If so you need to optimize these databases. So for example if (table A) is connected to (table B) they're should be an index on the field that links them together. This saves mysql from having to go through more records to find the correct data which will allow mysql to run much faster. In some cases you may have to re-write queries if it's a custom system as occasionly as a programmer you can write a bad query, that could run better if written in a different way. This is also why you have slow queries as if mysql has to go through 100,000 records for example to find 1 record this is very slow. If you write a good query with proper indexes it may be possible to do that same thing with only going through 500 records or even less.

    Start with that above and restart mysql, wait 24 hours at least and post a new mysqltuner.pl

    All of the above go under the mysqld section

    Shawn
     
    #2 srpurdy, Feb 20, 2012
    Last edited: Feb 20, 2012
  3. howzit

    howzit Member

    Joined:
    Feb 20, 2012
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Howzit! Free Advertising Classifieds
    cPanel Access Level:
    Website Owner
    Thanks Shawn,

    Will do so... Thanks for helping.

    This is a custom build database and I started as a novice two years ago!

    I did the changes and my my.cnf now looks as follow:
    cat /etc/my.cnf
    Code:
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    skip-innodb
    max_connections = 200
    key_buffer = 220M
    myisam_sort_buffer_size = 64M
    join_buffer_size = 256k
    read_buffer_size = 256k
    sort_buffer_size = 256k
    tmp_table_size = 64M
    max_heap_table_size = 64M
    table_cache = 1024
    thread_cache_size = 16
    wait_timeout = 1800
    connect_timeout = 10
    max_allowed_packet = 16M
    max_connect_errors = 10
    query_cache_limit = 1M
    query_cache_size = 32M
    query_cache_type = 1
    long_query_time = 10
    log-queries-not-using-indexe
    log-slow-queries=/var/lib/mysql/slow.log
    
    [mysqld_safe]
    err-log=/var/log/mysqld.log
    open_files_limit = 8192
    
    [mysqldump]
    quick
    max_allowed_packet = 16M
    
    [myisamchk]
    key_buffer = 64M
    sort_buffer = 64M
    read_buffer = 16M
    write_buffer = 16M
    
    [mysql.server]
    user=mysql
    
     
  4. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Please remove the components not needed in an /etc/my.cnf file due to being defaults (I've removed them in what follows):

    Code:
    [mysqld]
    skip-innodb
    max_connections = 200
    key_buffer_size = 220M
    myisam_sort_buffer_size = 64M
    join_buffer_size = 256K
    read_buffer_size = 256K
    sort_buffer_size = 256K
    tmp_table_size = 64M
    max_heap_table_size = 64M
    table_cache = 1024
    thread_cache_size = 16
    wait_timeout = 1800
    max_allowed_packet = 16M
    query_cache_limit = 1M
    query_cache_size = 32M
    query_cache_type = 1
    log-queries-not-using-indexes
    log-slow-queries=/var/lib/mysql/slow.log
    
    [mysqld_safe]
    err-log=/var/log/mysqld.log
    open_files_limit = 8192
    
    [mysqldump]
    max_allowed_packet = 16M
    
    [myisamchk]
    key_buffer_size = 64M
    sort_buffer_size = 64M
    read_buffer_size = 16M
    write_buffer_size = 16M
    
    [mysql.server]
    user=mysql
    If you would like to see the variable default before adding it or modifying it, please run the following:

    Code:
    mysqladmin var | grep variablename
    Please replace variablename with the name of the variable such as long_query_time

    Code:
    mysqladmin var | grep long_query_time
    You'd get something like this to show that the 10 set previously before removing the value in /etc/my.cnf was already 10, so there is no need to set that same value when it's the one being used:

    Code:
    #mysqladmin var | grep long_query_time
    | long_query_time                         | 10   |
    Only add variables when changing the value from the default. This helps keep /etc/my.cnf to a reasonable length.
     
  5. howzit

    howzit Member

    Joined:
    Feb 20, 2012
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Howzit! Free Advertising Classifieds
    cPanel Access Level:
    Website Owner
    Thanks for the reply Tristan.
    I made the changes as you added.

    I also notice that the log-slow-queries=/var/lib/mysql/slow.log file is getting big at an alarming rate...

    Any suggestions regarding this?
     
  6. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    If the file is getting large, that means some database or databases are running a lot of slow queries. You'd want to check the log to see what database or databases those happen to be.
     
  7. srpurdy

    srpurdy Well-Known Member

    Joined:
    Jun 1, 2011
    Messages:
    101
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    After you see what slow queries you have check that they have proper indexes.

    like the example I have above.

    if you have a query in TABLE A that loads data from TABLE B. lets say an ID number links these two. TABLE B needs to have an index on the ID field that links it to TABLE A. You should do this also on where clauses as well.

    It's not so much a problem on small databases but when you have a lot of data this is when you will see the major difference it will make. It's quite a huge difference. You should read up on some about joins and indexes. They're is quite a few articles out there that can be helpful to understand it. :) More indexes = less table scans :) but of course you also don't want to go overboard and index something unless you actually need too. :)

    Once you start adding indexes you might end up having to increase the key_buffer_size again as the 220M you're using right now will be higher. According to the mysqltuner was 200M. I put a 20M headroom in there, but that may or may not be enough when you add indexes.

    For example when you start linking tables like here is a pretty complicated query. this query runs in 0.0001 seconds from memory. and 0.01 with a clean restart of mysql. There is probably room for improvement here too, but these tables combined have probably around 50,000 records. The Reason it runs so fast is because of the indexes if I had no indexes this query would take 0.5 or more seconds to run. That's a single query as well. (with 2 sub queries) So when you have many queries that kind of load it's easy to slow down an application.

    The other reason is before you had almost no caching so these operations were not running in memory and causing your cpu to do too much work, and likely running into high disk i/o. I noticed you kept 32M query cache. You probably need 64 at least but see how it goes on the next report. I'm thinking it will likely sit at 96MB at least in the end, but mostly was interested in getting a more stable mysql report first.
     
    #7 srpurdy, Feb 20, 2012
    Last edited: Feb 20, 2012
  8. howzit

    howzit Member

    Joined:
    Feb 20, 2012
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Howzit! Free Advertising Classifieds
    cPanel Access Level:
    Website Owner
    Firstly... I will probably say this a number of times more... But thanks for all the help from all you guys...

    This morning when the site got busy I got a message in my browser...
    Code:
    Error Connecting.
    To many connections
    I changed my.cnf to see if it can fix the problem.... (Had no time to wait for 24hrs to pass)
    I changed:
    query cache to 64 as per info given by srpurdy
    max_connections = 200 to 500 and the problem gone away...

    Restarted MySql and the system went haywire with Load Averages: 31.71 22.11 42.26
    I then restarted the server... (Made a few visitors angry.... :eek: )
    Now its back to Load Averages: 1.53 2.00 2.21 - STILL TO HIGH but workable.... :-(

    I also looked at the slow.log and the following lookup is repeated - Joined tables....
    Code:
    # Time: 120221  9:15:43
    # User@Host: XXXXXX @ localhost []
    # Query_time: 3  Lock_time: 0  Rows_sent: 30  Rows_examined: 144749
    
    And the query is
    Code:
    SELECT ads.id AS id, ad_image.image AS image, ads.userid AS userid, ads.adnr AS adnr,
              ads.location AS town, search_town.townFileName AS townlink, search_prov.provLabel AS province,
              adcat.name AS catname, adcat.clinkname AS clinkname, adsubcat.name AS subcatname, adsubcat.linkname AS subcatlink,
              adcat.id AS catid, adsubcat.id AS subcatid, ads.r_option AS r_option, ads.R_rand AS R_rand, 
              adcat.catcol1 AS catcol1, adcat.catcol2 AS catcol2, ad_image.aproved AS aproved, ads.scam AS scam
              FROM ads 
              JOIN adsubcat ON adsubcat.id=ads.subcatid
              JOIN adcat ON adcat.id=ads.catid
              JOIN search_town ON search_town.townLabel=ads.location
              JOIN search_region ON search_region.regionId=search_town.relRegionId
              JOIN search_prov ON search_prov.provId=search_region.relProvId
              JOIN ad_image ON ad_image.ad=ads.adnr
              WHERE adactive=1 AND ads.scam!='1' AND ads.userid=ad_image.user AND ad_image.aproved !='0' AND ad_image.picorder=1 AND ads.subcatid !='765' AND ads.subcatid !='767' AND ads.subcatid !='768'
              GROUP BY ads.id, ads.userid ORDER BY addate DESC LIMIT 30
    
    Now... All these fields are indexed and if I do this query in phpMyAdmin it give me the following query time:
    Showing rows 0 - 29 ( 30 total, Query took 0.6585 sec)....
    Why is it then logged in the slow query log???

    I am trying to read up on Google to see if I should change how the index are done and how to link the tables to do an index... (If it's possible)

    I am currently sitting between STRAIGHT_JOIN *, article and creating an index with foreign keys... And am REALLY confused...

    But... On the other hand... Can all of this not be a hardware issue??? This was not a problem on my server two months ago, but on the other hand the site now has to deal with anything between 500 & 1000 more visitors per day... The site is booming but my server/Mysql/my custom script/ is just failing the popularity...

    Please help...
     
  9. srpurdy

    srpurdy Well-Known Member

    Joined:
    Jun 1, 2011
    Messages:
    101
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Are you using persistent mysql connections? using over 200 seems really high to me.
    You should check what it shows under mysql connections on the mysqltuner.pl report now that you have got to peak load. The report you had above had only 34 connections. What kind of daily raw traffic are you getting? jumping 200 to 500 is huge. I'd start off moving it up much slower than that. But if your really using all 200 connections than it's not surprising your having load problems. You should disable pconnect on your application if you can. This will help a lot.

    Normally I can run 30,000 or more daily traffic on less than 10 mysql connections. So this is why I ask.

    The load you have is partly to do with the fact your database is not optimized. This is just not an acceptable result
    Query_time: 3 Lock_time: 0 Rows_sent: 30 Rows_examined: 144749

    if only 30 rows are outputted your query should not have to scan 144,000 rows to get that result. I'd look really carefully to make sure indeed all the indexes are in place. There is a lot of reasons why it can take longer in some cases so the fact it's getting flagged as a slow query is not that surprising.

    One thing will help is the next report though, and re-tweaking mysql again will help the load a bit as well as really the suggestions above were to get things under control first.
     
  10. howzit

    howzit Member

    Joined:
    Feb 20, 2012
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Howzit! Free Advertising Classifieds
    cPanel Access Level:
    Website Owner
    Mmm... Yes... Am sure I am using persistent mysql connections?

    I built a classified website from the ground up with very little knowledge and am learning as I go along...
    With only 2500 - 3000 visitors per day I am having this problem...

    I am not sure if I understand the indexing so nicely and also not sure if I should change my DB and what the effect will be to rebuild those indexes.

    Either way... I need to get the hang of this as I want to hit the international market with my system but know I am going to have trouble as the international market has 10000x more internet users as in my country!

    I will also in the meantime read up on pconnect and what the implications will be to disable it...
     
  11. howzit

    howzit Member

    Joined:
    Feb 20, 2012
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Howzit! Free Advertising Classifieds
    cPanel Access Level:
    Website Owner
    mysql pconnect is now disabled in my php.ini
     
  12. srpurdy

    srpurdy Well-Known Member

    Joined:
    Jun 1, 2011
    Messages:
    101
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Well make a backup of the database. Rebuilding the indexes should only help. :) Anyway I understand. I built a complex system a couple years back myself at the time thought I knew a lot but ended up learning a lot more than I thought about databases :) I'm no expert either. :) I always had a good grasp on php, but ended taking a lot more out of the project that was valuable. We're all always learning :) This kind of work it's impossible to know everything. :)

    At 2000-3000 unique per day this is a good test for a database, but like you said it's such a small portion of your target, but keep in mind your original my.cnf was not setup correctly to begin with, so These problems are related.
    I have a similar website that's developed by myself around the 4200-5000 range, however it has a smaller data size than yours. I'm doing that with a peak of 11 mysql connections after 20 Days uptime on mysql.

    This site runs with about 0.2 to 0.6 load depending on time of day. So there is light at the end of the tunnel if you can get things running more smooth. I'm also being a bit cheap with ram so I could probably lower this a bit. :)

    When you have mysql running for a day run a new report and post it here. See how things are progressing. :)

    You'll have to make changes occasionally after you have solid configuration you'll want to make small changes at that point. :)

    Shawn
     
    #12 srpurdy, Feb 21, 2012
    Last edited: Feb 21, 2012
  13. howzit

    howzit Member

    Joined:
    Feb 20, 2012
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Howzit! Free Advertising Classifieds
    cPanel Access Level:
    Website Owner
    Howzit! Shawn,

    I did indeed look at those Queries marked as taking to long... All the fields in the queries is indexed!!???
    Its now even more confusing... Not sure if I do the indexing as it should???

    Anyway... Here is the latest Mysqltuner report.
    :confused:
    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.92-community-log
    [OK] Operating on 32-bit architecture with less than 2GB RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 446M (Tables: 425)
    [!!] Total fragmented tables: 23
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 0h 28m 15s (12M q [139.358 qps], 268K conn, TX: 4B, RX: 2B)
    [--] Reads / Writes: 82% / 18%
    [--] Total buffers: 358.0M global + 1.2M per thread (500 max threads)
    [OK] Maximum possible memory usage: 951.8M (47% of installed RAM)
    [OK] Slow queries: 1% (216K/12M)
    [OK] Highest usage of available connections: 8% (44/500)
    [OK] Key buffer size / total MyISAM indexes: 220.0M/204.4M
    [OK] Key buffer hit rate: 100.0% (6B cached / 230K reads)
    [OK] Query cache efficiency: 68.5% (7M cached / 10M selects)
    [!!] Query cache prunes per day: 599641
    [OK] Sorts requiring temporary tables: 0% (696 temp sorts / 2M sorts)
    [!!] Joins performed without indexes: 100600
    [!!] Temporary tables created on disk: 48% (2M on disk / 4M total)
    [OK] Thread cache hit rate: 99% (253 created / 268K connections)
    [OK] Table cache hit rate: 63% (941 open / 1K opened)
    [OK] Open file limit used: 54% (1K/2K)
    [OK] Table locks acquired immediately: 99% (8M immediate / 8M locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        query_cache_size (> 64M)
        join_buffer_size (> 256.0K, or always use indexes with joins)
        tmp_table_size (> 64M)
        max_heap_table_size (> 64M)
    
     
  14. howzit

    howzit Member

    Joined:
    Feb 20, 2012
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Howzit! Free Advertising Classifieds
    cPanel Access Level:
    Website Owner
    Code:
    if you have a query in TABLE A that loads data from TABLE B. lets say an ID number links these two. TABLE B needs to have an index on the ID field that links it to TABLE A. You should do this also on where clauses as well
    How can a person link the index for joins and will it have a major inpact on my current DB structure or scripts?
     
  15. srpurdy

    srpurdy Well-Known Member

    Joined:
    Jun 1, 2011
    Messages:
    101
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Increase Query Cache to 128M

    You can try
    tmp_table_size = 96M
    max_heap_table_size = 96M

    If Temporary tables created on disk: 48% doesn't drop below 48% on the next report than go back to 64M, but you should probably let it run for a couple days first to make sure.

    keep in mind joins without indexes can happen on queries that don't show up in the slow log. So You should carefully check any query you have. Based on the report you have 100,000 joins without indexes in a day which is a lot. I would say look at queries that get run the most often as those are the likely ones that are not indexed properly.

    for example the page with the most traffic probably your homepage. Find all the queries than run on this page and look through each one. It's mostly likely a global query something that is being run on every page load.
     
  16. srpurdy

    srpurdy Well-Known Member

    Joined:
    Jun 1, 2011
    Messages:
    101
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    It will have zero impact on your structure or scripts except make them run faster.

    to give you a really basic example

    Tables
    video_categories
    videos

    Fields
    video_categories.id
    video_category.name
    --
    videos.id
    videos.cat_id
    videos.name

    videos.cat_id should have an index for example. as you'd likely have a query that these are connected. Any where clause or join, and order_by should have an index on the fields that are being used in the statement.
     
  17. howzit

    howzit Member

    Joined:
    Feb 20, 2012
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Howzit! Free Advertising Classifieds
    cPanel Access Level:
    Website Owner
    Ok... This might be a problem for me... I have no idea how to link the fields in two tables on an index... I am using MyISAM storage engine and I read somewhere that it must change to be able to join two or more table's fields...

    Will this have an impact on how I wrote my script or any data in my tables?

    Or... Does this only mean that I still have fields not individually indexed?


    (I made the changes on the my.cnf and will let it run for 2 days to get the results...)
     
  18. srpurdy

    srpurdy Well-Known Member

    Joined:
    Jun 1, 2011
    Messages:
    101
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    I think I didn't explain correctly.

    You don't have to change the storage engine adding an index to 1 table or multiple tables is all you need to do. It's as simple as pushing a button in phpmyadmin. It doesn't change anything at all by adding an index like I explained. The application will work exactly the same way. Just it will run faster.

    Here's another example

    video_categories.id
    video_categories.cat_id
    video_categories.name

    videos.id
    videos.name

    video_comments.id
    video_comments.video_id

    now if you have a query the loads say a specific video by id number. you also want the comments and this video to be in the right category. So your query would likely do that. so adding an index on cat_id and video_id it's as simple as that.

    So lets use the query you have.
    add indexes on.
    ads.subcatid
    ads.catid
    ads.location
    ads.adnr
    ads.scam
    ads.userid
    ad_image.ad
    ad_image.user
    ad_image.aproved
    ad_image.picorder
    search_town.townLabel
    search_region.regionId
    search_region.relProvId
    search_town.relRegionId
    search_prov.provId

    Also on
    addate (whatever field that refers to)

    Does that make sense?
     
    #18 srpurdy, Feb 23, 2012
    Last edited: Feb 23, 2012
  19. howzit

    howzit Member

    Joined:
    Feb 20, 2012
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Howzit! Free Advertising Classifieds
    cPanel Access Level:
    Website Owner
    :rolleyes: Or it was me not reading so nicely!

    Thanks Shawn...
    But tell me if there is indexes for all fields and it still show up in slow query log... Can it be a hardware issue?
     
  20. srpurdy

    srpurdy Well-Known Member

    Joined:
    Jun 1, 2011
    Messages:
    101
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Not always depends on how much data you have. How big is the database size?

    I don't think it's a hardware issue, as mysql is not running at it's best right now. So we can keep tweaking first. :)

    queries with or without indexes can still end up in the slow log. Just because they take a long time. Part of that can be because mysql is not running so well right now. You have this option on log_queries_not_using_indexes so even fast queries can show here. It will tell you the query time. Go through the log file and try to find any queries that are missing indexes, based on the numbers from the other report the slow query log should be giant sized by now.

    Do you have a new mysql tuner report?

    Shawn
     
Loading...

Share This Page