CODEWITHSUNDEEP
typo3
smartcoderx
smartcoderx

Reputation: 1081

Queries are very slow

I build a Website with CMS Typo3. Everything works fine, but i have i performance issue. The frontend was generated very slow. So i decide to install the extension "T3Profiler" to analyze the problem.

I find in profiler queries like:

SELECT content FROM cf_cache_hash WHERE identifier = 'f33c135b63eac6bb7194edab51f3c57a' AND cf_cache_hash.expires >= 1441015330 LIMIT 1

Such queries takes 90.000 - 600.000 ms. Why this selects are so slow? How can solve my issue?

Can someone give me a hint?

Upvotes: 0

Views: 869

Answers (2)

Viktor Livakivskyi
Viktor Livakivskyi

Reputation: 3228

In addition to @biesior answer, another tip is to store caches outside of your TYPO3 database, because db is already stressed enough by data selection.

For TYPO3 7.x I do it like this in AdditionalConfiguration.php:

$redisCacheOptions = [
    'hostname' => 'localhost',
    'port' => 6379,
    'database' => 2,
    'password' => '******',
];
$cacheConfigurations = [
    'cache_hash',
    'cache_imagesizes',
    'cache_pages',
    'cache_pagesection',
    'cache_rootline',
    'extbase_datamapfactory_datamap',
    'extbase_object',
    'extbase_reflection',
    'extbase_typo3dbbackend_queries',
    'extbase_typo3dbbackend_tablecolumns'
];
foreach ($cacheConfigurations as $cacheConfiguration) {
    $GLOBALS['TYPO3_CONF_VARS']['SYS']['caching']['cacheConfigurations'][$cacheConfiguration]['backend'] = \TYPO3\CMS\Core\Cache\Backend\RedisBackend::class;
    $GLOBALS['TYPO3_CONF_VARS']['SYS']['caching']['cacheConfigurations'][$cacheConfiguration]['options'] =
        $redisCacheOptions + (array)$GLOBALS['TYPO3_CONF_VARS']['SYS']['caching']['cacheConfigurations'][$cacheConfiguration]['options'];
}

Upvotes: 0

biesior
biesior

Reputation: 55798

Sometimes when system tables like sys_log, *_cache_* and others grows to huge sizes it causes that querying them becomes slower and slooooower... While they are often accessed during common rendering process, they can become real performance killers

There are several workarounds for this:

  • Add scheduler's tasks to clean these tables regularly i.e. sys_log and history entries can be purged after ie. 30 days especially when system is in dev state and maaaany changes are done every day.
  • Check what fills the tables - i.e. some extension may add several hundreds logs to sys_log i.e. when some method doesn't get some expected argument - if it's used in loop with collection of hundred items, logger has to write the error several hundreds times per request (!), fix the code to avoid such situations
  • Make sure that all tables structures are correct via Install Tool > Compare current database with specification
  • Finally use your database GUI to optimize and/or repair tables.

Upvotes: 1

Related Questions