Re: Mysql mysql hangs up 1-2 times per day
Create Table: workprice :
Create Table: CREATE TABLE `workprice` (
`wp_id` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
`wp_prod_id` smallint(5) unsigned NOT NULL,
`wp_code_srch` varchar(32) NOT NULL,
`wp_code_printed` varchar(32) NOT NULL,
`wp_descr` varchar(64) NOT NULL,
`wp_qty` smallint(5) unsigned NOT NULL,
`wp_qty_type` enum('EQUAL','MORE') NOT NULL,
`wp_weight` float(7,3) DEFAULT NULL,
`wp_sps_id` int(10) unsigned NOT NULL,
`wp_sup_id` int(10) unsigned NOT NULL,
`wp_pt_id` int(10) unsigned NOT NULL,
`wp_price` float(9,2) unsigned NOT NULL,
`wp_cur_id` tinyint(3) unsigned NOT NULL,
`wp_qty_change_date` datetime NOT NULL,
`wp_price_change_date` datetime NOT NULL,
`wp_damaged_flag` enum('N','Y') NOT NULL,
`wp_used_flag` enum('N','Y') NOT NULL,
`wp_restored_flag` enum('N','Y') NOT NULL,
`wp_oldcode_flag` enum('N','Y') NOT NULL,
`wp_info_flag` enum('N','Y') NOT NULL,
`wp_img_flag` enum('N','Y') NOT NULL,
`wp_apl_flag` enum('N','Y') NOT NULL,
`wp_remote_id` int(10) unsigned DEFAULT NULL,
`wp_remote_str1` varchar(128) NOT NULL,
`wp_remote_str2` varchar(128) NOT NULL,
PRIMARY KEY (`wp_id`,`wp_sps_id`),
KEY `wp_code_srch` (`wp_code_srch`(12)),
KEY `wp_prod_id` (`wp_prod_id`),
KEY `wp_sup_pt` (`wp_sup_id`,`wp_pt_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12791967249 DEFAULT CHARSET=cp1251
/*!50100 PARTITION BY RANGE (wp_sps_id)
(PARTITION p0 VALUES LESS THAN (1) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (2) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (3) ENGINE = InnoDB,
...............................................
PARTITION p994 VALUES LESS THAN (995) ENGINE = InnoDB,
PARTITION p995 VALUES LESS THAN (996) ENGINE = InnoDB,
PARTITION p996 VALUES LESS THAN (997) ENGINE = InnoDB,
PARTITION p997 VALUES LESS THAN (998) ENGINE = InnoDB,
PARTITION p998 VALUES LESS THAN (999) ENGINE = InnoDB,
PARTITION p999 VALUES LESS THAN (1000) ENGINE = InnoDB) */
SHOW TABLE STATUS LIKE 'workprice':
Name: workprice
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 47434004
Avg_row_length: 112
Data_length: 5336842240
Max_data_length: 0
Index_length: 4412358656
Data_free: 1195376640
Auto_increment: 12791989781
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: cp1251_general_ci
Checksum: NULL
Create_options: partitioned
By partitioning we changed query :
"DELETE FROM table WHERE wp_sps_id = N"
to
"ALTER TABLE TRUNCATE PARTITION pN"
becase we have too much such queries per day(about 200-300) and time of DELETE is too much...
we have 32GB of ram at our server, here is my.cnf :
# MYISAM
key_buffer_size = 32M
myisam_recover = FORCE,BACKUP
# SAFETY
max_allowed_packet = 8M
max_connect_errors = 100000
# DATA STORAGE
datadir = /mnt/ssd/mysql
# BINARY LOGGING
#log_bin = /mnt/ssd/mysql/mysql-binlog
#expire_logs_days = 1
#sync_binlog = 1
tmp_table_size = 128M
max_heap_table_size = 128M
query_cache_type = 0
query_cache_size = 0
max_connections = 1000
thread_cache_size = 100
open_files_limit = 65535
table_definition_cache = 4096
table_open_cache = 4096
# INNODB
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_buffer_pool_size = 8G
innodb_log_buffer_size = 64M
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_file_format = Barracuda
innodb_print_all_deadlocks
any idea why queries from workprice slows and making MySQL dead in 3-5 min ?
any idea why there is semaphore waits ?