The mailing list for listmasters using Sympa

[IKEDA Soji <address@concealed>]

Hi Guenter and Sympa-users,

Sympa 6.1.14 partially adopted contribution by Riseup,
which keeps the cache of list information on database so that
List of Lists page appears faster (Please try adding
"db_list_cache on" to sympa.conf).

Riseup patch also included the feature to speedup searching lists
using DB cache, however, it had not been adopted because
implementation depends on MySQL.

I'm planning to improve DB list cache to apply it to searching
lists.  Works will be done on current development branch, i.e.
Sympa 6.2.

(I forgot to ask: David, may I create a branch dedicated for this
work?)


Regards,

 --- Soji

On Tue, 21 Aug 2012 11:04:40 +0200
Guenter Lau <address@concealed> wrote:

> Hi,
> I have installed a Sympa 6.1.14. with 2000 lists.
> The function "search lists" is very slowly (90 seconds).
> In sympa.conf the parameter cache_list_config is binary_file.
> 
> In search of "xyz" as listmaster address@concealed in
> sympa.log come out
> Aug 20 09:31:31 sympau1 wwsympa[32066]: info [robot lists.hu-berlin.de]
> [session 46453575260084] [client 141.20.8.215] [user
> address@concealed] main::do_search_list() do_search_list(xyz)
> 
> The result is correct, but the long time.
> 
> In during the function "search lists" Sympa writes for each list the
> files config.lock, include_admin_user.lock and stats.lock
> 
> For each list Sympa makes the queries:
> 
> Aug 20 08:58:55 sympau1 wwsympa[31955]: debug2
> List::get_first_admin_user() SQL:
>   SELECT user_admin AS email, comment_admin AS gecos, reception_admin AS
> reception, visibility_admin AS visibility, UNIX_TIMESTAMP(date_admin) AS
> "date", UNIX_TIMESTAMP(update_admin) AS update_date, info_admin AS info,
> profile_admin AS profile, subscribed_admin AS subscribed, included_admin
> AS included, include_sources_admin AS id, REVERSE(SUBSTRING(user_admin
> FROM position('@' IN user_admin) FOR 50)) AS "dom" FROM admin_table
> WHERE (list_admin = 'listenname' AND robot_admin =
>   'lists.hu-berlin.de' AND role_admin = 'owner' ) ORDER BY "dom"
> Aug 20 08:58:55 sympau1 wwsympa[31955]: debug2
> List::get_first_admin_user() SQL:
>   SELECT user_admin AS email, comment_admin AS gecos, reception_admin AS
> reception, visibility_admin AS visibility, UNIX_TIMESTAMP(date_admin) AS
> "date", UNIX_TIMESTAMP(update_admin) AS update_date, info_admin AS info,
> profile_admin AS profile, subscribed_admin AS subscribed, included_admin
> AS included, include_sources_admin AS id, REVERSE(SUBSTRING(user_admin
> FROM position('@' IN user_admin) FOR 50)) AS "dom" FROM admin_table
> WHERE (list_admin = 'listenname' AND robot_admin =
>   'lists.hu-berlin.de' AND role_admin = 'editor' ) ORDER BY "dom"
> Aug 20 08:58:55 sympau1 wwsympa[31955]: debug2
> List::get_first_admin_user() SQL:
>   SELECT user_admin AS email, comment_admin AS gecos, reception_admin AS
> reception, visibility_admin AS visibility, UNIX_TIMESTAMP(date_admin) AS
> "date", UNIX_TIMESTAMP(update_admin) AS update_date, info_admin AS info,
> profile_admin AS profile, subscribed_admin AS subscribed, included_admin
> AS included, include_sources_admin AS id, REVERSE(SUBSTRING(user_admin
> FROM position('@' IN user_admin) FOR 50)) AS "dom" FROM admin_table
> WHERE (list_admin = 'listenname' AND robot_admin =
>   'lists.hu-berlin.de' AND role_admin = 'owner' ) ORDER BY "dom"
> Aug 20 08:58:55 sympau1 wwsympa[31955]: debug2
> List::get_first_admin_user() SQL:
>   SELECT user_admin AS email, comment_admin AS gecos, reception_admin AS
> reception, visibility_admin AS visibility, UNIX_TIMESTAMP(date_admin) AS
> "date", UNIX_TIMESTAMP(update_admin) AS update_date, info_admin AS info,
> profile_admin AS profile, subscribed_admin AS subscribed, included_admin
> AS included, include_sources_admin AS id, REVERSE(SUBSTRING(user_admin
> FROM position('@' IN user_admin) FOR 50)) AS "dom" FROM admin_table
> WHERE (list_admin = 'listenname' AND robot_admin =
>   'lists.hu-berlin.de' AND role_admin = 'owner' ) ORDER BY "dom"
> 
> 
> 
> 
> 3 times the same query with role_admin = 'owner'.     ???
> 
> What can I changed to make it fastener?
> 
> Thanks so much for the help.
> 
> Guenter Lau
> 
> 
> 


-- 
株式会社 コンバージョン  セキュリティ&OSSソリューション部   池田荘児
〒231-0004　神奈川県横浜市中区元浜町3-21-2 ヘリオス関内ビル7F
e-mail address@concealed  TEL 045-640-3550
http://www.conversion.co.jp/