Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a relatively large 4-deep relational data setup like this:

client_applications: (potentially 1,000's of records)
   - ...
   - account_id
   - deleted_at

client_application_versions: (potentially 10,000's of records)
   - ...
   - client_application_id
   - deleted_at

cloud_logs: (potentially 1,000,000's of records)
   - ...
   - client_application_version_id
   - deleted_at

logs: (potentially 1,000,000,000's of records)
   - ...
   - cloud_log_id
   - time_stamp
   - deleted_at


I am still in development so the structure and setup is not set in stone, but I think it is setup ok. Using Rails 3.2.11 and InnoDB MySQL. The database is filled with a small (compared to the eventual db size) set of data (logs only has 700,000 rows) I have 4 queries, 3 of which are problematic, to retrieve logs.

  1. Grab first page of logs, ordered by timestamp, limited by account_id, client_application_id, client_application_version_id (Over 100 seconds)
  2. Grab first page of logs, ordered by timestamp, limited by account_id, client_application_id (Over 100 seconds)
  3. Grab first page of logs, ordered by timestamp, limited by account_id (Over 100 seconds)
  4. Grab first page of logs, ordered by timestamp (~2 seconds)


Here are the EXPLAIN statements. I have indexes on all applicable fields already. Would it be better to duplicate the various ...id fields on the logs table to prevent joins? Or is there some magic sauce I am missing when making these queries? I have never dealt with this quantity of data before so perhaps my standard way of approaching the setup and queries just doesn't scale? How can I alter my setup or statements to make these queries return in a reasonable time?


UPDATE
I have added a few combined indexes on the Logs table and shaved a tiny bit off of the time. Here are the explains for that. I have concluded that the ORDER method is the source of the delay. Removing the order by timestamp desc causes the query to return in a second or so. So the new question is why, when indexed on timestamp, does it still take over a minute to run this query?


UPDATE 2
Using a subquery to fetch the 100 id's increased performance to 14 seconds, but that is still far too long. The optimizations I have tried so far have all shortened the time a bit, but I feel that they are not getting at the root of the problem. Here is the EXPLAIN for the subquery approach.

share|improve this question
    
You probability need a horizontal partitioning, especially on the "logs" table. Partitioning on timestamp allows the latest records be put together. But this depends on the version and storage of your MySQL. Here are some references: dev.mysql.com/tech-resources/articles/partitioning.html, dev.mysql.com/doc/refman/5.5/en/partitioning.html –  user1931858 Jan 13 '13 at 3:02
    
This is an interesting idea. New logs come in as the most recent though, so it seems like I would have to continually and manually add partitions as the "new" partition gets excessively large. –  coneybeare Jan 13 '13 at 16:25

1 Answer 1

Not a DBA or MySQL expert here, but let's try :). So let's take your second query - a bit smaller than the 1st one - and simplify the table names.

We have something like : (LO = logs, CL = cloud_logs, CAV = client_application_versions, CA = client_applications)

 SELECT LO.* FROM LO 
 INNER JOIN CL      ON CL.id    = LO.cloud_log_id 
 INNER JOIN CAV     ON CAV.id   = CL.client_application_version_id 
 INNER JOIN CA      ON CA.id    = CAV.client_application_id 
 WHERE (LO.deleted_at IS NULL) 
 AND (CA.account_id = '3') 
 AND (CA.id = '5') 
 ORDER BY timestamp DESC LIMIT 100 OFFSET 0

And so you say it takes about 100 seconds, correct ?

When you say :

I have indexes on all applicable fields already.

Yet I believe that's where the flaw is. You don't have that much joins, and you may have 7 billion data or just 700, that should be performing well if indexing is correctly thought, and I think that's probably the order by / limit that is messing with your performance because of poor indexing.

1/ Have you tried :

SELECT LO.* FROM LO WHERE (LO.deleted_at IS NULL)

or

SELECT * FROM CA WHERE (CA.account_id = '3') AND (CA.id = '5')

See how these requests perform in time, if everything ok with these 2 tables ?

2/ Have you indexed timestamp as well ? Indexing the column you are making the "order by" on is crucial as well. In fact, you should even think about your data and how many values you're gonna have for each of the data you're querying on. This is very well explained right there : http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/ and will certainly help you.

3/ From what I've read on MySQL a few mins ago you could also try a MySQLCheck see if everything ok with your tables if you think your indexing is OK http://dev.mysql.com/doc/refman/5.0/en/mysqlcheck.html . I know that in older versions of oracle we had to compute stats after creating indexes, maybe something similar here ?

Hope this helps.

[EDIT : 12/01/13 After comments ]

Ok, glad to see you already divided the time by 4 but indeed 25s is way too long.

1/ Have you tried to play with indexes by creating one that would make sense, like explained by Peter here (http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/) ? Like an index on (CA.account_id, CA.id, timestamp) etc ?


2/ How long does it take when you get rid of the order by / limit like below ?

SELECT LO.* FROM LO 
 INNER JOIN CL      ON CL.id    = LO.cloud_log_id 
 INNER JOIN CAV     ON CAV.id   = CL.client_application_version_id 
 INNER JOIN CA      ON CA.id    = CAV.client_application_id 
 WHERE (LO.deleted_at IS NULL) 
 AND (CA.account_id = '3') 
 AND (CA.id = '5') 

To check if this would be the order by/limit that mess up with your performance ?


3/ In case 2 is verified, you could try something like :

SELECT LO.* FROM LO 
 INNER JOIN CL      ON CL.id    = LO.cloud_log_id 
 INNER JOIN CAV     ON CAV.id   = CL.client_application_version_id 
 INNER JOIN CA      ON CA.id    = CAV.client_application_id 
 INNER JOIN 
 (
    SELECT LO.id FROM LO 
    INNER JOIN CL      ON CL.id    = LO.cloud_log_id 
    INNER JOIN CAV     ON CAV.id   = CL.client_application_version_id 
    INNER JOIN CA      ON CA.id    = CAV.client_application_id 
    WHERE (LO.deleted_at IS NULL) 
    AND (CA.account_id = '3') 
    AND (CA.id = '5') 
    ORDER BY timestamp DESC LIMIT 0,100
  ) AS PERF  ON PERF.id = LO.id

Where you replace LO.id by the column that makes sense with Logs (I suppose you have some sort of Logs id . This is based on : http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/ Note you can change the LIMIT 0,100 and keep the OFFSET keyword instead in case you need it (if PostgreSQL compatibility is required).

share|improve this answer
    
1. Turns out i did not have an index on deleted_at. –  coneybeare Jan 12 '13 at 2:56
    
2. Timestamp was indexed. Adding deleted_at index took it down to 25 seconds, but it still needs further work –  coneybeare Jan 12 '13 at 2:57
    
Ok I edited my answer and added some stuff to help. –  Melvin PRESSOUYRE Jan 12 '13 at 12:00
    
Ok, the previous cut time only dealt with the 2nd query, the longer one still took 70 seconds to run. Upon new tests, it seems the 25 seconds may have been an anomaly because I can no longer get it that low. I have added a deleted_at_cloud_log_id on LO and that didn't help much, but I have concluded that removing the ordering clause in the SQL causes it to execute in under 3 seconds. So, I will add a deleted_at_cloud_log_id_timestamp index and see if that helps, then report back –  coneybeare Jan 14 '13 at 16:21
    
To answer your last question, quoting the official MySQL doc : If you have a problem with indexes not being used when you believe that they should be, run ANALYZE TABLE to update table statistics, such as cardinality of keys, that can affect the choices the optimizer makes. See Section 13.7.2.1, “ANALYZE TABLE Syntax”. But see my step 3 in my answer, try it see if it helps. –  Melvin PRESSOUYRE Jan 14 '13 at 18:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.