GBrowse v2 and one configuration with more databases

[Biliards]

Hi all,

I have got more tracks: simple match type about short reads, more
xyplot type, some wiggle_xyplot type, more segments.. for a total:
1) if I manage all records in one database, 19G
2) if I manage records in 15 databases, 15G splitted in ~1G for each database

With first methods, GBrowse runs but it is very slowly.
With second methods, GBrowse runs and it is very fastly (I'm very happy!!!)

Second configuration has a big problem: after some request, I have got
500 error system.
Mysql didn't cater to requests but if I kill all GBrowse process, then
Mysql begins again to carter to requests.
With Phpmyadmin Version 3.2.2, I observed Mysql returns warning about
some parameters:

Handler_read_rnd: 2,378
[remember: this number is big when more queries request data ordering
or when joins use keys not correct]
Handler_read_rnd_next:26 k
[remember: generally if it is big, then tables are not correct
index-link or queries wrote without using index-link]
Select_full_join: 2
[remember: number of joins that they didn't use index-link]
Opened_tables: 2,892
[remember: if it is big, maybe table cache is more little]
Table_locks_waited: 7
[remember: if this number is big, maybe problems about queries optimizing]

Well, I could increase in size about 'table cache' parameter but I think that:
1) I make a mistake about my configuration files (files attached)
2) and/or there are serious problems about tables index-link
3) and/or there are keys not correct about joins
4) and/or there are queries wrote without using index-link

Can you help me about refine configuration file?
The problem is mine or points like 2,3 or 4 are there true?

Best,
Alessandra.



------------------------------------------------------------------------------
Come build with us! The BlackBerry® Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9-12, 2009. Register now!
http://p.sf.net/sfu/devconf
_______________________________________________
Gmod-gbrowse mailing list
Gmod-gbrowse@...
https://lists.sourceforge.net/lists/listinfo/gmod-gbrowse

[Dave Clements, GMOD Help Desk-2]

Hi Alessandra,

Thanks for the reminder on this unanswered email.  My comments are inline below.

> I have got more tracks: simple match type about short reads, more
> xyplot type, some wiggle_xyplot type, more segments.. for a total:
> 1) if I manage all records in one database, 19G
> 2) if I manage records in 15 databases, 15G splitted in ~1G for each database
>
> With first methods, GBrowse runs but it is very slowly.
> With second methods, GBrowse runs and it is very fastly (I'm very happy!!!)

That strikes me as strange, but I'm not a MySQL guru either.  I'm
going to guess that all of this speedup comes from MySQL, rather than
GBrowse (others may correct me on that).  Are these 15 different
databases in one MySQL server, or are there multiple MySQL servers
involved?

> Second configuration has a big problem: after some request, I have got
> 500 error system.
> Mysql didn't cater to requests but if I kill all GBrowse process, then
> Mysql begins again to carter to requests.
> With Phpmyadmin Version 3.2.2, I observed Mysql returns warning about
> some parameters:
>
> Handler_read_rnd: 2,378
> [remember: this number is big when more queries request data ordering
> or when joins use keys not correct]
> Handler_read_rnd_next:26 k
> [remember: generally if it is big, then tables are not correct
> index-link or queries wrote without using index-link]
> Select_full_join: 2
> [remember: number of joins that they didn't use index-link]
> Opened_tables: 2,892
> [remember: if it is big, maybe table cache is more little]
> Table_locks_waited: 7
> [remember: if this number is big, maybe problems about queries optimizing]

Having 15 separate databases instead of one will increase your
connections, open databases, and open tables by 15x.  I would not
think it would affect your overall need for cache.  Increasing
table_cache (max # of open tables for all threads - and this param is
deprecated in most recent releases) might be the right solution here.
You may also want to check max_connections, max_user_connections.

>
> Well, I could increase in size about 'table cache' parameter but I think that:
> 1) I make a mistake about my configuration files (files attached)
> 2) and/or there are serious problems about tables index-link
> 3) and/or there are keys not correct about joins
> 4) and/or there are queries wrote without using index-link
>
> Can you help me about refine configuration file?
> The problem is mine or points like 2,3 or 4 are there true?

I favor the table cache explanation, until we have a reason to think
that is not it.  Issues 2,3 and 4 would be the same with 1 database or
15 databases.

Dave C.

>
> Best,
> Alessandra.
>
> ------------------------------------------------------------------------------
> Come build with us! The BlackBerry® Developer Conference in SF, CA
> is the only developer event you need to attend this year. Jumpstart your
> developing skills, take BlackBerry mobile applications to market and stay
> ahead of the curve. Join us from November 9-12, 2009. Register now!
> http://p.sf.net/sfu/devconf
> _______________________________________________
> Gmod-gbrowse mailing list
> Gmod-gbrowse@...
> https://lists.sourceforge.net/lists/listinfo/gmod-gbrowse
>
>

--
Please keep responses on the list!
http://gmod.org/wiki/January_2010_GMOD_Meeting
http://gmod.org/wiki/GMOD_News
Was this helpful? http://gmod.org/wiki/Help_Desk_Feedback

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Gmod-gbrowse mailing list
Gmod-gbrowse@...
https://lists.sourceforge.net/lists/listinfo/gmod-gbrowse

[Josh Goodman]

Just based on this limited information my guess is that you have an index problem somewhere.  It
sounds to me like MySQL is performing a table scan instead of using an index.  Thus, when there are
lots of records in a table it is slow and it speeds up considerably when you reduce the number of
records.

You should switch back to the single database setup, perform whatever action you are doing to test
the speed of GBrowse and then execute a "show full processlist;" command on your mysql server to see
what the slow query or queries are.  You can then start using EXPLAIN commands with the slow queries
to make sure that all queries are using indices when possible.

Josh

Dave Clements, GMOD Help Desk wrote:

> Hi Alessandra,
>
> Thanks for the reminder on this unanswered email.  My comments are inline below.
>
>> I have got more tracks: simple match type about short reads, more
>> xyplot type, some wiggle_xyplot type, more segments.. for a total:
>> 1) if I manage all records in one database, 19G
>> 2) if I manage records in 15 databases, 15G splitted in ~1G for each database
>>
>> With first methods, GBrowse runs but it is very slowly.
>> With second methods, GBrowse runs and it is very fastly (I'm very happy!!!)
>
> That strikes me as strange, but I'm not a MySQL guru either.  I'm
> going to guess that all of this speedup comes from MySQL, rather than
> GBrowse (others may correct me on that).  Are these 15 different
> databases in one MySQL server, or are there multiple MySQL servers
> involved?
>
>> Second configuration has a big problem: after some request, I have got
>> 500 error system.
>> Mysql didn't cater to requests but if I kill all GBrowse process, then
>> Mysql begins again to carter to requests.
>> With Phpmyadmin Version 3.2.2, I observed Mysql returns warning about
>> some parameters:
>>
>> Handler_read_rnd: 2,378
>> [remember: this number is big when more queries request data ordering
>> or when joins use keys not correct]
>> Handler_read_rnd_next:26 k
>> [remember: generally if it is big, then tables are not correct
>> index-link or queries wrote without using index-link]
>> Select_full_join: 2
>> [remember: number of joins that they didn't use index-link]
>> Opened_tables: 2,892
>> [remember: if it is big, maybe table cache is more little]
>> Table_locks_waited: 7
>> [remember: if this number is big, maybe problems about queries optimizing]
>
> Having 15 separate databases instead of one will increase your
> connections, open databases, and open tables by 15x.  I would not
> think it would affect your overall need for cache.  Increasing
> table_cache (max # of open tables for all threads - and this param is
> deprecated in most recent releases) might be the right solution here.
> You may also want to check max_connections, max_user_connections.
>
>> Well, I could increase in size about 'table cache' parameter but I think that:
>> 1) I make a mistake about my configuration files (files attached)
>> 2) and/or there are serious problems about tables index-link
>> 3) and/or there are keys not correct about joins
>> 4) and/or there are queries wrote without using index-link
>>
>> Can you help me about refine configuration file?
>> The problem is mine or points like 2,3 or 4 are there true?
>
> I favor the table cache explanation, until we have a reason to think
> that is not it.  Issues 2,3 and 4 would be the same with 1 database or
> 15 databases.
>
> Dave C.
>> Best,
>> Alessandra.
>>
>> ------------------------------------------------------------------------------
>> Come build with us! The BlackBerry® Developer Conference in SF, CA
>> is the only developer event you need to attend this year. Jumpstart your
>> developing skills, take BlackBerry mobile applications to market and stay
>> ahead of the curve. Join us from November 9-12, 2009. Register now!
>> http://p.sf.net/sfu/devconf
>> _______________________________________________
>> Gmod-gbrowse mailing list
>> Gmod-gbrowse@...
>> https://lists.sourceforge.net/lists/listinfo/gmod-gbrowse
>>
>>
>
>
>

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Gmod-gbrowse mailing list
Gmod-gbrowse@...
https://lists.sourceforge.net/lists/listinfo/gmod-gbrowse