mySQL logs
3 posts by 2 authors in: Forums > CMS Builder
Last Post: November 17, 2011 (RSS)
Hi all, mySQL question here.
I have several tables (over 20) in a database with some that have over 20,000 records. Those are used primarily for archiving records from other similar tables and are not accessed on a regular basis. Those that are accessed, 4 or 5 normally, for the most part contain less than 2,000 records. All run in a virtual dedicated server.
Just recently (within the last couple of days) the time it takes to access the website has increased by at least 10 times what it normally would take (from 1-2 seconds to 10-15 seconds and as much as 30 seconds) even though the increase in records has not been unusually high. I was told by my ISP to "optimize" my tables, which I did via the admin for mySQL, which didn't help (all came out "ok"). I was further told by my by ISP: "Regarding the database on your site; one additional thing that you can look into is the query logging for MySQL. Sometimes this can be set to log every query to the database and this can also decrease performance. Basically if every query has to also log it's actions, even with several small optimized queries it can cause latency issues for the website." The link they provided for further information, http://dev.mysql.com/doc/refman/5.1/en/query-log.html, explained a lot but was rather confusing.
I researched this issue and found a possible command to run: "mysql> SET GLOBAL general_log=OFF;") to see if that helps. I tried doing that from the plugin "MySQL Console" and received the following error: MySQL Error: Unknown system variable 'general_log'
Question: is there a way to shut off the 'general' log from working while running a specific file? Or another way to limit all this writing that seemingly is causing the increase in latency?
TIA,
Eric
I have several tables (over 20) in a database with some that have over 20,000 records. Those are used primarily for archiving records from other similar tables and are not accessed on a regular basis. Those that are accessed, 4 or 5 normally, for the most part contain less than 2,000 records. All run in a virtual dedicated server.
Just recently (within the last couple of days) the time it takes to access the website has increased by at least 10 times what it normally would take (from 1-2 seconds to 10-15 seconds and as much as 30 seconds) even though the increase in records has not been unusually high. I was told by my ISP to "optimize" my tables, which I did via the admin for mySQL, which didn't help (all came out "ok"). I was further told by my by ISP: "Regarding the database on your site; one additional thing that you can look into is the query logging for MySQL. Sometimes this can be set to log every query to the database and this can also decrease performance. Basically if every query has to also log it's actions, even with several small optimized queries it can cause latency issues for the website." The link they provided for further information, http://dev.mysql.com/doc/refman/5.1/en/query-log.html, explained a lot but was rather confusing.
I researched this issue and found a possible command to run: "mysql> SET GLOBAL general_log=OFF;") to see if that helps. I tried doing that from the plugin "MySQL Console" and received the following error: MySQL Error: Unknown system variable 'general_log'
Question: is there a way to shut off the 'general' log from working while running a specific file? Or another way to limit all this writing that seemingly is causing the increase in latency?
TIA,
Eric
Re: [eduran582] mySQL logs
By Jason - November 17, 2011
Hi Eric,
The command they are referring to in the docs is only good for MySQL versions 5.1.12 and above. It is possible that you are running an older version. You could try this:
But if you're running an older MySQL version, you may run into the same issue.
It seems like the problem probably doesn't have anything to do with query logging. Since the general log only logs the query executed (not the results), you would expect logging to take the same amount of time regardless of the size of the database. It's also strange that you've had a large number of records in your database for awhile, yet are only experiencing the problem now.
Do you find that things run just as slow when accessing a small table as a large one? If you set up a small test section, with 1 or no records in it, do you still experience slow response time?
Let us know and we can take a closer look into this.
Thanks
The command they are referring to in the docs is only good for MySQL versions 5.1.12 and above. It is possible that you are running an older version. You could try this:
SET GLOBAL LOG := 0;But if you're running an older MySQL version, you may run into the same issue.
It seems like the problem probably doesn't have anything to do with query logging. Since the general log only logs the query executed (not the results), you would expect logging to take the same amount of time regardless of the size of the database. It's also strange that you've had a large number of records in your database for awhile, yet are only experiencing the problem now.
Do you find that things run just as slow when accessing a small table as a large one? If you set up a small test section, with 1 or no records in it, do you still experience slow response time?
Let us know and we can take a closer look into this.
Thanks
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Re: [Jason] mySQL logs
Hi Jason,
Thanks for the quick reply! I tried running the command you gave but ended up with the error: "MySQL Error: Variable 'log' is a read only variable". The MySQL version running is: v5.0.77
Just before I posted this request for help, I became rather insistent with the "server group" support and asked them if they could check the other accounts sharing the same virtual server I was on. Even thought I haven't heard back from them (yet), to my amazement when I went to check the code you gave me, the site is now coming up within 1-2 seconds! [shocked]
In reply to your question about testing a smaller table for access, it seems it didn't matter how big or small the table was; it was still taking a ton of time to access it. However, maybe the problem is solved.
Thanks again for your quick response! I'll follow-up when I hear back from the ISP (if I do). [;)]
Eric
Thanks for the quick reply! I tried running the command you gave but ended up with the error: "MySQL Error: Variable 'log' is a read only variable". The MySQL version running is: v5.0.77
Just before I posted this request for help, I became rather insistent with the "server group" support and asked them if they could check the other accounts sharing the same virtual server I was on. Even thought I haven't heard back from them (yet), to my amazement when I went to check the code you gave me, the site is now coming up within 1-2 seconds! [shocked]
In reply to your question about testing a smaller table for access, it seems it didn't matter how big or small the table was; it was still taking a ton of time to access it. However, maybe the problem is solved.
Thanks again for your quick response! I'll follow-up when I hear back from the ISP (if I do). [;)]
Eric