Thanks for your input. I'll try give my.cnf a try.
I'm not technical at all and am unable to describe the queries myself.
Attached some output from the server (Sorry for the long post):
SHOW STATUS
---------------------
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 106 |
| Aborted_connects | 1 |
| Bytes_received | 1386152010 |
| Bytes_sent | 1017147314 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_change_db | 404084 |
| Com_delete | 954139 |
| Com_flush | 0 |
| Com_insert | 38972 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_select | 5274720 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 4 |
| Com_show_fields | 2 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 8 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 9 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 56 |
| Com_show_variables | 20 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 1412924 |
| Connections | 403875 |
| Created_tmp_disk_tables | 688 |
| Created_tmp_tables | 105261 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 27226 |
| Handler_read_first | 611592 |
| Handler_read_key | 2133888023 |
| Handler_read_next | 2198593130 |
| Handler_read_prev | 278 |
| Handler_read_rnd | 34278228 |
| Handler_read_rnd_next | 249766468 |
| Handler_rollback | 0 |
| Handler_update | 1007151 |
| Handler_write | 184271195 |
| Key_blocks_used | 106432 |
| Key_read_requests | 3936331483 |
| Key_reads | 100865 |
| Key_write_requests | 1897202 |
| Key_writes | 1444468 |
| Max_used_connections | 234 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 1024 |
| Open_files | 1055 |
| Open_streams | 0 |
| Opened_tables | 6601 |
| Questions | 17691302 |
| Qcache_queries_in_cache | 1564 |
| Qcache_inserts | 5109816 |
| Qcache_hits | 9173397 |
| Qcache_lowmem_prunes | 89550 |
| Qcache_not_cached | 183073 |
| Qcache_free_memory | 4490312 |
| Qcache_free_blocks | 1239 |
| Qcache_total_blocks | 4393 |
| Rpl_status | NULL |
| Select_full_join | 712 |
| Select_full_range_join | 119 |
| Select_range | 24655 |
| Select_range_check | 0 |
| Select_scan | 407032 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 2892 |
| Sort_merge_passes | 0 |
| Sort_range | 244321 |
| Sort_rows | 2279797622 |
| Sort_scan | 151217 |
| Table_locks_immediate | 7551643 |
| Table_locks_waited | 957623 |
| Threads_cached | 220 |
| Threads_created | 235 |
| Threads_connected | 15 |
| Threads_running | 7 |
| Uptime | 155131 |
+--------------------------+------------+
132 rows in set (0.00 sec)
Snapshot of MYTOP
--------------------------
MySQL on localhost (4.0.14-Max)
up 1+19:18:02 [12:20:47]
Queries: 17.0M qps: 114 Slow: 31.0 Se/In/Up/De(%):
00/00/00/00
qps now: 87 Slow qps: 3.2 Threads: 129 ( 119/ 106)
75/01/09/07
Cache Hits: 7.9k Hits/s: 0.1 Hits now: 43.7 Ratio: 61.0% Ratio now:
66.7%
Key Efficiency: 100.0% Bps in/out: 8.4/ 60.1 Now in/out: 6.5k/54.1k
Id User Host/IP DB Time Cmd Query or
State
-- ---- ------- -- ---- --- ----------
408302 root localhost baby_kingd 0 Query show full
processlist
408582 xoops 192.168.0.1 baby_kingd 0 Sleep
408636 xoops 192.168.0.1 baby_kingd 0 Query SELECT p.*,
t.post_text FROM xoops_bb_posts p, xoops_bb_posts_
408627 xoops 192.168.0.1 baby_kingd 1 Query SELECT p.*,
t.post_text FROM xoops_bb_posts p, xoops_bb_posts_
408635 xoops 192.168.0.1 baby_kingd 1 Query SELECT u.uid,
u.uname FROM xoops_users u, xoops_bb_forum_mods
408637 xoops 192.168.0.1 baby_kingd 1 Query SELECT p.*,
t.post_text FROM xoops_bb_posts p, xoops_bb_posts_
408638 xoops 192.168.0.1 baby_kingd 1 Query SELECT p.*,
t.post_text FROM xoops_bb_posts p, xoops_bb_posts_
408639 xoops 192.168.0.1 baby_kingd 1 Query SELECT * FROM
xoops_users WHERE uid=3867
408641 xoops 192.168.0.1 baby_kingd 1 Query SELECT u.uid,
u.uname FROM xoops_users u, xoops_bb_forum_mods
408631 xoops 192.168.0.1 baby_kingd 2 Query SELECT * FROM
xoops_users WHERE uid=6104
408632 xoops 192.168.0.1 baby_kingd 2 Query SELECT * FROM
xoops_users WHERE uid=555
408634 xoops 192.168.0.1 baby_kingd 2 Query SELECT * FROM
xoops_users WHERE uid=15313
408553 xoops 192.168.0.1 baby_kingd 3 Query INSERT INTO
xoops_bb_posts_text (post_id, post_text) VALUES (1
408629 xoops 192.168.0.1 baby_kingd 3 Query SELECT * FROM
xoops_users WHERE uid=393
408630 xoops 192.168.0.1 baby_kingd 3 Query SELECT t.*,
u.uname, u2.uname as last_poster, p.post_time as l
408625 xoops 192.168.0.1 baby_kingd 4 Query SELECT p.*,
t.post_text FROM xoops_bb_posts p, xoops_bb_posts_
408626 xoops 192.168.0.1 baby_kingd 4 Query SELECT p.*,
t.post_text FROM xoops_bb_posts p, xoops_bb_posts_
408628 xoops 192.168.0.1 baby_kingd 4 Query SELECT * FROM
xoops_users WHERE uid=645
408620 xoops 192.168.0.1 baby_kingd 5 Query SELECT p.*,
t.post_text FROM xoops_bb_posts p, xoops_bb_posts_
408621 xoops 192.168.0.1 baby_kingd 5 Query SELECT * FROM
xoops_users WHERE uid=12308
408624 xoops 192.168.0.1 baby_kingd 5 Query SELECT * FROM
xoops_users WHERE uid=12013
408615 xoops 192.168.0.1 baby_kingd 6 Query SELECT p.*,
t.post_text FROM xoops_bb_posts p, xoops_bb_posts_
408616 xoops 192.168.0.1 baby_kingd 6 Query SELECT t.*,
u.uname, u2.uname as last_poster, p.post_time as l
408617 xoops 192.168.0.1 baby_kingd 6 Query SELECT p.*,
t.post_text FROM xoops_bb_posts p, xoops_bb_posts_
408618 xoops 192.168.0.1 baby_kingd 6 Query SELECT u.uid,
u.uname FROM xoops_users u, xoops_bb_forum_mods
408619 xoops 192.168.0.1 baby_kingd 6 Query SELECT * FROM
xoops_users WHERE uid=4896
408597 xoops 192.168.0.1 baby_kingd 7 Query SELECT p.*,
t.post_text FROM xoops_bb_posts p, xoops_bb_posts_
408611 xoops 192.168.0.1 baby_kingd 7 Query SELECT p.*,
t.post_text FROM xoops_bb_posts p, xoops_bb_posts_
408614 xoops 192.168.0.1 baby_kingd 7 Query SELECT * FROM
xoops_users WHERE uid=81
408509 xoops 192.168.0.1 baby_kingd 8 Query INSERT INTO
xoops_bb_posts_text (post_id, post_text) VALUES (1
408601 xoops 192.168.0.1 baby_kingd 8 Query SELECT p.*,
t.post_text FROM xoops_bb_posts p, xoops_bb_posts_
408609 xoops 192.168.0.1 baby_kingd 8 Query SELECT * FROM
xoops_users WHERE uid=5876
408610 xoops 192.168.0.1 baby_kingd 8 Query SELECT * FROM
xoops_users WHERE uid=382
408526 xoops 192.168.0.1 baby_kingd 9 Query SELECT p.*,
t.post_text FROM xoops_bb_posts p, xoops_bb_posts_
408572 xoops 192.168.0.1 baby_kingd 9 Query SELECT p.*,
t.post_text FROM xoops_bb_posts p, xoops_bb_posts_
408585 xoops 192.168.0.1 baby_kingd 9 Query SELECT p.*,
t.post_text FROM xoops_bb_posts p, xoops_bb_posts_
408606 xoops 192.168.0.1 baby_kingd 9 Query SELECT p.*,
t.post_text FROM xoops_bb_posts p, xoops_bb_posts_
408607 xoops 192.168.0.1 baby_kingd 9 Query SELECT * FROM
xoops_users WHERE uid=367
408608 xoops 192.168.0.1 baby_kingd 9 Query SELECT * FROM
xoops_users WHERE uid=2388
408470 xoops 192.168.0.1 baby_kingd 10 Query SELECT p.*,
t.post_text FROM xoops_bb_posts p, xoops_bb_posts_
408491 xoops 192.168.0.1 baby_kingd 10 Query SELECT p.*,
t.post_text FROM xoops_bb_posts p, xoops_bb_posts_
408510 xoops 192.168.0.1 baby_kingd 10 Query SELECT p.*,
t.post_text FROM xoops_bb_posts p, xoops_bb_posts_
408517 xoops 192.168.0.1 baby_kingd 10 Query SELECT p.*,
t.post_text FROM xoops_bb_posts p, xoops_bb_posts_
408519 xoops 192.168.0.1 baby_kingd 10 Query SELECT p.*,
t.post_text FROM xoops_bb_posts p, xoops_bb_posts_
408581 xoops 192.168.0.1 baby_kingd 10 Query SELECT p.*,
t.post_text FROM xoops_bb_posts p, xoops_bb_posts_
408604 xoops 192.168.0.1 baby_kingd 10 Query SELECT uname
FROM xoops_users WHERE uid = 793
408605 xoops 192.168.0.1 baby_kingd 10 Query SELECT * FROM
xoops_users WHERE uid=3089
408490 xoops 192.168.0.1 baby_kingd 11 Query SELECT p.*,
t.post_text FROM xoops_bb_posts p, xoops_bb_posts_
408492 xoops 192.168.0.1 baby_kingd 11 Query SELECT p.*,
t.post_text FROM xoops_bb_posts p, xoops_bb_posts_
408497 xoops 192.168.0.1 baby_kingd 11 Query SELECT p.*,
t.post_text FROM xoops_bb_posts p, xoops_bb_posts_
408592 xoops 192.168.0.1 baby_kingd 11 Query SELECT t.*,
u.uname, u2.uname as last_poster, p.post_time as l
408599 xoops 192.168.0.1 baby_kingd 11 Query SELECT t.*,
u.uname, u2.uname as last_poster, p.post_time as l
408600 xoops 192.168.0.1 baby_kingd 11 Query SELECT t.*,
u.uname, u2.uname as last_poster, p.post_time as l
408602 xoops 192.168.0.1 baby_kingd 11 Query SELECT * FROM
xoops_users WHERE uid=3819
TOP
------
12:10:01 up 2 days, 8:52, 1 user, load average: 4.15, 6.79, 7.02
40 processes: 38 sleeping, 2 running, 0 zombie, 0 stopped
CPU0 states: 67.2% user 25.4% system 0.0% nice 0.0% iowait 6.4%
idle
CPU1 states: 10.0% user 7.3% system 0.0% nice 0.0% iowait 82.2%
idle
Mem: 2064500k av, 1130168k used, 934332k free, 0k shrd, 62876k
buff
933640k actv, 49892k in_d, 26704k in_c
Swap: 2040244k av, 36k used, 2040208k free 684320k
cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
3138 mysql 15 0 264M 264M 2232 S 99.9 13.0 1792m 0 mysqld-max
SAR
------
07:00:00 HK all 0.66 0.00 0.47 98.87
07:10:00 HK all 5.04 0.00 1.89 93.06
07:20:00 HK all 4.85 0.00 2.23 92.92
07:30:00 HK all 2.25 0.00 1.15 96.60
07:40:00 HK all 2.88 0.00 1.34 95.78
07:50:00 HK all 2.00 0.00 1.12 96.88
08:00:00 HK all 4.09 0.00 1.86 94.05
08:10:00 HK all 4.90 0.00 2.38 92.72
08:20:00 HK all 3.40 0.00 1.73 94.88
08:30:00 HK all 2.65 0.00 1.42 95.92
08:40:00 HK all 4.25 0.00 1.98 93.77
08:50:00 HK all 5.92 0.00 2.74 91.34
09:00:00 HK all 7.57 0.00 3.36 89.07
09:10:00 HK all 12.44 0.00 5.50 82.06
09:20:00 HK all 32.86 0.00 14.31 52.84
09:30:00 HK all 24.03 0.00 9.57 66.40
09:40:00 HK all 31.26 0.00 13.83 54.91
09:50:00 HK all 43.56 0.00 21.61 34.83
10:00:03 HK all 42.48 0.00 19.74 37.78
10:10:00 HK all 38.89 0.00 19.31 41.80
10:10:00 HK CPU %user %nice %system %idle
10:20:00 HK all 42.50 0.00 20.21 37.29
10:30:00 HK all 35.91 0.00 16.64 47.45
10:40:00 HK all 46.26 0.00 22.03 31.72
10:50:00 HK all 43.28 0.00 19.37 37.35
11:00:00 HK all 35.22 0.00 16.42 48.36
11:10:00 HK all 33.16 0.00 15.55 51.29
11:20:00 HK all 36.43 0.00 16.64 46.93
11:30:00 HK all 35.75 0.00 16.84 47.41
11:40:00 HK all 39.29 0.00 18.52 42.20
11:50:00 HK all 38.25 0.00 19.21 42.54
12:00:01 HK all 57.51 0.00 28.40 14.09
12:10:00 HK all 50.69 0.00 26.55 22.76
Average: all 15.37 0.00 7.33 77.30
----- Original Message -----
From: "David Griffiths" <[EMAIL PROTECTED]>
To: "Rainer Sip" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Wednesday, October 22, 2003 3:11 AM
Subject: Re: Mysql Performance Question
> You need to figure out what's slowing down your application. It could be
> expensive queries which in turn could be caused by missing indexes. It
could
> be that the machine is too slow or the configuration of MySQL is
> sub-optimal.
>
> In this case, the machine looks fine.
>
> I can't comment on queries or indexes.
>
> Your tuning, I can.
>
> set-variable = key_buffer=512M
>
> This one is important; it caches data from the database in memory. How big
> is your database? Is it bigger than 512 meg? If so, is there free memory
on
> the machine? If so, I'd recommend bumping it up as much as you can; it's
> faster to get data from memory than it is from disk.
>
>
> set-variable = sort_buffer=16M
>
> This is used to sort the results of queries; it's per-connection. I
believe
> it is allocated on an as-needed basis. Regardless, are you doing a lot of
> ORDER BY/GROUP BY in your queries? This might be a little high.
>
>
> set-variable = table_cache=1024
>
> How many tables do you have? This tells the database how many tables to
keep
> open. It probably won't make much of a difference lowering it.
>
>
> set-variable = join_buffer=8M
>
> This one is used to join tables where no indexes exist. In otherwords, if
> you're using indexes, it won't be used.
>
>
> set-variable = record_buffer=8M
>
> This is used for reading in rows after a sort (from the sort_buffer).
Again,
> per client. Do you need it?
>
>
> set-variable = query_cache_size=6M
>
> This one can be a waste of memory, or a huge bonus. Queries and their
result
> sets are stored here. If you need to run the same query a second time, the
> database just pulls the results from the cache.
>
> There are a few issues, tho.
>
> If you have a query,
>
> "SELECT * FROM table_1 WHERE condition_1 = 12"
>
> then the result of the query will be stored. But if the next statement is,
> "UPDATE TABLE table_1..." then the data in the cache relating to table_1
> have to be unloaded, as the UPDATE statement could have invalidated all of
> it.
>
> But, if you have some stock queries that constantly read data from tables
> that, in the business logic of your application, are read-only (ie you
> rarely, if ever, update the data in them), then the query cache can be a
big
> bonus.
>
> Another interesting note is the query_cache_type variable. You can set it
to
> 2 in the my.cnf file; this means that in your select statements, you add a
> hint to tell the database to cache or not cache the results of the query.
So
> if you know a query and it's results are very dynamic, then it's not much
> use to use the query cache and you can tell the database to not put it in
> the query cache. The SELECT statement would look like, 'SELECT SQL_CACHE *
> FROM table_1 WHERE...'. Here's the page:
> http://www.mysql.com/doc/en/Query_Cache.html
>
>
>
> Next, moving to InnoDB. I found that, properly tuned, InnoDB is almost as
> fast as MyISAM. You'll want to set the innodb_buffer_pool_size as large as
> possible (it's the MyISAM key_buffer) to cache as much data.
>
> You'll need to set up a tablespace; you can put them all over your disks.
> Finally, play with innodb_flush_method. I set it to O_DSYNC and got a
> substantial increase in performance. Search the MySQL list-archives for
> discussions on the options, or check out http://www.innodb.com
>
>
> Hope that helps,
> David
>
>
>
> ----- Original Message -----
> From: "Rainer Sip" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Tuesday, October 21, 2003 9:48 AM
> Subject: Mysql Performance Question
>
>
> I'm running a community site (Xoops) on Mysql 4.0.14.
>
> I found that the speed of my site is slow during peak hours, when there
> are 450 concurrent uers hanging on the site. Mytop showed that the
> queries per second maxed at 500. I believe this could be higher,
> provided that I have it running on a dedicated machine. I also noticed
> the load average is very high (12+ during peak hours)
>
> In the mid run I'm planning to mirgrate to innodb for higher concurrency
> (I'm currently using myisam). However, I'm seeking suggestions in fine
> tuning the parameters.
>
> The machine is a Dell PowerEdge 4400 with 2 Xeon 1G processors and 2GB
> of memory. There are 4 disks running raid 0+1. Attached the my.cnf for
> your easy review. Thanks a lot in advance.
>
> Cheers,
> Rainer
>
>
> [client]
> socket=/var/lib/mysql/mysql.sock
>
> [client]
> socket=/var/lib/mysql/mysql.sock
>
> [mysqld]
> datadir=/var/lib/mysql
> socket=/var/lib/mysql/mysql.sock
> skip-locking
> set-variable = key_buffer=512M
> set-variable = max_allowed_packet=1M
> set-variable = table_cache=1024
> set-variable = sort_buffer=16M
> set-variable = join_buffer=8M
> set-variable = record_buffer=8M
> set-variable = thread_cache=512
> set-variable = max_connections=500
> set-variable = tmp_table_size=128M
> set-variable = thread_concurrency=4
> set-variable = myisam_sort_buffer_size=512M
> set-variable = query_cache_size=6M
> #set-variable = max_connect_errors=1000
> #set-variable = back_log=100
> #log-bin
> skip-innodb
>
> [mysql.server]
> user=mysql
> basedir=/var/lib
>
> [safe_mysqld]
> err-log=/var/log/mysqld.log
> pid-file=/var/run/mysqld/mysqld.pid
>
> [mysqldump]
> quick
> set-variable = max_allowed_packet=16M
>
> [mysql]
> no-auto-rehash
> # Remove the next comment character if you are not familiar with SQL
> #safe-updates
>
> [isamchk]
> set-variable = key_buffer=256M
> set-variable = sort_buffer=124M
> set-variable = read_buffer=2M
> set-variable = write_buffer=2M
>
> [myisamchk]
> set-variable = key_buffer=256M
> set-variable = sort_buffer=256M
> set-variable = read_buffer=8M
> set-variable = write_buffer=4M
>
> [mysqlhotcopy]
> interactive-timeout
>
> --
> 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]
