Re: Suggestion Setting For Highload Server
Hi Mike, I don't understand with the delete the 'deleted' rows. Can you explain me more? And about the RAM, yes we are going to upgrade it. The application opens 1000 simultan connections to service the requests For the tables, we have 2 identical tables, the flow will be like this. First when there is an incoming request application A will store it into the first table, and application A will parse it to redirect it application B to get the response. After responsed, application B will store it into the second table. And application A will always check the second table by searching new rows, if found it will copy it to the first table and push it to the external request as the result. In simple word there will always 3 records for each transaction and these 3 records have the same transaction ID. I heard the lock and unlocking method is only optimized for InnoDB type? Is it right? Regards, Willy -- --- SMS SERVER, SMS BULK, SMS PREMIUM, WEB DESIGN, NETWORKING Contact [EMAIL PROTECTED], 62 811 923 464, 62 21 944 8282 4 http://www.binbit.co.id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Suggestion Setting For Highload Server
At 04:42 PM 7/19/2008, sangprabv wrote: Hi Mike, Thanks for the reply. 1. Currently the hardware is P4 2.8 on Slackware 12 with 1GB of DDR Memory (we plan to upgrade it) 2. The table type is MyISAM 3. There is no slow query, because all of the queries are a simple type 4. The table's size is increasing dynamically with at least 10 thousands new records / minute 5. Here is the current my.cnf key_buffer = 16M max_allowed_packet = 16M thread_stack= 128K thread_cache_size = 8 max_connections = 1000 query_cache_limit = 1M query_cache_size= 16M tmp_table_size = 64M max_heap_table_size = 32M log_slow_queries= /var/log/mysql/mysql-slow.log long_query_time = 2 log-queries-not-using-indexes log_bin = /var/log/mysql/mysql-bin.log expire_logs_days= 10 max_binlog_size = 100M skip-bdb [mysqldump] quick quote-names max_allowed_packet = 16M [isamchk] key_buffer = 16M TIA Regards, Willy Willy, You definitely need more RAM. Use as much RAM as you can cram into the machine (8-16gb). You didn't mention a problem with table locking? As the rows are being inserted, is anyone reading from the table? If so the table will be locked as rows are inserted and people are prevented from executing a Select on the table until the inserts are completed. You can get around this locking problem by optimizing the table which removes holes in the table (deleted rows) and then the inserts will not require table locks. If you want to delete a row then set a row column to 0 to indicated 'deleted' so it can't be seen by your Select statements. Once a day delete these "deleted" rows and re-optimize the table. The slowest part about rebuilding a large table is building the index. You will need to add 500M (or more) to key_buffer_size in order to try and build the index in memory (100x faster than building the index on disk). Do you really need 1000 connections?? Are you not using connection pooling? Otherwise it appears your settings are extremely conservative. There is a book called "High Performance MySQL: Optimization, Backups, Replication, and More" published 2008. It is available from Amazon. (There was a 2004 version of the book so make sure you order the newer one.) You can also post your explanation to the MySQL list to get more help. Mike On Sat, 2008-07-19 at 15:45 -0500, mos wrote: > At 12:11 PM 7/19/2008, sangprabv wrote: > >Hi, > >I have situation where a MySQL server processes about 10-20 thousands > >requests per minute. I need suggestions from you for tuning up this > >server to get optimized setting. TIA > > > > > >Willy > > Willy, > You will need post more information: > > 1) What type of hardware are you running this on? (OS, Drives, Memory etc.) > 2) What table engine are you using (MyISAM or InnoDb?) > 3) What do the slow queries look like? Updates? Table joins? Inserts? > Simple selects? > 4) How large are the tables? > 5) What does your My.Cnf file look like? > > Mike > > -- --- SMS SERVER, SMS BULK, SMS PREMIUM, WEB DESIGN, NETWORKING Contact [EMAIL PROTECTED], 62 811 923 464, 62 21 944 8282 4 http://www.binbit.co.id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Suggestion Setting For Highload Server
Hi Mike, Thanks for the reply. 1. Currently the hardware is P4 2.8 on Slackware 12 with 1GB of DDR Memory (we plan to upgrade it) 2. The table type is MyISAM 3. There is no slow query, because all of the queries are a simple type 4. The table's size is increasing dynamically with at least 10 thousands new records / minute 5. Here is the current my.cnf key_buffer = 16M max_allowed_packet = 16M thread_stack= 128K thread_cache_size = 8 max_connections = 1000 query_cache_limit = 1M query_cache_size= 16M tmp_table_size = 64M max_heap_table_size = 32M log_slow_queries= /var/log/mysql/mysql-slow.log long_query_time = 2 log-queries-not-using-indexes log_bin = /var/log/mysql/mysql-bin.log expire_logs_days= 10 max_binlog_size = 100M skip-bdb [mysqldump] quick quote-names max_allowed_packet = 16M [isamchk] key_buffer = 16M TIA Regards, Willy On Sat, 2008-07-19 at 15:45 -0500, mos wrote: > At 12:11 PM 7/19/2008, sangprabv wrote: > >Hi, > >I have situation where a MySQL server processes about 10-20 thousands > >requests per minute. I need suggestions from you for tuning up this > >server to get optimized setting. TIA > > > > > >Willy > > Willy, > You will need post more information: > > 1) What type of hardware are you running this on? (OS, Drives, Memory etc.) > 2) What table engine are you using (MyISAM or InnoDb?) > 3) What do the slow queries look like? Updates? Table joins? Inserts? > Simple selects? > 4) How large are the tables? > 5) What does your My.Cnf file look like? > > Mike > > -- --- SMS SERVER, SMS BULK, SMS PREMIUM, WEB DESIGN, NETWORKING Contact [EMAIL PROTECTED], 62 811 923 464, 62 21 944 8282 4 http://www.binbit.co.id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Suggestion Setting For Highload Server
At 12:11 PM 7/19/2008, sangprabv wrote: Hi, I have situation where a MySQL server processes about 10-20 thousands requests per minute. I need suggestions from you for tuning up this server to get optimized setting. TIA Willy Willy, You will need post more information: 1) What type of hardware are you running this on? (OS, Drives, Memory etc.) 2) What table engine are you using (MyISAM or InnoDb?) 3) What do the slow queries look like? Updates? Table joins? Inserts? Simple selects? 4) How large are the tables? 5) What does your My.Cnf file look like? Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Suggestion Setting For Highload Server
Hi, I have situation where a MySQL server processes about 10-20 thousands requests per minute. I need suggestions from you for tuning up this server to get optimized setting. TIA Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
