IPP Software Navigation Tools IPP Links Communication Pan-STARRS Links

Changeset 34921


Ignore:
Timestamp:
Jan 13, 2013, 2:21:48 PM (14 years ago)
Author:
eugene
Message:

mysql inserts work at reasonable rate

Location:
branches/eam_branches/ipp-20121219/Ohana/src/dvopsps
Files:
6 edited

Legend:

Unmodified
Added
Removed

  • branches/eam_branches/ipp-20121219/Ohana/src/dvopsps/Makefile

    r34916 r34921  
    2929$(SRC)/insert_detections_dvopsps_catalog.$(ARCH).o
    3030
     31# $(SRC)/insert_detections_dvopsps_catalog.$(ARCH).o
     32
    3133$(DVOPSPS): $(INC)/dvopsps.h
    3234$(BIN)/dvopsps.$(ARCH): $(DVOPSPS)

  • branches/eam_branches/ipp-20121219/Ohana/src/dvopsps/include/dvopsps.h

    r34919 r34921  
    55
    66# define DVO_MAX_PATH 1024
     7# define MAX_BUFFER 0x080000
    78
    89/* global variables set in parameter file */

  • branches/eam_branches/ipp-20121219/Ohana/src/dvopsps/src/insert_detections_dvopsps.c

    r34919 r34921  
    7373    dvo_catalog_free (&catalog);
    7474  }
     75
     76  if (0) {
     77    char query[256];
     78    MYSQL_RES *result;
     79
     80    sprintf (query, "commit;");
     81    if (mysql_query (mysqlReal, query)) {
     82      fprintf (stderr, "failed to turn off autocommit\n");
     83      fprintf (stderr, "%s\n", mysql_error (mysqlReal));
     84      return FALSE;
     85    }
     86    result = mysql_store_result (mysqlReal);
     87    mysql_free_result (result);
     88  }
     89   
    7590  return (TRUE);
    7691}     

  • branches/eam_branches/ipp-20121219/Ohana/src/dvopsps/src/insert_detections_dvopsps_catalog.c

    r34919 r34921  
    11# include "dvopsps.h"
     2# define DEBUG 0
     3# define SQL_INSERT 1
     4// 1 = write data via sql insert values = (),
     5// 0 = write data via load from file
    26
    37# define MARKTIME(MSG,...) {                    \
    … …  
    2428
    2529  insert_detections_mysql_init (&buffer);
     30  int Ninsert = 0;
    2631
     32  // NOTE for testing, just do a few objects
    2733  if (!mysql) {
    2834    catalog[0].Naverage = 5;
    … …  
    4046      insert_detections_mysql_value (&buffer, &average[i], &measure[m+j]);
    4147
    42       if (buffer.Nalloc > 0x100000) {
     48      if (buffer.Nbuffer > MAX_BUFFER) {
    4349        insert_detections_mysql_commit (&buffer, mysql);
     50        if (DEBUG) fprintf (stderr, "inserted %d rows\n", Ninsert);
     51        Ninsert = 0;
    4452        if (0) {
    4553          FlushIOBuffer (&buffer);
    … …  
    5159      }
    5260
     61      Ninsert ++;
    5362      found ++;
    5463    }
    5564  }
    5665  insert_detections_mysql_commit (&buffer, mysql);
     66  if (DEBUG) fprintf (stderr, "inserted %d rows\n", Ninsert);
     67  FreeIOBuffer (&buffer);
    5768
    5869  MARKTIME("-- inserted "OFF_T_FMT" rows in %f sec, skipped %d without IDs\n", found, dtime, missingID);
    … …  
    6677  }
    6778
     79# if (SQL_INSERT)
    6880  PrintIOBuffer (buffer, "INSERT INTO dvoDetection (imageID, ippDetectID, detectID, ippObjID, objID, flags, zp, zpErr, airMass, expTime, ra, dec_, raErr, decErr) VALUES \n");
     81# endif
    6982
    7083  return TRUE;
    … …  
    7588  // XXX I am changing the def of ippObjID if I use the code below
    7689  PhotCode *code = GetPhotcodebyCode(measure->photcode);
     90# if (SQL_INSERT)
    7791  PrintIOBuffer (buffer, "    (%d, %u, %lu, %lu, %lu, %u, %f, %f, %f, %f, %lf, %lf, %f, %f),\n",
     92# else
     93  PrintIOBuffer (buffer, "    %d %u %lu %lu %lu %u %f %f %f %f %lf %lf %f %f\n",
     94# endif
    7895          measure->imageID,    // imageID
    7996          measure->detID,   // ippDetectID
    … …  
    96113int insert_detections_mysql_commit (IOBuffer *buffer, MYSQL *mysql) {
    97114
     115  MYSQL_RES *result;
     116
     117# if (SQL_INSERT) 
    98118  // check that the last two chars are ,\n and replace with ;\n
    99119  if (!strcmp(&buffer->buffer[buffer->Nbuffer-2], ",\n")) {
    100     fprintf (stderr, "valid sql\n");
    101120    buffer->buffer[buffer->Nbuffer-2] = ';';
    102121  } else {
    … …  
    104123    return FALSE;
    105124  }
     125# endif
    106126
    107127  // XXX check return status
    108128  if (mysql) {
    109     mysql_query(mysql, buffer->buffer);
     129# if (SQL_INSERT)   
     130    int status = mysql_query(mysql, buffer->buffer);
     131# else
     132    FILE *f = fopen ("/tmp/dvopsps.dat", "w");
     133    if (!f) {
     134      fprintf (stderr, "failed to write buffer file\n");
     135      return FALSE;
     136    }
     137    int fd = fileno (f);
     138    int Nbytes = write (fd, buffer->buffer, buffer->Nbuffer);
     139    if (Nbytes != buffer->Nbuffer) {
     140      fprintf (stderr, "error writing data to buffer file\n");
     141    }
     142    char sql[256];
     143    snprintf (sql, 256, "load data infile '/tmp/dvopsps.dat' into table dvoDetection;");
     144    int status = mysql_query(mysql, sql);
     145# endif
     146    if (status) {
     147      fprintf (stderr, "error with insert:\n");
     148      fprintf (stderr, "%s\n", mysql_error(mysql));
     149      fprintf (stderr, "Nbuffer: %d\n", buffer->Nbuffer);
     150    }
     151    result = mysql_store_result (mysql);
     152    mysql_free_result (result);
    110153  } else {
    111154    fprintf (stderr, "%s\n", buffer->buffer);

  • branches/eam_branches/ipp-20121219/Ohana/src/dvopsps/src/mysql_dvopsps.c

    r34916 r34921  
    11# include "dvopsps.h"
     2
     3int dump_result (MYSQL *connection) {
     4
     5  MYSQL_RES *result = mysql_store_result (connection);
     6  if (!result) return FALSE;
     7  int Nrows = mysql_num_rows(result);
     8  int Ncols = mysql_num_fields(result);
     9
     10  int i, j;
     11  for (j = 0; j < Nrows; j++) {
     12    MYSQL_ROW row = mysql_fetch_row(result);
     13    for (i = 0; i < Ncols; i++) {
     14      fprintf (stderr, "%s ", row[i]);
     15    }
     16    fprintf (stderr, "\n");
     17  }
     18  mysql_free_result (result);
     19  return (TRUE);
     20}
    221
    322// DATABASE_* are supplied on the command line and are global variables in dvopsps.h
    … …  
    524
    625  char query[256];
    7   MYSQL_RES *result;
    826
    927  mysql_init (mysqlBase);
    … …  
    1634  }
    1735   
    18   sprintf (query, "set @@interactive_timeout = 30000");
     36  sprintf (query, "set @@interactive_timeout = 30000;");
    1937  if (mysql_query (connection, query)) {
    2038    fprintf (stderr, "failed to set interactive timout\n");
    … …  
    2240    return NULL;
    2341  }
    24   result = mysql_store_result (connection);
    25   mysql_free_result (result);
     42  dump_result (connection);
    2643   
    27   sprintf (query, "set @@wait_timeout = 30000");
     44  sprintf (query, "set @@wait_timeout = 30000;");
    2845  if (mysql_query (connection, query)) {
    2946    fprintf (stderr, "failed to set wait timout\n");
    … …  
    3148    return NULL;
    3249  }
    33   result = mysql_store_result (connection);
    34   mysql_free_result (result);
     50  dump_result (connection);
     51   
     52  sprintf (query, "set max_allowed_packet = %d;", 2*MAX_BUFFER);
     53  if (mysql_query (connection, query)) {
     54    fprintf (stderr, "failed to set max_allowed_packet\n");
     55    fprintf (stderr, "%s\n", mysql_error (connection));
     56    return NULL;
     57  }
     58  dump_result (connection);
     59   
     60  sprintf (query, "show variables like 'max_allowed_packet';");
     61  if (mysql_query (connection, query)) {
     62    fprintf (stderr, "failed to set max_allowed_packet\n");
     63    fprintf (stderr, "%s\n", mysql_error (connection));
     64    return NULL;
     65  }
     66  dump_result (connection);
     67   
     68  if (0) {
     69    sprintf (query, "set autocommit=0;");
     70    if (mysql_query (connection, query)) {
     71      fprintf (stderr, "failed to turn off autocommit\n");
     72      fprintf (stderr, "%s\n", mysql_error (connection));
     73      return NULL;
     74    }
     75    dump_result (connection);
     76  }
    3577   
    3678  return connection;

  • branches/eam_branches/ipp-20121219/Ohana/src/dvopsps/test/dbadmin.sh

    r34919 r34921  
    7777  echo "      create a new test db"
    7878  echo ""
    79   echo "  dbadmin.sh user (dbhost) (dbname) (dbuser) (password)"
     79  echo "  dbadmin.sh delete (dbhost) (dbname) (dbuser)"
     80  echo "      delete an existing test db (enter pass 2x)"
     81  echo ""
     82  echo "  dbadmin.sh user (dbhost) (dbuser) (password)"
    8083  echo "      create a new user and password for the test db"
    8184  echo ""
Note: See TracChangeset for help on using the changeset viewer.