Depressing bad MYSQL performance

MySQL & MariaDB Other MySQL® Questions
1

I’ve been dealing with a extremely high load, of around 20.00+ on my server at almost all times, if i restart my server it will be on 1.00-2.00 for a few hours, then 2.00-8.00 for a few more hours, then eventually it will go to a maximum of 20.00 for an indefinate amount of time until i do a full reset of my server.

My current top is.

top - 10:32:37 up 2 days, 8:23, 1 user, load average: 18.87, 20.28, 20.90
Tasks: 131 total, 1 running, 130 sleeping, 0 stopped, 0 zombie
Cpu(s): 20.1% us, 7.6% sy, 0.0% ni, 21.6% id, 50.6% wa, 0.0% hi, 0.0% si
Mem: 4149100k total, 4123204k used, 25896k free, 189076k buffers
Swap: 2040244k total, 2780k used, 2037464k free, 3199640k cached

PID USER PR NI %CPU TIME+ %MEM VIRT RES SHR S COMMAND
1354 mysql 16 0 40 0:44.88 1.0 145m 42m 3840 S mysqld
8939 root 16 0 30 4:46.98 0.9 45844 37m 2520 S MailScanner
6437 root 17 0 28 4:52.23 0.9 45736 37m 2444 S MailScanner
6225 root 17 0 24 4:53.94 0.9 45840 37m 2520 S MailScanner
6092 root 16 0 20 4:53.85 0.9 45736 37m 2484 S MailScanner
8435 root 16 0 16 4:49.42 0.9 45736 37m 2484 S MailScanner
1035 apache 16 0 2 0:00.38 0.4 35528 14m 2920 S httpd
1427 apache 15 0 2 0:00.31 0.4 35604 15m 3768 S httpd
1429 apache 15 0 2 0:00.27 0.4 35512 14m 2820 S httpd
2936 apache 15 0 2 0:00.13 0.4 35532 14m 2800 S httpd
2937 apache 15 0 2 0:00.14 0.4 35528 14m 2816 S httpd
3004 apache 16 0 2 0:00.15 0.4 35520 14m 2804 S httpd
3718 apache 16 0 2 0:00.08 0.4 35508 14m 2792 S httpd
3727 apache 15 0 2 0:00.08 0.4 35528 14m 2792 S httpd
1 root 16 0 0 0:04.25 0.0 2524 548 468 S init
2 root RT 0 0 0:00.17 0.0 0 0 0 S migration/0
3 root 34 19 0 0:00.25 0.0 0 0 0 S ksoftirqd/0

I’ve activated slow mysql queries to try to find those and fix them, but the file reports nothing.

I’m kinda desperate here since all i have are 7-8 tables with 50,000 rows in them being accessed, and a DUAL XEON cloverfield server with 2gig memory and i’ve been told that i really shouldnt be having this problem.

Any help is appreciated.

2

OK, you seem to have 4GB RAM (according to your top) and the applications on your server is only using about 1GB right now.

But what is much more interesting is that you have a I/O-wait of 50%!!!

What kind of disk setup do you have in your server?

And how much data is your MailScanner’s processing?

Because the CPU % value for mysql can be misleading due to rounding errors since mysql is running a lot of threads within the same process.
But the fact that your mailscanners consume 15-30% cpu per each of the 5 processes is also a very big figure.

Attach a txt file with the output from SHOW STATUS; and SHOW VARIABLES; run in mysql and we can tell you if mysql has any obvious limitations in it’s current setup.

3

Is your apllication using information schema? Perhaps this bug is causing the performance issues: [URL]MySQL Bugs: #19588: INFORMATION_SCHEMA performs much too slow on large servers

4

I hardly send any mail from my server, since we have no specific client based mails (pop3’s) and the PHP sendmail only sends around 150 small emails a day (thank for you registering) emails.

My server/disk set up is :-

1 Dell \ 1066/1333FSB Dual Xeon \ PowerEdge 1950/2950
1 Intel \ 2.0 GHz 1333FSB - Woodcrest \ Xeon 5130 (Dual Core)
1 Dell \ 9G Drive Controller - SAS/SATA \ SAS 5/i
1 Maxtor \ 146GB:SAS:10K RPM \ Atlas 10K - SAS
1 XGeneric \ 4096 MB \ DDR2 667 FB DIMM

SHOW STATUSVariable_name ValueAborted_clients 0Aborted_connects 1Binlog_cache_disk_use 0Binlog_cache_use 0Bytes_received 123147659Bytes_sent 807131893Com_admin_commands 0Com_alter_db 0Com_alter_table 0Com_analyze 0Com_backup_table 0Com_begin 0Com_change_db 153749Com_change_master 0Com_check 0Com_checksum 0Com_commit 0Com_create_db 0Com_create_function 0Com_create_index 0Com_create_table 0Com_dealloc_sql 0Com_delete 784Com_delete_multi 0Com_do 0Com_drop_db 0Com_drop_function 0Com_drop_index 0Com_drop_table 0Com_drop_user 0Com_execute_sql 0Com_flush 0Com_grant 0Com_ha_close 0Com_ha_open 0Com_ha_read 0Com_help 0Com_insert 36348Com_insert_select 0Com_kill 0Com_load 0Com_load_master_data 0Com_load_master_table 0Com_lock_tables 0Com_optimize 0Com_preload_keys 0Com_prepare_sql 0Com_purge 0Com_purge_before_date 0Com_rename_table 0Com_repair 0Com_replace 1Com_replace_select 0Com_reset 0Com_restore_table 0Com_revoke 0Com_revoke_all 0Com_rollback 0Com_savepoint 0Com_select 1071138Com_set_option 10Com_show_binlog_events 0Com_show_binlogs 0Com_show_charsets 2Com_show_collations 2Com_show_column_types 0Com_show_create_db 0Com_show_create_table 0Com_show_databases 2Com_show_errors 0Com_show_fields 0Com_show_grants 1Com_show_innodb_status 0Com_show_keys 0Com_show_logs 0Com_show_master_status 0Com_show_ndb_status 0Com_show_new_master 0Com_show_open_tables 0Com_show_privileges 0Com_show_processlist 0Com_show_slave_hosts 0Com_show_slave_status 0Com_show_status 1Com_show_storage_engines 0Com_show_tables 1Com_show_variables 4Com_show_warnings 0Com_slave_start 0Com_slave_stop 0Com_stmt_close 0Com_stmt_execute 0Com_stmt_prepare 0Com_stmt_reset 0Com_stmt_send_long_data 0Com_truncate 0Com_unlock_tables 0Com_update 151029Com_update_multi 0Connections 153750Variable_name ValueCreated_tmp_disk_tables 0Created_tmp_files 10Created_tmp_tables 876Delayed_errors 0Delayed_insert_threads 0Delayed_writes 0Flush_commands 1Handler_commit 0Handler_delete 2079Handler_discover 0Handler_read_first 1279Handler_read_key 46865239Handler_read_next 5619791Handler_read_prev 1734386Handler_read_rnd 311211Handler_read_rnd_next 1526821854Handler_rollback 0Handler_update 100760Handler_write 45422081Key_blocks_not_flushed 0Key_blocks_unused 3149Key_blocks_used 4169Key_read_requests 142883373Key_reads 8757Key_write_requests 104301Key_writes 69051Max_used_connections 52Not_flushed_delayed_rows 0Open_files 115Open_streams 0Open_tables 64Opened_tables 3435Qcache_free_blocks 0Qcache_free_memory 0Qcache_hits 0Qcache_inserts 0Qcache_lowmem_prunes 0Qcache_not_cached 0Qcache_queries_in_cache 0Qcache_total_blocks 0Questions 1566837Rpl_status NULLSelect_full_join 0Select_full_range_join 0Select_range 575Select_range_check 0Select_scan 176615Slave_open_temp_tables 0Slave_retried_transactions 0Slave_running OFFSlow_launch_threads 0Slow_queries 505Sort_merge_passes 6Sort_range 434Sort_rows 760266Sort_scan 2327Ssl_accept_renegotiates 0Ssl_accepts 0Ssl_callback_cache_hits 0Ssl_cipher Ssl_cipher_list Ssl_client_connects 0Ssl_connect_renegotiates 0Ssl_ctx_verify_depth 0Ssl_ctx_verify_mode 0Ssl_default_timeout 0Ssl_finished_accepts 0Ssl_finished_connects 0Ssl_session_cache_hits 0Ssl_session_cache_misses 0Ssl_session_cache_mode NONESsl_session_cache_overflows 0Ssl_session_cache_size 0Ssl_session_cache_timeouts 0Ssl_sessions_reused 0Ssl_used_session_cache_entries 0Ssl_verify_depth 0Ssl_verify_mode 0Ssl_version Table_locks_immediate 1258552Table_locks_waited 6141Threads_cached 0Threads_connected 1Threads_created 153749Threads_running 1Uptime 9403SHOW VARIABLESback_log 50basedir /usr/bdb_cache_size 8388600bdb_home bdb_log_buffer_size 0bdb_logdir bdb_max_lock 10000bdb_shared_data OFFbdb_tmpdir binlog_cache_size 32768bulk_insert_buffer_size 8388608character_set_client utf8character_set_connection utf8character_set_database latin1character_set_results utf8character_set_server latin1character_set_system utf8character_sets_dir /usr/share/mysql/charsets/collation_connection utf8_unicode_cicollation_database latin1_swedish_cicollation_server latin1_swedish_ciconcurrent_insert ONconnect_timeout 5datadir /var/lib/mysql/date_format %Y-%m-%ddatetime_format %Y-%m-%d %H:%i:%sdefault_week_format 0delay_key_write ONdelayed_insert_limit 100delayed_insert_timeout 300delayed_queue_size 1000expire_logs_days 0flush OFFflush_time 0ft_boolean_syntax + -><()~*:“”&|ft_max_word_len 84ft_min_word_len 4ft_query_expansion_limit 20ft_stopword_file (built-in)group_concat_max_len 1024have_archive NOhave_bdb DISABLEDhave_blackhole_engine NOhave_compress YEShave_crypt YEShave_csv NOhave_example_engine NOhave_geometry YEShave_innodb YEShave_isam YEShave_ndbcluster NOhave_openssl YEShave_query_cache YEShave_raid NOhave_rtree_keys YEShave_symlink YESinit_connect init_file init_slave innodb_additional_mem_pool_size 1048576innodb_autoextend_increment 8innodb_buffer_pool_awe_mem_mb 0innodb_buffer_pool_size 8388608innodb_data_file_path ibdata1:10M:autoextendinnodb_data_home_dir innodb_fast_shutdown ONinnodb_file_io_threads 4innodb_file_per_table OFFinnodb_flush_log_at_trx_commit 1innodb_flush_method innodb_force_recovery 0innodb_lock_wait_timeout 50innodb_locks_unsafe_for_binlog OFFinnodb_log_arch_dir innodb_log_archive OFFinnodb_log_buffer_size 1048576innodb_log_file_size 5242880innodb_log_files_in_group 2innodb_log_group_home_dir ./innodb_max_dirty_pages_pct 90innodb_max_purge_lag 0innodb_mirrored_log_groups 1innodb_open_files 300innodb_table_locks ONinnodb_thread_concurrency 8interactive_timeout 28800join_buffer_size 131072key_buffer_size 8388600key_cache_age_threshold 300key_cache_block_size 1024key_cache_division_limit 100language /usr/share/mysql/english/large_files_support ONlicense GPLlocal_infile ONlocked_in_memory OFFlog OFFlog_bin OFFlog_error log_slave_updates OFFVariable_name Valuelog_slow_queries ONlog_update OFFlog_warnings 1long_query_time 1low_priority_updates OFFlower_case_file_system OFFlower_case_table_names 0max_allowed_packet 1048576max_binlog_cache_size 4294967295max_binlog_size 1073741824max_connect_errors 10max_connections 100max_delayed_threads 20max_error_count 64max_heap_table_size 16777216max_insert_delayed_threads 20max_join_size 4294967295max_length_for_sort_data 1024max_prepared_stmt_count 16382max_relay_log_size 0max_seeks_for_key 4294967295max_sort_length 1024max_tmp_tables 32max_user_connections 0max_write_lock_count 4294967295myisam_data_pointer_size 4myisam_max_extra_sort_file_size 2147483648myisam_max_sort_file_size 2147483647myisam_recover_options OFFmyisam_repair_threads 1myisam_sort_buffer_size 8388608myisam_stats_method nulls_unequalnet_buffer_length 16384net_read_timeout 30net_retry_count 10net_write_timeout 60new OFFold_passwords ONopen_files_limit 1024pid_file /var/run/mysqld_app_init/mysqld.pidport 3306preload_buffer_size 32768prepared_stmt_count 0protocol_version 10query_alloc_block_size 8192query_cache_limit 1048576query_cache_min_res_unit 4096query_cache_size 0query_cache_type ONquery_cache_wlock_invalidate OFFquery_prealloc_size 8192range_alloc_block_size 2048read_buffer_size 131072read_only OFFread_rnd_buffer_size 262144relay_log_purge ONrelay_log_space_limit 0rpl_recovery_rank 0secure_auth OFFserver_id 0skip_external_locking ONskip_networking OFFskip_show_database OFFslave_net_timeout 3600slave_transaction_retries 0slow_launch_time 2socket /home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql…sort_buffer_size 2097144sql_mode sql_notes ONsql_warnings ONstorage_engine MyISAMsync_binlog 0sync_frm ONsync_replication 0sync_replication_slave_id 0sync_replication_timeout 0system_time_zone GMTtable_cache 64table_type MyISAMthread_cache_size 0thread_stack 196608time_format %H:%i:%stime_zone SYSTEMtmp_table_size 33554432tmpdir transaction_alloc_block_size 8192transaction_prealloc_size 4096tx_isolation REPEATABLE-READversion 4.1.20-logversion_bdb Sleepycat Software: Berkeley DB 4.1.24: (May 24, 2…version_comment Source distributionversion_compile_machine i686version_compile_os redhat-linux-gnuwait_timeout 28800

Here as requested are the outputs of STATUS and VARIABLES i used Phpmyadmin while in the specific database’s controls to get these.

5

OK, here are some comments:
1.
You have about 10% of the queries that performs a table scan on the table. But these queries are probably executed under 1 second and that is probably why you don’t see them in the slow query log.
Read the manual about the slow query log settings. So that you can also see the queries that doesn’t use index.

Are you using InnoDB or MyISAM tables for your tables?
Because you seem to be using default settings for the cache so that value could be increased a bit but which variable to change depends on which storage engine you are using.

Are the MailScanner processes always there consuming so much CPU?
Or was that just in that screen shot?
If they are always there consuming so much CPU, although you say that you only have about 150 small mails a day, I would check so that your sendmail isn’t misconfigured and acts as an open mail relay which forwards all kinds of spam mail through it.

6

Im currently using MyISAM tables.

Mailscanner tends to be up there around 80% of the time.

I have nothing coming up on my slow query log after days and set to 1 second.

Any recommendations based on that on what i can do?

7

I would suggest that you start by stopping the MTA(sendmail) and the mailscanner. Or is it primarily the mailscanner that uses the mysql database?
Something like:
/etc/init.d/MailScanner stop
/etc/init.d/sendmail stop
should work.
And then see what happens to the load on the server.
Because right now I think the mailscanner is imposing a lot more load on the server than mysql.

For the MyISAM tables you should check the mysql data dir (usually /var/lib/mysql/[databasename] if it is a mysql that comes with the distribution).
And roughly calculate the sum of the .MYI files. That is the total index size of the DB. And if that size is less than 25% of the free RAM then you should set the value to approximately this.
Otherwise you set the key_buffer_size to 25% of the free RAM on your server.
Then you add something like:
key_buffer_size=128M
in your /etc/my.cnf file.

For the slow query, try some of the alternatives for slow query log:
–log-long-format
or
–log-queries-not-using-indexes

Because you want to get the queries that doesn’t use index but still takes less than one second to execute.

8

I turned off sendmail and mailscanner and now the load is around 0.50-1.00, althrough i did do abit of MYSQL optimiziation after i learnt a few things earlier today.

Turning them both on makes the load start to rise alot, any ideas what this could be? Like i said, i hardly use sendmail compared to anything else, especially enough to make this monster server raise by like 600% use.

9

I would suggest that you check the log files for what sendmail and the mailscanner is up to.
Either:
/var/log/syslog
or
/var/log/mail/[something]

Because my guess is that you:
1.
Your host is wide open and acts as an open relay and a lot of spammers is using it to send a lot of mail through it.

OR
2.
You have managed to create a mail loop in your mail configuration so that each incoming mail is continuing to be circling around in your server.

Either way you should go to the bottom with this before you look any further at MySQL.

10

Ever time i turn sendmail off now, it returns to 0.15-0.50 load from 6.00 so there is definately something wrong with it.

I have used

#mailq -v

and all i get is

/home/virtual/FILESYSTEMTEMPLATE/services/sendmail/mqueue (1 request)
-----Q-ID----- --Size-- -Priority- —Q-Time— --------Sender/Recipient--------
m2HLbB3h001700 304 930724+Mar 17 21:39 <apache&#64;mywebsite.theplanet.host>
(Deferred: 421 Refused. The domain of your sender address has no mail exchanger (MX).)
<alexVelinov&#64;data.bg>
(Deferred: 421 Refused. The domain of your sender address has no mail exchanger (MX).)
Total requests: 1

I dont suppose you can help me with sendmail problems since this is a MYSQL forum?

11

Well I could actually, but not on this forum.

For two reasons: It does not belong here and it will take a lot of time to try to figure out what the problem is.

I’m sending you a private message instead.