Recommended MySQL Configuration settings

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Recommended MySQL Configuration settings

Tosh, Michael J
All,

We just cut over to Bugzilla 4.0 (with 42000 bugs and 3600 users) from a custom-built application. Running on MySQL 5.0.77 with mostly default parameters.  We have not configured mod_perl yet, but are planning to.  The queries are AMAZINGLY slow.  I run 'show processlist' within the database, and see some queries that are running for over 400 seconds.  This is a basic query.

Question is, what are the recommended MySQL performance settings?  We have 2GB RAM and dual processor on a dedicated server.  CentOS.

Thanks,
Mike Tosh


_______________________________________________
support-bugzilla mailing list
[hidden email]
https://lists.mozilla.org/listinfo/support-bugzilla
PLEASE put [hidden email] in the To: field when you reply.
Reply | Threaded
Open this post in threaded view
|

Re: Recommended MySQL Configuration settings

Max Kanat-Alexander
On 03/21/2011 11:58 AM, Tosh, Michael J wrote:
> Question is, what are the recommended MySQL performance settings?  We have 2GB RAM and dual processor on a dedicated server.  CentOS.

        That is not a large enough server for 42,000 bugs and 3600 users.

        Most importantly, for MySQL, you want an innodb_buffer_pool_size that
is as large as your entire database (without causing the machine to
swap) and a key_buffer_size that's as large as all the indexes on the
bugs_fulltext table combined.

        It would also help us if you were more specific about exactly which
queries were slow.

        -Max
--
http://www.bugzillasource.com/
Competent, Friendly Bugzilla, Perl, and IT Services
_______________________________________________
support-bugzilla mailing list
[hidden email]
https://lists.mozilla.org/listinfo/support-bugzilla
PLEASE put [hidden email] in the To: field when you reply.
Reply | Threaded
Open this post in threaded view
|

RE: Recommended MySQL Configuration settings

Tosh, Michael J
Max Kanat-Alexander wrote:
> On 03/21/2011 11:58 AM, Tosh, Michael J wrote:
>> Question is, what are the recommended MySQL performance settings?  We have
>> 2GB RAM and dual processor on a dedicated server.  
>> CentOS.
>
> That is not a large enough server for 42,000 bugs and 3600 users.

We are planning to upgrade the machine's ram to 4GB tonight, as well as turn on mod_perl.  It was off during development of the templates and customizations.  Mod_perl should take about 1.5GB, and mysql seems to use about another 1.5 GB.

> Most importantly, for MySQL, you want an innodb_buffer_pool_size that
> is as large as your entire database (without causing the machine to
> swap) and a key_buffer_size that's as large as all the indexes on the
> bugs_fulltext table combined.

Ok, that is very helpful.  We will update those.
 
> It would also help us if you were more specific about exactly which
> queries were slow.

Unfortunately for our day 1 of being online, it appears that all users are doing large 'All Open Bugs' type queries.  

The innodb file had grown to 8GB because I had imported all attachments into the DB.  I since removed them and put them on the local disk.  The plan is to dump, drop, then create and restore the database.  The whole dump file is only 255MB.  Not sure if the database file being so large is part of the problem, but as part of our reload, we will use the innodb_file_per_table option.
_______________________________________________
support-bugzilla mailing list
[hidden email]
https://lists.mozilla.org/listinfo/support-bugzilla
PLEASE put [hidden email] in the To: field when you reply.