Howdy - I have a question concerning MySQL performance. From the list, it seems to be the topic as of late!
Database Server: Dual Pentium III 1.2GHz COMPAQ server with 2GB of RAM running Red Hat LInux, 7.2 (Enigma). MySQL version is 3.23.52-Max-log. All tables are InnoDB. Storage: All MySQL InnoDB table space is located on an EMC SAN box. Application servers: 1 box configured like database server (dual processor), 5 boxes same except single processors and 1 GB of RAM Network: Application servers are connected to each other and database server via 100 Mbit full duplex. Database server is connected to EMC SAN via Gigabit ethernet. Load on MySQL databse server at the time is 30 connections to MySQL, 43 Linux process on the box total. This is a combination of inserts, updates and deletes. Each of these commands are accessing a single record (via primary key on the table, spread over about 30 tables, table sizes varying from 2K rows to 30M+ rows. None of the tables have autoincrement columns in them. All tables have indexes on the appropriate fields so the commands built are able to use them. Slow Query log is clean. All code is written on C++ and uses the C api calls to the mysql client library. Each transaction consists of individual commands: begin transaction select via indexed field (about 7 records result set) and do for each row: -update into some table (one row) -check result to see that the update took place (via calling mysql_info to see that the update found rows to apply to) and turning the update into an insert if no records were applied (happens about 10% of the time) -insert into a large table (one row) -delete from a large table (one row) commit transaction I obtain my timings by running batches of anywhere between 20,000 and 150,000 of the above transactions. When 1 run a process on an application server and calculate what is actually going on, I am seeing MySQL server able to process about 500 SQL commands/second. When I look at the InnoDB status, I can verify about 500 commands/second throughput. Of course, everything has limits and from my earlier playing with MySQL, I found that it has as a limiting factor of how many SQL commands per second it can accept, interpret and execute an a given hardware setup. For example, on my early tests, I saw that if I did single record inserts, I could process only a few hundred inserts per second, but when I did multiple inserts per command, I could actually insert tens of thousands of records per second. So, here are my questions: 1) Given the database server above and wrapping everything in transactions, is 500 SQL commands/second accepted, interpreted, and executed a good number? By good I mean high performance, not valid. 2) What experience do other folks have with InnoDB and transactions? Yes, I know they work fine (I have tried to break them and they do work fine), but, what are the performance issues with using them? Exactly how expensive are transactions? 3) What experiences have others had with MySQL throughput in terms of commands/second? Thanks in advance for your input! Ken Hylton Programmer Analyst IV LEC Systems & Programming Billing Concepts, Inc. 7411 John Smith Drive San Antonio, Texas 78229-4898 (210) 949-7261 --------------------------------------------------------------------- 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 <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
