[MTOS-dev] Performance

[Hirotaka Ogawa]

Very informative!

I think, adding a new published entry is a relatively large
transaction.  I'd like to know analysis for more minute operations if
you can.

# 133 "SELECT COUNT(*)" queries is probably for determining
unique_basename.  Actually, it is one of the most inefficient
subroutines in MT.

On Sun, Mar 2, 2008 at 6:24 AM, Alvar Freude <alvar at a-blast.org> wrote:
> 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/
>
> _______________________________________________
>  MTOS-dev mailing list
>  MTOS-dev at sixapart.com
>  http://www.sixapart.com/mailman/listinfo/mtos-dev
>
>



-- 
Hirotaka Ogawa makes no sense.
http://as-is.net/blog/