Rainer, To find out if there is any query that is taking too long to run, enable the "log-slow-queries" option and set "long_query_time" to some initial value like 5 or 10 (it's in seconds).
Then check your slow queries log file and try to understand why those queries are taking too much time to run. After you optimize those queries, lower the long_query_time value and keep looking at the slow queries log, until you get almost no slow queries or your long_query_time is near 1 (or zero!) If you didn't understand, just let me know :) Regards, Sergio. On Wed, 22 Oct 2003, Rainer Sip wrote: > 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]
