Re: [rt-users] Mysql queries slow
On Thu 22.Jul'10 at 9:52:14 -0700, William Graboyes wrote: Hi Ken, I do not have the full hardware specs. It is a hosted RT system. The specs that I do have access to are as follows: Probably a dual core single CPU system, speed unknown 2gb of ram 80 gb hd space they are using md but I do not know the configuration particulars. I know it is not much help. I have applied all the mysql tuning tricks I know to it. It's worth trying out mysqltuner.pl and seeing what it tells you. (http://mysqltuner.pl) Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Mysql queries slow
On Wed, Jul 21, 2010 at 06:19:51PM -0700, William Graboyes wrote: Hi List, As an example of what I am talking about the query `select count(id) from Attachments;` The returned result is 174039, but it takes 39.1549 seconds to return that simple query. The Transactions table returns 343259 in .4358 seconds. Does anyone have some optimization tips beyond what is already on the wiki. After a little more of my own tweaking I have the Attachments query down to 24.9559 seconds. Has anyone successfully integrated RT3 with memcached? Would I be better off moving the mysql server to it's own server? Running version: RT 3.8.7 MySQL 5.0.67 Total tickets as of this writing: 7282 Total time on RT: 1yr 3m Thanks in advance for any help that can be provided. Thanks, Bill Hi Bill, You mentioned your version of the software but no details of your actual hardware. To provide the answer to the count(*) query, the entire table concerned needs to be read from disk. For your Attachments result off 39s for 174039, is that the value for the first time the query is run or the value after multiple runs when the table is cached in memory? We use PostgreSQL as the backend and the first time the select query is run: # select count(*) from attachments; count - 2807604 (1 row) Time: 16707.404 ms But the second time, the result is much faster because of caching: # select count(*) from attachments; count - 2807622 (1 row) Time: 2909.343 ms Similarly for the transactions table: # select count(*) from transactions; count - 6468511 (1 row) Time: 4030.046 ms And for the 2nd run with caching: # select count(*) from transactions; count - 6468511 (1 row) Time: 1094.672 ms It does seem like your times are slower, but it could easily be the hardware setup that you are using for RT. Cheers, Ken Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Mysql queries slow
Hi Ken, I do not have the full hardware specs. It is a hosted RT system. The specs that I do have access to are as follows: Probably a dual core single CPU system, speed unknown 2gb of ram 80 gb hd space they are using md but I do not know the configuration particulars. I know it is not much help. I have applied all the mysql tuning tricks I know to it. Thanks, Bill On Thu, Jul 22, 2010 at 06:09, Kenneth Marshall k...@rice.edu wrote: On Wed, Jul 21, 2010 at 06:19:51PM -0700, William Graboyes wrote: Hi List, As an example of what I am talking about the query `select count(id) from Attachments;` The returned result is 174039, but it takes 39.1549 seconds to return that simple query. The Transactions table returns 343259 in .4358 seconds. Does anyone have some optimization tips beyond what is already on the wiki. After a little more of my own tweaking I have the Attachments query down to 24.9559 seconds. Has anyone successfully integrated RT3 with memcached? Would I be better off moving the mysql server to it's own server? Running version: RT 3.8.7 MySQL 5.0.67 Total tickets as of this writing: 7282 Total time on RT: 1yr 3m Thanks in advance for any help that can be provided. Thanks, Bill Hi Bill, You mentioned your version of the software but no details of your actual hardware. To provide the answer to the count(*) query, the entire table concerned needs to be read from disk. For your Attachments result off 39s for 174039, is that the value for the first time the query is run or the value after multiple runs when the table is cached in memory? We use PostgreSQL as the backend and the first time the select query is run: # select count(*) from attachments; count - 2807604 (1 row) Time: 16707.404 ms But the second time, the result is much faster because of caching: # select count(*) from attachments; count - 2807622 (1 row) Time: 2909.343 ms Similarly for the transactions table: # select count(*) from transactions; count - 6468511 (1 row) Time: 4030.046 ms And for the 2nd run with caching: # select count(*) from transactions; count - 6468511 (1 row) Time: 1094.672 ms It does seem like your times are slower, but it could easily be the hardware setup that you are using for RT. Cheers, Ken Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Mysql queries slow
On Thu, Jul 22, 2010 at 09:52:14AM -0700, William Graboyes wrote: Hi Ken, I do not have the full hardware specs. It is a hosted RT system. The specs that I do have access to are as follows: Probably a dual core single CPU system, speed unknown 2gb of ram 80 gb hd space they are using md but I do not know the configuration particulars. I know it is not much help. I have applied all the mysql tuning tricks I know to it. Thanks, Bill Hi Bill, It looks like you are being constrained by your hardware. The rows in the Transactions table are much, much smaller than those in the Attachments table. This means that on a system such as yours with only 2GB of memory, pretty much the entire Transactions table can be cached in memory. Unless you have very small attachments, this will not be the case for the Attachments table. I suspect that the lookups are quick but retrieving all the attachments via effectively random I/O on a single disk is holding you back. Cheers, Ken Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Mysql queries slow
with no real mysql tweaking on RT 3.8.8 I get: mysql select count(id) from Attachments; +---+ | count(id) | +---+ | 41636 | +---+ 1 row in set (0.62 sec) while significantly less that yours, .62 seconds isn't too shabby... -Original Message- From: rt-users-boun...@lists.bestpractical.com on behalf of William Graboyes Sent: Wed 7/21/2010 8:19 PM To: rt-users Subject: [rt-users] Mysql queries slow Hi List, As an example of what I am talking about the query `select count(id) from Attachments;` The returned result is 174039, but it takes 39.1549 seconds to return that simple query. The Transactions table returns 343259 in .4358 seconds. Does anyone have some optimization tips beyond what is already on the wiki. After a little more of my own tweaking I have the Attachments query down to 24.9559 seconds. Has anyone successfully integrated RT3 with memcached? Would I be better off moving the mysql server to it's own server? Running version: RT 3.8.7 MySQL 5.0.67 Total tickets as of this writing: 7282 Total time on RT: 1yr 3m Thanks in advance for any help that can be provided. Thanks, Bill Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
