Databases
StackOverflow2013
Postsdbo
POOne mySQL database is very slow while others are fine
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOne mySQL database is very slow while others are fine
    primarykey
    data
    text
    <p>I have a mysql database that is currently running very slow. There are a number of databases on the server, and all other databases are running fine, however this one database is very slow, often taking between 2-10 seconds for a very simple update (updating 1 row in a small table) or select query (selecting 1 row from a small table).</p> <p>My question is to ask if anyone could think of a reason why this one database is running so slow. The traffic is minimal (it is the database for the development site). I have ruled out my scripts, as phpMyAdmin is also very slow and often times out with this database. I have also checked the running processes to check nothing untoward is there, and there can be no processes ongoing, but still the database will be running really slowly.</p> <p>So please can anyone shed some light on what may be happening here. I am very happy to give extra information if you require it.</p> <p>EDIT:</p> <p>Further to the comments, here is an example of a slow running query:</p> <pre><code>UPDATE `Users` SET `last active` = 1334763602 WHERE `ID` = 1 </code></pre> <p>This simple update query is taking between 2 and 10 seconds to complete. ID is a primary key and this table has around 30 records.</p> <p>Further to my initial problem, it does seem as if SELECT is not as bad, though I have seen slow periods for SELECT, a lot of SELECT statements currently are running fast, whereas UPDATE is still slow.</p> <p>EDIT 2:</p> <p>After more investigation it appears it might be tied to the InnoDB table type. I have changed a table to MyISAM and a query that was taking 1.5 seconds in InnoDB took just 0.0006 seconds in MyISAM. I guess my question becomes how do I best tune to InnoDB tables?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCodePB
    1. USCodePB
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COYou will need to provide specific information regarding the queries that are running slow, the table designs, some query profiles (using `EXPLAIN` etc), to be able to get any particularly specific response.
      singulars
      1. POOne mySQL database is very slow while others are fine
      1. USSimon at mso.net
    2. COAre all the database schemas the same in terms of tables and amount of records? Are there indexes created for the tables? Have you tried rebuilding indexes with optimize table?
      singulars
      1. POOne mySQL database is very slow while others are fine
      1. USChristian
    3. COThe databases are varying sizes, and the one that is slow is certainly the largest. There is another database which is similar but smaller (the live version of this development database, the development database has been added to). There are indexes across most tables (if not all), though I have not tried recreating them.
      singulars
      1. POOne mySQL database is very slow while others are fine
      1. USCodePB
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload