Results 1 to 6 of 6
  1. #1
    Join Date
    May 2012
    Posts
    2

    Red face Unanswered: sql to correct errors

    Hi friends,
    I can not use a website for a long time. SQL database table structure of the problem. Excessive CPU usage values​​. Sql files by choice, the problems between the structure of the table, perfect Would you recommend a program that could transform the situation?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no I wouldn't
    But thats because I don't understand the problem
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    and i couldn't, because i don't know of any

    my understanding of the problem is that somebody's web site is down because the sql that runs the pages is inefficient, and he wants to know whether the problem is in the design of the tables or are the queries poorly written

    he then asks for recommendation of a program that could pinpoint the source of the processing inefficiencies

    and i don't know of any
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    May 2012
    Posts
    2

    Arrow The Message

    The message;

    "

    Hello,
    This message is to advise you of a temporary block placed on your database. The database was found to be consuming an inordinate amount of processor time, to the point of degrading overall system performance. While we do limit each account to no more than 25% of a system's CPU in our terms of service, we do not actively disable accounts until they greatly exceed that number, which is what happened in this case. Requests to this database may become degraded by limiting the maximum number of queries or connections for a limited amount of time, or if there are sustained issues, ultimately we may be forced to block access to this database until the issue has been resolved.
    Resolving this situation may be as simple as adding additional indexes to your database, optimizing the queries used, or something equally easy. If not, it may simply be a matter of moving this database to dedicated services, as it may have outgrown a shared environment.
    If you believe you have a solution to this overuse, we are happy to discuss the situation with you and possibly reinstate the database on the server. Otherwise, we will be happy to assist you with the upgrade process if a dedicated server is the most appropriate solution. Thank you, and we look forward to hearing from you shortly.
    ~~~
    Excessive MySQL activity is caused by (a) a long-running process that locks a table, causing other queries to back up, (b) a query that is not optimized ][example: select all from ... and involving a large or complex query], (c) huge table copies/maintenance during peak hours.

    NOTE:, the following are just possible fixes or suggestions, and are not endorsed or supported by HostGator. They are included in the hope that they may apply to your situation, and/or help you reduce the amount of resources your SQL queries consume. As always, it's best to backup any data before making any changes or adjustments.
    First and foremost, you may need to optimize your tables. The frequency depends on the size and usage of the database, but most databases would benefit from doing something like this on a yearly basis: a) Enter your phpMyAdmin/MySQL control panel. Click on the database (not the table, the database name), and on the right hand column your tables should be listed. Scroll down till you see the .Check all. link. Click on that link, make sure all database tables are checked and then from the drop-down next to it, and carefully select .Optimize table..
    Additionally, adding indexes to your table(s) may improve performance. If you're not sure what you're doing, it's best not to modify any table; caution is recommended. There are various articles (Four Ways to Optimize Your MySQL Database - Developer.com and Optimizing MySQL: Queries and Indexes — DatabaseJournal.com). It may be best to Google for something like [Your Software Name] MySQL indexes for suggestions.
    CPU_TIME:421 table_rows_read:178354252 SELECTS:2646 ROWS_UPDATED:0 ROWS_FETCHED:0 BUSY_TIME:482 BYTES_SENT:20386770 BYTES_RECEIVED:330773


    Top table row reads:
    DB_USER: liveand_liveand -- TOTAL_CONNECTIONS: 324 -- CPU_TIME: 421 -- TABLE_ROW_READS: 178354252 -- SELECT_COMMANDS: 2646 -- UPATE_COMMANDS: 200 -- BUSY_TIME: 482 -- BYTES_SENT: 20386770 -- BYTES_RECEIVED: 330773



    Top CPU TIME:
    DB_USER: liveand_liveand -- TOTAL_CONNECTIONS: 324 -- CPU_TIME: 421 -- TABLE_ROW_READS: 178354252 -- SELECT_COMMANDS: 2646 -- UPATE_COMMANDS: 200 -- BUSY_TIME: -- BYTES_SENT: 20386770 -- BYTES_RECEIVED: 330773



    liveand 24635 7.0 0.1 34336 13468 ? SN 01:52 0:00 /usr/bin/php /home/x/public_html/ara.php
    root 24660 0.0 0.0 2548 864 ? SN 01:52 0:00 sh -c ps aux | grep liveand
    root 24662 0.0 0.0 1988 424 ? SN 01:52 0:00 grep liveand

    Tue Jul 26 01:52:33 CDT 2011
    Running Processes:
    liveand 24635 7.0 0.1 34336 13468 ? SN 01:52 0:00 /usr/bin/php /home/x/public_html/ara.php

    Running Queries:
    *************************** 1. row ***************************
    USER: x_x
    DB: x_x
    STATE: Sending data
    TIME: 0
    COMMAND: Query
    INFO: SELECT * FROM haber where durum='1' and (baslik LIKE '%YMM%' OR aciklama LIKE '%YMM%') order by hid DESC limit 300,30

    Current Site Requests:
    x.ip x.com /amy-winehouse--tears-dry-on-their-own-lyrics.html

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    meb-oyun, i would definitely follow your host's advice and optimize your queries

    the first article is good, and the second one, despite being over 10 years old, is also good
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    looking at the number of tablescans I'd reckon that you will get a big performance boost by making certain that columns used frequently in join expressions are indexed
    that columns used frequently in where clauses are indexed.

    but in the mean time go read the supplied references
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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