List:Internals« Previous MessageNext Message »
From:Andrej van der Zee Date:March 24 2009 7:38am
Subject:Re: LOAD DATA LOCAL INFILE in background thread brings down MySQL
server
View as plain text  
Hi,

Thanks for taking a look!

> You only showed the code that works, not the code that fails ...

Yes you are right, I put it in this email below.

>
> But the #1 reason for thread problems is using a single mysql connection with
> multiple threads simultaneously. Each thread needs exclusive locking around
> the use of the mysql connection object.
>

The MySQL connection is local to the background thread, not shared
with other threads. Moreover, there is only one background thread
started.

Below is the code for creating/starting the thread and then the thread
itself (stripped version). The thread hangs on mysql_real_query() for
a few seconds and then the query fails with no error-message.

Thank you!
Andrej


------------ Creating / starting the background thread -----------

static void create_mbrace_thread()
{
  DBUG_ENTER("create_mbrace_thread");

        if (!opt_mbrace_on) /* Extra safe-guard */
                DBUG_VOID_RETURN;

  pthread_attr_t thr_attr;
  (void) pthread_attr_init(&thr_attr);
  (void) pthread_attr_setdetachstate(&thr_attr, PTHREAD_CREATE_DETACHED);
  pthread_attr_setscope(&thr_attr, PTHREAD_SCOPE_SYSTEM);
//  my_pthread_attr_setprio(&thr_attr, MBRACE_PRIOR);

  THD *thd= new THD;
  my_net_init(&thd->net,(st_vio*) 0);
  thd->max_client_packet_length= thd->net.max_packet;
  thd->security_ctx->master_access= ~(ulong)0;
  thd->thread_id= thd->variables.pseudo_thread_id= thread_id++;
  thread_count++;

  if (pthread_create(&thd->real_id,&thr_attr,handle_mbrace, (void*) thd))
  {
    sql_print_warning("Can't create thread to handle mbrace");
    DBUG_VOID_RETURN;
  }

  DBUG_VOID_RETURN;
}

------------ The thread itself -----------

pthread_handler_t handle_mbrace(void *arg)
{
  static const char * query = "LOAD DATA LOCAL INFILE
'/dev/shm/mbrace_infile.txt' INTO TABLE request_mysql FIELDS
TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\\\"' LINES TERMINATED BY
'\\n'";
  MYSQL *conn;
  int i;

  THD *thd=(THD*) arg;
  thd->thread_stack= (char*) &thd;
  if (my_thread_init() || thd->store_globals())
  {
    close_connection(thd, ER_OUT_OF_RESOURCES, 1);
    thd->fatal_error();
    goto end;
  }

  DBUG_ENTER("handle_mbrace");

  for (i=0;i<10;++i)
  {
    conn = mysql_init(NULL);
    if (!mysql_real_connect(conn, "mbrace3", "mbrace", "mbrace",
"mbracelog", 3306, "/var/run/mysqld/mysqld.sock", CLIENT_LOCAL_FILES))
    {
      general_log_print(thd, COM_MBRACE, "Failed to connect to
mbracelog database: %s", mysql_error(conn));
      goto end;
    }

    if (mysql_options(conn, MYSQL_OPT_LOCAL_INFILE, NULL))
    {
      general_log_print(thd, COM_MBRACE, "Failed to set mysql option
MYSQL_OPT_LOCAL_INFILE: %s", mysql_error(conn));
      mysql_close(conn);
      goto end;
    }

    if (mysql_real_query(conn, query, strlen(query)))
    {
      general_log_print(thd, COM_MBRACE, "Failed to execute query: %s:
%s", query, mysql_error(conn));
      continue;
    }

    sleep(opt_mbrace_load_infile_interval);
  }


  mysql_close(conn);

end:
  close_thread_tables(thd);
  net_end(&thd->net);
  thd->cleanup();
  delete thd;

  my_thread_end();
  pthread_exit(0);

  DBUG_RETURN(0);
}


-- 
Andrej van der Zee
2-40-19 Koenji-minami
Suginami-ku, Tokyo
166-0003 JAPAN
Mobile: +81-(0)80-65251092
Phone/Fax: +81-(0)3-3318-3155
Thread
LOAD DATA LOCAL INFILE in background thread brings down MySQL serverAndrej van der Zee24 Mar
  • Re: LOAD DATA LOCAL INFILE in background thread brings down MySQL serverAndrej van der Zee24 Mar
  • Re: LOAD DATA LOCAL INFILE in background thread brings down MySQL serverKristian Nielsen24 Mar
    • Re: LOAD DATA LOCAL INFILE in background thread brings down MySQL serverAndrej van der Zee24 Mar
      • Re: LOAD DATA LOCAL INFILE in background thread brings down MySQL serverAndrej van der Zee25 Mar
Re: LOAD DATA LOCAL INFILE in background thread brings down MySQL serverAndrej van der Zee25 Mar