Professional Web Applications Themes

MySQL serverand hight CPU usage - MySQL

Hello, I have a website hosted on Windows using asp pages to display ads. The database server is a MySQL server (4.1.15) on an other server (bi-xeon 3GHz, 1Gb RAM) . The requests are quit big : One of the most used request looks like this one : SELECT SQL_CALC_FOUND_ROWS tblProduits.departementCode, paysFR, paysFR, tblproduits.produitID, produitNbImgs > 0 AS imgExiste, tblproprietesvaleurs1.propvaleurFR, tblproduits.prop9, (tblproduits.prop4 + 0.00) AS prop4, (tblproduits.prop5 + 0.00) AS prop5, (tblproduits.prop2 + 0.00) AS prop2, tblproprietesvaleurs6.propvaleurFR, (tblproduits.prop15 + 0.00) AS prop15 FROM tblproduits, tblpays, tblproprietesvaleurs AS tblproprietesvaleurs1, tblproprietesvaleurs AS tblproprietesvaleurs6 WHERE tblproduits.paysID = tblpays.paysID AND tblproduits.prop1 = tblproprietesvaleurs1.propvaleurID And ...

  1. #1

    Default MySQL serverand hight CPU usage

    Hello,

    I have a website hosted on Windows using asp pages to display ads.
    The database server is a MySQL server (4.1.15) on an other server
    (bi-xeon 3GHz, 1Gb RAM) .

    The requests are quit big :
    One of the most used request looks like this one :
    SELECT SQL_CALC_FOUND_ROWS tblProduits.departementCode, paysFR, paysFR,
    tblproduits.produitID, produitNbImgs > 0 AS imgExiste,
    tblproprietesvaleurs1.propvaleurFR, tblproduits.prop9,
    (tblproduits.prop4 + 0.00) AS prop4, (tblproduits.prop5 + 0.00) AS
    prop5, (tblproduits.prop2 + 0.00) AS prop2,
    tblproprietesvaleurs6.propvaleurFR, (tblproduits.prop15 + 0.00) AS
    prop15 FROM tblproduits, tblpays, tblproprietesvaleurs AS
    tblproprietesvaleurs1, tblproprietesvaleurs AS tblproprietesvaleurs6
    WHERE tblproduits.paysID = tblpays.paysID AND tblproduits.prop1 =
    tblproprietesvaleurs1.propvaleurID And tblproduits.prop20 =
    tblproprietesvaleurs6.propvaleurID And tblproduits.rubriqueID = 510 AND
    tblProduits.produitType <> 'S' AND tblProduits.produitType <> 'F' AND
    tblProduits.produitDateFin > CURRENT_DATE() AND tblProduits.produitActif
    = 'TRUE' ORDER BY produitID DESC LIMIT 0, 30

    I have a high CPU usage on the server (up to 75%). MySQL Administrator
    gives me the following indications :
    Connexions : 60
    Number of SQL Queries : 180
    Hitrate : 63%

    I'm using my-large.ini file and I already checked indexes on my database
    (35.000 records for tblproduits)

    Is there a way to improve the CPU usage or will I have to add a second
    server for clustering ? Will I have to change my database engine (ie
    migrate to MySQL 5 or SQL Server, for instance) ?

    Thanks for your help

    Fred
    Fred Guest

  2. #2

    Default Re: MySQL serverand hight CPU usage

    Fred wrote:
    > I have a high CPU usage on the server (up to 75%). MySQL Administrator
    > gives me the following indications :
    > Connexions : 60
    > Number of SQL Queries : 180
    > Hitrate : 63%
    Do you mean the key buffer hitrate from the Health->Memory tab? Why
    such a low value? If you're using my-large.ini, your key buffer should
    be at least 256MB. Isn't that large enough to store all your indexes fully?

    Have you considered preloading the indexes?
    See [url]http://dev.mysql.com/doc/refman/5.0/en/load-index.html[/url]
    > I'm using my-large.ini file and I already checked indexes on my database
    > (35.000 records for tblproduits)
    What does EXPLAIN tell you about index usage in your query?

    Which columns in your tables are indexed? I would recommend at least
    the following:

    - tblProduits.produitID
    - tblProduits.rubriqueID
    - tblProduits.produitDateFin
    - tblpays.paysID
    - tblproprietesvaleurs.propvaleurID

    But even with additional columns indexed in tblProduits, MySQL will
    probably use the primary key index only. Remember that MySQL can use
    only one index per table per query.

    Are these tables updated less frequently as they are queried? If a
    given query is likely to be repeated, and is read more frequently than
    the tables are updated, you could consider using the query cache:
    [url]http://dev.mysql.com/doc/refman/5.0/en/query-cache.html[/url]

    Is the query less expensive if you leave out the SQL_CALC_FOUND_ROWS
    modifier? I understand why you need that if you're using LIMIT, but it
    would be useful to know if that is the source of the great resource
    usage of this query.

    Regards,
    Bill K.
    Bill Karwin Guest

  3. #3

    Default MySQL serverand hight CPU usage

    Very high CPU load by 20% do you think? I want you to help.
    [URL=http://www.livetvizle.com]Tv izle[/URL]
    [URL=http://www.livetvizle.com/trhepsi/showtv_canli_izle.asp]Show Tv izle[/URL]
    [URL=http://www.livetvizle.com/tnt-tv-izle.asp]TNT izle[/URL]
    remixindir is offline Junior Member
    Join Date
    Jan 2012
    Location
    Türkiye
    Posts
    2

Similar Threads

  1. CPU usage of mysql on windows 2000.
    By somez72 in forum MySQL
    Replies: 3
    Last Post: April 4th, 01:18 PM
  2. CSS 100% table hight...
    By Brian in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: June 1st, 11:23 PM
  3. Frameset Hight
    By einish in forum Dreamweaver AppDev
    Replies: 1
    Last Post: May 16th, 04:43 PM
  4. stapel with different hight
    By hetta webforumsuser@macromedia.com in forum Macromedia Director Lingo
    Replies: 1
    Last Post: October 11th, 02:25 PM
  5. Are you able to hight light leters
    By Joe {RoastHorse} in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 16th, 12:58 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139