Huge load on MySQL server

[coudenysj]

We're using prooph in a fairly small project (for now), but we see a big load on our MySQL processes due to the projections polling the server each 0.1 second.

The versions of the packages are:

prooph/common                                  v4.3.0                          Common classes used across prooph packages
prooph/event-sourcing                          v5.6.0                          PHP EventSourcing library
prooph/event-store                             v7.5.0                          PHP EventStore Implementation
prooph/event-store-bus-bridge                  v3.3.0                          Marry CQRS with Event Sourcing
prooph/event-store-symfony-bundle              v0.5.0                         
prooph/pdo-event-store                         v1.10.3                         Prooph PDO EventStore
prooph/service-bus                             v6.2.2                          PHP Enterprise Service Bus Implementation supporting CQRS and DDD
prooph/service-bus-symfony-bundle              v0.7.0                         

We have 15 projections running, and we're using the MysqlSingleStreamStrategy.

Is there anything we can do about this load?

[prolic]

Yes you can configure this polling timeout. Have a look at the docs

On Wed, Apr 24, 2019, 10:27 Jachim Coudenys notifications@github.com wrote:

We're using prooph in a fairly small project (for now), but we see a big load on our MySQL processes due to the projections polling the server each 0.1 second.

The versions of the packages are:

prooph/common v4.3.0 Common classes used across prooph packages prooph/event-sourcing v5.6.0 PHP EventSourcing library prooph/event-store v7.5.0 PHP EventStore Implementation prooph/event-store-bus-bridge v3.3.0 Marry CQRS with Event Sourcing prooph/event-store-symfony-bundle v0.5.0 prooph/pdo-event-store v1.10.3 Prooph PDO EventStore prooph/service-bus v6.2.2 PHP Enterprise Service Bus Implementation supporting CQRS and DDD prooph/service-bus-symfony-bundle v0.7.0

We have 15 projections running, and we're using the MysqlSingleStreamStrategy.

Is there anything we can do about this load?

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/prooph/pdo-event-store/issues/200, or mute the thread https://github.com/notifications/unsubscribe-auth/AADAJPCULALSJEYHZNBPRCDPSBU6HANCNFSM4HIEPEVA .

[coudenysj]

Thanks @prolic for the advice, I know the setting, but this will still generate some load when the projection count increases.

Are you guys using a different approach? Or are you just living with the load?

[prolic]

Configure this setting until you have a sweet spot with load VS up to date read models. The only "better" way is having truly asynchronous projections and listeners (available in postgres).

[atymic]

Hey @prolic

Sorry for jumping on this closed topic, but could you explain the benefit of postgres here?

We've got about 40 projections, and the lag is horrendous, the projections lags many seconds behind due to locking issues on the projections table (them all trying to update the locked_until every 100ms)

[fritz-gerneth]

(them all trying to update the locked_until every 100ms)

100ms seems incredibly short for locks. That alone would be ~400 updates/second on that table. In a production environment you certainly should increased these values to seconds/minutes.

Both OPTION_LOCK_TIMEOUT_MS and OPTION_UPDATE_LOCK_THRESHOLD only control how fast projections recover from crashes/ reboots. This does not affect how often new events are queried for. Hence it it is rather safe to increase. We use these values:

PdoEventStoreReadModelProjector::OPTION_LOCK_TIMEOUT_MS => 30000,
PdoEventStoreReadModelProjector::OPTION_UPDATE_LOCK_THRESHOLD => 15000,

Another factor to improve performance is the option DEFAULT_PERSIST_BLOCK_SIZE. Every time the state is persistent, another update to the table occurs. This update includes stream positions but also the projections' states. Now if that projection state is large the update may take quite a while (e.g. a couple of MBs). That'd then equal to an mb-sized update query every XX events. So performance can also be drasticaly improved by keeping the projection state small (we do not use it at all for that reason).

[atymic]

Hey @fritz-gerneth, first off, thanks for your awesome in depth reply :)

You are right, it's actually 1s (I was wrong, assumed every poll it also did a write)

However, it seems that even at the default 1s lock, there's still ~150 writes per second (this is on a test server, currently zero event write load)

Thanks for your suggestions re; those options. Will give them a go tomorrow!

[fritz-gerneth]

Many concurrent queries certainly can have a huge cascading effect, possibly skewing metrics. Judging from your first screenshot your queries certainly seem slow (avg 300ms to update the table).. At that point you might need to invoke the query log to actually see which queries are sent. The data also lists 100 client connections, these certainly cannot be all from projections. Some other load of the server might come from other clients?