Very strange and sporadic performance issues..

[geniusj]

I have a site with about 200-600 concurrent users (40-60 queries per second) on at any given time (usually about 350). I am running MySQL using InnoDB tables. I get good performance at first (for the first 40 minutes or so), but then it just tumbles out of control and gets caught in a endless cycle of catch-up. It doesn't make any sense because it's performing beautifully up until that point! There is never any huge increase in load to justify it either. Has anybody else seen this?

My relevant config is as follows:

FreeBSD ods.org 4.5-RELEASE FreeBSD 4.5-RELEASE #0: Thu Jan 31 04:22:05 EST 2002 geniusj@ods.org:/usr/src/sys/compile/ODS i386

[mysqld]
innodb_data_home_dir = /usr/MYSQL
innodb_data_file_path = ibdata1:5000M
set-variable = innodb_buffer_pool_size=200M
set-variable = innodb_additional_mem_pool_size=75M
innodb_log_group_home_dir = /disk/d01/mysql/iblogs
innodb_log_arch_dir = /disk/d01/mysql/iblogs
innodb_log_archive=0
set-variable = innodb_log_files_in_group=3
set-variable = innodb_log_file_size=80M
set-variable = innodb_log_buffer_size=24M
innodb_flush_log_at_trx_commit=0
#innodb_flush_method=fdatasync
set-variable = innodb_file_io_threads=4
set-variable = innodb_lock_wait_timeout=50
set-variable = max_connections=500
set-variable = thread_concurrency=2
set-variable = thread_cache_size=256


+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 765 |
| Aborted_connects | 1 |
| Bytes_received | 65764487 |
| Bytes_sent | 1133408039 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 17232 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_delete | 4660 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_insert | 5719 |
| Com_insert_select | 59 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 180 |
| Com_replace_select | 6 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_select | 352991 |
| Com_set_option | 66 |
| Com_show_binlogs | 0 |
| Com_show_create | 66 |
| Com_show_databases | 0 |
| Com_show_fields | 66 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_stat | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_stat | 0 |
| Com_show_status | 6876 |
| Com_show_tables | 4 |
| Com_show_variables | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 68602 |
| Connections | 23885 |
| Created_tmp_disk_tables | 55 |
| Created_tmp_tables | 20143 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 4558 |
| Handler_read_first | 103500 |
| Handler_read_key | 7467844 |
| Handler_read_next | 360270674 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 1950583 |
| Handler_read_rnd_next | 272552987 |
| Handler_update | 12846 |
| Handler_write | 4067830 |
| Key_blocks_used | 7793 |
| Key_read_requests | 169154 |
| Key_reads | 43271 |
| Key_write_requests | 20461 |
| Key_writes | 19820 |
| Max_used_connections | 210 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 64 |
| Open_files | 6 |
| Open_streams | 0 |
| Opened_tables | 75805 |
| Questions | 472755 |
| Select_full_join | 48 |
| Select_full_range_join | 1 |
| Select_range | 43423 |
| Select_range_check | 0 |
| Select_scan | 118007 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 2152 |
| Sort_merge_passes | 0 |
| Sort_range | 46193 |
| Sort_rows | 1970243 |
| Sort_scan | 38144 |
| Table_locks_immediate | 492141 |
| Table_locks_waited | 498 |
| Threads_cached | 196 |
| Threads_created | 211 |
| Threads_connected | 15 |
| Threads_running | 1 |
| Uptime | 11676 |
+--------------------------+------------+

CPU utilization of mysql when it's functioning properly is between 10 and 20 percent.. snapshot:
6402 mysql 2 0 310M 195M poll 78:34 13.09% 13.09% mysqld

The machine is an Athlon 1.4GHz with 640MB of ram.

CPU does not seem to be the issue and I can't find the problem in any other hardware devices either.. I've tried tweaking concurrent_threads (gone everywhere from 2 to 500), and every other variable I could think of that might help. The problem seems to be completely sporadic though and I can't find why after running PERFECTLY for 30-40 minutes, it just falls behind majorly while the thread count builds and builds. I have persistant connections OFF because I seem to get better performance that way, but when it's running properly the number of therads hovers between 5 and 15 or so. When it spins out of control it reaches up to 200.

Has anyone had this problem? It's driving me NUTS!

[Freddie Bingham]

What version of vBulletin are you using?

[geniusj]

Oh, I thought that was in my profile :)..

Version 2.2.2

Thanks in advance,
-JD-

[nuno]

and have you followed John's instructions?
http://www.vbulletin.com/forum/showthread.php?postid=242990#post242990

[geniusj]

Originally posted by nuno
and have you followed John's instructions?
http://www.vbulletin.com/forum/showthread.php?postid=242990#post242990

Yes :).. I just double checked and did a diff on the one he posted and the one I have and they are indeed the same.

[nuno]

can you show us your mysql variables?

[geniusj]

Originally posted by nuno
can you show us your mysql variables?

Here they are:

+---------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| back_log | 50
| basedir | /usr/local/
| bdb_cache_size | 8388600
| bdb_log_buffer_size | 32768
| bdb_home | /var/db/mysql/
| bdb_max_lock | 10000
| bdb_logdir |
| bdb_shared_data | OFF
| bdb_tmpdir | /var/tmp/
| bdb_version | Sleepycat Software: Berkeley DB 3.2.9a: (December 27, 2001)
| binlog_cache_size | 32768
| character_set | latin1
| character_sets | latin1 dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 |
| concurrent_insert | ON
| connect_timeout | 5
| datadir | /var/db/mysql/
| delay_key_write | ON
| delayed_insert_limit | 100
| delayed_insert_timeout | 300
| delayed_queue_size | 1000
| flush | OFF
| flush_time | 0
| have_bdb | YES
| have_gemini | NO
| have_innodb | YES
| have_isam | YES
| have_raid | NO
| have_openssl | NO
| init_file |
| innodb_additional_mem_pool_size | 78643200
| innodb_buffer_pool_size | 209715200
| innodb_data_file_path | ibdata1
| innodb_data_home_dir | /usr/MYSQL
| innodb_file_io_threads | 4
| innodb_force_recovery | 0
| innodb_thread_concurrency | 8
| innodb_flush_log_at_trx_commit | OFF
| innodb_fast_shutdown | OFF
| innodb_flush_method |
| innodb_lock_wait_timeout | 50
| innodb_log_arch_dir | /disk/d01/mysql/iblogs
| innodb_log_archive | OFF
| innodb_log_buffer_size | 25165824
| innodb_log_file_size | 83886080
| innodb_log_files_in_group | 3
| innodb_log_group_home_dir | /disk/d01/mysql/iblogs
| innodb_mirrored_log_groups | 1
| interactive_timeout | 28800
| join_buffer_size | 131072
| key_buffer_size | 8388600
| language | /usr/local/share/mysql/english/
| large_files_support | ON
| log | OFF
| log_update | OFF
| log_bin | OFF
| log_slave_updates | OFF
| log_long_queries | OFF
| long_query_time | 10
| low_priority_updates | OFF
| lower_case_table_names | 0
| max_allowed_packet | 1048576
| max_binlog_cache_size | 4294967295
| max_binlog_size | 1073741824
| max_connections | 45 # lowered this recently so the DB could recover
| max_connect_errors | 10
| max_delayed_threads | 20
| max_heap_table_size | 16777216
| max_join_size | 4294967295
| max_sort_length | 1024
| max_user_connections | 0
| max_tmp_tables | 32
| max_write_lock_count | 4294967295
| myisam_max_extra_sort_file_size | 256
| myisam_max_sort_file_size | 2047
| myisam_recover_options | 0
| myisam_sort_buffer_size | 8388608
| net_buffer_length | 16384
| net_read_timeout | 30
| net_retry_count | 1000000
| net_write_timeout | 60
| open_files_limit | 0
| pid_file | /var/db/mysql/ods.pid
| port | 3306
| protocol_version | 10
| record_buffer | 131072
| record_rnd_buffer | 131072
| query_buffer_size | 0
| safe_show_database | OFF
| server_id | 0
| slave_net_timeout | 3600
| skip_locking | OFF
| skip_networking | OFF
| skip_show_database | OFF
| slow_launch_time | 2
| socket | /tmp/mysql.
| sort_buffer | 2097144
| sql_mode | 0
| table_cache | 64
| table_type | MYISAM
| thread_cache_size | 256
| thread_stack | 65536
| transaction_isolation | READ-COMMITTED
| timezone | EST
| tmp_table_size | 33554432
| tmpdir | /var/tmp/
| version | 3.23.47
| wait_timeout 28800

[nuno]

your table_cache is set too low, the number of open_tables is to high, and your uptime is less than a day. If you have more memory, up the table_cache value. :)

[geniusj]

Originally posted by nuno
your table_cache is set too low, the number of open_tables is to high, and your uptime is less than a day. If you have more memory, up the table_cache value. :)

How high would you recommend I raise it?

I can only guess ;(

[nuno]

http://www.vbulletin.org/forum/images/smilies/greedy.gif http://www.vbulletin.org/forum/images/smilies/greedy.gif http://www.vbulletin.org/forum/images/smilies/greedy.gif

Key_read_requests = 169154
Key_reads = 43271 :eek:

the ratio should be no more than 1:100, but also no much less than 1:100
yours - 1/4

Solution - increase key_buffer_size, that equals RAM:
key_buffer_size = 8388600
which means 8M
optimal for 640 MB of ram should be between 160(160000000) and 320(320000000) :)

[geniusj]

Originally posted by nuno
http://www.vbulletin.org/forum/images/smilies/greedy.gif http://www.vbulletin.org/forum/images/smilies/greedy.gif http://www.vbulletin.org/forum/images/smilies/greedy.gif

Key_read_requests = 169154
Key_reads = 43271 :eek:

the ratio should be no more than 1:100, but also no much less than 1:100
yours - 1/4

Solution - increase key_buffer_size, that equals RAM:
key_buffer_size = 8388600
which means 8M
optimal for 640 MB of ram should be between 160(160000000) and 320(320000000) :)

| innodb_additional_mem_pool_size | 78643200
| innodb_buffer_pool_size | 209715200

what are these for then? Is key_buffer_size only for MyISAM? The process is alraedy taking 380MB and they say for innodb to use between 50% and 80% of your memory for the innodb_buffer_pool .. Do you know if these are related?

If I make both of these their suggested sizes, then that's over the amount of memory that I have.

Cheers,
-JD-

[nuno]

paste this into your my.cnf file
set-variable = key_buffer=256M

[geniusj]

Originally posted by nuno
paste this into your my.cnf file
set-variable = key_buffer=256M

Thanks! I'm not as sure about the key_buffer size, but the table cache definitely seems to have helped. I raised it to 512 and the sporadic stuff seems to have gone :).. I'll definitely know when the peak time rolls around tomorrow..

Thanks,
-JD-

[eva2000]

actually your key_buffer = 256M would be too high

make sure

Warning: on Linux x86 you must be careful you do not set memory usage too high. glibc will allow the process heap to grow over thread stacks, which will crash your server. Make sure

innodb_buffer_pool_size + key_buffer +
max_connections * (sort_buffer + record_buffer) + max_connections * 1 MB

is significantly smaller than 2 GB

http://www.mysql.com/doc/I/n/InnoDB_start.html

also make sure what mysql table type your databases are using, if some databases are Myisam and some are Innobase you'd need to fine tune both myisam and innobase settings

also from the above url your

innodb_data_file_path is set too large

innodb_data_file_path

Paths to individual data files and their sizes. The full directory path to each data file is acquired by concatenating innodb_data_home_dir to the paths specified here. The file sizes are specified in megabytes, hence the 'M' after the size specification above. InnoDB also understands the abbreviation 'G', 1G meaning 1024M. Starting from 3.23.44 you can set the file size bigger than 4 GB on those operating systems which support big files. On some operating systems files must be < 2 GB. The sum of the sizes of the files must be at least 10 MB.

[eva2000]

Originally posted by geniusj
I have a site with about 200-600 concurrent users (40-60 queries per second) on at any given time (usually about 350). I am running MySQL using InnoDB tables. I get good performance at first (for the first 40 minutes or so), but then it just tumbles out of control and gets caught in a endless cycle of catch-up. It doesn't make any sense because it's performing beautifully up until that point! There is never any huge increase in load to justify it either. Has anybody else seen this?

My relevant config is as follows:

FreeBSD ods.org 4.5-RELEASE FreeBSD 4.5-RELEASE #0: Thu Jan 31 04:22:05 EST 2002 geniusj@ods.org:/usr/src/sys/compile/ODS i386

[mysqld]
innodb_data_home_dir = /usr/MYSQL
innodb_data_file_path = ibdata1:5000M
set-variable = innodb_buffer_pool_size=200M
set-variable = innodb_additional_mem_pool_size=75M
innodb_log_group_home_dir = /disk/d01/mysql/iblogs
innodb_log_arch_dir = /disk/d01/mysql/iblogs
innodb_log_archive=0
set-variable = innodb_log_files_in_group=3
set-variable = innodb_log_file_size=80M
set-variable = innodb_log_buffer_size=24M
innodb_flush_log_at_trx_commit=0
#innodb_flush_method=fdatasync
set-variable = innodb_file_io_threads=4
set-variable = innodb_lock_wait_timeout=50
set-variable = max_connections=500
set-variable = thread_concurrency=2
set-variable = thread_cache_size=256


also

1. max_connections is too low at 45 you need at least to set it to 500 or 650

2. your innodb_log_file_size and innodb_log_buffer_size need optimising

the innodb_log_file_size should be around 15% of your buffer pool size or 1/nth the size of buffer pool where nth is the number of log files in the group

i.e.

between 15% of 200M and 200M * 1/3 = 30 to 66M you currently have it as 80M

also the innodb_log_buffer_size should be from 1M to half the size of the combined log file sizes.. which i assume would be 15 - 33M

for the innodb stuff i'm just going from what is recommended here http://www.mysql.com/doc/I/n/InnoDB_start.html

might also want to read Innodb performance tuning tips here http://www.mysql.com/doc/S/E/SEC456.html

[geniusj]

Originally posted by eva2000
actually your key_buffer = 256M would be too high

make sure

Warning: on Linux x86 you must be careful you do not set memory usage too high. glibc will allow the process heap to grow over thread stacks, which will crash your server. Make sure

innodb_buffer_pool_size + key_buffer +
max_connections * (sort_buffer + record_buffer) + max_connections * 1 MB

is significantly smaller than 2 GB

http://www.mysql.com/doc/I/n/InnoDB_start.html

also make sure what mysql table type your databases are using, if some databases are Myisam and some are Innobase you'd need to fine tune both myisam and innobase settings

also from the above url your

innodb_data_file_path is set too large


I don't think there's a such thing as "too large" for innodb_data_file_path. My OS supports files greater than 2G, it is refering to an OS specific issue. As for glibc, this is a freebsd box :).. Perhaps I'm misunderstanding though. I'll look into the key_buffer size.

Thanks!
-JD-

[geniusj]

Originally posted by eva2000
actually your key_buffer = 256M would be too high


Hmm.. I tried your equation and I ended up at with :

209715200+268431360+100*(2097144+131072)+100*10240 00


Result: 803368160 (about 800MB)

This is significantly lower than 2GB right? :)

Or are you saying it's too high because we have 640MB of ram?

Cheers,
-JD-

[eva2000]

the above equation is for innobase specifically, the recommendation i make for lowering the key_buffer is for mysql's myisam database tables

if you have some myisam database tables as well as innobase you'll find that with 256M key_buffer you're going to end up swapping to disk when you reach 400 - 600+ max_connections (this is from observed experienced not theory)

if you output your extended-status from mysql and look at your max_used_connections that will show you your max concurrent connections to mysql

if thats around 400 - 600 i should you add more memory - 1.5 - 2gb more if you use innobase tables and maybe get a database server separate from the web server

as to the file size OS limitation i have no idea what that is in FreeBSD :o

also might want to look into Apache's httpd.conf and see what settings you have for minspareservers, max and start spare servers and maxclients as well

[geniusj]

Originally posted by eva2000
the above equation is for innobase specifically, the recommendation i make for lowering the key_buffer is for mysql's myisam database tables

if you have some myisam database tables as well as innobase you'll find that with 256M key_buffer you're going to end up swapping to disk when you reach 400 - 600+ max_connections (this is from observed experienced not theory)

So is keybuffer important with innobase? Or only with MyISAM?


if you output your extended-status from mysql and look at your max_used_connections that will show you your max concurrent connections to mysql

I'm not using persistent connections so fortunately it's not that high :)


if thats around 400 - 600 i should you add more memory - 1.5 - 2gb more if you use innobase tables and maybe get a database server separate from the web server

as to the file size OS limitation i have no idea what that is in FreeBSD :o


bluenugget% truncate -s 8191G test
bluenugget% ls -lh test
-rw-r--r-- 1 geniusj 8.0T Feb 9 05:10 test
bluenugget% truncate -s 8192G test
truncate: test: File too large

8192GB is limit :)


also might want to look into Apache's httpd.conf and see what settings you have for minspareservers, max and start spare servers and maxclients as well

Yea, I've tweaked these pretty good, apache should be fine.. (I also have lingerd running to take some load off of apache)

Cheers,
-JD-

[eva2000]

this is some expert advice straight from the author of Innobase himself, Heikki Tuuri ( http://www.innodb.com/background.html ) :)


The user should print SHOW PROCESSLIST during the slow performance, so that we see what queries are stuck. The user should also run the Unix 'top' during the slow performance and report to us the CPU usage %, so that we see if it is CPU-bound or disk-bound operation.

The user should run innodb_monitor, as explained in section 9.1 of the manual http://www.innodb.com/ibman.html so that we see if there is lock contention or semaphore contention.

vBulletin calculates COUNT(*) from big tables very often, I think. That could be the reason for excessive CPU or disk i/o usage.

What version of MySQL is the user running?

MySQL version 3.23.48, which was released yesterday, has some performance optimizations which might help. One could try setting

set-variable=innodb_thread_concurrency=2

in my.cnf. The default in .48 is 8.

Regards,

Heikki Tuuri
Innobase Oy