1

I have an InnoDB table running on MySQL 5.0.45 in CentOS. To make matters worse it is all running in a virtual machine. There are multiple tables that have the probability of exceeding 2 million records very easily. I have noticed that starting around the 900K to 1M record mark DB performance starts to nosedive.

I have all the power necessary to make any and all changes for the future to keep this thing standing up as well as possible under the circumstances. Should I be using MyIsam? There are only a few indices and I am most worried about getting good write performance. The program that writes data to the tables batches them in groups of about 250 requests per table and performs them on a per table basis to help things out.

I've included the create table statement for one of the larger tables, and yes, it is a very wide table- I understand. I have tried to make the columns as narrow as I can, while still being able to reliably accommodate the data coming in.

EDIT:

The program does employ transactions for rolling back the changes if something hits the fan during the run, but it's basically just pumping data into the database firehose style. One 8 hour run can easily put 400K lines in EACH table like this one. This table is one of 25 that are of similar size and all have the same indices. They are all joined for querying by LINE and RUN_ID. The read performance- I am not too particularly worried about. I am trying to make the write as fast as possible.

CREATE TABLE IF NOT EXISTS `TMD_INDATA_INVOICE` (
  `ID` int(11) NOT NULL auto_increment,
  `LINE` int(11) NOT NULL,
  `RUN_ID` int(11) NOT NULL,
  `INDATA_INVOICE_ALLOCATION_GROUP_NAME` varchar(128) default NULL,
  `INDATA_INVOICE_ALLOCATION_GROUP_OWNER` varchar(128) default NULL,
  `INDATA_INVOICE_ALLOCATION_NAME` varchar(128) default NULL,
  `INDATA_INVOICE_IS_AUDITED` varchar(5) default NULL,
  `INDATA_INVOICE_BASIS_PERCENT` varchar(32) default NULL,
  `INDATA_INVOICE_COUNTRY_OF_ORIGIN` varchar(64) default NULL,
  `INDATA_INVOICE_CUSTOMER_GROUP_NAME` varchar(128) default NULL,
  `INDATA_INVOICE_CUSTOMER_GROUP_OWNER` varchar(128) default NULL,
  `INDATA_INVOICE_CUSTOMER_NAME` varchar(128) default NULL,
  `INDATA_INVOICE_CUSTOMER_TAX_CATEGORY` varchar(128) default NULL,
  `INDATA_INVOICE_DELIVERY_TERMS` varchar(128) default NULL,
  `INDATA_INVOICE_DEPARTMENT_OF_CONSIGN` varchar(128) default NULL,
  `INDATA_INVOICE_DOCUMENT_TYPE` varchar(128) default NULL,
  `INDATA_INVOICE_END_USE` varchar(128) default NULL,
  `INDATA_INVOICE_END_USER_NAME` varchar(128) default NULL,
  `INDATA_INVOICE_FILTER_GROUP_NAME` varchar(128) default NULL,
  `INDATA_INVOICE_FILTER_GROUP_OWNER` varchar(128) default NULL,
  `INDATA_INVOICE_FISCAL_DATE` varchar(32) default NULL,
  `INDATA_INVOICE_INPUT_RECOVERY_TYPE` varchar(50) default NULL,
  `INDATA_INVOICE_INVOICE_NUMBER` varchar(128) default NULL,
  `INDATA_INVOICE_IS_AUDITING_MESSAGES` varchar(5) default NULL,
  `INDATA_INVOICE_IS_AUDIT_UPDATE` varchar(5) default NULL,
  `INDATA_INVOICE_IS_BUSINESS_SUPPLY` varchar(5) default NULL,
  `INDATA_INVOICE_IS_CREDIT` varchar(5) default NULL,
  `INDATA_INVOICE_IS_EXEMPT` varchar(5) default NULL,
  `INDATA_INVOICE_IS_NO_TAX` varchar(5) default NULL,
  `INDATA_INVOICE_IS_REPORTED` varchar(5) default NULL,
  `INDATA_INVOICE_IS_REVERSED` varchar(5) default NULL,
  `INDATA_INVOICE_IS_ROUNDING` varchar(5) default NULL,
  `INDATA_INVOICE_IS_SIMPLIFICATION` varchar(5) default NULL,
  `INDATA_INVOICE_MODE_OF_TRANSPORT` varchar(128) default NULL,
  `INDATA_INVOICE_MOVEMENT_DATE` varchar(32) default NULL,
  `INDATA_INVOICE_MOVEMENT_TYPE` varchar(128) default NULL,
  `INDATA_INVOICE_NATURE_OF_TRANSACTION_CODE` varchar(128) default NULL,
  `INDATA_INVOICE_OVERRIDE_AMOUNT` varchar(128) default NULL,
  `INDATA_INVOICE_OVERRIDE_RATE` varchar(32) default NULL,
  `INDATA_INVOICE_PORT_OF_ENTRY` varchar(128) default NULL,
  `INDATA_INVOICE_PORT_OF_LOADING` varchar(128) default NULL,
  `INDATA_INVOICE_PRODUCT_MAPPING_GROUP_NAME` varchar(128) default NULL,
  `INDATA_INVOICE_PRODUCT_MAPPING_GROUP_OWNER` varchar(128) default NULL,
  `INDATA_INVOICE_REGIME` varchar(128) default NULL,
  `INDATA_INVOICE_SUPPLY_EXEMPT_PERCENT` varchar(32) default NULL,
  `INDATA_INVOICE_SUPPLY_TYPE` varchar(128) default NULL,
  `INDATA_INVOICE_TITLE_TRANSFER_LOCATION` varchar(128) default NULL,
  `INDATA_INVOICE_VENDOR_NAME` varchar(128) default NULL,
  `INDATA_INVOICE_VENDOR_NUMBER` varchar(128) default NULL,
  `INDATA_INVOICE_VENDOR_TAX` varchar(128) default NULL,
  `INDATA_INVOICE_VERSION` varchar(5) default NULL,
  `INDATA_INVOICE_CALCULATION_DIRECTION` varchar(5) default NULL,
  `INDATA_INVOICE_CALLING_SYSTEM_NUMBER` varchar(128) default NULL,
  `INDATA_INVOICE_COMPANY_NAME` varchar(128) default NULL,
  `INDATA_INVOICE_COMPANY_ROLE` varchar(20) default NULL,
  `INDATA_INVOICE_CUSTOMER_NUMBER` varchar(128) default NULL,
  `INDATA_INVOICE_CURRENCY_CODE` varchar(32) default NULL,
  `INDATA_INVOICE_EXTERNAL_COMPANY_ID` varchar(128) default NULL,
  `INDATA_INVOICE_HOST_SYSTEM` varchar(128) default NULL,
  `INDATA_INVOICE_INVOICE_DATE` varchar(32) default NULL,
  `INDATA_INVOICE_POINT_OF_TITLE_TRANSFER` varchar(32) default NULL,
  `INDATA_INVOICE_REGISTRATIONS_BUYER_ROLE` varchar(32) default NULL,
  `INDATA_INVOICE_REGISTRATIONS_MIDDLEMAN_ROLE` varchar(32) default NULL,
  `INDATA_INVOICE_REGISTRATIONS_SELLER_ROLE` varchar(32) default NULL,
  `INDATA_INVOICE_VAT_GROUP_REGISTRATION` varchar(32) default NULL,
  `INDATA_INVOICE_TRANSACTION_TYPE` varchar(5) default NULL,
  `INDATA_INVOICE_UNIQUE_INVOICE_NUMBER` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE1` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE2` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE3` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE4` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE5` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE6` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE7` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE8` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE9` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE10` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE11` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE12` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE13` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE14` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE15` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE16` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE17` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE18` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE19` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE20` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE21` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE22` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE23` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE24` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE25` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE26` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE27` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE28` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE29` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE30` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE31` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE32` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE33` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE34` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE35` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE36` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE37` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE38` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE39` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE40` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE41` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE42` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE43` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE44` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE45` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE46` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE47` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE48` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE49` varchar(128) default NULL,
  `INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE50` varchar(128) default NULL,
  `INDATA_INVOICE_ORIGINAL_DOCUMENT_ID` varchar(128) default NULL,
  `INDATA_INVOICE_ORIGINAL_DOCUMENT_ITEM` varchar(128) default NULL,
  `INDATA_INVOICE_ORIGINAL_DOCUMENT_TYPE` varchar(128) default NULL,
  `INDATA_INVOICE_ORIGINAL_INVOICE_DATE` varchar(32) default NULL,
  `INDATA_INVOICE_ORIGINAL_INVOICE_NUMBER` varchar(128) default NULL,
  `INDATA_INVOICE_ORIGINAL_MOVEMENT_DATE` varchar(32) default NULL,
  PRIMARY KEY  (`ID`),
  KEY `RUN_ID` USING BTREE (`RUN_ID`),
  KEY `LINE` (`LINE`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4011 ;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `TMD_INDATA_INVOICE`
--
ALTER TABLE `TMD_INDATA_INVOICE`
  ADD CONSTRAINT `TMD_INDATA_INVOICE_ibfk_1` FOREIGN KEY (`RUN_ID`) REFERENCES `RunHistory` (`id`) ON DELETE CASCADE;

3 Answers 3

4

From what I can see there the table seems fairly self contained (i.e. you don't need to do any LOJ's to pull out normalised data) so MyISAM could certainly have a positive effect on the access speed.

Secondly, and most importantly, do you have the correct indexes for your queries? 2 million rows is a few, but it's not really that many. You need to carefully go through all your SELECT queries and make sure that you have an appropriate index for each one. This will consume a bit of disk space, but the tradeoff is incredibly fast query times.

Thirdly, and this is just a personal preference and not really much to do with your specific problem I don't think, but NDATA_INVOICE_USER_ELEMENT_ATTRIBUTE1 to NDATA_INVOICE_USER_ELEMENT_ATTRIBUTE50 - this could be designed a lot smarter by moving them into a table called DATA_INVOICE_USER_ELEMENT_ATTRIBUTES with a PK of INVID,ATTRIBUTEID and having them stored vertically in there, and immediately you've saved yourself 6.25kb of space per row.

3
  • I was going to make the same comment on the attributes, assuming of course all 50 aren't always used for each record.
    – Kyle Buser
    Sep 14, 2010 at 23:15
  • 1
    normalizing those attributes is not just a personal preference. IT WILL improve performance once they become normalized. Less table scanning (assuming you create some effective indexes) and smaller row size Sep 23, 2010 at 12:28
  • The reason the USER_ELEMENT_ATTRIBUTE# are denormalized and wide in the table, is because they all pretty much get used. If they were used sparingly, I would probably have put them in their own lookup table. The application that uses them uses them the same way I have them laid out as generic information dumps based on user defined settings, but they are almost always utilized to about 40-45 columns, so its not quite as retarded as I feel it looks.
    – manyxcxi
    Oct 28, 2010 at 16:22
2

make sure at least your indexes fit in memory. set innodb_buffer_pool_size large enough. if you need transactions or have lot of concurrent write access - stick to innodb.

if it's read only mostly and little updates - myisam perheps + tuned memory allocation for it.

try mysqltuner.pl for some generic advice and dive into mysqlperformanceblog for more in-depth details.

1
  • +1 on the buffer_pool. That thing needs to live in RAM if it can. Sep 22, 2010 at 6:25
0

2mln of rows isn't too much. Maybe is to much for your VM size?

You should not worry about the nubmer of rows as much as of size of your dataset. As dataset size increase, it will not fit in the buffer pool and will start reading data from disk.

Here is my answer about about improving MySQL/InnoDB insert/update performance

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .