Databases
StackOverflow2013
Postsdbo
POServer Crash - Optimizing Mysql queries
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POServer Crash - Optimizing Mysql queries
    primarykey
    data
    text
    <p>I've been on shared hosting for a long time and have been running the same queries for months without a problem. All of the sudden I get an email from my host saying they put a block on my database because "The database was found to be consuming an inordinate amount of processor time". What I don't understand is why now? So anyways I thought maybe I need to go VPS. Trying to get my site up on a VPS and immediately it the whole site has an out of memory limit error and has crashed. For a while the connection was timing out while going to the site at all. </p> <p>None of my tables are very large nothing more than 10,000 rows. Here are some things my original host sent me:</p> <pre><code>DB_USER: my_user -- TOTAL_CONNECTIONS: 37 -- CONNECTED_TIME: 4906 -- CPU_TIME: 1920 -- TABLE_ROW_READS: 79999077 -- SELECT_COMMANDS: 214 -- UPDATE_COMMANDS: -- BUSY_TIME: 4879 -- BYTES_SENT: 252225 -- BYTES_RECEIVED: 105418 -- WAIT_TIME (IO): 2959 </code></pre> <p>Table row reads 79,999,077?? Seriously how does that work. My tables are all small. I don't get a lot of traffic. Maybe 100-150 people a day. But that doesn't seem to matter because the site completely died right when I put it up on the new server. Here is a query that was in the email saying my database is down. Is it really only this one query that's killing me? It's been up for literally months without a problem:</p> <pre><code>SELECT `Person`.`first_name`, `Person`.`last_name`,`Person`.`id`,`Upload`.`path`,`TeacherBiography`.`final_biography`, `TeacherPhilosophy`.`final_philosophy` FROM `my_database`.`people` AS `Person` LEFT JOIN `my_database`.`teacher_drive_cities` AS `TeacherDriveCity` ON (`TeacherDriveCity`.`person_id` = `Person`.`id`) LEFT JOIN `my_database`.`instruments_people` AS `InstrumentsPerson` ON (`InstrumentsPerson`.`person_id` = `Person`.`id`) LEFT JOIN `my_database`.`instruments` AS `Instrument` ON (`Instrument`.`id` = `InstrumentsPerson`.`instrument_id`) LEFT JOIN `my_database`.`teachers` AS `Teacher` ON (`Teacher`.`person_id` = `Person`.`id`) LEFT JOIN `my_database`.`uploads` AS `Upload` ON (`Upload`.`person_id` = `Person`.`id`) LEFT JOIN `my_database`.`teacher_biographies` AS `TeacherBiography` ON (`TeacherBiography`.`person_id` = `Person`.`id`) LEFT JOIN `my_database`.`teacher_philosophies` AS `TeacherPhilosophy` ON (`TeacherPhilosophy`.`person_id` = `Person`.`id`) WHERE `TeacherDriveCit! y`.`city` = 'Dana Point' AND `TeacherDriveCity`.`state` = 'ca' AND `Instrument`.`instrument` = 'saxophone' AND `Teacher`.`status` = 6 AND `Upload`.`description` = 'profile_picture' AND `TeacherBiography`.`final_biography` IS NOT NULL AND `TeacherPhilosophy`.`final_philosophy` IS NOT NULL GROUP BY `Person`.`id` ORDER BY RAND() ASC LIMIT 3 </code></pre> <p>I've heard order by rand() is slow, but I didn't think it would crash the entire server in one call. Am I missing something here? Any help would be appreciated! My sites been down for 24 hours and I live off the income from it. Thanks! </p> <p>Edit: Here is the explain on the query:</p> <pre><code>1 SIMPLE TeacherBiography ALL NULL NULL NULL NULL 41 Using where; Using temporary; Using filesort 1 SIMPLE TeacherPhilosophy ALL NULL NULL NULL NULL 41 Using where; Using join buffer 1 SIMPLE Upload ALL NULL NULL NULL NULL 166 Using where; Using join buffer 1 SIMPLE Teacher ALL NULL NULL NULL NULL 381 Using where; Using join buffer 1 SIMPLE InstrumentsPerson ALL NULL NULL NULL NULL 647 Using join buffer 1 SIMPLE Instrument eq_ref PRIMARY PRIMARY 4 yml_yml.InstrumentsPerson.instrument_id 1 Using where 1 SIMPLE Person eq_ref PRIMARY PRIMARY 4 yml_yml.TeacherPhilosophy.person_id 1 Using where 1 SIMPLE TeacherDriveCity ALL NULL NULL NULL NULL 7489 Using where; Using join buffer </code></pre> <p>I know it's not formatted so you can read it easy... I don't know how to make it a table and what not here. Sorry!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USBrian Jenkins
    1. USBrian Jenkins
    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. VO
      singulars
      1. POServer Crash - Optimizing Mysql queries
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. COCould you please post the result of using EXPLAIN on your query.
      singulars
      1. POServer Crash - Optimizing Mysql queries
      1. USXint0
 

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