title: performance issues with MYISAM
body: <p>folks</p>
  
  <p>i need to generate a report from the  table A  ( about 60 million records and myisam storage engine). The table A is dumped with cdr files every 15 minutes. </p>
  
  <p>I would like to generate a report based on (date to) and (date from) </p>
  
  <p>couple of scenarios that i tried are as below </p>
  
  <p>1) i tried to create a temporary table B ( using create table B Engine=Myisam select a,c,d from table A) </p>
  
  <p>in the processlist , the Table A is locked if the table A is being dumped with cdr records. And will remain locked. </p>
  
  <p>if there are no inserts of cdr records in Table A, then the temporary table B is created in about 1 minutes. </p>
  
  <p>is there any way i can create temporary table B  when the files are being dumped in table A.</p>
  
  <p>2) i tried to create a temporary table B ( using create table B select a.c.d from table A) </p>
  
  <p>in this case , table b that is created INNODB engine and will take lot of space</p>
  
  <p>3) Just need info if i create temporary b engine=Memory, will it have any performance issues or not. </p>
  
  <p>I m attaching my.cnf as below </p>
  
  <pre><code># Example MySQL config file for very large systems.
  #
  # This is for a large system with memory of 1G-2G where the system runs mainly
  # MySQL.
  #
  # MySQL programs look for option files in a set of
  # locations which depend on the deployment platform.
  # You can copy this option file to one of those
  # locations. For information about these locations, see:
  # http://dev.mysql.com/doc/mysql/en/option-files.html
  #
  # In this file, you can use all long options that a program supports.
  # If you want to know which options a program supports, run the program
  # with the "--help" option.
  
  # The following options will be passed to all MySQL clients
  [client]
  #password   = your_password
  port        = 3306
  socket      = /var/lib/mysql/mysql.sock
  
  # Here follows entries for some specific programs
  
  # The MySQL server
  [mysqld]
  port        = 3306
  socket      = /var/lib/mysql/mysql.sock
  skip-locking
  key_buffer_size = 3G
  max_allowed_packet = 16M
  table_open_cache = 1024
  sort_buffer_size = 256M
  read_buffer_size = 1024M
  read_rnd_buffer_size = 64M
  myisam_sort_buffer_size = 4G
  thread_cache_size = 8
  query_cache_size = 256M
  query_cache_limit = 256M
  
  max_connections = 100   
  max_heap_table_size = 64M
  join_buffer_size = 8M
  tmp_table_size = 1024M
  myisam_max_sort_file_size = 3G
  slow_query_log
  
  
  
  # Try number of CPU's*2 for thread_concurrency
  thread_concurrency = 8
  
  # Don't listen on a TCP/IP port at all. This can be a security enhancement,
  # if all processes that need to connect to mysqld run on the same host.
  # All interaction with mysqld must be made via Unix sockets or named pipes.
  # Note that using this option without enabling named pipes on Windows
  # (via the "enable-named-pipe" option) will render mysqld useless!
  #
  #skip-networking
  
  # Replication Master Server (default)
  # binary logging is required for replication
  log-bin=mysql-bin
  
  # required unique id between 1 and 2^32 - 1
  # defaults to 1 if master-host is not set
  # but will not function as a master if omitted
  server-id   = 1
  
  # Replication Slave (comment out master section to use this)
  #
  # To configure this host as a replication slave, you can choose between
  # two methods :
  #
  # 1) Use the CHANGE MASTER TO command (fully described in our manual) -
  #    the syntax is:
  #
  #    CHANGE MASTER TO MASTER_HOST=&lt;host&gt;, MASTER_PORT=&lt;port&gt;,
  #    MASTER_USER=&lt;user&gt;, MASTER_PASSWORD=&lt;password&gt; ;
  #
  #    where you replace &lt;host&gt;, &lt;user&gt;, &lt;password&gt; by quoted strings and
  #    &lt;port&gt; by the master's port number (3306 by default).
  #
  #    Example:
  #
  #    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
  #    MASTER_USER='joe', MASTER_PASSWORD='secret';
  #
  # OR
  #
  # 2) Set the variables below. However, in case you choose this method, then
  #    start replication for the first time (even unsuccessfully, for example
  #    if you mistyped the password in master-password and the slave fails to
  #    connect), the slave will create a master.info file, and any later
  #    change in this file to the variables' values below will be ignored and
  #    overridden by the content of the master.info file, unless you shutdown
  #    the slave server, delete master.info and restart the slaver server.
  #    For that reason, you may want to leave the lines below untouched
  #    (commented) and instead use CHANGE MASTER TO (see above)
  #
  # required unique id between 2 and 2^32 - 1
  # (and different from the master)
  # defaults to 2 if master-host is set
  # but will not function as a slave if omitted
  #server-id       = 2
  #
  # The replication master for this slave - required
  #master-host     =   &lt;hostname&gt;
  #
  # The username the slave will use for authentication when connecting
  # to the master - required
  #master-user     =   &lt;username&gt;
  #
  # The password the slave will authenticate with when connecting to
  # the master - required
  #master-password =   &lt;password&gt;
  #
  # The port the master is listening on.
  # optional - defaults to 3306
  #master-port     =  &lt;port&gt;
  #
  # binary logging - not required for slaves, but recommended
  #log-bin=mysql-bin
  #
  # binary logging format - mixed recommended
  #binlog_format=mixed
  
  # Uncomment the following if you are using InnoDB tables
  #innodb_data_home_dir = /var/lib/mysql
  #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
  #innodb_log_group_home_dir = /var/lib/mysql
  # You can set .._buffer_pool_size up to 50 - 80 %
  # of RAM but beware of setting memory usage too high
  #innodb_buffer_pool_size = 384M
  #innodb_additional_mem_pool_size = 20M
  # Set .._log_file_size to 25 % of buffer pool size
  #innodb_log_file_size = 100M
  #innodb_log_buffer_size = 8M
  #innodb_flush_log_at_trx_commit = 1
  #innodb_lock_wait_timeout = 50
  
  [mysqldump]
  quick
  max_allowed_packet = 100M
  
  [mysql]
  no-auto-rehash
  # Remove the next comment character if you are not familiar with SQL
  #safe-updates
  
  [myisamchk]
  key_buffer_size = 3G
  sort_buffer_size = 512M
  read_buffer = 1024M
  write_buffer = 8M
  
  [mysqlhotcopy]
  interactive-timeout
  </code></pre>
  
  <p>dhairya</p>