3

I have two servers: a VPS and a laptop. I recently re-built both of them, and MySQL is running about 20 times slower on the laptop.

Both servers used to run CentOS 5.8 and I think MySQL 5.1, and the laptop used to do great so I do not think it is the hardware.

For the VPS, my provider installed CentOS 6.4, and then I installed MySQL 5.1.69 using yum with the CentOS repo.

For the laptop, I installed CentOS 6.4 basic server and then installed MySQL 5.1.69 using yum with the CentOS repo.

my.cnf for both servers are identical, and I have shown below. For both servers, I've also included below the output from SHOW VARIABLES; as well as output from sysbench, file system information, and cpu information. I have tried adding skip-name-resolve, but it didn't help.

The matrix below shows the SHOW VARIABLES output from both servers which is different. Again, MySQL was installed the same way, so I do not know why it is different, but it is and I think this might be why the laptop is executing MySQL so slowly.

Why is the laptop running MySQL slowly, and how do I fix it?

Differences between SHOW VARIABLES on both servers

+---------------------------+-----------------------+-------------------------+
|         Variable          |       Value-VPS       |      Value-Laptop       |
+---------------------------+-----------------------+-------------------------+
| hostname                  | vps.site1.com         | laptop.site2.com        |
| max_binlog_cache_size     | 4294963200            | 18446744073709500000    |
| max_seeks_for_key         | 4294967295            | 18446744073709500000    |
| max_write_lock_count      | 4294967295            | 18446744073709500000    |
| myisam_max_sort_file_size | 2146435072            | 9223372036853720000     |
| myisam_mmap_size          | 4294967295            | 18446744073709500000    |
| plugin_dir                | /usr/lib/mysql/plugin | /usr/lib64/mysql/plugin |
| pseudo_thread_id          | 7568                  | 2                       |
| system_time_zone          | EST                   | PDT                     |
| thread_stack              | 196608                | 262144                  |
| timestamp                 | 1372252112            | 1372252046              |
| version_compile_machine   | i386                  | x86_64                  |
+---------------------------+-----------------------+-------------------------+

my.cnf for both servers

[root@server1 ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

innodb_strict_mode=on
sql_mode=TRADITIONAL
# sql_mode=STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE
character-set-server=utf8
collation-server=utf8_general_ci
log=/var/log/mysqld_all.log

[root@server1 ~]#

VPS SHOW VARIABLES Info

Same as Laptop shown below but changes per above matrix (removed to allow me to be under the 30000 characters as required by ServerFault)

Laptop SHOW VARIABLES Info

auto_increment_increment    1
auto_increment_offset   1
autocommit  ON
automatic_sp_privileges ON
back_log    50
basedir /usr/
big_tables  OFF
binlog_cache_size   32768
binlog_direct_non_transactional_updates OFF
binlog_format   STATEMENT
bulk_insert_buffer_size 8388608
character_set_client    utf8
character_set_connection    utf8
character_set_database  latin1
character_set_filesystem    binary
character_set_results   utf8
character_set_server    latin1
character_set_system    utf8
character_sets_dir  /usr/share/mysql/charsets/
collation_connection    utf8_general_ci
collation_database  latin1_swedish_ci
collation_server    latin1_swedish_ci
completion_type 0
concurrent_insert   1
connect_timeout 10
datadir /var/lib/mysql/
date_format %Y-%m-%d
datetime_format %Y-%m-%d %H:%i:%s
default_week_format 0
delay_key_write ON
delayed_insert_limit    100
delayed_insert_timeout  300
delayed_queue_size  1000
div_precision_increment 4
engine_condition_pushdown   ON
error_count 0
event_scheduler OFF
expire_logs_days    0
flush   OFF
flush_time  0
foreign_key_checks  ON
ft_boolean_syntax   + -><()~*:""&|
ft_max_word_len 84
ft_min_word_len 4
ft_query_expansion_limit    20
ft_stopword_file    (built-in)
general_log OFF
general_log_file    /var/run/mysqld/mysqld.log
group_concat_max_len    1024
have_community_features YES
have_compress   YES
have_crypt  YES
have_csv    YES
have_dynamic_loading    YES
have_geometry   YES
have_innodb YES
have_ndbcluster NO
have_openssl    DISABLED
have_partitioning   YES
have_query_cache    YES
have_rtree_keys YES
have_ssl    DISABLED
have_symlink    DISABLED
hostname    server1.site2.com
identity    0
ignore_builtin_innodb   OFF
init_connect    
init_file   
init_slave  
innodb_adaptive_hash_index  ON
innodb_additional_mem_pool_size 1048576
innodb_autoextend_increment 8
innodb_autoinc_lock_mode    1
innodb_buffer_pool_size 8388608
innodb_checksums    ON
innodb_commit_concurrency   0
innodb_concurrency_tickets  500
innodb_data_file_path   ibdata1:10M:autoextend
innodb_data_home_dir    
innodb_doublewrite  ON
innodb_fast_shutdown    1
innodb_file_io_threads  4
innodb_file_per_table   OFF
innodb_flush_log_at_trx_commit  1
innodb_flush_method 
innodb_force_recovery   0
innodb_lock_wait_timeout    50
innodb_locks_unsafe_for_binlog  OFF
innodb_log_buffer_size  1048576
innodb_log_file_size    5242880
innodb_log_files_in_group   2
innodb_log_group_home_dir   ./
innodb_max_dirty_pages_pct  90
innodb_max_purge_lag    0
innodb_mirrored_log_groups  1
innodb_open_files   300
innodb_rollback_on_timeout  OFF
innodb_stats_method nulls_equal
innodb_stats_on_metadata    ON
innodb_support_xa   ON
innodb_sync_spin_loops  20
innodb_table_locks  ON
innodb_thread_concurrency   8
innodb_thread_sleep_delay   10000
innodb_use_legacy_cardinality_algorithm ON
insert_id   0
interactive_timeout 28800
join_buffer_size    131072
keep_files_on_create    OFF
key_buffer_size 8384512
key_cache_age_threshold 300
key_cache_block_size    1024
key_cache_division_limit    100
language    /usr/share/mysql/english/
large_files_support ON
large_page_size 0
large_pages OFF
last_insert_id  0
lc_time_names   en_US
license GPL
local_infile    ON
locked_in_memory    OFF
log OFF
log_bin OFF
log_bin_trust_function_creators OFF
log_bin_trust_routine_creators  OFF
log_error   /var/log/mysqld.log
log_output  FILE
log_queries_not_using_indexes   OFF
log_slave_updates   OFF
log_slow_queries    OFF
log_warnings    1
long_query_time 10.000000
low_priority_updates    OFF
lower_case_file_system  OFF
lower_case_table_names  0
max_allowed_packet  1048576
max_binlog_cache_size   18446744073709547520
max_binlog_size 1073741824
max_connect_errors  10
max_connections 151
max_delayed_threads 20
max_error_count 64
max_heap_table_size 16777216
max_insert_delayed_threads  20
max_join_size   18446744073709551615
max_length_for_sort_data    1024
max_long_data_size  1048576
max_prepared_stmt_count 16382
max_relay_log_size  0
max_seeks_for_key   18446744073709551615
max_sort_length 1024
max_sp_recursion_depth  0
max_tmp_tables  32
max_user_connections    0
max_write_lock_count    18446744073709551615
min_examined_row_limit  0
multi_range_count   256
myisam_data_pointer_size    6
myisam_max_sort_file_size   9223372036853727232
myisam_mmap_size    18446744073709551615
myisam_recover_options  OFF
myisam_repair_threads   1
myisam_sort_buffer_size 8388608
myisam_stats_method nulls_unequal
myisam_use_mmap OFF
net_buffer_length   16384
net_read_timeout    30
net_retry_count 10
net_write_timeout   60
new OFF
old OFF
old_alter_table OFF
old_passwords   OFF
open_files_limit    1024
optimizer_prune_level   1
optimizer_search_depth  62
optimizer_switch    index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on
pid_file    /var/run/mysqld/mysqld.pid
plugin_dir  /usr/lib64/mysql/plugin
port    3306
preload_buffer_size 32768
profiling   OFF
profiling_history_size  15
protocol_version    10
pseudo_thread_id    3
query_alloc_block_size  8192
query_cache_limit   1048576
query_cache_min_res_unit    4096
query_cache_size    0
query_cache_type    ON
query_cache_wlock_invalidate    OFF
query_prealloc_size 8192
rand_seed1  
rand_seed2  
range_alloc_block_size  4096
read_buffer_size    131072
read_only   OFF
read_rnd_buffer_size    262144
relay_log   
relay_log_index 
relay_log_info_file relay-log.info
relay_log_purge ON
relay_log_space_limit   0
report_host 
report_password 
report_port 3306
report_user 
rpl_recovery_rank   0
secure_auth OFF
secure_file_priv    
server_id   0
skip_external_locking   ON
skip_name_resolve   OFF
skip_networking OFF
skip_show_database  OFF
slave_compressed_protocol   OFF
slave_exec_mode STRICT
slave_load_tmpdir   /tmp
slave_max_allowed_packet    1073741824
slave_net_timeout   3600
slave_skip_errors   OFF
slave_transaction_retries   10
slow_launch_time    2
slow_query_log  OFF
slow_query_log_file /var/run/mysqld/mysqld-slow.log
socket  /var/lib/mysql/mysql.sock
sort_buffer_size    2097144
sql_auto_is_null    ON
sql_big_selects ON
sql_big_tables  OFF
sql_buffer_result   OFF
sql_log_bin ON
sql_log_off OFF
sql_log_update  ON
sql_low_priority_updates    OFF
sql_max_join_size   18446744073709551615
sql_mode    
sql_notes   ON
sql_quote_show_create   ON
sql_safe_updates    OFF
sql_select_limit    18446744073709551615
sql_slave_skip_counter  
sql_warnings    OFF
ssl_ca  
ssl_capath  
ssl_cert    
ssl_cipher  
ssl_key 
storage_engine  MyISAM
sync_binlog 0
sync_frm    ON
system_time_zone    PDT
table_definition_cache  256
table_lock_wait_timeout 50
table_open_cache    64
table_type  MyISAM
thread_cache_size   0
thread_handling one-thread-per-connection
thread_stack    262144
time_format %H:%i:%s
time_zone   SYSTEM
timed_mutexes   OFF
timestamp   1372254399
tmp_table_size  16777216
tmpdir  /tmp
transaction_alloc_block_size    8192
transaction_prealloc_size   4096
tx_isolation    REPEATABLE-READ
unique_checks   ON
updatable_views_with_limit  YES
version 5.1.69
version_comment Source distribution
version_compile_machine x86_64
version_compile_os  redhat-linux-gnu
wait_timeout    28800
warning_count   0

VPS Sysbench Info

Deleted to stay under 30000 characters.

Laptop Sysbench Info

[root@server1 ~]# cat sysbench.txt
sysbench 0.4.12:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 8

Doing OLTP test.
Running mixed OLTP test
Doing read-only test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Threads started!
Time limit exceeded, exiting...
(last message repeated 7 times)
Done.

OLTP test statistics:
    queries performed:
        read:                            634718
        write:                           0
        other:                           90674
        total:                           725392
    transactions:                        45337  (755.56 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 634718 (10577.78 per sec.)
    other operations:                    90674  (1511.11 per sec.)

Test execution summary:
    total time:                          60.0048s
    total number of events:              45337
    total time taken by event execution: 479.4912
    per-request statistics:
         min:                                  2.04ms
         avg:                                 10.58ms
         max:                                 85.56ms
         approx.  95 percentile:              19.70ms

Threads fairness:
    events (avg/stddev):           5667.1250/42.18
    execution time (avg/stddev):   59.9364/0.00

[root@server1 ~]#

VPS File Info

[root@vps ~]# df -T
Filesystem    Type   1K-blocks      Used Available Use% Mounted on
/dev/simfs   simfs    20971520  16187440   4784080  78% /
none         tmpfs     6224432         4   6224428   1% /dev
none         tmpfs     6224432         0   6224432   0% /dev/shm
[root@vps ~]#

Laptop File Info

[root@server1 ~]# df -T
Filesystem    Type   1K-blocks      Used Available Use% Mounted on
/dev/mapper/vg_server1-lv_root
              ext4    72383800   4243964  64462860   7% /
tmpfs        tmpfs      956352         0    956352   0% /dev/shm
/dev/sdb1     ext4      495844     60948    409296  13% /boot
[root@server1 ~]#

VPS CPU Info

Removed to stay under the 30000 character limit required by ServerFault

Laptop CPU Info

[root@server1 ~]# cat /proc/cpuinfo
processor       : 0
vendor_id       : GenuineIntel
cpu family      : 6
model           : 15
model name      : Intel(R) Core(TM)2 Duo CPU     T7100  @ 1.80GHz
stepping        : 13
cpu MHz         : 800.000
cache size      : 2048 KB
physical id     : 0
siblings        : 2
core id         : 0
cpu cores       : 2
apicid          : 0
initial apicid  : 0
fpu             : yes
fpu_exception   : yes
cpuid level     : 10
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx lm constant_tsc arch_perfmon pebs bts rep_good aperfmperf pni dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm lahf_lm ida dts tpr_shadow vnmi flexpriority
bogomips        : 3591.39
clflush size    : 64
cache_alignment : 64
address sizes   : 36 bits physical, 48 bits virtual
power management:

processor       : 1
vendor_id       : GenuineIntel
cpu family      : 6
model           : 15
model name      : Intel(R) Core(TM)2 Duo CPU     T7100  @ 1.80GHz
stepping        : 13
cpu MHz         : 800.000
cache size      : 2048 KB
physical id     : 0
siblings        : 2
core id         : 1
cpu cores       : 2
apicid          : 1
initial apicid  : 1
fpu             : yes
fpu_exception   : yes
cpuid level     : 10
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx lm constant_tsc arch_perfmon pebs bts rep_good aperfmperf pni dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm lahf_lm ida dts tpr_shadow vnmi flexpriority
bogomips        : 3591.39
clflush size    : 64
cache_alignment : 64
address sizes   : 36 bits physical, 48 bits virtual
power management:

[root@server1 ~]#

EDIT New Info requested by shakalandy

[root@localhost ~]# cat /proc/meminfo
MemTotal:        2044804 kB
MemFree:          761464 kB
Buffers:           68868 kB
Cached:           369708 kB
SwapCached:            0 kB
Active:           881080 kB
Inactive:         246016 kB
Active(anon):     688312 kB
Inactive(anon):     4416 kB
Active(file):     192768 kB
Inactive(file):   241600 kB
Unevictable:           0 kB
Mlocked:               0 kB
SwapTotal:       4095992 kB
SwapFree:        4095992 kB
Dirty:                 0 kB
Writeback:             0 kB
AnonPages:        688428 kB
Mapped:            65156 kB
Shmem:              4216 kB
Slab:              92428 kB
SReclaimable:      31260 kB
SUnreclaim:        61168 kB
KernelStack:        2392 kB
PageTables:        28356 kB
NFS_Unstable:          0 kB
Bounce:                0 kB
WritebackTmp:          0 kB
CommitLimit:     5118392 kB
Committed_AS:    1530212 kB
VmallocTotal:   34359738367 kB
VmallocUsed:      343604 kB
VmallocChunk:   34359372920 kB
HardwareCorrupted:     0 kB
AnonHugePages:    520192 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
DirectMap4k:        8556 kB
DirectMap2M:     2078720 kB
[root@localhost ~]# ps aux | grep mysql
root      2227  0.0  0.0 108332  1504 ?        S    07:36   0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/localhost.badobe.com.pid
mysql     2319  0.1 24.5 1470068 501360 ?      Sl   07:36   0:57 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/localhost.badobe.com.err --pid-file=/var/lib/mysql/localhost.badobe.com.pid
root      3579  0.0  0.1 201840  3028 pts/0    S+   07:40   0:00 mysql -u root -p
root     13887  0.0  0.1 201840  3036 pts/3    S+   18:08   0:00 mysql -uroot -px xxxxxxxxxx
root     14449  0.0  0.0 103248   840 pts/2    S+   18:16   0:00 grep mysql
[root@localhost ~]# ps aux | grep mysql
root      2227  0.0  0.0 108332  1504 ?        S    07:36   0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/localhost.badobe.com.pid
mysql     2319  0.1 24.5 1470068 501356 ?      Sl   07:36   0:57 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/localhost.badobe.com.err --pid-file=/var/lib/mysql/localhost.badobe.com.pid
root      3579  0.0  0.1 201840  3028 pts/0    S+   07:40   0:00 mysql -u root -p
root     13887  0.0  0.1 201840  3048 pts/3    S+   18:08   0:00 mysql -uroot -px xxxxxxxxxx
root     14470  0.0  0.0 103248   840 pts/2    S+   18:16   0:00 grep mysql
[root@localhost ~]# vmstat 1
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 0  0      0 742172  76376 371064    0    0     6     6   78  202  2  1 97  1  0
 0  0      0 742164  76380 371060    0    0     0    16  191  467  2  1 93  5  0
 0  0      0 742164  76380 371064    0    0     0     0  148  388  2  1 98  0  0
 0  0      0 742164  76380 371064    0    0     0     0  159  418  2  1 98  0  0
 0  0      0 742164  76380 371064    0    0     0     0  145  380  2  1 98  0  0
 0  0      0 742164  76380 371064    0    0     0     0  166  429  2  1 97  0  0
 1  0      0 742164  76380 371064    0    0     0     0  148  373  2  1 98  0  0
 0  0      0 742164  76380 371064    0    0     0     0  149  382  2  1 98  0  0
 0  0      0 742164  76380 371064    0    0     0     0  168  408  2  0 97  0  0
 0  0      0 742164  76380 371064    0    0     0     0  165  394  2  1 98  0  0
 0  0      0 742164  76380 371064    0    0     0     0  159  354  2  1 98  0  0
 0  0      0 742164  76388 371060    0    0     0    16  180  447  2  0 91  6  0
 0  0      0 742164  76388 371064    0    0     0     0  143  344  2  1 98  0  0
 0  1      0 742784  76416 370044    0    0    28   580  360  678  3  1 74 23  0
 1  0      0 744768  76496 367772    0    0    40  1036  437  865  3  1 53 43  0
 0  1      0 747248  76596 365412    0    0    48  1224  561  923  3  2 53 43  0
 0  1      0 749232  76696 363092    0    0    32  1132  512  883  3  2 52 44  0
 0  1      0 751340  76772 361020    0    0    32  1008  472  872  2  1 52 45  0
 0  1      0 753448  76840 358540    0    0    36  1088  512  860  2  1 51 46  0
 0  1      0 755060  76936 357636    0    0    28  1012  481  922  2  2 52 45  0
 0  1      0 755060  77064 357988    0    0    12   896  444  902  2  1 53 45  0
 0  1      0 754688  77148 358448    0    0    16  1096  506 1007  1  1 56 42  0
 0  2      0 754192  77268 358932    0    0    12  1060  481  957  1  2 53 44  0
 0  1      0 753696  77380 359392    0    0    12  1052  512 1025  2  1 55 42  0
 0  1      0 751028  77480 359828    0    0     8   984  423  909  2  2 52 45  0
 0  1      0 750524  77620 360200    0    0     8   788  367  869  1  2 54 44  0
 0  1      0 749904  77700 360664    0    0     8   928  439  924  2  2 55 43  0
 0  1      0 749408  77796 361084    0    0    12   976  468  967  1  1 56 43  0
 0  1      0 748788  77896 361464    0    0    12   992  453  944  1  2 54 43  0
 1  1      0 748416  77992 361996    0    0    12   784  392  868  2  1 52 46  0
 0  1      0 747920  78092 362336    0    0     4   896  382  874  1  1 52 46  0
 0  1      0 745252  78172 362780    0    0    12  1040  444  923  1  1 56 42  0
 0  1      0 744764  78288 363220    0    0     8  1024  448  934  2  1 55 43  0
 0  1      0 744144  78408 363668    0    0     8  1000  461  982  2  1 53 44  0
 0  1      0 743648  78488 364148    0    0     8   872  443  888  2  1 54 43  0
 0  1      0 743152  78548 364468    0    0    16  1020  511  995  2  1 55 43  0
 0  1      0 742656  78632 365024    0    0    12   928  431  913  1  2 53 44  0
 0  1      0 742160  78728 365468    0    0    12   996  470  955  2  2 54 44  0
 1  1      0 739492  78840 365896    0    0     8   988  447  939  1  2 52 46  0
 0  1      0 738872  78996 366352    0    0    12   972  442  928  1  1 55 44  0
 1  1      0 738244  79148 366812    0    0     8   948  549 1126  2  2 54 43  0
 0  1      0 737624  79312 367188    0    0    12   996  456  953  2  2 54 43  0
 0  1      0 736880  79456 367660    0    0    12   960  444  918  1  1 53 46  0
 0  1      0 736260  79584 368124    0    0     8   884  414  921  1  1 54 44  0
 0  1      0 735648  79716 368488    0    0    12   976  450  955  2  1 56 41  0
 0  1      0 733104  79840 368988    0    0    12   932  453  918  1  2 55 43  0
 0  1      0 732608  79996 369356    0    0    16   916  444  889  1  2 54 43  0
 1  1      0 731476  80128 369800    0    0    16   852  514  978  2  2 54 43  0
 0  1      0 731244  80252 370200    0    0     8   904  398  870  2  1 55 43  0
 1  1      0 730624  80384 370612    0    0    12  1032  447  977  1  2 57 41  0
 0  1      0 730004  80524 371096    0    0    12   984  469  941  2  2 52 45  0
 0  1      0 729508  80636 371544    0    0    12   928  438  922  2  1 52 46  0
 0  1      0 728888  80756 371948    0    0    16   972  439  943  2  1 55 43  0
 0  1      0 726468  80900 372272    0    0     8   960  545 1024  2  1 54 43  0
 1  1      0 726344  81024 372272    0    0     8   464  490 1057  1  2 53 44  0
 0  1      0 726096  81148 372276    0    0     4   328  441 1063  2  1 53 45  0
 1  1      0 726096  81256 372292    0    0     0   296  387  975  1  1 53 45  0
 0  1      0 725848  81380 372284    0    0     4   332  425 1034  2  1 54 44  0
 1  1      0 725848  81496 372300    0    0     4   308  386  992  2  1 54 43  0
 0  1      0 725600  81616 372296    0    0     4   328  404 1060  1  1 54 44  0
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 0  1      0 725600  81732 372296    0    0     4   328  439 1011  1  1 53 44  0
 0  1      0 725476  81848 372308    0    0     0   316  441 1023  2  2 52 46  0
 1  1      0 725352  81972 372300    0    0     4   344  451 1021  1  1 55 43  0
 2  1      0 725228  82088 372320    0    0     0   328  427 1058  1  1 54 44  0
 1  1      0 724980  82220 372300    0    0     4   336  419  999  2  1 54 44  0
 1  1      0 724980  82328 372320    0    0     4   320  430 1019  1  1 54 44  0
 1  1      0 724732  82436 372328    0    0     0   388  363  942  2  1 54 44  0
 1  1      0 724608  82560 372312    0    0     4   308  419  993  1  2 54 44  0
 1  0      0 724360  82684 372320    0    0     0   304  421 1028  2  1 55 42  0
 1  0      0 724360  82684 372388    0    0     0     0  158  416  2  1 98  0  0
 1  1      0 724236  82720 372360    0    0     0  6464  243  855  3  2 84 12  0
 1  0      0 724112  82748 372360    0    0     0  5356  266  895  3  1 84 12  0
 2  1      0 724112  82764 372380    0    0     0  3052  221  511  2  2 93  4  0
 1  0      0 724112  82796 372372    0    0     0  4548  325 1067  2  2 81 16  0
 1  0      0 724112  82816 372368    0    0     0  3240  259  829  3  1 90  6  0
 1  0      0 724112  82836 372380    0    0     0  3260  309  822  3  2 88  8  0
 1  1      0 724112  82876 372364    0    0     0  4680  326  978  3  1 77 19  0
 1  0      0 724112  82884 372380    0    0     0   512  207  508  2  1 95  2  0
 1  0      0 724112  82884 372388    0    0     0     0  138  361  2  1 98  0  0
 1  0      0 724112  82884 372388    0    0     0     0  158  397  2  1 98  0  0
 1  0      0 724112  82884 372388    0    0     0     0  146  395  2  1 98  0  0
 2  0      0 724112  82884 372388    0    0     0     0  160  395  2  1 98  0  0
 1  0      0 724112  82884 372388    0    0     0     0  163  382  1  1 98  0  0
 1  0      0 724112  82884 372388    0    0     0     0  176  422  2  1 98  0  0
 1  0      0 724112  82884 372388    0    0     0     0  134  351  2  1 98  0  0
 0  0      0 724112  82884 372388    0    0     0     0  190  429  2  1 97  0  0
 0  0      0 724104  82884 372392    0    0     0     0  139  358  2  1 98  0  0
 0  0      0 724848  82884 372392    0    0     0     4  211  432  2  1 97  0  0
 1  0      0 724980  82884 372392    0    0     0     0  166  370  2  1 98  0  0
 0  0      0 724980  82884 372392    0    0     0     0  164  397  2  1 98  0  0
^C
[root@localhost ~]#

Database size

mysql> SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB", sum( data_free )/ 1024 / 1024 "Free Space in MB" FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+----------------------+------------------+
| Data Base Name     | Data Base Size in MB | Free Space in MB |
+--------------------+----------------------+------------------+
| bidjunction        |           4.68750000 |       0.00000000 |
| information_schema |           0.00976563 |       0.00000000 |
| mysql              |           0.63899899 |       0.00105286 |
+--------------------+----------------------+------------------+
3 rows in set (0.01 sec)

mysql>

Before Query

mysql> SHOW SESSION STATUS like '%Tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 0     |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql>

After Query

mysql> SHOW SESSION STATUS like '%Tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 2     |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql>
user1032531
  • 568
  • 2
  • 11
  • 24
  • when you rebuilt your machines, did you change BIOS hard disk options? e.g. RAID or not RAID? – user1866880 Jun 29 '13 at 23:17
  • No I didn't. What do you expect? – user1032531 Jun 30 '13 at 21:30
  • If you didn't then that's not relevant question. new machines come with RAID option, which means whatever data you write to disk ends up in 2 copes. This ensures you always have good data, but does slow down disk write. – user1866880 Jun 30 '13 at 21:40

8 Answers8

8

You "fix" it by getting a newer laptop.

First, your laptop is using a six year old CPU (and is in power saving!) which isn't even really designed for server workloads.

Second... no, that really is the root of the "problem."

Michael Hampton
  • 237,123
  • 42
  • 477
  • 940
  • Thanks Michael. As I indicated, the laptop was working great with CentOS 5.8 and MySQL earlier. Why the change now? The purpose of the laptop is just local development. – user1032531 Jun 26 '13 at 14:17
  • Your benchmarks aren't out of line from what I would expect, and MySQL 5.1 is somewhat different to MySQL 5.0. – Michael Hampton Jun 26 '13 at 14:18
  • I think I was previously operating MySQL 5.1 on the laptop, so all I think was changed was going from CentOS 5.8 to CentOS 6.4. I am seeing queries taking 50x longer than they used to. Are there other tools I should try to benchmark performance? Are there ways to find if I have something configured wrong? (Or should I just get a new Server!) – user1032531 Jun 26 '13 at 14:25
  • You should just get a new Laptop! I'm surprised that antique you have is still running. – Michael Hampton Jun 26 '13 at 14:27
  • Why did MySQL used to run very fast on CentOS 5.8, and 50x slower on CentOS 6.4 using the same laptop? – user1032531 Jun 28 '13 at 15:05
  • @user1032531 Could be anything including different MYSQL builds. That question is impossible to pin down. – Nathan C Jun 28 '13 at 15:13
  • @NathanC. Thanks Nathan, Just don't want to buy a new server, and find that it too runs MySQL very slowly. Hopefully, anything currently will do okay. – user1032531 Jun 28 '13 at 18:41
  • I bought a new computer. $700 desktop without monitor from Costco. Still going slow. – user1032531 Jun 30 '13 at 00:27
  • Dear gawd, I could get a nice entry level HP Proliant G8 server for that. And you bought a desktop? – Michael Hampton Jun 30 '13 at 00:32
  • @MichaelHampton Still got the receipt, and created a Windows8 recovery disk so I can put back together. This is driving me crazy. `Architecture x86_64 CPU op-mode(s 32-bit, 64-bit Byte Order Little Endian CPU(s 4 On-line CPU(s) list 0-3 Thread(s) per core 1 Core(s) per socket 4 Socket(s 1 NUMA node(s 1 Vendor ID GenuineIntel CPU family 6 Model 58 Stepping 9 CPU MHz 3001.000 BogoMIPS 5986.42 Virtualization VT-x L1d cache 32K L1i cache 32K L2 cache 256K L3 cache 6144K NUMA node0 CPU(s 0-3` – user1032531 Jun 30 '13 at 00:56
  • For those reading this post, while this might have been the most popular answer, new hardware was not the best answer. – user1032531 Jul 04 '13 at 16:57
2

CentOS 6.x uses ext4. If your datadir is on ext4 set the barrier=0 option.

See here.

Cheers

Community
  • 1
HTTP500
  • 4,827
  • 4
  • 22
  • 31
  • Thank you. Sounds promising since it is related to somethings specifically with CentOS 6.x. I am still a novice. Can you tell me how to check if my datadir is on ext4 and how to set barrier equal to 0? Thanks! – user1032531 Jun 30 '13 at 21:30
  • The default datadir is /var/lib/mysql and given your laptop file info the datadir is on ext4. Edit /etc/fstab to include barrier=0 as a mount option and reboot. – HTTP500 Jul 01 '13 at 13:17
  • I need to learn more! I added changed fstab to `/dev/mapper /vg_localhost-lv_root / ext4 defaults,barrier=0 1 1`. Probably not a good thing to do. Now I am trying to use a LinuxLive CD to access the hard drive to change it back. – user1032531 Jul 01 '13 at 14:02
  • barrier=0 instead of defaults – HTTP500 Jul 01 '13 at 15:17
  • Off topic, but trying to restore system after I corrupted fstab. Booted LinuxLive CD, and trying to mount /dev/sda2, but am told it is already mounted. Try to umount, but am told it isn't mounted. How do I get to /etc/fstab so I may edit? – user1032531 Jul 01 '13 at 15:44
  • Never mind, fixed. Now back to adding barrier=0 – user1032531 Jul 01 '13 at 15:47
  • Okay, got set up. Big improvement, but still 10x slower than CentOS 5.9. real 0m1.744s user 0m0.018s sys 0m0.016s – user1032531 Jul 01 '13 at 16:03
  • Still don't know if this is the perfect solution, but it is by far the best. I ended up creating a new partition with ext3 and barrier=0, and almost did as well as CentOS5x. – user1032531 Jul 04 '13 at 16:56
1

although you posted your configuration, a lot other information is missing.

  1. How much memory do you have?
  2. How much memory is MySQL consuming on your laptop? (ps aux)
  3. How big is the database your testing with?
  4. Is your database using MyIsam or InnoDB table engine?
  5. Can you put some workload on it and log the output of vmstat 1 for some minutes?
shakalandy
  • 768
  • 4
  • 10
  • Thank you shakalandy, While I probably need a new computer, I am more interested in why I have the problem, and appreciate your help. Please see the very end of my OP where I add "EDIT New Info requested by shakalandy", and provided the information you requested in your answer. The first`ps aux` is when the DB isn't doing any work, and the second is when I gave it `source somefile.slq` which does abut 300 queries (and takes 45 seconds!!!). `vmstat` also shows no work, then doing the 300 queries, then no work. In addition, the database size is tiny and I am using InnoDB. Thanks! – user1032531 Jun 29 '13 at 01:32
  • vmstat shows that you have iowaits on your laptop. is the size of the DB bigger than 8MB? Then you should increase "innodb buffer size" to a value equal or greater to your DB Size. Could you please check the MySQL values "Created_tmp_disk_tables" and "Created_tmp_tables" before and after your query execution? (SHOW SESSION STATUS like '%Tmp%';" Seems that either you need more InnoDB Buffer Size or that your queries are using temp. tables on disk which could be lot slower than on your server. – shakalandy Jun 29 '13 at 11:17
  • Info was added to original post. My vps which runs well has the same size innodb buffer, so I am not sure. Thanks – user1032531 Jun 29 '13 at 12:03
  • Strange, i would have thought that the IO waits come from some temp table or Buffer Size issue. But since your DB is smaller than your Buffer Size this should not be the problem here. Your query cache is enabled but with zero size. Could you increase it? But this shouldn't be the issue either. You need to find out where the io wait comes from, defect filesystem, harddrives, etc. Something odd in messages/syslog/dmesg? – shakalandy Jun 29 '13 at 13:25
1

On your laptop running EL6, do the following:

yum install tuned-utils tuned
tuned-adm profile enterprise-storage

Then try your test again.

According to the notes here: Understanding RedHat's recommended tuned profiles

Community
  • 1
ewwhite
  • 194,921
  • 91
  • 434
  • 799
  • Hi ewwhite, Good news is this provided a 10% improvement! `real 0m9.534s|user 0m0.005s | sys 0m0.015s`. Bad news is we need much more to be comparable to CentOS 5X. Removing of barriers or reverting to CentOS 5X is still the only solution, and I feel both are not ideal. – user1032531 Jul 03 '13 at 01:07
  • You want to remove write barriers. – ewwhite Jul 03 '13 at 11:18
  • See HTTP500's answer. Funny how I was the only one who up-voted him. It still is quite a bit slower than CentOS 5X, but at least it is reasonable. Also, what is given up when barriers are removed? – user1032531 Jul 03 '13 at 11:35
0

The server/vps probably has a caching RAID card with memory battery backup, so the writes are acknowledged directly in RAID controller memory before hitting slow disk iops, your laptop does not unless it has an SSD in it. It has a huge performance impact.

In your settings file: innodb_flush_log_at_trx_commit 1

I suggest you try and slax the innodb_flush_log_at_trx_commit variable to 0 and or experiment with my-large.cnf your MySQL devel server is no longer full ACID compliant but it will run a lot faster.

Christian
  • 317
  • 1
  • 2
  • 8
  • I changed innodb_flush_log_at_trx_commit to 0. Much faster, but still much slower than it used to be before I went from CentOS 5.8 to 6.4. PS. I bought a new computer, and while slightly faster than my old laptop, not too much. Maybe I go back to 5.8? – user1032531 Jun 30 '13 at 00:51
  • I've since gone back to CentOS 5.9, and it runs 10000% faster. Still want to know why this is happening with CentOS 6x. – user1032531 Jun 30 '13 at 21:32
0

This is not an acceptable answer

Go back to CentOS 5X because I am obviously doing something wrong with CentOS 6X.

To test performance, I created a sql script which created 40 tables and inserted 5 rows in each table. I then entered time mysql -uroot -pMyPassword < test.sql using bash.

The VPS had CentOS 6.4 installed by my provider, and I installed CentOS 6.4 on both the desktop and laptop doing nothing special. I installed MySQL numerous different ways such as yum with CentOS repos, yum with IUS's repos, and RPM per the MySQL official documentation. As seen below, the desktop and laptop took as much as 100 times longer. I changed innodb_flush_log_at_trx_commit to 0 and then saw performance only 50 times slower.

I then went back to CentOS 5.9, and witnessed speeds increase by 100 times as shown on the last test.

VPS Centos 6.4, MySQL 5.1.69 (centos repo), innodb_flush_log_at_trx_commit=1
real    0m0.204s
user    0m0.005s
sys     0m0.004s

VPS Centos 6.4, MySQL 5.1.69 (centos repo), innodb_flush_log_at_trx_commit=0
real    0m0.314s
user    0m0.004s
sys     0m0.006s

Desktop Centos 6.4, MySQL 5.5.31 (centos repo), innodb_flush_log_at_trx_commit=1
real    0m10.031s
user    0m0.014s
sys     0m0.017s

Desktop Centos 6.4, MySQL 5.5.31 (ius repo), innodb_flush_log_at_trx_commit=0
real    0m3.179s
user    0m0.009s
sys     0m0.008s

Desktop Centos 6.4, MySQL 5.1.69 (ius repo), innodb_flush_log_at_trx_commit=1
real    0m8.140s
user    0m0.012s
sys     0m0.009s

Desktop Centos 6.4, MySQL 5.1.69 (centos repo), innodb_flush_log_at_trx_commit=0
real    0m4.106s
user    0m0.009s
sys     0m0.004s

Laptop Centos 6.4, MySQL 5.6.12 (MySQL RPM), innodb_flush_log_at_trx_commit=1
real    0m19.702s
user    0m0.017s
sys     0m0.014s

Laptop Centos 6.4, MySQL 5. 6.12 (MySQL RPM), innodb_flush_log_at_trx_commit=0
real    0m9.067s
user    0m0.011s
sys     0m0.012s

Desktop Centos 5.9, MySQL 5.0.95 (centos repo), innodb_flush_log_at_trx_commit=1
real    0m0.112s
user    0m0.008s
sys     0m0.005s
user1032531
  • 568
  • 2
  • 11
  • 24
  • 2
    Yes, you shouldn't just allow people to tell you to buy new hardware instead of locating the problem. The laptop might be old, but it's still worth finding out why MySQL runs so much faster on one distro versus the other. – ujjain Jun 30 '13 at 20:59
  • @ujjain. I agree! I tried to install CentOS6.2, but my stupid new desktop that I was pressured to buy is probably too new for it. I then just installed CentOS6.3, and the problem still exists. – user1032531 Jun 30 '13 at 21:13
0

Unless you've got an exabyte of ram, then your laptop configuration is a bit silly.

Copy over the config from the VPS and run it for a while, then try running mysqltuner.pl against the installation to get more sensible values.

It's unlikely that your laptop will have faster I/O than the VPS - and looking at the vmstat output, that's where your bottleneck is.

You've provided no details of the application nor how it's used - but unless this a relatively large analytics / data warehouse then the poor performance is most likely down to badly written queries / schema - and you should be tuning these. Go get a good book on MySQL tuning, e.g. this one or this one.

Alternatively plug your laptop into a SAN.

(hint: the book will be cheaper)

symcbean
  • 19,931
  • 1
  • 29
  • 49
  • Thanks Symcbean, I wish it was badly written SQL but it isn't. They are simple inserts with no joins, no wheres, no groups, nothing. I've since bought a useless new PC, and performance is exactly as bad. I then installed CentOS 5.9, and things run 100 times (literally) faster. I obviously did not do something correct. My steps were install CentOS 6X with no special settings, `yum install mysql-server`, and then `mysql_secure_installation`. – user1032531 Jun 30 '13 at 21:19
  • Why do you thjink inserts cannot be tuned? And the list of things I do to a server between installation and tuning it for the application goes way beyond te scope of an answer here (I/O scheduling, mount options, memory tuning, task scheduling....) – symcbean Jul 01 '13 at 15:18
  • I am sure they can be tuned, but my inserts are very simple `INSERT INTO t1(c1,c2) VALUES(1,2);` Question is why it takes 100 times longer on CentOS 6x than CentOS 5x? – user1032531 Jul 01 '13 at 15:40
0

The biggest difference I see is that one is 64bit and the other 32bit:

+---------------------------+-----------------------+-------------------------+
|         Variable          |       Value-VPS       |      Value-Laptop       |
+---------------------------+-----------------------+-------------------------+
| plugin_dir                | /usr/lib/mysql/plugin | /usr/lib64/mysql/plugin |

If you now use e.g. the 32bit client on both machines then you could get some slowness due to the internal translation from 32bit to 64bit. (But this is just a guess.)

Could you give us a "rpm -qa | grep mysql" from both systems?

Raffael Luthiger
  • 2,011
  • 2
  • 17
  • 26
  • The speed differences are huge (10000%), not small. VPS is `php54-mysql-5.4.16-1.ius.centos6.i686 mysql-libs-5.1.69-1.el6_4.i686 mysql-server-5.1.69-1.el6_4.i686 mysql-5.1.69-1.el6_4.i686 mysql-devel-5.1.69-1.el6_4.i686` Desktop is ` mysql-server-5.1.69-1.el6_4.x86_64 mysql-libs-5.1.69-1.el6_4.x86_64 mysql-5.1.69-1.el6_4.x86_64` – user1032531 Jun 30 '13 at 21:23
  • @user1032531 could you once try to use the same versions on both machines and see if it solves the problem? E.g. both i686 packages? – Raffael Luthiger Jul 01 '13 at 17:49