Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Poor performance when cap works with mysql. #36

Closed
HongyanShen opened this issue Aug 18, 2017 · 7 comments
Closed

Poor performance when cap works with mysql. #36

HongyanShen opened this issue Aug 18, 2017 · 7 comments
Labels
enhancement

Comments

@HongyanShen
Copy link

The sql

SELECT `MessageId`,`MessageType` FROM `{_prefix}.queue` LIMIT 1 FOR UPDATE;
DELETE FROM `{_prefix}.queue` LIMIT 1;

causes a table lock.
@yang-xiaodong
Copy link
Member

Because of the mysql does not support the SKIP LOCKED syntax before 8.0 version , so we haven't found a better treatment way.
I will pay attention to this issue.

yang-xiaodong added a commit that referenced this issue Oct 9, 2017
@yang-xiaodong
Improved query performance without lock table. (#36)
0a94921
yang-xiaodong added a commit that referenced this issue Nov 17, 2017
@yang-xiaodong
Release 2.1 (#55)
3a77d37 
* add dashboard branch.

* add dashboard

* add helper methods.

* Add data model.

* add options.

* add empty implement

* add dashbaord

* add dashboard

* Fixed spelling error

* rename file.

* add dashbaord feature

* impl dashboard storage

* update nuget reference.

* add pages.

* deleted unused fiels.

* modify resource.

* update resource.

* rename

* update dashboard

* rename

* impl monitoring api interface.

* update solution files.

* update samples.

* dashboard.

* add jsonview resource files.

* add json dispatcher.

* modify published pages.

* add routes.

* update pages.

* update resources.

* update dashboard.

* add dashboard of sql server storage impl

* nesting files.

* fixed query bug.

* update resource.

* remove some api.

* add subscriber page.

* update sample

* add resource.

* remove api.

* add SubscriberPage

* add resource

* generate cshtml.

* modify html two table to one table.

* update resource.

* update css

* update subscriber page.

* refactor.

* cleanup.

* cleanup code.

* impl history monitoring api.

* add home page recevied message real-time

* add legend styles.

* update js.

* modify axis color.

* add resource.

* update dashboard home page.

* update css.

* update resource.

* modify DefaultSucceedMessageExpirationAfter to 24 hours.

* add resx,

* add consul discovery.

* remove unused file.

* add node page.

* add node page

* node discovery

* add kafka sqlserver sample.

* update sample.

* add okstats.

* refactor.

* fixed kafka client bugs.

* modify node and subscriber pages

* refactor.

* add Gateway middleware

* remove unused files.

* update resource.

* update gateway.

* refactor.

* update samples.

* remove base middleware

* add node switch click event.

* add NodeId config to options.

* upgrade dependent version.

* add PathMatch configuration

* update NodePage.cshtml

* remove session

* remove matchPath

* refactor

* refactor dashboard middleware

* gateway proxy middleware function maturation

* remove cookie exp

* refactor and remove files.

* add CapCache to cache server nodes.

* refactor.

* renamed message dto.

* add extended interface of IContentSerializer and JsonContentSerializer

* modify unit test

* check the requirement when CAP start.

* correct spelling

* cleanup code.

* add resources.

* processing pages will contains  Scheduled  and Enqueued messages.

* processing pages will contains  Scheduled  and Enqueued messages.

* ignore NU1701 Warning.

* renamed file.

* refactor

* implements dashboard interface.

* rename reference class.

* add mysql monitoring api impl

* fix bug of connection driver.

* remove cap.UseDashboard.  It's will be automatically enabled by registerd services.

* fix sql bug

* cleanup code and fix spelling

* fix postgre sql bug.

* fix mysql sql bug.

* when storage a received message raising an eception, we will reject the message to queue.

* fix spelling mistake

* add dashboard instructions to readme

* modify error log content.

* fix postger sql bug.

* fix consul discovery bug.

* add dashboard introduction to readme.md

* update english resource.

* Update README.md

* renamed files.

* fix postgre sql bug.

* cleanup code.

* update tests.

* rename file.

* update samples.

* Improved query performance without lock table. (#36)

* update sample.

* update samples.

* fix data reader uncolsed bug.

* update add jsonproperty

* refactor

* revert FetchNextMessageAsync sql

* add helper method.

* rafactor subscriber handler.

* add FailedRetryCount options.

* rafactor publisher excutor.

* add IPublishExecutor

* add failed message processor.

* inject failed message processor.

* refactor sql storage.

* fixed unit tests.

* fixed unit test.

* fixed postgresql tests.

* Update .travis.yml

* Update .travis.yml

* Update .travis.yml

* Update .travis.yml

* Update .travis.yml

* Update .travis.yml

* Update .travis.yml

* Update .travis.yml

* Update .travis.yml

* Update .travis.yml

* Update .travis.yml

* add LAN ip to LocalRequestsOnlyAuthorizationFilter

* add and update resource.

* add current node name to layout page if user enabled node discovery

* modify unit tests.

* add callback message sende tests.

* add deserlizer by type to  IContentSerializer

* refactor unit tests

* add summary comment.

* refactor async method.

* add comment and fixed spell error.

* refactor.

* add custom content serializer extension to CapBuilder

* add IMessagePacker

* refactor.

* add connection pool for kafka producer.

* add determines whether the query is null.

* add connection pool size config to KafkaOption.

* fixed json JObject bug

* add custom message wapper interface

* remove unused code.

* fixed callback topic send error bug.

* refactor.

* update unit tests.

* update samples.

* upgrade dependent package.

* remove some class from Abstraction namespce to Internal.

* optimize consumer related code

* add ICallbackMessageSender to DI with singleton.

* add and fixed some unit tests.

* refactor namespace.

* modify class protected level

* assemblies internal class are visible to test project

* add DeSerialize method to IContentSerializer with type deseralize

* refactoring

* Fix the phone style dispaly problem

* add logs

* refactoring

* upgrading `Confluent.Kafka` package

* Optimize message queue error message prompt.

* reorganize error message prompts.

* modify error message prompt

* add summary comments.

* modify dependent

* disabled print connection closed log. see: confluentinc/librdkafka#516

* Update README.md

* Update README.md

* Update README.zh-cn.md

* Update README.zh-cn.md

* fix dashboard not config discovery throw exceptions bug.

* Update README.md

* update readme

* Fixed serialized the message type bug. (#53)

* Update README.md

* Update README.md

* refactoring

* refactoring

* update readme.

* update readme.

* add summary comment.

* refactoring

* optimizing publisher interface

* update readme

* update readme.

* add summary comment.

* add summary comment.

* add summary comment.

* add summary comment.

* upgrading package

* add summary comment.

* optimize the RabbitMQ connection pool

* fix the producer connection returned

* dispose resource when connection pool is full
@ajdwfnhaps
Copy link

ajdwfnhaps commented Dec 8, 2017 •
edited by yang-xiaodong
Loading

I also have the same problem, will keep deadlocks, has any new optimization idea?

MySql.Data.MySqlClient.MySqlException (0x80004005): The Command Timeout expired before the operation completed. ---> MySql.Data.MySqlClient.MySqlException (0x80004005): 
The Command Timeout expired before the operation completed.

@yang-xiaodong
Copy link
Member

@ajdwfnhaps
Your situation seems different from the title description.
Are your performance problems or programs running bugs?

@ajdwfnhaps
Copy link

ajdwfnhaps commented Dec 8, 2017 •
edited by yang-xiaodong
Loading

When a consumer takes a time-consuming operation, e.g. System.Threading.Thread.Sleep(5000);
and CAP's QueueProcessorCount set to 15.

@yang-xiaodong
Copy link
Member

yang-xiaodong commented Dec 8, 2017 •
edited
Loading

Please use a more friendly format to describe your problem, so that other users can more easily read it.
I think your question needs open a new issue.

yang-xiaodong added a commit that referenced this issue Dec 17, 2017
@yang-xiaodong
Because of deadlock and performance issues, mysql does not use transa…
c2df287 
…ctions when consuming messages, and requeue the message to queue table when they exception. (#36,#68)
@yang-xiaodong
Copy link
Member

Fixed in version 2.1.2

@yang-xiaodong
Copy link
Member

@HongyanShen 加一下我QQ 615709110 , 问你个事情。

yang-xiaodong added a commit that referenced this issue Dec 28, 2017
@yang-xiaodong
Release 2.1 (#55)
544325a 
* add dashboard branch.

* add dashboard

* add helper methods.

* Add data model.

* add options.

* add empty implement

* add dashbaord

* add dashboard

* Fixed spelling error

* rename file.

* add dashbaord feature

* impl dashboard storage

* update nuget reference.

* add pages.

* deleted unused fiels.

* modify resource.

* update resource.

* rename

* update dashboard

* rename

* impl monitoring api interface.

* update solution files.

* update samples.

* dashboard.

* add jsonview resource files.

* add json dispatcher.

* modify published pages.

* add routes.

* update pages.

* update resources.

* update dashboard.

* add dashboard of sql server storage impl

* nesting files.

* fixed query bug.

* update resource.

* remove some api.

* add subscriber page.

* update sample

* add resource.

* remove api.

* add SubscriberPage

* add resource

* generate cshtml.

* modify html two table to one table.

* update resource.

* update css

* update subscriber page.

* refactor.

* cleanup.

* cleanup code.

* impl history monitoring api.

* add home page recevied message real-time

* add legend styles.

* update js.

* modify axis color.

* add resource.

* update dashboard home page.

* update css.

* update resource.

* modify DefaultSucceedMessageExpirationAfter to 24 hours.

* add resx,

* add consul discovery.

* remove unused file.

* add node page.

* add node page

* node discovery

* add kafka sqlserver sample.

* update sample.

* add okstats.

* refactor.

* fixed kafka client bugs.

* modify node and subscriber pages

* refactor.

* add Gateway middleware

* remove unused files.

* update resource.

* update gateway.

* refactor.

* update samples.

* remove base middleware

* add node switch click event.

* add NodeId config to options.

* upgrade dependent version.

* add PathMatch configuration

* update NodePage.cshtml

* remove session

* remove matchPath

* refactor

* refactor dashboard middleware

* gateway proxy middleware function maturation

* remove cookie exp

* refactor and remove files.

* add CapCache to cache server nodes.

* refactor.

* renamed message dto.

* add extended interface of IContentSerializer and JsonContentSerializer

* modify unit test

* check the requirement when CAP start.

* correct spelling

* cleanup code.

* add resources.

* processing pages will contains  Scheduled  and Enqueued messages.

* processing pages will contains  Scheduled  and Enqueued messages.

* ignore NU1701 Warning.

* renamed file.

* refactor

* implements dashboard interface.

* rename reference class.

* add mysql monitoring api impl

* fix bug of connection driver.

* remove cap.UseDashboard.  It's will be automatically enabled by registerd services.

* fix sql bug

* cleanup code and fix spelling

* fix postgre sql bug.

* fix mysql sql bug.

* when storage a received message raising an eception, we will reject the message to queue.

* fix spelling mistake

* add dashboard instructions to readme

* modify error log content.

* fix postger sql bug.

* fix consul discovery bug.

* add dashboard introduction to readme.md

* update english resource.

* Update README.md

* renamed files.

* fix postgre sql bug.

* cleanup code.

* update tests.

* rename file.

* update samples.

* Improved query performance without lock table. (#36)

* update sample.

* update samples.

* fix data reader uncolsed bug.

* update add jsonproperty

* refactor

* revert FetchNextMessageAsync sql

* add helper method.

* rafactor subscriber handler.

* add FailedRetryCount options.

* rafactor publisher excutor.

* add IPublishExecutor

* add failed message processor.

* inject failed message processor.

* refactor sql storage.

* fixed unit tests.

* fixed unit test.

* fixed postgresql tests.

* Update .travis.yml

* Update .travis.yml

* Update .travis.yml

* Update .travis.yml

* Update .travis.yml

* Update .travis.yml

* Update .travis.yml

* Update .travis.yml

* Update .travis.yml

* Update .travis.yml

* Update .travis.yml

* add LAN ip to LocalRequestsOnlyAuthorizationFilter

* add and update resource.

* add current node name to layout page if user enabled node discovery

* modify unit tests.

* add callback message sende tests.

* add deserlizer by type to  IContentSerializer

* refactor unit tests

* add summary comment.

* refactor async method.

* add comment and fixed spell error.

* refactor.

* add custom content serializer extension to CapBuilder

* add IMessagePacker

* refactor.

* add connection pool for kafka producer.

* add determines whether the query is null.

* add connection pool size config to KafkaOption.

* fixed json JObject bug

* add custom message wapper interface

* remove unused code.

* fixed callback topic send error bug.

* refactor.

* update unit tests.

* update samples.

* upgrade dependent package.

* remove some class from Abstraction namespce to Internal.

* optimize consumer related code

* add ICallbackMessageSender to DI with singleton.

* add and fixed some unit tests.

* refactor namespace.

* modify class protected level

* assemblies internal class are visible to test project

* add DeSerialize method to IContentSerializer with type deseralize

* refactoring

* Fix the phone style dispaly problem

* add logs

* refactoring

* upgrading `Confluent.Kafka` package

* Optimize message queue error message prompt.

* reorganize error message prompts.

* modify error message prompt

* add summary comments.

* modify dependent

* disabled print connection closed log. see: confluentinc/librdkafka#516

* Update README.md

* Update README.md

* Update README.zh-cn.md

* Update README.zh-cn.md

* fix dashboard not config discovery throw exceptions bug.

* Update README.md

* update readme

* Fixed serialized the message type bug. (#53)

* Update README.md

* Update README.md

* refactoring

* refactoring

* update readme.

* update readme.

* add summary comment.

* refactoring

* optimizing publisher interface

* update readme

* update readme.

* add summary comment.

* add summary comment.

* add summary comment.

* add summary comment.

* upgrading package

* add summary comment.

* optimize the RabbitMQ connection pool

* fix the producer connection returned

* dispose resource when connection pool is full
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
enhancement
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@yang-xiaodong @HongyanShen @ajdwfnhaps