CODEWITHSUNDEEP
pythonmysqlubuntuamazon-ec2sqlalchemy
wont_compile
wont_compile

Reputation: 876

Poor MySQL performance on EC2 Micro instance

I have one small webapp, which uses Pyhon/Flask and a MySQL db for storage of data. I have a studentsdatabase, which has around 3 thousand rows. When trying to load that page, the loading takes very much time, sometimes even a minute or so. Its around 20 seconds, which is really slow and I am wondering what is causing this. This is the state of the server before any request is made, and this happens when I try to load that site.

As I said, this is not too much records, and I am puzzled by why this is so ineffective. I am using Ubuntu 12.04, with Ver 14.14 Distrib 5.5.32, for debian-linux-gnu (x86_64) using readline 6.2 mysql version. Other queries run fine, for example listing students whose name starts with some letter takes around 2-3 seconds, which is acceptable. That shows the portion of the table, so I am guessing something is not optimized right.

My.cnf file is located here. I tried some stuff, added some lines at the bottom, but without too much success.

The actual queries are done by sqlalchemy, and this is the specific code used to load this:

score = db.session.query(Scores.id).order_by(Scores.date.desc()).correlate(Students).filter(Students.email == Scores.email).limit(1)
students = db.session.query(Students, score.as_scalar()).filter_by(archive=0).order_by(Students.exam_date)
return render_template("students.html", students=students.all())

This appears to be the sql generated:

SELECT student.id AS student_id, student.first_name AS student_first_name, student.middle_name AS student_middle_name, student.last_name AS student_last_name, student.email AS student_email, student.password AS student_password, student.address1 AS student_address1, student.address2 AS student_address2, student.city AS student_city, student.state AS student_state, student.zip AS student_zip, student.country AS student_country, student.phone AS student_phone, student.cell_phone AS student_cell_phone, student.active AS student_active, student.archive AS student_archive, student.imported AS student_imported, student.security_pin AS student_security_pin, (SELECT scores.id \nFROM scores \nWHERE student.email = scores.email ORDER BY scores.date DESC \n LIMIT 1) AS anon_1 \nFROM student \nWHERE student.archive = 0"

Thanks in advance for your time and help!

Upvotes: 1

Views: 1342

Answers (2)

Adam Morris
Adam Morris

Reputation: 8545

@datasage is right - the micro instance can only do so much. You might try starting a second micro-instance for your mysql database. Running both apache and mysql on a single micro instance will be slow.

From my experience, when using AWS's RDS service (mysql)- you can get reasonable performance on the micro-instance for testing. Depending on how long the instance has been on, sometimes you can get crawlers pinging your site, so it can help to IP restrict it to your computer in the security policy.

It doesn't look like your database structure is that complex - you might add an index on your email fields, but I suspect unless your dataset is over 5000 rows it won't make much difference. If you're using the sqlalchemy ORM, this would look like:

class Scores(base):
    __tablename__ = 'center_master'
    id = Column(Integer(), primary_key=True)
    email = Column(String(255), index=True)

Upvotes: 2

datasage
datasage

Reputation: 19573

Micro instances are pretty slow performance wise. They are designed with burstable CPU profiles and will be heavily restricted when the burstable time is exceeded.

That said, your problem here is likely with your database design. Any time you want to join two tables, you want to have indexes on columns of the right and left side of the join. In this case you are using the email field.

Using strings to join on isn't quite as optimal as using an integer id. Also using the Explain keyword will running the query directly in mysql will show you an execution plan and can help you quickly identify where you may be missing indexes or have other problems.

Upvotes: 2

Related Questions