MySQL server 99.99% usage

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • O.L.
    #1

    MySQL server 99.99% usage

    Hello,

    On my LAMP webserver, I noticed that sometimes the MySQL daemon uses
    99.99% of the CPU, and does not answer SQL queries, while several
    minutes.
    Does someone know what could be the causes ?

    I searched on MySQL.com forums, and I found answers about query cache,
    but this is not enabled on my server.
    I looked the process list (SHOW FULL PROCESSLIST) :
    - almost all the queries where "Locked"
    - almost all queries where SELECT
    - there was no "difficult" query (big UPDATE or big DELETE or REPAIR or
    OPTIMIZE ...)
    My database is mainly used with the 'members' table which contains
    100000+ records, and a lot of INDEXes. All the queries shown in the
    process list were about this table.

    Any idea ? Any solution to prevent this periodical server lag ?

    Thanks in advance for your help :)
    Olivier (and excuse my bad english !)

    --
    Olivier Ligny
    Créateur web free-lance / www.cyber-tamtam.net


    Tags: None
    • Erwin Moller
      #2
      Re: MySQL server 99.99% usage

      O.L. wrote:
      Hello,
      >
      On my LAMP webserver, I noticed that sometimes the MySQL daemon uses
      99.99% of the CPU, and does not answer SQL queries, while several
      minutes.
      Does someone know what could be the causes ?
      >
      I searched on MySQL.com forums, and I found answers about query cache,
      but this is not enabled on my server.
      I looked the process list (SHOW FULL PROCESSLIST) :
      - almost all the queries where "Locked"
      - almost all queries where SELECT
      - there was no "difficult" query (big UPDATE or big DELETE or REPAIR or
      OPTIMIZE ...)
      My database is mainly used with the 'members' table which contains
      100000+ records, and a lot of INDEXes. All the queries shown in the
      process list were about this table.
      >
      Any idea ? Any solution to prevent this periodical server lag ?
      >
      Thanks in advance for your help :)
      Olivier (and excuse my bad english !)
      >
      Hi Oliver,

      You say you have a lot of indexes.
      This means that the simple insert or update you mentioned must also update
      all these indexes.
      Could that explain the high processorload?

      Remember that indexes can be usefull, but only if you do a lot more selects
      with the indexed columns in the where-clause than you do inserts/updates.
      Indexes slow down inserts/updates, but will greatly increase selects, so
      use them wisely.

      Regards,
      Erwin Moller

        Comment

        • O.L.
          #3
          Re: MySQL server 99.99% usage

          Erwin Moller a couché sur son écran :
          O.L. wrote:
          >
          >Hello,
          >>
          >On my LAMP webserver, I noticed that sometimes the MySQL daemon uses
          >99.99% of the CPU, and does not answer SQL queries, while several
          >minutes.
          >Does someone know what could be the causes ?
          >>
          >I searched on MySQL.com forums, and I found answers about query cache,
          >but this is not enabled on my server.
          >I looked the process list (SHOW FULL PROCESSLIST) :
          >- almost all the queries where "Locked"
          >- almost all queries where SELECT
          >- there was no "difficult" query (big UPDATE or big DELETE or REPAIR or
          >OPTIMIZE ...)
          >My database is mainly used with the 'members' table which contains
          >100000+ records, and a lot of INDEXes. All the queries shown in the
          >process list were about this table.
          >>
          >Any idea ? Any solution to prevent this periodical server lag ?
          >>
          >Thanks in advance for your help :)
          >Olivier (and excuse my bad english !)
          >>
          >
          Hi Oliver,
          >
          You say you have a lot of indexes.
          This means that the simple insert or update you mentioned must also update
          all these indexes.
          Could that explain the high processorload?
          >
          Remember that indexes can be usefull, but only if you do a lot more selects
          with the indexed columns in the where-clause than you do inserts/updates.
          Indexes slow down inserts/updates, but will greatly increase selects, so
          use them wisely.
          >
          Regards,
          Erwin Moller
          Hi Erwin, and thank you for your answer.

          When I looked the process list, there was only 1 update, and about 20
          or 30 SELECTs. My website mainly do SELECTs, rather than UPDATEs. The
          INSERTs are rare : only 1 every minute approximatively (each time a new
          member join the site). PHPMyAdmin shows me that the average query/sec
          is 15 queries by second on the whole server, and I suppose it's SELECTs
          at 80%.


          --
          Olivier Ligny
          Créateur web free-lance / www.cyber-tamtam.net


            Comment

            Previous template Next
            Working...