We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Too many negative votes

This post has too many negative votes. The cause of this could be:

  • Irrelevant or controversial information
  • Confusing question or not a real question
  • Aggressive vocabulary, excessive rudeness, etc
  1. Home
  2. Database

MySQL Performance

We have hit a wall and are trying to understand what may have happened.

We have written a restful API using phalcon for speed. The root return from phalcon is blinding quick at 300ms as there is no MySQL. The additional latency (above our target of 100ms) is that the network has not been optimised. That said, the application responds very quickly considering it is running on a t1.small backed by a t1.micro database on RDS.

As soon as we run a MySQL command, the whole system slows down. Queries take on average 1.2 seconds to execute which is beyond acceptable.

we have set up the MySQL connection as:

<?php
    $di->setShared('db', function() use ($di) {
            $type = strtolower($di->get('config')->database->adapter);
            $creds = array(
                'host' => $di->get('config')->database->host,
                'username' => $di->get('config')->database->username,
                'password' => $di->get('config')->database->password,
                'dbname' => $di->get('config')->database->name
            );

The simplest query that runs first is an authorization query and this looks like:

<?php

$org = Organisation::findFirst("searchID = '".$that."' AND thisOther = '".$this."'");

We tried this other query which is closer to raw SQL but had the same result.

<?php       

            $query  = $this->modelsManager->createQuery("SELECT searchID FROM \Models\Organisation WHERE searchID = :org: AND thisOther = '".$this."'");
            $org   = $query->execute(array('org' => $that))->getFirst();

            $sID = $org['orgUID'];

This takes on avergae 1.2 seconds to excecute.

We then thought ah! Database. So we upgraded the instance to a m3.2xlarge with 30gigs of memory and the result was almost exactly the same at 1.12 secs.

There are pages with 5-6 queries on them that execute in order and drive the API response time to 8 seconds in some instances which is painful for such a fast framework. The models have also all be correctly defined with the column names declared to conserve resources.

Thus we think that maybe there is something we are doing wrong in the framework that we are unaware of.

any ideas where the issue could be? what settings do we need to modify?

3


16.4k
edited Sep '15

hello ok

1- did you apply index to your tables?

2- did you activate the model metadata cache?

3- are you using cache on the query?

i would imaginen ia the first , index to thr table

let me know



6.5k
Accepted
answer
edited Sep '15

  1. Check but there are only three records in that table so I am sure it is ot that.

  2. What is a model meta data cache? :S

  3. Cannot cache this query as the permissions change regularly so it needs to be checked each time a request is made. It should not take a 1.2seconds to run though surely even if it is run every time.

I figure they may be a giant mistake in the config file that is causing MySQL to take too long.

//Edit config file was optimised as per spec. issues turns out to be batch inserts not supported by Phalcon



6.5k

After caching what is possible, it turns out the bottle neck is the insert statements. Apparently the method used by Phalcon to insert records into the database is just inherently slow. We will need to move those to Raw SQL out of the PHalcon framework to increase performance.

We are chaching a few SQL queries but this saved only about 2 seconds of the 6 we need to get rid off.

Anyone else who stumbles upon this, if you are writing an application that needs to be realy quick at high volumes (4000+ requests a minute), avoid the native Functions and just use raw SQL. We are at the point where we want to try and shave off just a few milliseconds (looking to kill 200ms) and it would seem that Phalcon is not optimised for this (Batch inserts) Willing to accept that we may be wrong but with no further advice on this we are not able to confirm either way.

Still great software though :) If we can crack the universal database issue, I think it will be a great development.



16.4k

ok but weird, I have something similar and haven't run to this problem , but again i would check the db index ;)



6.5k
edited Sep '15

Mm. Runinng the query in a DB client:

SELECT orgUID FROM tableOrganisations where someUID = '12398983hjf9s8fieuwr'

runs at 188ms. Which is with the index on somUID and just three rows of data in the table. This is back on the t2.micro DB instance so I am not expecting blazing response times. Network and CPU limits mean this is about as fast as it will ever get.

I basically setup a timer using PHP microtime() in between each step of script and as soon as there was a MySQL function there was a huge increase in response times. Something about the connection to the database? Loading the models? The coloumns to be used have all been declared as per the documentaion, so as to save memory. etc. Cannot think whatelse it could possibly be.

The solution for now is to just cache as many queries as possible. Will keep investigating to try and hone in on what could be wrong but it looks like we may have to ditch the Phalcon PHQL in some places and just code SQL. Again we are looking at a real world application for this where 1ms translates to a tangible annoyance spread over serveral thousand ops per second.

Will post whatever I find here later. I wish whoever it was had not marked this as solved as it is far from :)

A realy interesting discussion.



16.4k

Yeah, I understand I just cant get why you are getting that slow response with PHQL, because if we take your example of the slow response, then all other devs using PHQL should be showing the same results with high # of inserts, thats why I say there as to be something else , but keep pushing and keep us update ;)



6.5k

Smacked my head against a wall with this one.

There is no valid reason why queries should be taking 6 seconds to complete. Again, the database has three records in it which it needs to query against so indexes or no 1.29 seconds is incredibly slow.

Additionally the inserts are 5 rows which take an additional 2 seconds.

I have filtered out the network latency and this accounts for around ~300ms.

The root returns a reply in about 400ms which is fine but as soon as the MySQL calls are included the whole system slows to a crawl.

Which phalcon mysql settings specifically need to be looked at over and above what has been mentioned already here (excluding the database because it is practically empty so poses no load to the system at all).



6.5k

Solution!

We used the microtime() function to debug where things were taking up time and slowing the process down and it was not even in the write or read from the database but the connection itself.

After digging around we discovered that the RDS instance was not in the same Zone as the server we were running off which was causing all kinds of DNS issues and network latency. We moved the dbase to the same zone and the latency vanished. Thus the error was totally our fault.

other things to note:

using PDO when you start up your AWS RDS instance always select publically available so that you get an IP assigned to your instance. This will also shave a few milliseconds off the connect time and turn that setting off in PDO as well.

hopefully this will help someone. I see this thread has been down voted etc. Sincere apologies if anything said was offensive.

We remain happy with the performance of Phalcon and are glad to have chosen it.