MySQL Performance

[Mediengruppe Thüringen]

Hi there,

we’ve now imported our events data (>50k events, >2k venues, 650k post meta rows!) but the performance is very poor:

http://www.akt-magazin.de/veranstaltungen

what’s the suggested server scaling for this huge amount of data?

[Mediengruppe Thüringen]

This reply is private.

[Mediengruppe Thüringen]

Hi there,

what about our paid pro support?
Just a quick notice or anything else should be possible!
Do you have any advise for tuning the my.cnf to get the calendar running with an acceptable performance?

Big thanks in advance!

[Cliff]

Hi. Sorry for the delayed reply.

We are aware of several areas that need performance improved. Could you please specify which URLs or what actions you perform to uncover these bottlenecks?

For performance recommendations, please reference https://theeventscalendar.com/knowledgebase/performance-considerations/

[Mediengruppe Thüringen]

Hi Cliff,

as mentioned in my last post, the posted SQL queries are the bottleneck. Because we’re coming into a massive temp & file sort. This is due the wp post meta data model, querying the _EventStartDate meta_value without any index. As we’re dealing with roundabout 50.000 events and therefore more then a half million post meta rows.

You can take a look here … be patient 😉
http://www.akt-magazin.de/veranstaltungen

I’ve tried some my.cnf tuning on the buffer and temp table options like sort_buffer_size, read_rnd_buffer_size, tmp_table_size, max_heap_table_size … without any notable effects. Maybe you got some advice for tuning the MySQL server for our special needs?

[Brook]

Howdy Mediengruppe Thüringen,

I would love to help with this as well. Cliff asked me to take a look because I really enjoy optimization and performance.

Thank you for sharing the background info on what you have tried and what data you have. It looks like you also have two questions:

what’s the suggested server scaling for this huge amount of data? Maybe you got some advice for tuning the MySQL server for our special needs?

Good questions! To best help you I need to learn just a little more about your current server.

1. Do you know what storage engine your wp_posts and wp_postmeta database tables are using, perhaps MyISAM or InnoDB? Out of the two InnoDB is basically always preferable, especially for performance.
2. Where are you hosted, and what plan are you on? Is it something you could share a link to?
3. Do you know how much RAM your database server has? Sometimes your database server is separate from your actual web server, and sometimes it has less RAM. So if your wp-config file doesn’t have the MySQL server set to localhost you might need to double check if your database server has a different amount of resources available.
4. What version of MySQL are you running?
5. Assuming your website is setup to use InnoDB everywhere, and you know how much RAM it has available, are you able to move some or all of the database into RAM using settings like this? That’s actually a pretty solid brief guide to tuning MySQL servers. If you have the access needed to tune your server like that, I would strongly recommend it.

15 second load times are crazy high, especially for the two queries you shared. That is a sign you either need to tune your server configuration, or have outgrown the server by quite a lot. With the above questions answered we should know a lot more definitely what’s going on, and provide some more advice from there.

Cheers!

– Brook

[Mediengruppe Thüringen]

This reply is private.

[Cliff]

This reply is private.

[Support Droid]

Hey there! This thread has been pretty quiet for the last three weeks, so we’re going to go ahead and close it to avoid confusion with other topics. If you’re still looking for help with this, please do open a new thread, reference this one and we’d be more than happy to continue the conversation over there.

Thanks so much!
The Events Calendar Support Team

[Author]

Posts