[MTOS-dev] Performance

[Alvar Freude]

Hi,

-- Alvar Freude <alvar at a-blast.org> wrote:

> I am sure that one of the bottlenecks is the SQL.

After a second look into the SQL log I found some remarkable SQL which 
may be worth a more detailed analysis:

When Adding a new entry to the a blog, there are more then 1700 
SQL-Queries for this action. WUUUH, 1700 single Queries!

The half of the queries, 864 in my example, are from the unnecessary ping 
test ("SELECT 'DBD::Pg ping test'").

231 of the queries select a template id for a template name:

   SELECT template_id
   FROM mt_template
   WHERE (template_identifier = $1) AND
         (template_type = $2) AND
         (template_blog_id = $3)


And 42 times all template data of one template are queried:

   SELECT template_id, [...] template_text, [...]
   FROM mt_template
   WHERE (template_id = $1)
   LIMIT 1


So it seems that the template data is cached, but not the template IDs. 
One relatively simple but effective optimization may be to select all 
template data at the beginning of the HTTP request (or in some setups at 
startup, when using mod_perl) with one SQL query and cache everything 
between the complete request (or server lifetime, when using a persistent 
environment).

Other strange queries are 33 "SELECT NEXTVAL('mt_...'); PostgreSQL can 
use function calls as default values for columns and even has a SERIAL 
data type, which does this transparently in the background.


There are also 133 "SELECT COUNT(*)" queries.

And there are 29 completely equal queries with the same parameter:

   SELECT COUNT(*)
   FROM mt_entry, mt_placement
   WHERE (entry_status = $1) AND
         (entry_class = $2) AND
         (entry_blog_id = $3) AND
         (placement_category_id = $4) AND
         (entry_id = placement_entry_id)
	
   Parameter: $1 = '2', $2 = 'entry', $3 = '4', $4 = '5'


I don't know for what the COUNT(*) queries are used, but when using the 
result in the next query for the generation of IDs this is *very* 
dangerous without transactions and the database may be broken when there 
are two of them at the same time.


There are also some INSERT statements, which are followed by an update on 
the same row.


Ciao
  Alvar


-- 
** Alvar C.H. Freude, http://alvar.a-blast.org/
** http://www.assoziations-blaster.de/
** http://www.wen-waehlen.de/
** http://odem.org/
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 194 bytes
Desc: not available
Url : http://www.sixapart.com/pipermail/mtos-dev/attachments/20080301/04a5584c/attachment.bin