0

I'm trying to increase performance in a mysqldb by using indexes and more buffer capacities.. My db is used by a symfony1.4 application and a Propel 1.6.9 ORM The result is fine when I execute my query directly on my db server but when the application does, is not good at all and it's took 22 second based on slow_query_log file.. So I think that the ORM is responsible for that. Any suggestion where to begin and if there is any way to optimize queries sent by an ORM. thanks.

Yassine LD
  • 137
  • 1
  • 1
  • 11
  • Have you tried comparing the SQL query Propel generated with the query you run directly on your DB? – Pierre-Loup Pagniez Mar 16 '16 at 10:38
  • The query I executed its the same that I found in the slow_query_log file. I took it and I directly executed it in the db server (with SQL_NO_CACHE). – Yassine LD Mar 16 '16 at 11:23
  • See http://stackoverflow.com/questions/6666631/sql-no-cache-does-not-work, this might be related to your problem. In short: if you ran the exact same query on Propel first, then in your DB directly, then it cached the results of the Propel query and used the cache on your second query, even if you explicitely asked for SQL_NO_CACHE, which disables caching the *result* of the query, but does not prevent MySQL from looking at the query cache to speed up the query. – Pierre-Loup Pagniez Mar 16 '16 at 12:55
  • Yes but why the Propel query still slow if it use the cache of previous queries ? I execute my query directly now i have good respond time but via application its bad according to slow_query_log file ...!!? – Yassine LD Mar 16 '16 at 14:45
  • 1
    @Pierre-LoupPagniez -- There are two caches. The Query cache records the exact query. When used, it will usually return the result in less than 1 millisecond. The other cache is a more general I/O cache. It re-executes the query, but avoids having to do the I/O again. This typically speeds up a query by 10x. That is, with normal caching the query might take 2.2s, but with the QC, it might take only 1ms. – Rick James Mar 17 '16 at 00:03
  • Let's see the query. Please add `EXPLAIN SELECT ...` and `SHOW CREATE TABLE`. From those we may be able to advise on something simple like adding a composite index. – Rick James Mar 17 '16 at 00:04
  • @RickJames the query cache is disabled im using innodb buffer pool instead and the question is why when the query is directly send to the db server is fine speed and when via application using ORM is slow thats where im lost. maybe i could find somthing in the Propel log file. thank you – Yassine LD Mar 17 '16 at 15:29
  • If the buffer_pool (which is a cache) is cold when the ORM runs the query, it could be slow. Then, if you run it manually before it gets cold again, it will be fast. – Rick James Mar 17 '16 at 15:44

0 Answers0