Re: very slow inserts on InnoDB

Catalin Trifu Mon, 25 Jul 2005 11:08:52 -0700

        Hi,

    Thanks for the reply.
    The setup is the following:
      Dual Processor SuSE 9.0 (kernel 2.4.21 SMP), apache 2.0.54, php 5.0.4, 
mysql-4.1.12 (RPM), 2GB RAM, 80GB scsi RAID 5
      The database config file is this one:



[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 128M
max_allowed_packet = 16M
table_cache = 512
sort_buffer_size = 64M
net_buffer_length = 8K
myisam_sort_buffer_size = 64M
thread_cache = 32
query_cache_size = 128M
thread_concurrency = 2
set-variable = max_connections=1000
set-variable = interactive_timeout=120
set-variable = wait_timeout=120
set-variable = query_prealloc_size=2M
set-variable = transaction_prealloc_size=2M
read_buffer_size = 32M

log-long-format
log-slow-queries = /var/log/mysqld.slow.log

innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:1000M;ibdata2:500M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_arch_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 = 256M
innodb_additional_mem_pool_size = 64M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 32M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50


    The services run all on the same machine so network bottleneck does not 
come into
discussion. Also the query i gave as an example is taken from the slow query 
log.
    The system faces from time to time heavy loads of up to 1000 
requests/second.


Catalin



[EMAIL PROTECTED] wrote:

news <[EMAIL PROTECTED]> wrote on 07/25/2005 10:41:46 AM:
     Hi,


     I have the following table :

CREATE TABLE `raw_outgoing_sms` (
  `id` bigint(20) NOT NULL auto_increment,
  `id_gsm_operator` bigint(20) NOT NULL default '0',
  `id_shortcode` bigint(20) NOT NULL default '0',
  `msisdn` varchar(20) NOT NULL default '',
  `sender` varchar(20) NOT NULL default '',
`ts` timestamp NOT NULL default CURRENT_TIMESTAMP on updateCURRENT_TIMESTAMP,
  `text` text,
  `udh` text,
  `data` text NOT NULL,
  `dlr_status` varchar(20) default NULL,
  `dlr_url` text,
  PRIMARY KEY  (`id`),
  KEY `idx_outgoing_gsm_op` (`id_gsm_operator`),
  KEY `idx_outgoing_shortcode` (`id_shortcode`)
) ENGINE=InnoDB

     When i insert data into it it takes around 11 seconds. Why ?


Thanks,
Catalin
The time it takes to process any statement includes but is not limited to:

client encoding time (bundles your statement for transport)
network lag (the time it takes to get the bundled statement to the server)
statement parsing time (the processing required for the server to bothvalidate and understand the syntax of your request)
statement processing time:
wait for locks to clear (concurrent processes may be getting inyour way)
        set locks
        read/write data on disk (how slow are your disks?)
        updating indexes, if necessary (again, how slow are your disks?)
        clearing locks
network lag (the time it takes for the server to respond with thecompletion status of your statement)client decoding time (to unbundle the results from the networkingprotocols)
Any or all of those may be causing your delay. You will have to provide usmore details about your setup before we could make a more educated guess.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Re: very slow inserts on InnoDB

Reply via email to