MySQL server memory usage after upgrade from FreeBSD 11.2 to 12.0

I upgrade my servers from FreeBSD 11.2 to 12.0 and after one day I notice that one of the servers mysql 5.7.25 RAM usage increases over time.

MySQL memory usage after one day is more than 30GB, but mysql data on this server is 4-5GB and innodb_buffer_pool_size is 4GB.

All other servers are fine so I am not sure if it's related to FreeBSD upgrade.

Any idea how to troubleshoot it?
 
I solve this issue by doing a new mysqldump, then I delete data in /var/db/mysql/* and then I restore the backup.

Commands for future reference:

Code:
mysqldump -u root --add-drop-table --routines --events --all-databases --force > backup.sql -p
service mysql-server stop
rm -fr /var/db/mysql/*
mysql -u root -p < backup.sql
 
Last edited by a moderator:
Finally the issue was not resolved.

I found that MySQL memory is increasing every time a Wordpress plugin is using this query:

Code:
                                SELECT
                                        pg.pagepath AS pagepath,
                                        pg.id AS page_id,
                                        pst.id AS post_id,
                                        -- coalesce returns the first result that is not NULL:
                                        -- either the sessions count or zero
                                        coalesce(t.sessions, 0) AS today_pageviews,
                                        coalesce(y.sessions, 0) AS yesterday_pageviews,

                                        -- calculate the weighted session averages.

                                        ( -- Calculate avg_pageviews in the last 24 hours
                                                (coalesce(t.sessions, 0) * 0.18819444444444) +
                                                (coalesce(y.sessions, 0) * (1 - 0.18819444444444))
                                        ) AS avg_pageviews,

                                        ( -- Calulate how many days_old the post is
                                                TIMESTAMPDIFF( hour, pst.post_date, NOW() ) - 1
                                        ) AS days_old,

                                        ( -- Calculate weighted_pageviews, using halflife to put less emphasis
                                          -- on older posts
                                                (
                                                        (coalesce(t.sessions, 0) * 0.18819444444444) +
                                                        (coalesce(y.sessions, 0) * (1 - 0.18819444444444))
                                                ) * POWER(
                                                        1/2,
                                                        ( TIMESTAMPDIFF( hour, pst.post_date, NOW() ) - 1 ) / (2 * 24)
                                                )
                                        ) AS weighted_pageviews
                                FROM
                                        wp_analyticbridge_pages as pg
                                LEFT JOIN (
                                        --
                                        -- Nested select returns today's sessions.
                                        --
                                        SELECT
                                                CAST(value as unsigned) as sessions,
                                                page_id
                                        FROM
                                                wp_analyticbridge_metrics as m
                                        WHERE
                                                m.metric = 'ga:pageviews'
                                        AND
                                                m.startdate >= CURDATE()
                                ) as t ON pg.id = t.page_id

                                LEFT JOIN (
                                        --
                                        -- Nested select returns yesterday's sessions.
                                        --
                                        SELECT
                                                CAST(value as unsigned) as sessions,
                                                page_id
                                        FROM
                                                wp_analyticbridge_metrics as m
                                        WHERE
                                                m.metric = 'ga:pageviews'
                                        AND
                                                m.startdate >= CURDATE() - 1
                                        AND
                                                m.enddate < CURDATE()
                                ) as y ON pg.id = y.page_id

                                LEFT JOIN wp_posts as pst
                                        ON pst.id = pg.post_id

                                -- For now, they must be posts.
                                WHERE pst.post_type = 'post'
                                        ORDER BY weighted_pageviews DESC
                                        LIMIT 8;
 
Hi there.

Do you have the default MySQL config?... Remember that you can tune your MySQL installation to tell it how much memory and time should it take from the system to process queries.

Check if a variable tunning under my.cnf help you.

Regards.
 
No I don't use the default config. The config I use allows 4-5GB of MySQL memory usage but this query increases the memory usage above the configured limit.
 
Ok.

What about the performance associated with the query and it's tables?
Maybe a create index by the critical fields will help you to decrease time and memory used at query run time???

If you have a lot of records in your table a partition by also could help to decrease times for example if you create partitions by date...

Just backup your original tables, make some changes and test again... I hope this help.

Regards
 
Back
Top