[Gmod-help] Re: [Gmod-gbrowse] GBrowse v2 and one configuration with more databases

[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 at lists.sourceforge.net
>> https://lists.sourceforge.net/lists/listinfo/gmod-gbrowse
>>
>>
> 
> 
>