Slow inserts after moving to new system
I recently moved to a new web server (running 5.5.32) on one of my production systems. The new server has more RAM, faster CPU, etc so we see queried results a lot quicker. However, with basically the default my.cnf config file on each system, we see simple inserts taking about 10x longer than on the old system. An example are simple inserts to the following InnoDB table: CREATE TABLE `test` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `num` int(10) unsigned DEFAULT NULL, `id_two` int(10) unsigned NOT NULL, `start` decimal(17,8) DEFAULT NULL, `stop` decimal(17,8) DEFAULT NULL, `length` int(10) unsigned DEFAULT NULL, UNIQUE KEY `ID` (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; One of our production systems creates ~3-8 thousand simple inserts to this table like: INSERT INTO `test` (`ID`, `num`, `id_two`, `start`, `stop`, `length`) VALUES (1,33602184,11172955,56617.0278,56617.05347222,2220); Sending all of these on the old system takes 5-20 seconds, but writes to the new server can take nearly 10 minutes. Both systems are running on nearly the default my.cnf files from the mysql installs. I’ve even altered the newer my.cnf file to match what was on our old server with no change in the results. I’ve also allocated more RAM to InnoDB with also no change in insert time. I’m not sure what to try looking at next. Does anybody have any ideas? Cheers, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Very slow inserts into InnoDB tables
hdparm -Tt /dev/sdX ? Ian Simpson wrote: That's pretty much what I've been doing to get that the drive is running at 100% bandwidth. What I'd like is something that just gives the bandwidth of the device in terms of Mb/s: you can probably work it out using that iostat command, seeing how much it wrote and what percentage of the bandwidth it's using, and then doing a calculation with those numbers to get the 100% value, but I don't know if that's valid, since there are generally a number of other operations going on at the same time. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Very slow inserts into InnoDB tables
Hi list, Have a bit of a mystery here that I hope somebody can help with. I've just got a new server that I'm using as a dedicated MySQL server. In terms of hardware it's pretty much identical, if not slightly superior to an existing server already in production use. It's having a real struggle processing INSERT statements to InnoDB tables; it's maxing out at around 100 inserts per second, even with very simple two column tables (inserts into MyISAM tables run fine). Meanwhile, the original server can happily process around 1000 inserts/sec into an identical table. The MySQL configuration of the two databases is identical, except for the tablespace file size (the new server has a larger tablespace defined), and the InnoDB logs (again, new server has larger logs). Can anybody suggest an area of investigation as to the cause? Thanks, -- Ian Simpson This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments.
Re: Very slow inserts into InnoDB tables
Please check if the my.cnf configurations to be the same. What are your configuration parameters in terms of innodh flush log trx commit , bin logging, sync binlog and innodb unsafe for binlog ? If the systems have raid, check if the BBWC is enabled on the new host and WB is enabled. On Fri, Jun 13, 2008 at 5:02 PM, Ian Simpson [EMAIL PROTECTED] wrote: Hi list, Have a bit of a mystery here that I hope somebody can help with. I've just got a new server that I'm using as a dedicated MySQL server. In terms of hardware it's pretty much identical, if not slightly superior to an existing server already in production use. It's having a real struggle processing INSERT statements to InnoDB tables; it's maxing out at around 100 inserts per second, even with very simple two column tables (inserts into MyISAM tables run fine). Meanwhile, the original server can happily process around 1000 inserts/sec into an identical table. The MySQL configuration of the two databases is identical, except for the tablespace file size (the new server has a larger tablespace defined), and the InnoDB logs (again, new server has larger logs). Can anybody suggest an area of investigation as to the cause? Thanks, -- Ian Simpson This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments. -- Thanks Alex http://alexlurthu.wordpress.com
Re: Very slow inserts into InnoDB tables
Hi Alex, Configurations are identical, other than the differences I initially mentioned. I've diffed both the configuration files and the output of SHOW VARIABLES on both servers. I've contacted my hosting provider to ask about the RAID settings. Variable_name: innodb_flush_log_at_trx_commit Value: 1 Variable_name: sync_binlog Value: 0 Variable_name: innodb_locks_unsafe_for_binlog Value: OFF Thanks -- Ian Simpson On Fri, 2008-06-13 at 17:43 +0530, Alex Arul Lurthu wrote: Please check if the my.cnf configurations to be the same. What are your configuration parameters in terms of innodh flush log trx commit , bin logging, sync binlog and innodb unsafe for binlog ? If the systems have raid, check if the BBWC is enabled on the new host and WB is enabled. On Fri, Jun 13, 2008 at 5:02 PM, Ian Simpson [EMAIL PROTECTED] wrote: Hi list, Have a bit of a mystery here that I hope somebody can help with. I've just got a new server that I'm using as a dedicated MySQL server. In terms of hardware it's pretty much identical, if not slightly superior to an existing server already in production use. It's having a real struggle processing INSERT statements to InnoDB tables; it's maxing out at around 100 inserts per second, even with very simple two column tables (inserts into MyISAM tables run fine). Meanwhile, the original server can happily process around 1000 inserts/sec into an identical table. The MySQL configuration of the two databases is identical, except for the tablespace file size (the new server has a larger tablespace defined), and the InnoDB logs (again, new server has larger logs). Can anybody suggest an area of investigation as to the cause? Thanks, -- Ian Simpson This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments. -- Thanks Alex http://alexlurthu.wordpress.com This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments.
Re: Very slow inserts into InnoDB tables
check for iostat to see if the disk is heavly used. On 6/13/08, Ian Simpson [EMAIL PROTECTED] wrote: Hi Alex, Configurations are identical, other than the differences I initially mentioned. I've diffed both the configuration files and the output of SHOW VARIABLES on both servers. I've contacted my hosting provider to ask about the RAID settings. Variable_name: innodb_flush_log_at_trx_commit Value: 1 Variable_name: sync_binlog Value: 0 Variable_name: innodb_locks_unsafe_for_binlog Value: OFF Thanks -- Ian Simpson On Fri, 2008-06-13 at 17:43 +0530, Alex Arul Lurthu wrote: Please check if the my.cnf configurations to be the same. What are your configuration parameters in terms of innodh flush log trx commit , bin logging, sync binlog and innodb unsafe for binlog ? If the systems have raid, check if the BBWC is enabled on the new host and WB is enabled. On Fri, Jun 13, 2008 at 5:02 PM, Ian Simpson [EMAIL PROTECTED] wrote: Hi list, Have a bit of a mystery here that I hope somebody can help with. I've just got a new server that I'm using as a dedicated MySQL server. In terms of hardware it's pretty much identical, if not slightly superior to an existing server already in production use. It's having a real struggle processing INSERT statements to InnoDB tables; it's maxing out at around 100 inserts per second, even with very simple two column tables (inserts into MyISAM tables run fine). Meanwhile, the original server can happily process around 1000 inserts/sec into an identical table. The MySQL configuration of the two databases is identical, except for the tablespace file size (the new server has a larger tablespace defined), and the InnoDB logs (again, new server has larger logs). Can anybody suggest an area of investigation as to the cause? Thanks, -- Ian Simpson This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments. -- Thanks Alex http://alexlurthu.wordpress.com This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments.
Re: Very slow inserts into InnoDB tables
also how often do you issue a commit. batching the inserts inside a transaction might help. On Fri, Jun 13, 2008 at 6:53 PM, Ananda Kumar [EMAIL PROTECTED] wrote: check for iostat to see if the disk is heavly used. On 6/13/08, Ian Simpson [EMAIL PROTECTED] wrote: Hi Alex, Configurations are identical, other than the differences I initially mentioned. I've diffed both the configuration files and the output of SHOW VARIABLES on both servers. I've contacted my hosting provider to ask about the RAID settings. Variable_name: innodb_flush_log_at_trx_commit Value: 1 Variable_name: sync_binlog Value: 0 Variable_name: innodb_locks_unsafe_for_binlog Value: OFF Thanks -- Ian Simpson On Fri, 2008-06-13 at 17:43 +0530, Alex Arul Lurthu wrote: Please check if the my.cnf configurations to be the same. What are your configuration parameters in terms of innodh flush log trx commit , bin logging, sync binlog and innodb unsafe for binlog ? If the systems have raid, check if the BBWC is enabled on the new host and WB is enabled. On Fri, Jun 13, 2008 at 5:02 PM, Ian Simpson [EMAIL PROTECTED] wrote: Hi list, Have a bit of a mystery here that I hope somebody can help with. I've just got a new server that I'm using as a dedicated MySQL server. In terms of hardware it's pretty much identical, if not slightly superior to an existing server already in production use. It's having a real struggle processing INSERT statements to InnoDB tables; it's maxing out at around 100 inserts per second, even with very simple two column tables (inserts into MyISAM tables run fine). Meanwhile, the original server can happily process around 1000 inserts/sec into an identical table. The MySQL configuration of the two databases is identical, except for the tablespace file size (the new server has a larger tablespace defined), and the InnoDB logs (again, new server has larger logs). Can anybody suggest an area of investigation as to the cause? Thanks, -- Ian Simpson This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments. -- Thanks Alex http://alexlurthu.wordpress.com This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments. -- Thanks Alex http://alexlurthu.wordpress.com
Re: Very slow inserts into InnoDB tables
Hi guys, thanks for pitching in. The inserts are from replication; we're not using transactions on the master (yet), and I don't think there's a way of telling MySQL to batch incoming replication statements if they're not already in a transaction. Disk usage: the older server (the one that's running fine) is running more transactions per second, but has lower blocks written and read per second than the new server: The working server (which in addition to replicating is also handling a bunch of read queries) Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 88.47 782.20 998.77 9046888130 11551757459 The new server, which is just trying to handle replication Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 77.83 1367.55 2914.72 358474084 764029986 Thanks, -- Ian Simpson On Fri, 2008-06-13 at 19:15 +0530, Alex Arul Lurthu wrote: also how often do you issue a commit. batching the inserts inside a transaction might help. On Fri, Jun 13, 2008 at 6:53 PM, Ananda Kumar [EMAIL PROTECTED] wrote: check for iostat to see if the disk is heavly used. On 6/13/08, Ian Simpson [EMAIL PROTECTED] wrote: Hi Alex, Configurations are identical, other than the differences I initially mentioned. I've diffed both the configuration files and the output of SHOW VARIABLES on both servers. I've contacted my hosting provider to ask about the RAID settings. Variable_name: innodb_flush_log_at_trx_commit Value: 1 Variable_name: sync_binlog Value: 0 Variable_name: innodb_locks_unsafe_for_binlog Value: OFF Thanks -- Ian Simpson On Fri, 2008-06-13 at 17:43 +0530, Alex Arul Lurthu wrote: Please check if the my.cnf configurations to be the same. What are your configuration parameters in terms of innodh flush log trx commit , bin logging, sync binlog and innodb unsafe for binlog ? If the systems have raid, check if the BBWC is enabled on the new host and WB is enabled. On Fri, Jun 13, 2008 at 5:02 PM, Ian Simpson [EMAIL PROTECTED] wrote: Hi list, Have a bit of a mystery here that I hope somebody can help with. I've just got a new server that I'm using as a dedicated MySQL server. In terms of hardware it's pretty much identical, if not slightly superior to an existing server already in production use. It's having a real struggle processing INSERT statements to InnoDB tables; it's maxing out at around 100 inserts per second, even with very simple two column tables (inserts into MyISAM tables run fine). Meanwhile, the original server can happily process around 1000 inserts/sec into an identical table. The MySQL configuration of the two databases is identical, except for the tablespace file size (the new server has a larger tablespace defined), and the InnoDB logs (again, new server has larger logs). Can anybody suggest an area of investigation as to the cause? Thanks, -- Ian Simpson This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify
Re: Very slow inserts into InnoDB tables
replication based inserts are serial whereas most of the time the inserts on masters are concurrent. this leads to the slaves falling behind. to tackle this we have used the following strategies : 1. Use raid 0 on the slaves (master users raid 10) so as to speed up writes. 2. pre fetch and cache the data that needs to be modified by the slave sql thread. 3. set innodb flush trx log commit to 2 or even 0. 4. Out of desperation sometimes disable innodb double write and also xa support. On Fri, Jun 13, 2008 at 7:33 PM, Ian Simpson [EMAIL PROTECTED] wrote: Hi guys, thanks for pitching in. The inserts are from replication; we're not using transactions on the master (yet), and I don't think there's a way of telling MySQL to batch incoming replication statements if they're not already in a transaction. Disk usage: the older server (the one that's running fine) is running more transactions per second, but has lower blocks written and read per second than the new server: The working server (which in addition to replicating is also handling a bunch of read queries) Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 88.47 782.20 998.77 9046888130 11551757459 The new server, which is just trying to handle replication Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 77.83 1367.55 2914.72 358474084 764029986 Thanks, -- Ian Simpson On Fri, 2008-06-13 at 19:15 +0530, Alex Arul Lurthu wrote: also how often do you issue a commit. batching the inserts inside a transaction might help. On Fri, Jun 13, 2008 at 6:53 PM, Ananda Kumar [EMAIL PROTECTED] wrote: check for iostat to see if the disk is heavly used. On 6/13/08, Ian Simpson [EMAIL PROTECTED] wrote: Hi Alex, Configurations are identical, other than the differences I initially mentioned. I've diffed both the configuration files and the output of SHOW VARIABLES on both servers. I've contacted my hosting provider to ask about the RAID settings. Variable_name: innodb_flush_log_at_trx_commit Value: 1 Variable_name: sync_binlog Value: 0 Variable_name: innodb_locks_unsafe_for_binlog Value: OFF Thanks -- Ian Simpson On Fri, 2008-06-13 at 17:43 +0530, Alex Arul Lurthu wrote: Please check if the my.cnf configurations to be the same. What are your configuration parameters in terms of innodh flush log trx commit , bin logging, sync binlog and innodb unsafe for binlog ? If the systems have raid, check if the BBWC is enabled on the new host and WB is enabled. On Fri, Jun 13, 2008 at 5:02 PM, Ian Simpson [EMAIL PROTECTED] wrote: Hi list, Have a bit of a mystery here that I hope somebody can help with. I've just got a new server that I'm using as a dedicated MySQL server. In terms of hardware it's pretty much identical, if not slightly superior to an existing server already in production use. It's having a real struggle processing INSERT statements to InnoDB tables; it's maxing out at around 100 inserts per second, even with very simple two column tables (inserts into MyISAM tables run fine). Meanwhile, the original server can happily process around 1000 inserts/sec into an identical table. The MySQL configuration of the two databases is identical, except for the tablespace file size (the new server has a larger tablespace defined), and the InnoDB logs (again, new server has larger logs). Can anybody suggest an area of investigation as to the cause? Thanks, -- Ian Simpson This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments. -- Thanks Alex http://alexlurthu.wordpress.com This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements
RE: Very slow inserts into InnoDB tables
Disk usage: the older server (the one that's running fine) is running more transactions per second, but has lower blocks written and read per second than the new server: [JS] That, to me, suggests that the difference might be in the way the systems themselves are configured. Unfortunately, I don't know how Linux handles file system buffering. The working server (which in addition to replicating is also handling a bunch of read queries) Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 88.47 782.20 998.77 9046888130 11551757459 The new server, which is just trying to handle replication Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 77.83 1367.55 2914.72 358474084 764029986 Thanks, ? -- Ian Simpson On Fri, 2008-06-13 at 19:15 +0530, Alex Arul Lurthu wrote: also how often do you issue a commit. batching the inserts inside a transaction might help. On Fri, Jun 13, 2008 at 6:53 PM, Ananda Kumar [EMAIL PROTECTED] wrote: check for iostat to see if the disk is heavly used. On 6/13/08, Ian Simpson [EMAIL PROTECTED] wrote: Hi Alex, Configurations are identical, other than the differences I initially mentioned. I've diffed both the configuration files and the output of SHOW VARIABLES on both servers. I've contacted my hosting provider to ask about the RAID settings. Variable_name: innodb_flush_log_at_trx_commit Value: 1 Variable_name: sync_binlog Value: 0 Variable_name: innodb_locks_unsafe_for_binlog Value: OFF Thanks -- Ian Simpson On Fri, 2008-06-13 at 17:43 +0530, Alex Arul Lurthu wrote: Please check if the my.cnf configurations to be the same. What are your configuration parameters in terms of innodh flush log trx commit , bin logging, sync binlog and innodb unsafe for binlog ? If the systems have raid, check if the BBWC is enabled on the new host and WB is enabled. On Fri, Jun 13, 2008 at 5:02 PM, Ian Simpson [EMAIL PROTECTED] wrote: Hi list, Have a bit of a mystery here that I hope somebody can help with. I've just got a new server that I'm using as a dedicated MySQL server. In terms of hardware it's pretty much identical, if not slightly superior to an existing server already in production use. It's having a real struggle processing INSERT statements to InnoDB tables; it's maxing out at around 100 inserts per second, even with very simple two column tables (inserts into MyISAM tables run fine). Meanwhile, the original server can happily process around 1000 inserts/sec into an identical table. The MySQL configuration of the two databases is identical, except for the tablespace file size (the new server has a larger tablespace defined), and the InnoDB logs (again, new server has larger logs). Can anybody suggest an area of investigation as to the cause? Thanks, -- Ian Simpson This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying
RE: Very slow inserts into InnoDB tables
Hi Guys, Having delved a little more into the capabilities of iostat, I've discovered that the drive bandwidth seems to be maxed out while MySQL is running, which I'd peg as the primary candidate for the problem. Looks like I'll be having more words with my hosting company about this... Thanks for all your help -- Ian Simpson On Fri, 2008-06-13 at 10:40 -0400, Jerry Schwartz wrote: Disk usage: the older server (the one that's running fine) is running more transactions per second, but has lower blocks written and read per second than the new server: [JS] That, to me, suggests that the difference might be in the way the systems themselves are configured. Unfortunately, I don't know how Linux handles file system buffering. The working server (which in addition to replicating is also handling a bunch of read queries) Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 88.47 782.20 998.77 9046888130 11551757459 The new server, which is just trying to handle replication Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 77.83 1367.55 2914.72 358474084 764029986 Thanks, ? -- Ian Simpson On Fri, 2008-06-13 at 19:15 +0530, Alex Arul Lurthu wrote: also how often do you issue a commit. batching the inserts inside a transaction might help. On Fri, Jun 13, 2008 at 6:53 PM, Ananda Kumar [EMAIL PROTECTED] wrote: check for iostat to see if the disk is heavly used. On 6/13/08, Ian Simpson [EMAIL PROTECTED] wrote: Hi Alex, Configurations are identical, other than the differences I initially mentioned. I've diffed both the configuration files and the output of SHOW VARIABLES on both servers. I've contacted my hosting provider to ask about the RAID settings. Variable_name: innodb_flush_log_at_trx_commit Value: 1 Variable_name: sync_binlog Value: 0 Variable_name: innodb_locks_unsafe_for_binlog Value: OFF Thanks -- Ian Simpson On Fri, 2008-06-13 at 17:43 +0530, Alex Arul Lurthu wrote: Please check if the my.cnf configurations to be the same. What are your configuration parameters in terms of innodh flush log trx commit , bin logging, sync binlog and innodb unsafe for binlog ? If the systems have raid, check if the BBWC is enabled on the new host and WB is enabled. On Fri, Jun 13, 2008 at 5:02 PM, Ian Simpson [EMAIL PROTECTED] wrote: Hi list, Have a bit of a mystery here that I hope somebody can help with. I've just got a new server that I'm using as a dedicated MySQL server. In terms of hardware it's pretty much identical, if not slightly superior to an existing server already in production use. It's having a real struggle processing INSERT statements to InnoDB tables; it's maxing out at around 100 inserts per second, even with very simple two column tables (inserts into MyISAM tables run fine). Meanwhile, the original server can happily process around 1000 inserts/sec into an identical table. The MySQL configuration of the two databases is identical, except for the tablespace file size (the new server has a larger tablespace defined), and the InnoDB logs (again, new server has larger logs). Can anybody suggest an area of investigation as to the cause? Thanks, -- Ian Simpson
RE: Very slow inserts into InnoDB tables
Having delved a little more into the capabilities of iostat, I've discovered that the drive bandwidth seems to be maxed out while MySQL is running, which I'd peg as the primary candidate for the problem. [JS] That suggests even more strongly that there is a difference in the kernel configuration. More physical I/O would drive the traffic up, by definition. Either MySQL is causing this, or the system file system is causing it. Looks like I'll be having more words with my hosting company about this... Thanks for all your help ? -- Ian Simpson On Fri, 2008-06-13 at 10:40 -0400, Jerry Schwartz wrote: Disk usage: the older server (the one that's running fine) is running more transactions per second, but has lower blocks written and read per second than the new server: [JS] That, to me, suggests that the difference might be in the way the systems themselves are configured. Unfortunately, I don't know how Linux handles file system buffering. The working server (which in addition to replicating is also handling a bunch of read queries) Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 88.47 782.20 998.77 9046888130 11551757459 The new server, which is just trying to handle replication Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 77.83 1367.55 2914.72 358474084 764029986 Thanks, ? -- Ian Simpson On Fri, 2008-06-13 at 19:15 +0530, Alex Arul Lurthu wrote: also how often do you issue a commit. batching the inserts inside a transaction might help. On Fri, Jun 13, 2008 at 6:53 PM, Ananda Kumar [EMAIL PROTECTED] wrote: check for iostat to see if the disk is heavly used. On 6/13/08, Ian Simpson [EMAIL PROTECTED] wrote: Hi Alex, Configurations are identical, other than the differences I initially mentioned. I've diffed both the configuration files and the output of SHOW VARIABLES on both servers. I've contacted my hosting provider to ask about the RAID settings. Variable_name: innodb_flush_log_at_trx_commit Value: 1 Variable_name: sync_binlog Value: 0 Variable_name: innodb_locks_unsafe_for_binlog Value: OFF Thanks -- Ian Simpson On Fri, 2008-06-13 at 17:43 +0530, Alex Arul Lurthu wrote: Please check if the my.cnf configurations to be the same. What are your configuration parameters in terms of innodh flush log trx commit , bin logging, sync binlog and innodb unsafe for binlog ? If the systems have raid, check if the BBWC is enabled on the new host and WB is enabled. On Fri, Jun 13, 2008 at 5:02 PM, Ian Simpson [EMAIL PROTECTED] wrote: Hi list, Have a bit of a mystery here that I hope somebody can help with. I've just got a new server that I'm using as a dedicated MySQL server. In terms of hardware it's pretty much identical, if not slightly superior to an existing server already in production use. It's having a real struggle processing INSERT statements to InnoDB tables; it's maxing out at around 100 inserts per second, even with very simple two column tables (inserts into MyISAM tables run fine). Meanwhile, the original server can happily process around 1000 inserts/sec into an identical table. The MySQL configuration of the two databases is identical, except for the tablespace file size (the new server has a larger tablespace defined), and the InnoDB logs (again, new server has larger logs).
RE: Very slow inserts into InnoDB tables
Hi Jerry, It could be a kernel issue; however, currently I'm suspecting that the drive in the new server simply doesn't have the same bandwidth capability. The iostat results I'm getting (although I'm not an expert in reading them, having only learned of it about 3 hours ago) suggest that the older server is handling roughly the same data quantities, but just using a much lower percentage of the drive's bandwidth. I can't seem to find a tool which reports on exactly how much write bandwidth a drive has; everything seems to focus on reading speed. Thanks, -- Ian Simpson On Fri, 2008-06-13 at 11:18 -0400, Jerry Schwartz wrote: Having delved a little more into the capabilities of iostat, I've discovered that the drive bandwidth seems to be maxed out while MySQL is running, which I'd peg as the primary candidate for the problem. [JS] That suggests even more strongly that there is a difference in the kernel configuration. More physical I/O would drive the traffic up, by definition. Either MySQL is causing this, or the system file system is causing it. Looks like I'll be having more words with my hosting company about this... Thanks for all your help ? -- Ian Simpson On Fri, 2008-06-13 at 10:40 -0400, Jerry Schwartz wrote: Disk usage: the older server (the one that's running fine) is running more transactions per second, but has lower blocks written and read per second than the new server: [JS] That, to me, suggests that the difference might be in the way the systems themselves are configured. Unfortunately, I don't know how Linux handles file system buffering. The working server (which in addition to replicating is also handling a bunch of read queries) Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 88.47 782.20 998.77 9046888130 11551757459 The new server, which is just trying to handle replication Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 77.83 1367.55 2914.72 358474084 764029986 Thanks, ? -- Ian Simpson On Fri, 2008-06-13 at 19:15 +0530, Alex Arul Lurthu wrote: also how often do you issue a commit. batching the inserts inside a transaction might help. On Fri, Jun 13, 2008 at 6:53 PM, Ananda Kumar [EMAIL PROTECTED] wrote: check for iostat to see if the disk is heavly used. On 6/13/08, Ian Simpson [EMAIL PROTECTED] wrote: Hi Alex, Configurations are identical, other than the differences I initially mentioned. I've diffed both the configuration files and the output of SHOW VARIABLES on both servers. I've contacted my hosting provider to ask about the RAID settings. Variable_name: innodb_flush_log_at_trx_commit Value: 1 Variable_name: sync_binlog Value: 0 Variable_name: innodb_locks_unsafe_for_binlog Value: OFF Thanks -- Ian Simpson On Fri, 2008-06-13 at 17:43 +0530, Alex Arul Lurthu wrote: Please check if the my.cnf configurations to be the same. What are your configuration parameters in terms of innodh flush log trx commit , bin logging, sync binlog and innodb unsafe for binlog ? If the systems have raid, check if the BBWC is enabled on the new host and WB is enabled. On Fri, Jun 13, 2008 at 5:02 PM, Ian Simpson [EMAIL PROTECTED] wrote: Hi list, Have a bit of a mystery here that I hope somebody can help with. I've just got a new server that I'm using as a dedicated MySQL server. In terms of hardware it's pretty much identical, if not slightly superior to an existing server already in production use. It's having a real struggle processing INSERT statements to InnoDB tables; it's maxing out at around 100 inserts per second, even with very
RE: Very slow inserts into InnoDB tables
On Fri, June 13, 2008 08:26, Ian Simpson wrote: Hi Jerry, It could be a kernel issue; however, currently I'm suspecting that the drive in the new server simply doesn't have the same bandwidth capability. The iostat results I'm getting (although I'm not an expert in reading them, having only learned of it about 3 hours ago) suggest that the older server is handling roughly the same data quantities, but just using a much lower percentage of the drive's bandwidth. I can't seem to find a tool which reports on exactly how much write bandwidth a drive has; everything seems to focus on reading speed. Thanks,  -- Ian Simpson Try something like: iostat -xk /dev/sda /dev/sdb /dev/sdc 10 where the /dev/... are the drives you want to examine and '10' is the redisplay rate. last column is %util. Hope this helps. On Fri, 2008-06-13 at 11:18 -0400, Jerry Schwartz wrote: Having delved a little more into the capabilities of iostat, I've discovered that the drive bandwidth seems to be maxed out while MySQL is running, which I'd peg as the primary candidate for the problem. [JS] That suggests even more strongly that there is a difference in the kernel configuration. More physical I/O would drive the traffic up, by definition. Either MySQL is causing this, or the system file system is causing it. Looks like I'll be having more words with my hosting company about this... Thanks for all your help ? -- Ian Simpson On Fri, 2008-06-13 at 10:40 -0400, Jerry Schwartz wrote: Disk usage: the older server (the one that's running fine) is running more transactions per second, but has lower blocks written and read per second than the new server: [JS] That, to me, suggests that the difference might be in the way the systems themselves are configured. Unfortunately, I don't know how Linux handles file system buffering. The working server (which in addition to replicating is also handling a bunch of read queries) Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 88.47 782.20 998.77 9046888130 11551757459 The new server, which is just trying to handle replication Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 77.83 1367.55 2914.72 358474084 764029986 Thanks, ? -- Ian Simpson On Fri, 2008-06-13 at 19:15 +0530, Alex Arul Lurthu wrote: also how often do you issue a commit. batching the inserts inside a transaction might help. On Fri, Jun 13, 2008 at 6:53 PM, Ananda Kumar [EMAIL PROTECTED] wrote: check for iostat to see if the disk is heavly used. On 6/13/08, Ian Simpson [EMAIL PROTECTED] wrote: Hi Alex, Configurations are identical, other than the differences I initially mentioned. I've diffed both the configuration files and the output of SHOW VARIABLES on both servers. I've contacted my hosting provider to ask about the RAID settings. Variable_name: innodb_flush_log_at_trx_commit Value: 1 Variable_name: sync_binlog Value: 0 Variable_name: innodb_locks_unsafe_for_binlog Value: OFF Thanks -- Ian Simpson On Fri, 2008-06-13 at 17:43 +0530, Alex Arul Lurthu wrote: Please check if the my.cnf configurations to be the same. What are your configuration parameters in terms of innodh flush log trx commit , bin logging, sync binlog and innodb unsafe for binlog ? If the systems have raid, check if the BBWC is enabled on the new host and WB is enabled. On Fri, Jun 13, 2008 at 5:02 PM, Ian Simpson [EMAIL PROTECTED] wrote: Hi list, Have a bit of a mystery here that I hope somebody can help with. I've just got a new server that I'm using as a dedicated MySQL server. In terms of hardware it's pretty much identical, if not slightly superior to an existing server already in production use. It's having a real
RE: Very slow inserts into InnoDB tables
That's pretty much what I've been doing to get that the drive is running at 100% bandwidth. What I'd like is something that just gives the bandwidth of the device in terms of Mb/s: you can probably work it out using that iostat command, seeing how much it wrote and what percentage of the bandwidth it's using, and then doing a calculation with those numbers to get the 100% value, but I don't know if that's valid, since there are generally a number of other operations going on at the same time. Thanks -- Ian Simpson On Fri, 2008-06-13 at 08:48 -0700, Wm Mussatto wrote: On Fri, June 13, 2008 08:26, Ian Simpson wrote: Hi Jerry, It could be a kernel issue; however, currently I'm suspecting that the drive in the new server simply doesn't have the same bandwidth capability. The iostat results I'm getting (although I'm not an expert in reading them, having only learned of it about 3 hours ago) suggest that the older server is handling roughly the same data quantities, but just using a much lower percentage of the drive's bandwidth. I can't seem to find a tool which reports on exactly how much write bandwidth a drive has; everything seems to focus on reading speed. Thanks,  -- Ian Simpson Try something like: iostat -xk /dev/sda /dev/sdb /dev/sdc 10 where the /dev/... are the drives you want to examine and '10' is the redisplay rate. last column is %util. Hope this helps. On Fri, 2008-06-13 at 11:18 -0400, Jerry Schwartz wrote: Having delved a little more into the capabilities of iostat, I've discovered that the drive bandwidth seems to be maxed out while MySQL is running, which I'd peg as the primary candidate for the problem. [JS] That suggests even more strongly that there is a difference in the kernel configuration. More physical I/O would drive the traffic up, by definition. Either MySQL is causing this, or the system file system is causing it. Looks like I'll be having more words with my hosting company about this... Thanks for all your help ? -- Ian Simpson On Fri, 2008-06-13 at 10:40 -0400, Jerry Schwartz wrote: Disk usage: the older server (the one that's running fine) is running more transactions per second, but has lower blocks written and read per second than the new server: [JS] That, to me, suggests that the difference might be in the way the systems themselves are configured. Unfortunately, I don't know how Linux handles file system buffering. The working server (which in addition to replicating is also handling a bunch of read queries) Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 88.47 782.20 998.77 9046888130 11551757459 The new server, which is just trying to handle replication Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 77.83 1367.55 2914.72 358474084 764029986 Thanks, ? -- Ian Simpson On Fri, 2008-06-13 at 19:15 +0530, Alex Arul Lurthu wrote: also how often do you issue a commit. batching the inserts inside a transaction might help. On Fri, Jun 13, 2008 at 6:53 PM, Ananda Kumar [EMAIL PROTECTED] wrote: check for iostat to see if the disk is heavly used. On 6/13/08, Ian Simpson [EMAIL PROTECTED] wrote: Hi Alex, Configurations are identical, other than the differences I initially mentioned. I've diffed both the configuration files and the output of SHOW VARIABLES on both servers. I've contacted my hosting provider to ask about the RAID settings. Variable_name: innodb_flush_log_at_trx_commit Value: 1 Variable_name: sync_binlog Value: 0 Variable_name: innodb_locks_unsafe_for_binlog Value: OFF Thanks -- Ian Simpson On Fri, 2008-06-13 at 17:43 +0530, Alex Arul Lurthu wrote: Please check if the my.cnf configurations to be the same. What are your configuration parameters in terms of innodh flush log trx commit , bin logging, sync binlog and innodb unsafe for binlog ? If the systems have raid, check if the BBWC is enabled on the new host and WB is enabled. On Fri, Jun 13, 2008 at 5:02 PM, Ian Simpson [EMAIL
Re: very slow inserts on InnoDB [InnoDB Performance Tuning]
Hi Catalin, Here are some InnoDB performance tuning tips that may boost your insert speed: Catalin Trifu wrote: ... innodb_buffer_pool_size = 256M Higher is better, in fact pushing this up to 60%-80% on a dedicated database would be good. If there are other things running like a web server, then you will have to take its memory requirements into account, but 256M could likely be bigger. Maybe this could be 512M ? Then data sets up to this size will be as fast as possible. innodb_additional_mem_pool_size = 64M Rarely does this need to be set over 8M. # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 32M Try setting this to 1/2 your buffer pool size, otherwise you might get too much checkpointing during lots of inserts. innodb_log_buffer_size = 8M Looks good. innodb_flush_log_at_trx_commit = 1 Try setting to trx_commit = 2 for faster insert performance, however you then lose ACID transactions, where if you have a system failure you could lose around 1 second worth committed data. These suggestions will not necessarily fix your problem. If you continue to have issues and they go unresolved on this list, you might consider getting help via our commercial offerings: http://www.mysql.com/network/ - OR - http://www.mysql.com/consulting/packaged/performance.html http://www.mysql.com/consulting/packaged/rapidresponse.html Regards, Josh -- Josh Chamas Director, Professional Services MySQL Inc., www.mysql.com Get More with MySQL! www.mysql.com/consulting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
very slow inserts on InnoDB
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 update CURRENT_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 -- 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
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 update CURRENT_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 both validate and understand the syntax of your request) statement processing time: wait for locks to clear (concurrent processes may be getting in your 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 the completion status of your statement) client decoding time (to unbundle the results from the networking protocols) Any or all of those may be causing your delay. You will have to provide us more details about your setup before we could make a more educated guess. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: very slow inserts on InnoDB
Catalin, I was able to create the table with the CREATE statement you pasted, and insert a row with some simple data. mysql insert into raw_outgoing_sms (id_gsm_operator,id_shortcode,msisdn,sender,text,dlr_url) values (10,20,'19284720','deva','hello world','yahoo.com'); Query OK, 1 row affected (0.02 sec) As SGreen pointed out, there are lots of possible contributing factors. Leaving out network, application, or disk latency, I wonder if the slowness is caused by the InnoDB configuration on your server... Could you send a copy of your server's my.cnf file, and some information about the servers themselves (CPU, RAM, etc)? Seeing the configuration parameters would help us to understand where your problem may be coming from. Regards, Devananda vdv Catalin Trifu wrote: 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 update CURRENT_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 -- 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
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 update CURRENT_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 both validate and understand the syntax of your request) statement processing time: wait for locks to clear (concurrent processes may be getting in your 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 the completion status of your statement) client decoding time (to unbundle the results from the networking protocols) Any or all of those may be causing your delay. You will have to provide us more 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]
Slow Inserts
I've been having trouble with some large tables getting what seems to be corrupted. Here's the situation: I have several tables that have 3 million to as much as 7 million records. I have a process that I run against those tables that pulls out a record based on specific criteria (select id,name from table where name !='' and status=0) does something in Perl and then changes the record it just pulled to a status of 9. So basically - every time I run this process, every records is scanned and many (90%) are changed to the status of 9. Well - on a newly imported list, it screams through that and I can get upwards of 3 million per second. Each time I run the process, it gets slower, however. I've used myisamchk and optimize table and neither seem to have any affect on the performance. The only thing that seems to work is mysqldumping the whole table, dropping the table and reimporting the table. I've read up on the site and have found a lot about what could be causing this and have tried many things. Now that I've found what's wrong and how to fix it, I'm happy, but I'd rather not have to dump and reimport. I'm running MySQL 4.0.11 on RHL 7.2. I'm using the huge my.cnf file with some minor tweaks. The tables I'm speaking of have no indexes in it. They had them, but I dumped them and that gave me a huge insert performance gain, but I'm still seeing slowdowns the more I run the process on the file. Thanks in advance, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow Inserts
I've tried it both as fixed (char) and variable (varchar). Interestingly when I set is as char when building the table, MySQL changes it to varchar sometimes (but not always). Here's a structure dump: CREATE TABLE soldierMain ( id int(20) NOT NULL auto_increment, timeadded varchar(14) NOT NULL default '', lastupdate timestamp(14) NOT NULL, name varchar(50) default NULL, email varchar(40) NOT NULL default '', status tinyint(1) NOT NULL default '0', PRIMARY KEY (id) ) TYPE=MyISAM; Here's what it looked like when I ran the import: CREATE TABLE soldierMain ( id int(20) NOT NULL auto_increment, timeadded varchar(14) NOT NULL default '', lastupdate timestamp(14) NOT NULL, name char(50) default NULL, email char(40) NOT NULL default '', status tinyint(1) NOT NULL default '0', PRIMARY KEY (id) ) TYPE=MyISAM; Incidentally - I waited a long time to post my own issue to this list and I'm quite pleased by the responsiveness and ideas I'm getting. Thanks to all that are consider the issues I'm having. Dan -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Thursday, March 27, 2003 4:40 PM To: 'Dan Wright'; [EMAIL PROTECTED] Subject: RE: Slow Inserts What does the table DDL look like. Is the table a fixed or dynamic format? -Original Message- From: Dan Wright [mailto:[EMAIL PROTECTED] Sent: Thursday, March 27, 2003 11:08 AM To: [EMAIL PROTECTED] Subject: Slow Inserts I've been having trouble with some large tables getting what seems to be corrupted. Here's the situation: I have several tables that have 3 million to as much as 7 million records. I have a process that I run against those tables that pulls out a record based on specific criteria (select id,name from table where name !='' and status=0) does something in Perl and then changes the record it just pulled to a status of 9. So basically - every time I run this process, every records is scanned and many (90%) are changed to the status of 9. Well - on a newly imported list, it screams through that and I can get upwards of 3 million per second. Each time I run the process, it gets slower, however. I've used myisamchk and optimize table and neither seem to have any affect on the performance. The only thing that seems to work is mysqldumping the whole table, dropping the table and reimporting the table. I've read up on the site and have found a lot about what could be causing this and have tried many things. Now that I've found what's wrong and how to fix it, I'm happy, but I'd rather not have to dump and reimport. I'm running MySQL 4.0.11 on RHL 7.2. I'm using the huge my.cnf file with some minor tweaks. The tables I'm speaking of have no indexes in it. They had them, but I dumped them and that gave me a huge insert performance gain, but I'm still seeing slowdowns the more I run the process on the file. Thanks in advance, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow Inserts
I've tried it both as fixed (char) and variable (varchar). Interestingly when I set is as char when building the table, MySQL changes it to varchar sometimes (but not always). Here's a structure dump: CREATE TABLE soldierMain ( id int(20) NOT NULL auto_increment, timeadded varchar(14) NOT NULL default '', lastupdate timestamp(14) NOT NULL, name varchar(50) default NULL, email varchar(40) NOT NULL default '', status tinyint(1) NOT NULL default '0', PRIMARY KEY (id) ) TYPE=MyISAM; Here's what it looked like when I ran the import: CREATE TABLE soldierMain ( id int(20) NOT NULL auto_increment, timeadded varchar(14) NOT NULL default '', lastupdate timestamp(14) NOT NULL, name char(50) default NULL, email char(40) NOT NULL default '', status tinyint(1) NOT NULL default '0', PRIMARY KEY (id) ) TYPE=MyISAM; Incidentally - I waited a long time to post my own issue to this list and I'm quite pleased by the responsiveness and ideas I'm getting. Thanks to all that are consider the issues I'm having. Dan -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Thursday, March 27, 2003 4:40 PM To: 'Dan Wright'; [EMAIL PROTECTED] Subject: RE: Slow Inserts What does the table DDL look like. Is the table a fixed or dynamic format? -Original Message- From: Dan Wright [mailto:[EMAIL PROTECTED] Sent: Thursday, March 27, 2003 11:08 AM To: [EMAIL PROTECTED] Subject: Slow Inserts I've been having trouble with some large tables getting what seems to be corrupted. Here's the situation: I have several tables that have 3 million to as much as 7 million records. I have a process that I run against those tables that pulls out a record based on specific criteria (select id,name from table where name !='' and status=0) does something in Perl and then changes the record it just pulled to a status of 9. So basically - every time I run this process, every records is scanned and many (90%) are changed to the status of 9. Well - on a newly imported list, it screams through that and I can get upwards of 3 million per second. Each time I run the process, it gets slower, however. I've used myisamchk and optimize table and neither seem to have any affect on the performance. The only thing that seems to work is mysqldumping the whole table, dropping the table and reimporting the table. I've read up on the site and have found a lot about what could be causing this and have tried many things. Now that I've found what's wrong and how to fix it, I'm happy, but I'd rather not have to dump and reimport. I'm running MySQL 4.0.11 on RHL 7.2. I'm using the huge my.cnf file with some minor tweaks. The tables I'm speaking of have no indexes in it. They had them, but I dumped them and that gave me a huge insert performance gain, but I'm still seeing slowdowns the more I run the process on the file. Thanks in advance, Dan If you have a varchar, text, or blob type field then MySQL silently converts all character fields longer than one character to varchar. This may be why you see it sometimes converting the fields. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow Inserts
What does the table DDL look like. Is the table a fixed or dynamic format? -Original Message- From: Dan Wright [mailto:[EMAIL PROTECTED] Sent: Thursday, March 27, 2003 11:08 AM To: [EMAIL PROTECTED] Subject: Slow Inserts I've been having trouble with some large tables getting what seems to be corrupted. Here's the situation: I have several tables that have 3 million to as much as 7 million records. I have a process that I run against those tables that pulls out a record based on specific criteria (select id,name from table where name !='' and status=0) does something in Perl and then changes the record it just pulled to a status of 9. So basically - every time I run this process, every records is scanned and many (90%) are changed to the status of 9. Well - on a newly imported list, it screams through that and I can get upwards of 3 million per second. Each time I run the process, it gets slower, however. I've used myisamchk and optimize table and neither seem to have any affect on the performance. The only thing that seems to work is mysqldumping the whole table, dropping the table and reimporting the table. I've read up on the site and have found a lot about what could be causing this and have tried many things. Now that I've found what's wrong and how to fix it, I'm happy, but I'd rather not have to dump and reimport. I'm running MySQL 4.0.11 on RHL 7.2. I'm using the huge my.cnf file with some minor tweaks. The tables I'm speaking of have no indexes in it. They had them, but I dumped them and that gave me a huge insert performance gain, but I'm still seeing slowdowns the more I run the process on the file. Thanks in advance, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow Inserts
I've tried it both as fixed (char) and variable (varchar). Interestingly when I set is as char when building the table, MySQL changes it to varchar sometimes (but not always). It will change a char to varchar if there is another column of variable size in the table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow Inserts
I had a very similar problem a couple weeks ago, although in that instance I was using MySQL 3.23. But in any case, I had a perl script that was inserting thousands of records in chunks, looking up an id based on a name for each record. It would get progressively slower and slower the longer it ran. I found that adding a key to the name used in the lookup did, indeeed, slow down inserts a little bit, but it sped up the lookup exponentially, and as it turns out, that's what was causing the slowdown. Brian McCain - Original Message - From: Dan Wright [EMAIL PROTECTED] To: Victor Pendleton [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, March 27, 2003 1:50 PM Subject: RE: Slow Inserts I've tried it both as fixed (char) and variable (varchar). Interestingly when I set is as char when building the table, MySQL changes it to varchar sometimes (but not always). Here's a structure dump: CREATE TABLE soldierMain ( id int(20) NOT NULL auto_increment, timeadded varchar(14) NOT NULL default '', lastupdate timestamp(14) NOT NULL, name varchar(50) default NULL, email varchar(40) NOT NULL default '', status tinyint(1) NOT NULL default '0', PRIMARY KEY (id) ) TYPE=MyISAM; Here's what it looked like when I ran the import: CREATE TABLE soldierMain ( id int(20) NOT NULL auto_increment, timeadded varchar(14) NOT NULL default '', lastupdate timestamp(14) NOT NULL, name char(50) default NULL, email char(40) NOT NULL default '', status tinyint(1) NOT NULL default '0', PRIMARY KEY (id) ) TYPE=MyISAM; Incidentally - I waited a long time to post my own issue to this list and I'm quite pleased by the responsiveness and ideas I'm getting. Thanks to all that are consider the issues I'm having. Dan -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Thursday, March 27, 2003 4:40 PM To: 'Dan Wright'; [EMAIL PROTECTED] Subject: RE: Slow Inserts What does the table DDL look like. Is the table a fixed or dynamic format? -Original Message- From: Dan Wright [mailto:[EMAIL PROTECTED] Sent: Thursday, March 27, 2003 11:08 AM To: [EMAIL PROTECTED] Subject: Slow Inserts I've been having trouble with some large tables getting what seems to be corrupted. Here's the situation: I have several tables that have 3 million to as much as 7 million records. I have a process that I run against those tables that pulls out a record based on specific criteria (select id,name from table where name !='' and status=0) does something in Perl and then changes the record it just pulled to a status of 9. So basically - every time I run this process, every records is scanned and many (90%) are changed to the status of 9. Well - on a newly imported list, it screams through that and I can get upwards of 3 million per second. Each time I run the process, it gets slower, however. I've used myisamchk and optimize table and neither seem to have any affect on the performance. The only thing that seems to work is mysqldumping the whole table, dropping the table and reimporting the table. I've read up on the site and have found a lot about what could be causing this and have tried many things. Now that I've found what's wrong and how to fix it, I'm happy, but I'd rather not have to dump and reimport. I'm running MySQL 4.0.11 on RHL 7.2. I'm using the huge my.cnf file with some minor tweaks. The tables I'm speaking of have no indexes in it. They had them, but I dumped them and that gave me a huge insert performance gain, but I'm still seeing slowdowns the more I run the process on the file. Thanks in advance, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow Inserts
I believe that we have both already figured out that the index was at-least part of the problem due to the fact that the MySQL needs to make more disk writes in that instance. I do not need fast search capability, so I didn't feel the need for the index, so dropping it helped. I've heard a bit from the group about the variable length field (varchar) behavior when building the table. Is the suggestion here that I need to solve that problem by simply making them all char so MySQL doesn't magically convert the field types (thanks to Jennifer for bringing up that valuable tidbit)? Is this why the table is getting slower and slower? Because it's fragmenting the db? If that's the case - then why doesn't myisamchk -r -f or optimize table fix the problem? Since dumping it and reloading fixes it, I'm thinking the table is becoming fragmented. Thanks again to all. This is very educational. Dan -Original Message- From: Brian McCain [mailto:[EMAIL PROTECTED] Sent: Thursday, March 27, 2003 7:22 PM To: Dan Wright; [EMAIL PROTECTED] Subject: Re: Slow Inserts I had a very similar problem a couple weeks ago, although in that instance I was using MySQL 3.23. But in any case, I had a perl script that was inserting thousands of records in chunks, looking up an id based on a name for each record. It would get progressively slower and slower the longer it ran. I found that adding a key to the name used in the lookup did, indeeed, slow down inserts a little bit, but it sped up the lookup exponentially, and as it turns out, that's what was causing the slowdown. Brian McCain - Original Message - From: Dan Wright [EMAIL PROTECTED] To: Victor Pendleton [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, March 27, 2003 1:50 PM Subject: RE: Slow Inserts I've tried it both as fixed (char) and variable (varchar). Interestingly when I set is as char when building the table, MySQL changes it to varchar sometimes (but not always). Here's a structure dump: CREATE TABLE soldierMain ( id int(20) NOT NULL auto_increment, timeadded varchar(14) NOT NULL default '', lastupdate timestamp(14) NOT NULL, name varchar(50) default NULL, email varchar(40) NOT NULL default '', status tinyint(1) NOT NULL default '0', PRIMARY KEY (id) ) TYPE=MyISAM; Here's what it looked like when I ran the import: CREATE TABLE soldierMain ( id int(20) NOT NULL auto_increment, timeadded varchar(14) NOT NULL default '', lastupdate timestamp(14) NOT NULL, name char(50) default NULL, email char(40) NOT NULL default '', status tinyint(1) NOT NULL default '0', PRIMARY KEY (id) ) TYPE=MyISAM; Incidentally - I waited a long time to post my own issue to this list and I'm quite pleased by the responsiveness and ideas I'm getting. Thanks to all that are consider the issues I'm having. Dan -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Thursday, March 27, 2003 4:40 PM To: 'Dan Wright'; [EMAIL PROTECTED] Subject: RE: Slow Inserts What does the table DDL look like. Is the table a fixed or dynamic format? -Original Message- From: Dan Wright [mailto:[EMAIL PROTECTED] Sent: Thursday, March 27, 2003 11:08 AM To: [EMAIL PROTECTED] Subject: Slow Inserts I've been having trouble with some large tables getting what seems to be corrupted. Here's the situation: I have several tables that have 3 million to as much as 7 million records. I have a process that I run against those tables that pulls out a record based on specific criteria (select id,name from table where name !='' and status=0) does something in Perl and then changes the record it just pulled to a status of 9. So basically - every time I run this process, every records is scanned and many (90%) are changed to the status of 9. Well - on a newly imported list, it screams through that and I can get upwards of 3 million per second. Each time I run the process, it gets slower, however. I've used myisamchk and optimize table and neither seem to have any affect on the performance. The only thing that seems to work is mysqldumping the whole table, dropping the table and reimporting the table. I've read up on the site and have found a lot about what could be causing this and have tried many things. Now that I've found what's wrong and how to fix it, I'm happy, but I'd rather not have to dump and reimport. I'm running MySQL 4.0.11 on RHL 7.2. I'm using the huge my.cnf file with some minor tweaks. The tables I'm speaking of have no indexes in it. They had them, but I dumped them and that gave me a huge insert performance gain, but I'm still seeing slowdowns the more I run the process on the file. Thanks in advance, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com
Performance issue: slow inserts
I have a lot of tables, and not all of them are filled
equally.
Inserts to tables that have a lot of entries(see the
count below), take a long time (about .06 secs on the
average in mysql, over 0.09-0.1 in DBI), for example
mysql INSERT INTO T1 VALUES
('3CCF571C1A88118801040302','072','7269','','','',''
,'','',3103,1,24,'2002-09-
01','05:46:52',0.99,13.7560,24.0480,0.0710,190.839,16,0,
0,1,0,0,0,0,0,0,0,0,0,3,4,0,0,0,0,0,0,10,0,'1',1,'0',0,1
,0,'200205010250_ampsc1','AMP',4,'-12015927269-1-95-
3855206',1,0);
Query OK, 1 row affected (0.06 sec)
inserts into tables like T2 which have few elements
take a much shorter time:
mysql INSERT INTO T2 VALUES
('3CCF571C1A88118801040302','072','7269','','','',''
,'','',3103,1,24,'2002-09-
01','05:46:52',0.99,13.7560,24.0480,0.0710,190.839,16,0,
0,1,0,0,0,0,0,0,0,0,0,3,4,0,0,0,0,0,0,10,0,'1',1,'0',0,1
,0,'200205010250_ampsc1','AMP',4,'-12015927269-1-95-
3855206',1,0);
Query OK, 1 row affected (0.00 sec)
mysql SELECT COUNT(*) FROM T2;
+--+
| COUNT(*) |
+--+
| 509 |
+--+
1 row in set (0.00 sec)
mysql SELECT COUNT(*) FROM T1;
+--+
| COUNT(*) |
+--+
| 782910 |
+--+
1 row in set (7.43 sec)
Of course DELETES are similar(actually much worse with
a where), but deletes are not an issue here
mysql DELETE FROM T1 WHERE date ='2002-09-01';
Query OK, 1 row affected (40.19 sec)
mysql DELETE FROM T2 WHERE date ='2002-09-01';
Query OK, 1 row affected (0.24 sec)
I presume the problem is that in the table definitions
I have a couple of unique constraints and
the INSERT time difference is due to mysql
checking all entries in the table for a unique
violation, hence the insert would be slow:
CREATE TABLE T1 (
corrno varchar(43) default NULL,
a varchar(30) default NULL,
b varchar(30) default NULL,
c varchar(30) default NULL,
oc varchar(30) default NULL,
red varchar(30) default NULL,
sanum varchar(30) default NULL,
sbnum varchar(30) default NULL,
tnum varchar(30) default NULL,
o int(5) default NULL,
d int(5) default NULL,
cic int(6) default NULL,
date date default NULL,
start_time time default NULL,
value decimal(30,2) default NULL,
iamtoacm decimal(7,4) default NULL,
iamtoans decimal(7,4) default NULL,
reltorlc decimal(7,4) default NULL,
duration decimal(12,3) default NULL,
rcv int(3) default NULL,
rcvabn int(1) default '0',
loc int(3) default NULL,
reldir int(1) default NULL,
secode int(3) default NULL,
trans int(3) default NULL,
tc int(3) default NULL,
rsp 0 int(3) default NULL,
ccq int(5) default NULL,
ccr int(3) default NULL,
ccc int(3) default NULL,
ccp int(3) default NULL,
ccsan int(3) default NULL,
nata int(2) default NULL,
natb int(2) default NULL,
natc int(2) default NULL,
natocn int(2) default NULL,
natredir int(2) default NULL,
natsccpa int(2) default NULL,
natsccpbint(2) default NULL,
nattrue int(2) default NULL,
acat int(2) default NULL,
bcat int(1) default NULL,
upi char(1) default NULL,
upp int(3) default NULL,
acind char(1) default NULL,
inter int(1) default NULL,
ech int(1) default NULL,
length int(3) default '0',
file varchar(30) default NULL,
link varchar(8) default NULL,
fileind int(11) default NULL,
unicor varchar(100) NOT NULL default '',
stp int(1) default '0',
alarm int(1) default '0',
UNIQUE KEY unicor (unicor),
UNIQUE KEY corrno (corrno)
) TYPE=InnoDB;
The question is what can be done about this?
I guess getting rid of the unique constraints
would help, but then I would be getting some
unwanted duplicates. I should add that currently
I need to do about 100,000
inserts/10 minutes and the system is too slow for this
as it is, while it used to be very fast. So, I am
looking fro what can be done to avoid performance
degradation
as the tables fill.
The server is a dual PIII x1000, 2GB RAM Linux 2.4.18
machine but the inserts are done from a client(a 1000
PIII , 1GB RAM Linux machine). Below is /etc/my.cnf
# Example mysql config file for very large systems.
#
# This is for large system with memory of 1G-2G where
the system runs mainly
# MySQL.
#
# You can copy this file to
# /etc/mf.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options
(in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# One can in this file use all long options that the
program supports.
# If you want to know which options a program support,
run the program
# with --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
set-variable = key_buffer_size=500M
set-variable = key_buffer=100M
set-variable = max_allowed_packet=1M
Re: Performance issue: slow inserts
InnoDB are supposed to be faster at Inserts and updates than MyISAM.
However, my dealings with InnoDB have made me think that unless you _need_
transactions, foreign keys, etc, MyISAM is still the way to go for speed on
large tables.
If you can go MyISAM (you may need that stuff), fixed width tables will
insert and update faster than variable width tables. You could turn all
those varchars into chars. There will be an increase in data size.
As for the delete, all you need is a key on the date column. That will
speed that up.
- Original Message -
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, August 22, 2002 4:22 AM
Subject: Performance issue: slow inserts
|
| I have a lot of tables, and not all of them are filled
| equally.
| Inserts to tables that have a lot of entries(see the
| count below), take a long time (about .06 secs on the
| average in mysql, over 0.09-0.1 in DBI), for example
|
| mysql INSERT INTO T1 VALUES
| ('3CCF571C1A88118801040302','072','7269','','','',''
| ,'','',3103,1,24,'2002-09-
| 01','05:46:52',0.99,13.7560,24.0480,0.0710,190.839,16,0,
| 0,1,0,0,0,0,0,0,0,0,0,3,4,0,0,0,0,0,0,10,0,'1',1,'0',0,1
| ,0,'200205010250_ampsc1','AMP',4,'-12015927269-1-95-
| 3855206',1,0);
| Query OK, 1 row affected (0.06 sec)
|
| inserts into tables like T2 which have few elements
| take a much shorter time:
| mysql INSERT INTO T2 VALUES
| ('3CCF571C1A88118801040302','072','7269','','','',''
| ,'','',3103,1,24,'2002-09-
| 01','05:46:52',0.99,13.7560,24.0480,0.0710,190.839,16,0,
| 0,1,0,0,0,0,0,0,0,0,0,3,4,0,0,0,0,0,0,10,0,'1',1,'0',0,1
| ,0,'200205010250_ampsc1','AMP',4,'-12015927269-1-95-
| 3855206',1,0);
| Query OK, 1 row affected (0.00 sec)
|
| mysql SELECT COUNT(*) FROM T2;
| +--+
| | COUNT(*) |
| +--+
| | 509 |
| +--+
| 1 row in set (0.00 sec)
|
| mysql SELECT COUNT(*) FROM T1;
| +--+
| | COUNT(*) |
| +--+
| | 782910 |
| +--+
| 1 row in set (7.43 sec)
|
| Of course DELETES are similar(actually much worse with
| a where), but deletes are not an issue here
| mysql DELETE FROM T1 WHERE date ='2002-09-01';
| Query OK, 1 row affected (40.19 sec)
|
| mysql DELETE FROM T2 WHERE date ='2002-09-01';
| Query OK, 1 row affected (0.24 sec)
|
|
| I presume the problem is that in the table definitions
| I have a couple of unique constraints and
| the INSERT time difference is due to mysql
| checking all entries in the table for a unique
| violation, hence the insert would be slow:
|
| CREATE TABLE T1 (
| corrno varchar(43) default NULL,
| a varchar(30) default NULL,
| b varchar(30) default NULL,
| c varchar(30) default NULL,
| oc varchar(30) default NULL,
| red varchar(30) default NULL,
| sanum varchar(30) default NULL,
| sbnum varchar(30) default NULL,
| tnum varchar(30) default NULL,
| o int(5) default NULL,
| d int(5) default NULL,
| cic int(6) default NULL,
| date date default NULL,
| start_time time default NULL,
| value decimal(30,2) default NULL,
| iamtoacm decimal(7,4) default NULL,
| iamtoans decimal(7,4) default NULL,
| reltorlc decimal(7,4) default NULL,
| duration decimal(12,3) default NULL,
| rcv int(3) default NULL,
| rcvabn int(1) default '0',
| loc int(3) default NULL,
| reldir int(1) default NULL,
| secode int(3) default NULL,
| trans int(3) default NULL,
| tc int(3) default NULL,
| rsp 0 int(3) default NULL,
| ccq int(5) default NULL,
| ccr int(3) default NULL,
| ccc int(3) default NULL,
| ccp int(3) default NULL,
| ccsan int(3) default NULL,
| nata int(2) default NULL,
| natb int(2) default NULL,
| natc int(2) default NULL,
| natocn int(2) default NULL,
| natredir int(2) default NULL,
| natsccpa int(2) default NULL,
| natsccpbint(2) default NULL,
| nattrue int(2) default NULL,
| acat int(2) default NULL,
| bcat int(1) default NULL,
| upi char(1) default NULL,
| upp int(3) default NULL,
| acind char(1) default NULL,
| inter int(1) default NULL,
| ech int(1) default NULL,
| length int(3) default '0',
| file varchar(30) default NULL,
| link varchar(8) default NULL,
| fileind int(11) default NULL,
| unicor varchar(100) NOT NULL default '',
| stp int(1) default '0',
| alarm int(1) default '0',
| UNIQUE KEY unicor (unicor),
| UNIQUE KEY corrno (corrno)
| ) TYPE=InnoDB;
|
|
| The question is what can be done about this?
| I guess getting rid of the unique constraints
|would help, but then I would be getting some
| unwanted duplicates. I should add that currently
| I need to do about 100,000
| inserts/10 minutes and the system is too slow for this
| as it is, while it used to be very fast. So, I am
| looking fro what can be done to avoid performance
| degradation
| as the tables fill.
| The server is a dual PIII x1000, 2GB RAM Linux 2.4.18
| machine but the inserts are done from a client(a 1000
| PIII , 1GB RAM Linux machine). Below is /etc/my.cnf
| # Example mysql config file
slow inserts and selects for fulltext indexes (mysql4)
Hi,
Is there a way to speed up inserts on a table with three fulltext
indexes? I'm using the multiple value insert format like:
insert into blah (field1, field2) values ('val1', 'val2'), ('val2',
'val3'), etc..
Perhaps this is a bug in the current mysql4 bk snapshot, but inserts and
selects on a table with three fulltext indexes are taking up to 23
seconds!
On average it's still taking up to 2.5 seconds to insert 50 records into
a table with 15000 records already (Which I think is kinda slow). But
every now and then it'll really slow down. I just logged 9 seconds for a
fulltext select on a table with 15000 records, and 23 seconds for an
insert of 106 rows on the same table. The machine has 400 megs of RAM
and is a PIII 750 with IDE drives. (as opposed to SCSI). Running MySQL
version 4 (the latest bk snapshot).
Here are the entries from the 'slow.log':
##
SET timestamp=1016613725;
select SQL_CACHE
id,search_category,search_jobtype,joblink,title,d_date_posted,n_date_posted,jobsite,jobsite_home,jobsite_con
/usr/local/mysql/libexec/mysqld, Version: 4.0.2-alpha-log, started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id CommandArgument
# Time: 020320 9:01:40
# User@Host: root[root] @ localhost []
# Query_time: 9 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
use workzoo;
SET timestamp=1016614900;
insert into search_cache (ctime,
search_category,search_jobtype,joblink,title,d_date_posted,n_date_posted,jobsite,jobsite_home
# Time: 020320 9:02:04
# User@Host: root[root] @ localhost []
# Query_time: 23 Lock_time: 0 Rows_sent: 106 Rows_examined: 13081
#
My my.cnf is included below. Based on the config above, particularly the
amount of RAM, is this the ideal config for my machine? (considering
that I do large inserts, updates and select on all three fulltext
indexes on the same table)
btw. I'm getting great performance on multiple sequential updates (over
200 records takes under .1 seconds) by locking the table.
[mysqld]
port= 3306
socket = /tmp/mysql.sock
skip-locking
memlock
set-variable= key_buffer=100M
set-variable= max_allowed_packet=5M
set-variable= table_cache=256
set-variable= record_buffer=1M
set-variable= sort_buffer=20M
set-variable= net_buffer_length=8K
set-variable= myisam_sort_buffer_size=20M
set-variable= long_query_time=1
set-variable= ft_min_word_len=1
#Query cache configured for on demand only
set-variable= query_cache_limit=2M
set-variable= query_cache_size=2M
set-variable= query_cache_startup_type=2
bind-address=10.1.1.1
log-bin
server-id = 1
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
log = /var/log/mysql/general.log
log-slow-queries = /var/log/mysql/slow.log
Thanks,
Mark.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
