Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
#1 2009-11-16 20:58:13
- saccade
- Plugin Author
- From: Neubeuern, Germany
- Registered: 2004-11-05
- Posts: 521
Slow MySQL performance - how can I improve performance?
Running a site on Joyents Hosting I get sometimes very poor performace.
My txp-install contains about 2700 articles, mostly simply text.
But with only 42 queries I get at the moment 14s of query-time for my main page:
<!-- Runtime: 14,983 -->
<!-- Query time: 14,855589 -->
<!-- Queries: 42 -->Already filed a support ticket some time ago, but the answer was there are lots of false/slow queries.
These are logs marked in red in my logfile:
Slow_queries 11 k
Innodb_buffer_pool_reads 4,225
Handler_read_rnd 7,139 k
Handler_read_rnd_next 687 M
Qcache_lowmem_prunes 526 k
Created_tmp_disk_tables 378 k
Select_full_join 65
Sort_merge_passes 87
Opened_tables 11 k
Table_locks_waited 522Maybe I have to improve something.
Can anyone help me?
Offline
Re: Slow MySQL performance - how can I improve performance?
Switch to debug instead of testing, so you can see the individual queries and how much time they consume.
Offline
#3 2009-11-16 21:36:52
- saccade
- Plugin Author
- From: Neubeuern, Germany
- Registered: 2004-11-05
- Posts: 521
Re: Slow MySQL performance - how can I improve performance?
Hi ruud,
so I did, but it is different from time to time where slow queries occur.
I have- a sticky article list (main)
- an article list (main column)
- a list of recent articles (article_custom)
- a list of events (article_custom)
for example one of the slow ones:
<txp:article_custom label="" limit="5" break="li" wraptag="ul" section="aktuell,evangelischsein,gemeinden,leben" form="titelliste" />
[SQL (5,7402651309967): select *, unix_timestamp(Posted) as uPosted, unix_timestamp(Expires) as uExpires, unix_timestamp(LastMod) as uLastMod from evnn_textpattern as textpattern where 1=1 and Status = 4 and Posted <= now() and Section IN ('aktuell','evangelischsein','gemeinden','leben') order by Posted desc limit 0, 5]
...
<txp:article_custom status="live" time="any" category="musik-aktuell" limit="20" form="rechtespalte" sort="Posted asc" />
[SQL (0,0002281665802002): select *, unix_timestamp(Posted) as uPosted, unix_timestamp(Expires) as uExpires, unix_timestamp(LastMod) as uLastMod from evnn_textpattern as textpattern where 1=1 and Status = 4 and (Category1 IN ('musik-aktuell') or Category2 IN ('musik-aktuell')) order by Posted asc limit 0, 20]and
<txp:article status="sticky" limit="5" listform="default_ohneexpired" />
[SQL (0,00014305114746094): select name from evnn_txp_section as txp_section where on_frontpage != '1']
[SQL (4,6556098461151): select *, unix_timestamp(Posted) as uPosted, unix_timestamp(Expires) as uExpires, unix_timestamp(LastMod) as uLastMod from evnn_textpattern as textpattern where 1=1 and Status = 5 and Posted <= now() and Section != 'artikel' and Section != 'kvwahl2006' and Section != 'christuskirche' and Section != 'stephanuskirche' and Section != 'kirchenvorstand' and Section != 'gottesdienst' and Section != 'kalender' and Section != 'veranstaltung' and Section != 'veranstaltung-christuskirche' and Section != 'veranstaltung-stephanuskirche' and Section != 'veranstaltung-rueckblick' and Section != 'testveranstaltun' and Section != 'tageskalender' and Section != 'stichwort' and Section != 'evangelischsein' and Section != 'leben' and Section != 'kindergaerten' and Section != 'gemeinden' and Section != 'kigadompedro' and Section != 'info' and Section != 'a-z' and Section != 'musik' and Section != 'kigaarnulf' and Section != 'kigastephanus' and Section != 'gemeinde-christuskirche' and Section != 'gemeinde-stephanuskirche' and Section != 'kinderchoere' and Section != 'voices' and Section != 'kantatenchor' and Section != 'chor-christuskirche' and Section != 'kammerchor' and Section != 'kontakt' and Section != 'erstinfo' and Section != 'engel-gesucht' order by Posted desc limit 0, 5]So it seems that article- and article_custom-tags are consuming those long query times.
But why?
I already repaired and analyzed the tables.
Last edited by saccade (2009-11-16 21:40:32)
Offline
Re: Slow MySQL performance - how can I improve performance?
If the query times are inconsistent in that slow queries are sometimes fast and fast queries are sometimes slow, then I’d stop looking at Textpattern and start looking at the webhost.
Offline