Re: power-hungry SELECT query - how to reduce its priority?

[Jan Dvorak]

Hi Szymon,

Poland is not at all distant to me!

1. You can go on with optimizing the query.
   If I were you, I would introduce an explicit column
   for your sign_up+24*intval expression in the prospects table,
   and put it at the end of your responder_4 key.
   That should make MySQL read less records.

2. Optimization aside, there is more serious work to be done.
   The concurrent updates and inserts fail,
   because the tables are locked by the big query:
   other threads can only read them at that time.
   
   Now, it is critical that you tie the tables
   for as short time as possible.

   You may be well served with two shadow tables,
   call them prospects_X and responders_X.
   The roles then are: the X-ed tables are for your intensive query,
   while the original tables still get the inserts and updates going
   on against them.

   For synchronization, keep a timestamp field in the original tables
   and do a

   SELECT DATE_SUB( NOW(), INTERVAL 1 SECOND );  -->
$present_sync_timestamp

   LOCK TABLES prospects READ, responders READ;

   REPLACE INTO prospects_X ( <prospects_X_column_list> )
   SELECT <prospects_X_column_list>
     FROM prospects
     WHERE timestamp_col BETWEEN $last_sync_timestamp AND
$present_sync_timestamp

   the same with responders -> responders_X

   UNLOCK TABLES;

   periodically.  After this is done, move $present_sync_timestamp to
$last_sync_timestamp,
   and launch your query! You may also want to keep a log about the
synchronizations.

   If you use 3.23, you should try to make the ..._X tables HEAP.
   And perhaps you don't need all those columns?

3. If you would like to lower the priority of the MySQL thread that runs
your query,
   I think a renice should do it. You just need to figure out which PID
that process
   runs under - a perl script to analyze the results of SHOW
PROCESSLIST, perhaps?
   (Uh, if you run on Linux, that is.  I'm not sure how to do it on
other OSes.)
   And you may need to run this script as root :-(


I hope this helps.

Jan


Szymon Grabowski wrote:
> 
> Markus & all,
> 
> > Show the Query and table design. So someone might help.
> 
> Sorry about that. Here's the query:
> 
> $currentdate=int(time()/3600);
> $sqlquery="select a.responder,a.from_field,a.pro, b.* from responders a,
> prospects b where a.active='y' and a.seq>0 and a.responder=b.responder
> and b.active='y' and (b.sign_up+(24*b.intval))<=$currentdate";
> 
> And here are two relevant tables (prospects & responders)
> 
> TABLE prospects (
>    prospect_id bigint(21) DEFAULT '0' NOT NULL auto_increment,
>    responder varchar(60) binary NOT NULL,
>    to_field varchar(240) NOT NULL,
>    email varchar(120) NOT NULL,
>    name varchar(120) NOT NULL,
>    first_name varchar(120) NOT NULL,
>    seq mediumint(9) DEFAULT '0' NOT NULL,
>    ref varchar(10) NOT NULL,
>    intval smallint(6) DEFAULT '0' NOT NULL,
>    active char(1) DEFAULT 'y' NOT NULL,
>    sign_up mediumint(9) DEFAULT '0' NOT NULL,
>    PRIMARY KEY (prospect_id),
>    KEY responder (responder),
>    KEY responder_2 (responder, seq),
>    KEY responder_3 (responder, intval),
>    KEY responder_4 (responder, active),
>    KEY to_field (to_field)
> );
> 
> TABLE responders (              ;only responder,from_field,pro,active,seq
> are relevant to this query
>    key_old varchar(16) NOT NULL,
>    responder varchar(60) binary NOT NULL,
>    name varchar(120) NOT NULL,
>    email varchar(120) NOT NULL,
>    id varchar(5) NOT NULL,
>    reg_date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
>    exp_days mediumint(6) DEFAULT '0' NOT NULL,
>    pro char(1) DEFAULT 'n' NOT NULL,
>    usage_type char(1) DEFAULT 'b' NOT NULL,
>    from_field varchar(120) NOT NULL,
>    inqto varchar(120) NOT NULL,
>    pwd varchar(80) NOT NULL,
>    defname varchar(40) DEFAULT 'Friend' NOT NULL,
>    seq mediumint(6) DEFAULT '0' NOT NULL,
>    ip varchar(240) NOT NULL,
>    referer varchar(240) NOT NULL,
>    parent varchar(60) binary NOT NULL,
>    active char(1) DEFAULT 'n' NOT NULL,
>    reg_exp datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
>    pwd_enc varchar(80) NOT NULL,
>    mass_limit smallint(6) DEFAULT '0' NOT NULL,
>    emailstats char(1) DEFAULT 'n' NOT NULL,
>    PRIMARY KEY (responder),
>    KEY responder (responder),
>    KEY pro (pro),
>    KEY usage_type (usage_type),
>    KEY active (active),
>    KEY active_2 (active, seq, responder),
>    KEY emailstats (emailstats)
> );
> 
> Thanks for your help!
> 
> -S
> 
> >
> > >One of my Perl scripts (run every single hour via
> > >crontab) starts mysql SELECT query that takes
> > >approx. 5 minutes to finish. It operates on 1.3 million
> > >records and it's fairly sophisticated (for me, at least).
> > >The problem is that while that query is running other
> > >Perl & PHP3 scripts tend to timeout when they try to
> > >access MySQL. It looks that this hourly SELECT
> > >eats up to much of mysql daemon and nothing's left
> > >for other applications & scripts. Non-mysql stuff
> > >works without a fail even when that SELECT is
> > >running.
> > >
> > >Is it possible to reduce priority of that job, so that it
> > >doesn't consume so much of mysql daemon resources?
> > >It wouldn't be a problem if that SELECT took 50 minutes,
> > >but let other scripts access mysql as well.
> > >
> > >Any ideas?
> > >
> > >I'm running MySQL 3.22.22.
> > >
> > >Here are MySQL system variables:
> > >
> > >  back_log   5
> > >  connect_timeout   5
> > >  delayed_insert_limit   100
> > >  delayed_insert_timeout   300
> > >  delayed_queue_size   1000
> > >  join_buffer   131072
> > >  flush_time   0
> > >  key_buffer   8388600
> > >  language
> > >/usr/local/mysql-3.22.22-sun-solaris2.6-sparc/share/mysql/english/
> > >  log   OFF
> > >  log_update   OFF
> > >  long_query_time   10
> > >  low_priority_updates   OFF
> > >  max_allowed_packet   1048576
> > >  max_connections   500
> > >  max_connect_errors   10
> > >  max_delayed_insert_threads   20
> > >  max_join_size   4294967295
> > >  max_sort_length   1024
> > >  net_buffer_length   16384
> > >  port   3306
> > >  protocol-version   10
> > >  record_buffer   131072
> > >  skip_locking   OFF
> > >  socket   /tmp/mysql.sock
> > >  sort_buffer   2097144
> > >  table_cache   64
> > >  thread_stack   65536
> > >  tmp_table_size   1048576
> > >  tmpdir   /var/tmp/
> > >  version   3.22.22
> > >  wait_timeout   28800
> > >
> > >
> > >Thanks for your help!
> > >
> > >Cheers,
> > >
> > >Simon
> > >
> > >
> > >--
> > >---------------------------------------------------------------------
> > >Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> > >posting. To request this thread, e-mail mysql-thread28060@stripped
> > >
> > >To unsubscribe, send a message to:
> > >     <mysql-unsubscribe-mw-mysql=welters.de@stripped>
> >
> > -
> > Mit freundlichen Grüßen
> >
> > Markus Welters
> >
> > ----
> > Welters Datentechnik   Grafenberger Allee 81  40237 Duesseldorf
> > Telefon [phone]  GSM 0172-2007477   Fax [phone]
> > http://www.welters.de    MW411-RIPE   mailto: markus@stripped
> > ---------------------------------------------------------------
> >
> > --
> > ---------------------------------------------------------------------
> > Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> > posting. To request this thread, e-mail mysql-thread28064@stripped
> >
> > To unsubscribe, send a message to:
> >     <mysql-unsubscribe-szymon=profitstudio.it.pl@stripped>
> >
> 
> --
> ---------------------------------------------------------------------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail mysql-thread28067@stripped
> 
> To unsubscribe, send a message to:
>     <mysql-unsubscribe-jan.dvorak=veda.cz@stripped>