Re: will a cluster be faster than a heap-table?
Hi Brent, Wow, it seems like you are going to extremes. To jump from myisam to heap is a big step. Did you try using InnoDB? It would handle locking issues much better since it doesn't lock the table. Heap tables can be pretty dangerous since it's all in memory. If the machine crashes, you'll lose the data. I know that, but I do regular (cornjobs) backups to myisam-tables and I can reconstruct the whole table from the machines that insert/update the data. Based on your information, you want to get the best disk I/O you can. You won't get that out of a single IDE drive, even if it is one of the latest SATA based with command queuing. I don't think you'll get anything faster than heap tables and tons of RAM. But there is certainly finite scalability because of the use of RAM. Clusters may be the way to go for scalability, but I would work on getting your data disk based for maximum scalability. For my case, scalability means more updates/second and more selects/second. Not larger tables. At least not much larger, and this table is using less than 300 MB of memory right now. So I see no point in using anything disk-based. 2 years ago we started with myisam, then changed to innodb, found out it wouldn't give any better performance in our case and switched back to myisam since that makes the setup of replications much easier. Then we changed it again 6 months ago and now use memory-tables. I would try InnoDB and maximize you disk setup. I don't know how many disks you have in your RAID and if it's hardware or software based. More disks will add speed by splitting the load across more disks. Just keep in mind the limits of your SCSI card too. You may need to add a card to split the load. These two systems have Hardware-RAID (SCSI storage controller: LSI Logic / Symbios Logic (formerly NCR) 53c1030 (rev 07)) width 2 disks in raid1 and the DB is myaybe 40 Gb of size. I have no performance trouble on any other table. Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
will a cluster be faster than a heap-table?
Hi, I am currently using a replication setup on two servers with mysql 4.1.13-standard-log (master/slave each a P4 2.4ghz, 3GB RAM, Hardware SCSI-RAID). I have a table that has lots of updates and selects. We converted this table (along with other tables) from a myisam to a heap-table 6 months ago which increased the performance a lot, but we are hitting the limits again - on the master as well as on the slave. We are only talking about 50-60 queries/second in peaks maybe 90 q/sec (which means more selects, but not much more inserts), but the inserts are bulk-inserts (each around 2500-3000 rows) doing INSERT INTO...ON DUPLICATE KEY UPDATE-Queries updating a total of around 50.000 rows/minute in the daytime, We are getting locking-Problems with selects having to wait for 5 seconds or sometimes even much longer. We expect that the amount of insert will increase slowly while the selects will get much more pretty soon. The selects are all optimized and respond within 0.x or 0.0x seconds in a mysql-shell in case they are not locked by an insert. It is weird that those inserts that usually only take 1-2 seconds (never saw anything older in the processlist) now sometimes take 10 seconds or more while more and more selects are waiting in the Locked-status. I saw this behaviour 3 weeks ago for the first time and maybe 4 or 5 more times since then... I am just wondering if a cluster-setup would help us speed up the system. If I understand this right, it is no problem to mix NDB-tables an memory/myisam-tables. I'd just have to install a cluster-enabled version of mysqld and set up 2 or more NDB-Nodes, right? I could then alter the few speed-critical tables to the NDB-storage-type and would not have to change any SQL? I'd just take 2 simple Athlon64-PCs with 1GB-2GB RAM each, Gbit-Ethernet and an IDE-Harddisk, no expensive Server-Hardware? Or would I need at least 4 Nodes to have an speed-improvement? I've been trying to find answers on this on mysql.com but was not successful. There is no info about possibilities of mixing NDB- and myisam-tables and all documentation on mysql-cluster focuses more on HA than on speed especially speed of cluster-tables compared to traditional mysql-memory-tables... thanks for any help on this! Jan in case this is important: show table status: *** 25. row *** Name: memtable_spr Engine: HEAP Version: 9 Row_format: Fixed Rows: 777330 Avg_row_length: 294 Data_length: 234729984 Max_data_length: 856336152 Index_length: 52598232 Data_free: 294 Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: will a cluster be faster than a heap-table?
Wow, it seems like you are going to extremes. To jump from myisam to heap is a big step. Did you try using InnoDB? It would handle locking issues much better since it doesn't lock the table. Heap tables can be pretty dangerous since it's all in memory. If the machine crashes, you'll lose the data. Based on your information, you want to get the best disk I/O you can. You won't get that out of a single IDE drive, even if it is one of the latest SATA based with command queuing. I don't think you'll get anything faster than heap tables and tons of RAM. But there is certainly finite scalability because of the use of RAM. Clusters may be the way to go for scalability, but I would work on getting your data disk based for maximum scalability. I would try InnoDB and maximize you disk setup. I don't know how many disks you have in your RAID and if it's hardware or software based. More disks will add speed by splitting the load across more disks. Just keep in mind the limits of your SCSI card too. You may need to add a card to split the load. On Oct 27, 2005, at 1:57 PM, Jan Kirchhoff wrote: Hi, I am currently using a replication setup on two servers with mysql 4.1.13-standard-log (master/slave each a P4 2.4ghz, 3GB RAM, Hardware SCSI-RAID). I have a table that has lots of updates and selects. We converted this table (along with other tables) from a myisam to a heap-table 6 months ago which increased the performance a lot, but we are hitting the limits again - on the master as well as on the slave. We are only talking about 50-60 queries/second in peaks maybe 90 q/ sec (which means more selects, but not much more inserts), but the inserts are bulk-inserts (each around 2500-3000 rows) doing INSERT INTO...ON DUPLICATE KEY UPDATE-Queries updating a total of around 50.000 rows/minute in the daytime, We are getting locking-Problems with selects having to wait for 5 seconds or sometimes even much longer. We expect that the amount of insert will increase slowly while the selects will get much more pretty soon. The selects are all optimized and respond within 0.x or 0.0x seconds in a mysql-shell in case they are not locked by an insert. It is weird that those inserts that usually only take 1-2 seconds (never saw anything older in the processlist) now sometimes take 10 seconds or more while more and more selects are waiting in the Locked-status. I saw this behaviour 3 weeks ago for the first time and maybe 4 or 5 more times since then... I am just wondering if a cluster-setup would help us speed up the system. If I understand this right, it is no problem to mix NDB-tables an memory/myisam-tables. I'd just have to install a cluster-enabled version of mysqld and set up 2 or more NDB-Nodes, right? I could then alter the few speed-critical tables to the NDB-storage-type and would not have to change any SQL? I'd just take 2 simple Athlon64-PCs with 1GB-2GB RAM each, Gbit- Ethernet and an IDE-Harddisk, no expensive Server-Hardware? Or would I need at least 4 Nodes to have an speed-improvement? I've been trying to find answers on this on mysql.com but was not successful. There is no info about possibilities of mixing NDB- and myisam- tables and all documentation on mysql-cluster focuses more on HA than on speed especially speed of cluster-tables compared to traditional mysql-memory-tables... thanks for any help on this! Jan in case this is important: show table status: *** 25. row *** Name: memtable_spr Engine: HEAP Version: 9 Row_format: Fixed Rows: 777330 Avg_row_length: 294 Data_length: 234729984 Max_data_length: 856336152 Index_length: 52598232 Data_free: 294 Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
