Log Size / Maintenance

  • Resolved fullworks

    (@fullworks)


    6 years, 9 months ago

    On each page load, the plugin queries the log files, with now some 260,000 rows this query takes 5 seconds.

    How are the logs meant to be trimmed? Is there a process or instruction?

    SELECT COUNT(*) 
FROM wp_gamipress_logs AS p 
LEFT JOIN wp_gamipress_logs_meta AS pm0
ON ( p.log_id = pm0.log_id )
LEFT JOIN wp_gamipress_logs_meta AS pm1
ON ( p.log_id = pm1.log_id )
LEFT JOIN wp_gamipress_logs_meta AS pm2
ON ( p.log_id = pm2.log_id ) 
WHERE p.user_id = '3' 
AND ( ( p.type = 'event_trigger' )
AND ( pm0.meta_key = '_gamipress_trigger_type'
AND pm0.meta_value = 'gamipress_user_post_visit' )
AND ( pm1.meta_key = '_gamipress_post_id'
AND pm1.meta_value = '3483' )
AND ( pm2.meta_key = '_gamipress_visitor_id'
AND pm2.meta_value = '1' ) )

    Time 6.2470 seconds

    • This topic was modified 6 years, 9 months ago by fullworks.
Viewing 5 replies - 1 through 5 (of 5 total)
  • Thread Starter fullworks

    (@fullworks)

    p.s.

    I don’t know why the query is needed or whythe logs and logs meta are needed,

    but the performance hit comes from the ‘longtext’ of wp_gamipress_logs_meta.meta_key which can’t be indexed and hence table scans multiple times.

    The maximum length in my instance of meta of 1.4 millions rows was 96 so I altered the column to varchar(255) and added an index and the query now takes milliseconds instead of6 seconds

    Thread Starter fullworks

    (@fullworks)

    Any update on the logs, why are they so big? Can they be trimmed? Is the count really necessary on every page load?

    Whilst indexing the table speeds things up a bit, this is still slow.

    • This reply was modified 6 years, 9 months ago by fullworks.
    Plugin Author Ruben Garcia

    (@rubengc)

    Hi @fullworks

    A speed performance suggestion is always welcome

    We can not modify the gamipress_logs_meta structure basically because this table is intended to be used in the same way as WordPress postmeta and usermeta tables to store meta data related to the log

    We are already working on improving this query and on next release you will see a substantial improvement on it

    Thank you so much for your suggestion

    Best regrads

    Thread Starter fullworks

    (@fullworks)

    Can the logs and logs meta be trimmed? Why are they so massive? I’m not really familiar with the setting, I have been through them but it isn’t clear how to optimise?

    Obviously a 6 second delay on every page load is massive and make the plugin un usable right now. Even with the indexing it still takes 3 plus seconds

    A straight count(*) without the meta joins takes milliseconds (i.e. is not a server issue )

    Alan

    Plugin Author Ruben Garcia

    (@rubengc)

    Hi @fullworks

    Check the new GamiPress release (1.4.2)

    It has an improvement on this function that reduces the time consumption to milliseconds (included on large databases)

    I will mark this topic as resolved

    Thank you so much for report it, we really appreciate this type of deep reports that let’s us improve GamiPress performance

    Best regards

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘Log Size / Maintenance’ is closed to new replies.