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]
