MySQL huge latency times
This is my first post here and first and foremost I would like to introduce myself. My name is João Cunha, I'm a brazilian web developer which - sadly - have poor to none experience with Linux and sysadmin tasks.
That said, my problem is the following: our mysql is having HUGE latency times, with page loads varying from 10 to 30 seconds. We have just migrated our database from a shared hosting company to Linode, and surprisingly enough, the performance went far worse.
It is probably a misconfiguration or something of the sort, that's why I ask for help. I have already followed these steps
mysql> show full processlist;
+-----+-------------+------------------------------+-------------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-------------+------------------------------+-------------+---------+------+-------+-----------------------+
| 733 | root | localhost | NULL | Sleep | 645 | | NULL |
| 891 | krobelus851 | whl0020.whservidor.com:44498 | gamerchants | Sleep | 0 | | NULL |
| 914 | krobelus851 | whl0020.whservidor.com:40727 | gamerchants | Sleep | 0 | | NULL |
| 915 | krobelus851 | whl0020.whservidor.com:40739 | gamerchants | Sleep | 0 | | NULL |
| 945 | krobelus851 | whl0020.whservidor.com:49983 | gamerchants | Sleep | 0 | | NULL |
| 948 | krobelus851 | whl0020.whservidor.com:33596 | gamerchants | Sleep | 0 | | NULL |
| 949 | krobelus851 | whl0020.whservidor.com:33754 | gamerchants | Sleep | 0 | | NULL |
| 950 | krobelus851 | whl0020.whservidor.com:34023 | gamerchants | Sleep | 0 | | NULL |
| 952 | krobelus851 | whl0020.whservidor.com:34207 | gamerchants | Sleep | 0 | | NULL |
| 953 | krobelus851 | whl0020.whservidor.com:35839 | gamerchants | Sleep | 0 | | NULL |
| 955 | krobelus851 | whl0020.whservidor.com:46545 | gamerchants | Sleep | 0 | | NULL |
| 958 | krobelus851 | whl0020.whservidor.com:55012 | gamerchants | Sleep | 0 | | NULL |
| 959 | krobelus851 | whl0020.whservidor.com:59239 | gamerchants | Sleep | 0 | | NULL |
| 960 | krobelus851 | whl0020.whservidor.com:34857 | gamerchants | Sleep | 0 | | NULL |
| 962 | krobelus851 | whl0020.whservidor.com:39735 | gamerchants | Sleep | 0 | | NULL |
| 964 | krobelus851 | whl0020.whservidor.com:41642 | gamerchants | Sleep | 0 | | NULL |
| 968 | krobelus851 | whl0020.whservidor.com:45218 | gamerchants | Sleep | 0 | | NULL |
| 970 | krobelus851 | whl0020.whservidor.com:50221 | gamerchants | Sleep | 0 | | NULL |
| 971 | krobelus851 | whl0020.whservidor.com:50312 | gamerchants | Sleep | 0 | | NULL |
| 972 | krobelus851 | whl0020.whservidor.com:50860 | gamerchants | Sleep | 0 | | NULL |
| 975 | root | localhost | NULL | Query | 0 | NULL | show full processlist |
| 977 | krobelus851 | whl0020.whservidor.com:38595 | gamerchants | Sleep | 0 | | NULL |
| 978 | krobelus851 | whl0020.whservidor.com:43689 | gamerchants | Sleep | 0 | | NULL |
+-----+-------------+------------------------------+-------------+---------+------+-------+-----------------------+
23 rows in set (0.00 sec)
Well, I've set some configuration values but nothing that would actually solve the problem.
Any help would be appreciated.
Thanks,
João
14 Replies
Could you give us a bit more information about the types of queries being performed, the schema involved, the wbe app involved, etc?
@Guspaz:
A bunch of sleeping processes usually indicates the improper use of persistent connections on the part of the web application, and extremely slow query times like you're seeing usually indicates the lack of proper indexing in your database schema.
Could you give us a bit more information about the types of queries being performed, the schema involved, the wbe app involved, etc?
Hi, Guspaz! Thanks for answering.
It's a ZenCart e-commerce platform, which is still installed on another hosting - we've chosen to migrate the database first to solve some of the hosting limitations, and also because we are migrating to another e-commerce platform.
The queries being run probably aren't very optimized, but they used to run just fine out there - and that is why I think is somewhat configuration related.
João
It seems that some people were able to resolve the problem by disabling the "category counting" feature.
In terms of optimizing mysql, I'd recommend starting with the my.cnf default file that is appropriate for how much RAM you have (my-medium.cnf or my-large.cnf, probably), run like that for a few days, then try
You should also make sure that your web server setup is sufficiently well configured that it isn't wasting all your RAM, which could cause problems, especially if it's pushing you into swap. Are you running apache? Could you post the output of top, 'free -m', 'ps -aux', etc? Censor any passwords or sensitive info if there is any.
There are a few other tweaks that are beneficial (gzip for output, a php cache, etc), but those are the kind of things you'd want to worry about after you solve the major slowdowns. Shaving 250ms off a page load is great if your pages load in half a second or a second, but that won't do much for a 10-second page load.
EDIT: I should say that I kind of cheat in this regard. If I deploy a new linode, I usually just throw up MySQL, Lighttpd, and PHP, plug in my-medium.cnf or my-large.cnf, drop the number of fastcgi PHP processes to something like 4+1 (I think the default is 8+2 last time I did a deployment), and call it a day. So I don't really bother with optimization beyond that. For people new to the game, they're better off with nginx than Lighttpd, I just stick to lighttpd since it's what I'm more familiar with. Apache can also be configured to be reasonably memory-efficient, it's just a bunch more work.
Show category counts is already set to false;
I'm using the default my.cnf file PLUS the mods suggested by Linode support itself [
http://library.linode.com/hosting-websi … node-51244">http://library.linode.com/hosting-website#sph_optimizing-mysql-for-a-linode-51244 I'm not running Apache neither PHP.
root@li215-20:~# free -m
total used free shared buffers cached
Mem: 496 267 229 0 20 155
-/+ buffers/cache: 92 404
Swap: 255 0 255
root@li215-20:~# ps -aux
Warning: bad ps syntax, perhaps a bogus '-'? See http://procps.sf.net/faq.html
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
root 1 0.0 0.3 2728 1604 ? Ss 08:50 0:00 /sbin/init
root 2 0.0 0.0 0 0 ? S 08:50 0:00 [kthreadd]
root 3 0.0 0.0 0 0 ? S 08:50 0:00 [ksoftirqd/0]
root 4 0.0 0.0 0 0 ? S 08:50 0:01 [kworker/0:0]
root 5 0.0 0.0 0 0 ? S 08:50 0:00 [kworker/u:0]
root 6 0.0 0.0 0 0 ? S 08:50 0:00 [migration/0]
root 7 0.0 0.0 0 0 ? S 08:50 0:00 [migration/1]
root 8 0.0 0.0 0 0 ? S 08:50 0:00 [kworker/1:0]
root 9 0.0 0.0 0 0 ? S 08:50 0:00 [ksoftirqd/1]
root 10 0.0 0.0 0 0 ? S 08:50 0:00 [migration/2]
root 11 0.0 0.0 0 0 ? S 08:50 0:00 [kworker/2:0]
root 12 0.0 0.0 0 0 ? S 08:50 0:00 [ksoftirqd/2]
root 13 0.0 0.0 0 0 ? S 08:50 0:00 [migration/3]
root 14 0.0 0.0 0 0 ? S 08:50 0:00 [kworker/3:0]
root 15 0.0 0.0 0 0 ? S 08:50 0:00 [ksoftirqd/3]
root 16 0.0 0.0 0 0 ? S< 08:50 0:00 [cpuset]
root 17 0.0 0.0 0 0 ? S< 08:50 0:00 [khelper]
root 18 0.0 0.0 0 0 ? S 08:50 0:00 [kdevtmpfs]
root 19 0.0 0.0 0 0 ? S 08:50 0:00 [kworker/u:1]
root 21 0.0 0.0 0 0 ? S 08:50 0:00 [xenwatch]
root 22 0.0 0.0 0 0 ? S 08:50 0:00 [xenbus]
root 155 0.0 0.0 0 0 ? S 08:50 0:00 [sync_supers]
root 157 0.0 0.0 0 0 ? S 08:50 0:00 [bdi-default]
root 159 0.0 0.0 0 0 ? S< 08:50 0:00 [kblockd]
root 169 0.0 0.0 0 0 ? S 08:50 0:01 [kworker/3:1]
root 171 0.0 0.0 0 0 ? S< 08:50 0:00 [md]
root 255 0.0 0.0 0 0 ? S< 08:50 0:00 [rpciod]
root 257 0.0 0.0 0 0 ? S 08:50 0:01 [kworker/2:1]
root 268 0.0 0.0 0 0 ? S 08:50 0:00 [kswapd0]
root 269 0.0 0.0 0 0 ? SN 08:50 0:00 [ksmd]
root 270 0.0 0.0 0 0 ? S 08:50 0:00 [fsnotify_mark]
root 274 0.0 0.0 0 0 ? S 08:50 0:00 [ecryptfs-kthrea]
root 276 0.0 0.0 0 0 ? S< 08:50 0:00 [nfsiod]
root 277 0.0 0.0 0 0 ? S< 08:50 0:00 [cifsiod]
root 280 0.0 0.0 0 0 ? S 08:50 0:00 [jfsIO]
root 281 0.0 0.0 0 0 ? S 08:50 0:00 [jfsCommit]
root 282 0.0 0.0 0 0 ? S 08:50 0:00 [jfsCommit]
root 283 0.0 0.0 0 0 ? S 08:50 0:00 [jfsCommit]
root 284 0.0 0.0 0 0 ? S 08:50 0:00 [jfsCommit]
root 285 0.0 0.0 0 0 ? S 08:50 0:00 [jfsSync]
root 286 0.0 0.0 0 0 ? S< 08:50 0:00 [xfsalloc]
root 287 0.0 0.0 0 0 ? S< 08:50 0:00 [xfs_mru_cache]
root 288 0.0 0.0 0 0 ? S< 08:50 0:00 [xfslogd]
root 289 0.0 0.0 0 0 ? S< 08:50 0:00 [glock_workqueue]
root 290 0.0 0.0 0 0 ? S< 08:50 0:00 [delete_workqueu]
root 291 0.0 0.0 0 0 ? S< 08:50 0:00 [gfs_recovery]
root 292 0.0 0.0 0 0 ? S< 08:50 0:00 [crypto]
root 855 0.0 0.0 0 0 ? S 08:50 0:00 [khvcd]
root 969 0.0 0.0 0 0 ? S< 08:50 0:00 [kpsmoused]
root 1004 0.0 0.0 0 0 ? S 08:50 0:01 [kworker/1:1]
root 1006 0.0 0.0 0 0 ? S 08:50 0:00 [kworker/0:1]
root 1010 0.0 0.0 0 0 ? S< 08:50 0:00 [deferwq]
root 1013 0.0 0.0 0 0 ? S 08:50 0:01 [kjournald]
root 1037 0.0 0.1 2364 608 ? S 08:50 0:00 upstart-udev-bridge --daemon
root 1039 0.0 0.1 2232 552 ? S
~~I don't know
João~~
Unfortunately, I'm really not familiar enough with ZenCart (as in, I've never used it or heard of it before), so I'm afraid I can't really suggest much to try in terms of why it specifically would be slow.
Could you clarify if you had performance issues on the shared hosting before migrating to a VPS? Your original post seems to imply that the 10-30 second page loads were happening there, and then it was even worse on the VPS. If this is the case, it's likely something wrong with ZenCart rather than your configuration. Perhaps someone else here is a ZenCart whiz who can offer more advice, but I wouldn't hold my breath for a response soon seeing as how it's Canada Day weekend in Canada, and the US Independence Day is on Wednesday.
If you're unable to try disabling addons because this is now your production environment, you could try deploying your ecommerce setup on a separate linode (they're pro-rated, so it'll cost you less than a buck a day to experiment) and try playing around with some more drastic changes to see if you can identify a cause.
My main concern is that the shared hosting, besides its problems, was running smoothly!
auto increment increment 1
auto increment offset 1
autocommit ON
automatic sp privileges ON
back log 500
basedir /
big tables OFF
binlog cache size 32,768
binlog direct non transactional updates OFF
binlog format STATEMENT
bulk insert buffer size 8,388,608
character set client utf8
(Valor global) latin1
character set connection utf8
(Valor global) latin1
character set database latin1
character set filesystem binary
character set results utf8
(Valor global) latin1
character set server latin1
character set system utf8
character sets dir /usr/share/mysql/charsets/
collation connection utf8_general_ci
(Valor global) latin1_swedish_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 1,000
div precision increment 4
engine condition pushdown ON
error count 0
event scheduler ON
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 3
ft query expansion limit 20
ft stopword file (built-in)
general log ON
general log file /var/log/mysql/mysql-auth.log
group concat max len 1,024
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 NO
have partitioning YES
have query cache YES
have rtree keys YES
have ssl NO
have symlink DISABLED
hostname a1-dbmy32-a11.host.intranet
identity 0
ignore builtin innodb ON
init connect
init file
init slave
innodb adaptive flushing ON
innodb adaptive hash index ON
innodb additional mem pool size 20,971,520
innodb autoextend increment 8
innodb autoinc lock mode 1
innodb buffer pool size 603,979,776
innodb change buffering inserts
innodb checksums ON
innodb commit concurrency 0
innodb concurrency tickets 500
innodb data file path ibdata1:10M:autoextend:max:5000M
innodb data home dir /var/lib/mysql/
innodb doublewrite ON
innodb fast shutdown 1
innodb file format Antelope
innodb file format check Barracuda
innodb file per table ON
innodb flush log at trx commit 2
innodb flush method
innodb force recovery 0
innodb io capacity 200
innodb lock wait timeout 50
innodb locks unsafe for binlog OFF
innodb log buffer size 8,388,608
innodb log file size 134,217,728
innodb log files in group 2
innodb log group home dir /var/lib/mysql/
innodb max dirty pages pct 75
innodb max purge lag 0
innodb mirrored log groups 1
innodb old blocks pct 37
innodb old blocks time 0
innodb open files 300
innodb read ahead threshold 56
innodb read io threads 4
innodb replication delay 0
innodb rollback on timeout OFF
innodb spin wait delay 6
innodb stats method nulls_equal
innodb stats on metadata ON
innodb stats sample pages 8
innodb strict mode OFF
innodb support xa ON
innodb sync spin loops 30
innodb table locks ON
innodb thread concurrency 0
innodb thread sleep delay 10,000
innodb use sys malloc ON
innodb version 1.0.17
innodb write io threads 4
insert id 0
interactive timeout 300
join buffer size 131,072
keep files on create OFF
key buffer size 268,435,456
key cache age threshold 300
key cache block size 1,024
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 ON
log bin OFF
log bin trust function creators OFF
log bin trust routine creators OFF
log error /var/lib/mysql/error.log
log output FILE
log queries not using indexes OFF
log slave updates OFF
log slow queries ON
log warnings 1
long query time 10
low priority updates OFF
lower case file system OFF
lower case table names 0
max allowed packet 16,777,216
max binlog cache size 4,294,963,200
max binlog size 1,073,741,824
max connect errors 50
max connections 750
max delayed threads 20
max error count 64
max heap table size 2,097,152
max insert delayed threads 20
max join size 18446744073709551615
max length for sort data 1,024
max long data size 16,777,216
max prepared stmt count 16,382
max relay log size 0
max seeks for key 4,294,967,295
max sort length 1,024
max sp recursion depth 0
max tmp tables 32
max user connections 50
(Valor global) 0
max write lock count 1
min examined row limit 0
multi range count 256
myisam data pointer size 6
myisam max sort file size 2,146,435,072
myisam mmap size 4,294,967,295
myisam recover options OFF
myisam repair threads 1
myisam sort buffer size 25,165,824
myisam stats method nulls_unequal
myisam use mmap OFF
net buffer length 16,384
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 66,296
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/lib/mysql/a1-dbmy32-a11.host.intranet.pid
plugin dir /usr/lib/mysql/plugin
port 3,306
preload buffer size 32,768
profiling OFF
profiling history size 15
protocol version 10
pseudo thread id 2,686,812
(Valor global) 0
query alloc block size 8,192
query cache limit 1,048,576
query cache min res unit 4,096
query cache size 67,108,864
query cache type ON
query cache wlock invalidate OFF
query prealloc size 8,192
rand seed1
rand seed2
range alloc block size 4,096
read buffer size 2,097,152
read only OFF
read rnd buffer size 8,388,608
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 3,306
report user
rpl recovery rank 0
secure auth OFF
secure file priv
server id 0
skip external locking ON
skip name resolve ON
skip networking OFF
skip show database OFF
slave compressed protocol OFF
slave exec mode STRICT
slave load tmpdir /tmp
slave net timeout 3,600
slave skip errors OFF
slave transaction retries 10
slow launch time 2
slow query log ON
slow query log file /var/log/mysql/mysql-slow.log
socket /var/lib/mysql/mysql.sock
sort buffer size 2,097,152
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 BRT
table definition cache 65,536
table lock wait timeout 50
table open cache 32,768
table type MyISAM
thread cache size 50
thread handling one-thread-per-connection
thread stack 196,608
time format %H:%i:%s
time zone SYSTEM
timed mutexes OFF
timestamp 1,341,009,036
tmp table size 16,777,216
tmpdir /tmp
transaction alloc block size 8,192
transaction prealloc size 4,096
tx isolation REPEATABLE-READ
unique checks ON
updatable views with limit YES
version 5.1.58-community-log
version comment MySQL Community Server (GPL)
version compile machine i686
version compile os pc-linux-gnu
wait timeout 30
warning count 0
mysql> SHOW VARIABLES;
+-----------------------------------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value |
+-----------------------------------------+-------------------------------------------------------------------------------------------+
| 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 | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_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 | 10 |
| 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/lib/mysql/li215-20.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 | YES |
| hostname | **************************** |
| 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 | 16384 |
| 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/mysql/error.log |
| log_output | TABLE |
| log_queries_not_using_indexes | ON |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| log_warnings | 1 |
| long_query_time | 1.000000 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 1048576 |
| max_binlog_cache_size | 4294963200 |
| max_binlog_size | 104857600 |
| max_connect_errors | 10 |
| max_connections | 500 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 67108864 |
| 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 | 4294967295 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 500 |
| max_write_lock_count | 4294967295 |
| min_examined_row_limit | 0 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 2146435072 |
| myisam_mmap_size | 4294967295 |
| myisam_recover_options | BACKUP |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 67108864 |
| myisam_stats_method | nulls_unequal |
| myisam_use_mmap | OFF |
| net_buffer_length | 2048 |
| 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 | 2500 |
| 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/lib/mysql/li215-20.pid |
| plugin_dir | /usr/lib/mysql/plugin |
| port | 3306 |
| preload_buffer_size | 32768 |
| profiling | OFF |
| profiling_history_size | 15 |
| protocol_version | 10 |
| pseudo_thread_id | 2267 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 29360128 |
| 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 | 4194304 |
| read_only | OFF |
| read_rnd_buffer_size | 4194304 |
| 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_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /var/log/mysql/mysql-slow.log |
| socket | /var/run/mysqld/mysqld.sock |
| sort_buffer_size | 65536 |
| 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 | EDT |
| table_definition_cache | 256 |
| table_lock_wait_timeout | 50 |
| table_open_cache | 4 |
| table_type | MyISAM |
| thread_cache_size | 8 |
| thread_handling | one-thread-per-connection |
| thread_stack | 131072 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1341010187 |
| tmp_table_size | 67108864 |
| 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.63-0ubuntu0.10.04.1-log |
| version_comment | (Ubuntu) |
| version_compile_machine | i486 |
| version_compile_os | debian-linux-gnu |
| wait_timeout | 28800 |
| warning_count | 0 |
+-----------------------------------------+-------------------------------------------------------------------------------------------+
276 rows in set (0.00 sec)
@joaocunha:
The Apache and PHP are still running on the shared hosting.
And you're wondering why and where the latency is coming from???
@vonskippy:
@joaocunha:The Apache and PHP are still running on the shared hosting.
And you're wondering why and where the latency is coming from???
But wouldn't it be better than a shared hosting only solution? It was running "fine" before.
Imagine that ZenCart did 100 queries, that take 10ms each to complete, and that there is 1ms of latency between your shared hosting provider's web and sql servers, and 100ms of latency between your shared hosting provider's web server and your linode.
Shared hosting web & shared hosting SQL latency:
100 * (10 + 1) = 1,100ms (~1 second)
Shared hosting web & linode SQL latency:
100 * (10 + 100) = 11,000ms (~11 seconds)
It's generally fine to have your database server on a different physical machine in the same datacenter, but it's a bad idea to separate your web and database servers by large distances. People use webservices for that, so that all the database work happens locally before feeding the results back over the network.
EDIT: The summary of this is either migrate everything to Linode, or keep everything at the shared host. You can't mix and match the web and database servers like you're doing.
Is there any way to actually measure these latencies? So if it is the problem, I'd put it all to run on Linode only.
64 bytes from whl0020.whservidor.com (200.98.197.36): icmp_seq=2 ttl=48 time=143 ms
64 bytes from whl0020.whservidor.com (200.98.197.36): icmp_seq=3 ttl=48 time=144 ms
64 bytes from whl0020.whservidor.com (200.98.197.36): icmp_seq=4 ttl=48 time=143 ms
It's so clear now… I'm feeling dumb like never before
I'll update this thread when I get it migrated as a whole.
You guys are amazing, thanks.