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.

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

Google Cloud SQL compatibility: do not call 'SHOW COLUMNS' often #5992

Open
sbesnaud opened this issue Aug 13, 2014 · 6 comments
Open

Google Cloud SQL compatibility: do not call 'SHOW COLUMNS' often #5992

sbesnaud opened this issue Aug 13, 2014 · 6 comments
Labels
c: Performance For when we could improve the performance / speed of Matomo. Task Indicates an issue is neither a feature nor a bug and it's purely a "technical" change.

Comments

@sbesnaud
Copy link

Hello,

I'm working with Google Cloud, 3 Virtual Machines as trackers (Google Compute Engine), working with Google Cloud SQL as database.

I'm encountering an issue which prevent me completely to use Piwik : my trackers are calling the requests "SHOW COLUMNS FROM piwik_log_visit / piwik_log_link_visit_action [...]"
I absolutely don't know why this request is called, but this query seems to be very slow and is threaded to a thousand of threads. By threading it with the thread_handling parameter in SQL (set to one_thread_per_connection) grows to the max_connections instantly => Every next requests encounter the "Too many connection" error from MySQL..
Usually, it doesn't last more than 30 minutes (which is HUGE amount of data lost), but I've been encountering this issue for more than 4 hours sometimes, and some other times it seems not to finish at all. My database contains about 40G of data, and I'm tracking more than 600k visits per day, so maybe that's why it takes so long, but it doesn't explain why this request is called..

I'm losing a lot of data each time it occurs so I'm really looking for a solution to this. The last thing I've been trying is disabling the "DBStats" plugin. It did not seem to solve the problem.
I'm using Piwik 2.3.0 atm.

Do you know why this request is called?

Thanks a lot for your help.

@mattab mattab added this to the Short term milestone Aug 13, 2014
@mattab
Copy link
Member

mattab commented Aug 13, 2014

Thanks for the report!

Likely this is called in order to know how many Custom Variables are currently setup on your piwik. Because more custom variables can be added, we need to dynamically fetch how many there are. Likely this cannot be fixed on our end. Is there any chance this could be worked around on your end?

@sbesnaud
Copy link
Author

Yes there is of course. If I just need to modify some code in order to make this call not necessary (meaning I will never change the custom variables's number's value), I can.
However, I will need to care everytime I make a Piwik update right?

@sbesnaud
Copy link
Author

Do you know how can I fix it on my end?

@sbesnaud
Copy link
Author

I think I misunderstood a bit, I thought you were going to tell me how to fix it on my end. Do I have to search for the file doing this work or do you know it?
Thanks.

@mattab
Copy link
Member

mattab commented Aug 20, 2014

@sbesnaud what you did was create an issue in the issue tracker. As you can see there are 630 other issues already opened. The way we work is pick the most important issues and work on them in the next release. So far you're only user to report a problem with SHOW COLUMNS so investigating this is not yet high priority. If you have more info feel free to post here! to learn more see: http://developer.piwik.org/guides/core-team-workflow

@sbesnaud
Copy link
Author

Just to keep you posted, I found where the request was called (core/Db.php) and I wrote in plain text my columns names in a table in order to return them directly when the function is called instead of querying the database. It seems to fix my issue for now.
I did it for 4 tables : log_link_visit_action, log_visit, log_action, log_conversion

@mattab mattab modified the milestones: Mid term, Short term Oct 12, 2014
@mattab mattab added Task Indicates an issue is neither a feature nor a bug and it's purely a "technical" change. and removed Bug For errors / faults / flaws / inconsistencies etc. labels Oct 12, 2014
@mattab mattab changed the title "SHOW COLUMNS" request called by trackers Google Cloud SQL compatibility: do not call 'SHOW COLUMNS' often Oct 12, 2014
@mattab mattab modified the milestones: Long term, Mid term Dec 5, 2016
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
c: Performance For when we could improve the performance / speed of Matomo. Task Indicates an issue is neither a feature nor a bug and it's purely a "technical" change.
Projects
None yet
Development

No branches or pull requests

2 participants