Pagalbos su mysql

Sveiki, norėčiau Jūsų pagalbos susijusios su mysql. Trumpai, svetainė perkalta į kitą ubutntu serverį su php5 +mysql + apache2. Viskas veikia puikiai, kol nėra importuojamas sql failas į mysql duomenų bazę ir nepajungiamas naudojimui, Pajungus duomenų bazę naudojimui, staiga lyg kad viskas užsikemša, svetainė nulūžta. SSH apkrovų pakitimų nerodo, viskas atrodo veikia normaliai, nors gan keistokai RAM atmintis keičiasi, o tuo tarpu serverio load artėja ties 0.00, tačiau webas nesivarto nei per kur. Beišekodamas kaip išspręst bėda sužinojau, kad galbūt tai susiję su 3306 portu. Patikrinus, vuolią, rodo kad portas uždarytas. Gal kam teko su tuo susidurti?

P.S. sql failas didelis, virš 400MB, buvo importuojamas naudojant SSH. Importuoto failo visos lentelės MyISAM, tačiau bijau sumeluot, bet regis 1 ar 2 turėtų būti innodb, nes tos lentelės turi po 1-2mln įrašų. Galbūt bėda čia?

Tikiuosi supratot, ir sulauksiu pagalbos.

per iptables atsidaryk portą

/var/log/ pažiūrėk mysql ir apache

ps aux | grep mysql

ps aux | grep apache (httpd)

pažiūrėk ar šios komandos išmeta kokią informaciją. Gal apache palūžta.

per iptables atsidaryk portą

/var/log/ pažiūrėk mysql ir apache

 

ps aux | grep mysql

ps aux | grep apache (httpd)

 

pažiūrėk ar šios komandos išmeta kokią informaciją. Gal apache palūžta.

Šios komandos išmeta: "Command not found". Naudajama OS - Ubuntu.

P.S. Pastebėjimas, per config failą atjungus jungimąsi į mysql duombazę, servas momentaliai atsigauna ir vartosi, tačiau tik aktyvavus kad jungtųsi į mysql, momentaliai viskas palūžta.

Viskas veikia puikiai, kol nėra importuojamas sql failas į mysql duomenų bazę ir nepajungiamas naudojimui, Pajungus duomenų bazę naudojimui, staiga lyg kad viskas užsikemša, svetainė nulūžta.

Cia bus kreivas svetaines kodas, uzsuka koki nsveika masyva kuris suryja visa RAM ir luzta viskas.

Cia bus kreivas svetaines kodas, uzsuka koki nsveika masyva kuris suryja visa RAM ir luzta viskas.

Kaip gali būt kreivas jei su ta pačia os ir tom pačiom konfiguracijom, tik kitame serve viskas sukosi labai gražiai? Be to, pasiskaityk ką rašiau PALŪŽUS SERVERIO LOADAS ARTĖJA TIES 0.00, O RAM IŠLIEKA VIRŠ 70 PROCENTŲ LAISVŲ.

Kaip gali būt kreivas jei su ta pačia os ir tom pačiom konfiguracijom, tik kitame serve viskas sukosi labai gražiai? Be to, pasiskaityk ką rašiau PALŪŽUS SERVERIO LOADAS ARTĖJA TIES 0.00, O RAM IŠLIEKA VIRŠ 70 PROCENTŲ LAISVŲ.

Error logus ziurek ten atsakymas bus 100%.

Error logus ziurek ten atsakymas bus 100%.

MYSQL error logas neilgas, bet daugumoj kartojasi šitai:

120615 19:53:21 [Note] Plugin 'FEDERATED' is disabled.

120615 19:53:21 InnoDB: Initializing buffer pool, size = 8.0M

120615 19:53:21 InnoDB: Completed initialization of buffer pool

120615 19:53:21 InnoDB: Started; log sequence number 0 44233

120615 19:53:21 [Note] Event Scheduler: Loaded 0 events

120615 19:53:21 [Note] /usr/sbin/mysqld: ready for connections.

Version: '5.1.61-0ubuntu0.10.10.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)

120615 21:45:28 [Note] /usr/sbin/mysqld: Normal shutdown

Apache erorr logas sveria apie 20MB ir visas pilnas šito:

[Fri Jun 15 19:30:11 2012] [error] [client 78.57.236.141] File does not exist: /var/www/announce.php

Bet dėja šis failas tikrai yra var/www/ direktorijoje.

MYSQL error logas neilgas, bet daugumoj kartojasi šitai:

 

 

 

Apache erorr logas sveria apie 20MB ir visas pilnas šito:

 

 

Bet dėja šis failas tikrai yra var/www/ direktorijoje.

O ka pacio serverio syslogai rodo ?

O ka pacio serverio syslogai rodo ?

Gal galima info, kur juos tiksliai rasti?

Gal galima info, kur juos tiksliai rasti?

/var/log/syslog

Gal galima info, kur juos tiksliai rasti?

Jun 15 20:22:01 server CRON[602]: (root) CMD (cd / && run-parts --report /etc/cron.hourly)

Jun 15 20:33:21 server -- MARK --

Jun 15 20:39:01 server CRON[643]: (root) CMD ( [ -x /usr/lib/php5/maxlifetime ] && [ -d /var/lib/php5 ] && find /var/lib/php5/ -depth -mindepth 1 -maxdepth 1 -type f -cmin +$(/usr/lib/php5/maxlifetime) -delete)

Jun 15 20:40:01 server CRON[658]: (smmsp) CMD (test -x /etc/init.d/sendmail && /usr/share/sendmail/sendmail cron-msp)

Jun 15 20:53:21 server -- MARK --

Jun 15 21:00:01 server CRON[686]: (smmsp) CMD (test -x /etc/init.d/sendmail && /usr/share/sendmail/sendmail cron-msp)

Jun 15 21:09:01 server CRON[762]: (root) CMD ( [ -x /usr/lib/php5/maxlifetime ] && [ -d /var/lib/php5 ] && find /var/lib/php5/ -depth -mindepth 1 -maxdepth 1 -type f -cmin +$(/usr/lib/php5/maxlifetime) -delete)

Jun 15 21:20:01 server CRON[803]: (smmsp) CMD (test -x /etc/init.d/sendmail && /usr/share/sendmail/sendmail cron-msp)

Jun 15 21:22:01 server CRON[831]: (root) CMD (cd / && run-parts --report /etc/cron.hourly)

Jun 15 21:33:21 server -- MARK --

Jun 15 21:39:01 server CRON[857]: (root) CMD ( [ -x /usr/lib/php5/maxlifetime ] && [ -d /var/lib/php5 ] && find /var/lib/php5/ -depth -mindepth 1 -maxdepth 1 -type f -cmin +$(/usr/lib/php5/maxlifetime) -delete)

Jun 15 21:40:01 server CRON[865]: (smmsp) CMD (test -x /etc/init.d/sendmail && /usr/share/sendmail/sendmail cron-msp)

Jun 15 21:45:33 server /etc/mysql/debian-start[920]: Upgrading MySQL tables if necessary.

Jun 15 21:45:33 server /etc/mysql/debian-start[923]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored

Jun 15 21:45:33 server /etc/mysql/debian-start[923]: Looking for 'mysql' as: /usr/bin/mysql

Jun 15 21:45:33 server /etc/mysql/debian-start[923]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck

Jun 15 21:45:33 server /etc/mysql/debian-start[923]: This installation of MySQL is already upgraded to 5.1.61, use --force if you still need to run mysql_upgrade

Jun 15 21:45:33 server /etc/mysql/debian-start[930]: Checking for insecure root accounts.

Jun 15 21:45:33 server /etc/mysql/debian-start[934]: Triggering myisam-recover for all MyISAM tables

Jun 15 21:50:30 server init: mysql main process (901) killed by KILL signal

Jun 15 21:50:30 server init: mysql main process ended, respawning

Jun 15 21:50:31 server /etc/mysql/debian-start[1080]: Upgrading MySQL tables if necessary.

Jun 15 21:50:31 server /etc/mysql/debian-start[1083]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored

Jun 15 21:50:31 server /etc/mysql/debian-start[1083]: Looking for 'mysql' as: /usr/bin/mysql

Jun 15 21:50:31 server /etc/mysql/debian-start[1083]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck

Jun 15 21:50:31 server /etc/mysql/debian-start[1083]: This installation of MySQL is already upgraded to 5.1.61, use --force if you still need to run mysql_upgrade

Jun 15 21:50:31 server /etc/mysql/debian-start[1090]: Checking for insecure root accounts.

Jun 15 21:50:31 server /etc/mysql/debian-start[1094]: Triggering myisam-recover for all MyISAM tables

Jun 15 22:00:01 server CRON[1258]: (smmsp) CMD (test -x /etc/init.d/sendmail && /usr/share/sendmail/sendmail cron-msp)

Jun 15 22:09:01 server CRON[1280]: (root) CMD ( [ -x /usr/lib/php5/maxlifetime ] && [ -d /var/lib/php5 ] && find /var/lib/php5/ -depth -mindepth 1 -maxdepth 1 -type f -cmin +$(/usr/lib/php5/maxlifetime) -delete)

Jun 15 22:20:02 server CRON[1290]: (smmsp) CMD (test -x /etc/init.d/sendmail && /usr/share/sendmail/sendmail cron-msp)

Jun 15 22:22:01 server CRON[1310]: (root) CMD (cd / && run-parts --report /etc/cron.hourly)

Jun 15 22:33:21 server -- MARK --

Jun 15 22:39:01 server CRON[1327]: (root) CMD ( [ -x /usr/lib/php5/maxlifetime ] && [ -d /var/lib/php5 ] && find /var/lib/php5/ -depth -mindepth 1 -maxdepth 1 -type f -cmin +$(/usr/lib/php5/maxlifetime) -delete)

Jun 15 22:40:01 server CRON[1344]: (smmsp) CMD (test -x /etc/init.d/sendmail && /usr/share/sendmail/sendmail cron-msp)

Jun 15 22:53:21 server -- MARK --

Jun 15 23:00:01 server CRON[1436]: (smmsp) CMD (test -x /etc/init.d/sendmail && /usr/share/sendmail/sendmail cron-msp)

Jun 15 23:09:01 server CRON[1494]: (root) CMD ( [ -x /usr/lib/php5/maxlifetime ] && [ -d /var/lib/php5 ] && find /var/lib/php5/ -depth -mindepth 1 -maxdepth 1 -type f -cmin +$(/usr/lib/php5/maxlifetime) -delete)

Jun 15 23:20:01 server CRON[1555]: (smmsp) CMD (test -x /etc/init.d/sendmail && /usr/share/sendmail/sendmail cron-msp)

Jun 15 23:22:01 server CRON[1575]: (root) CMD (cd / && run-parts --report /etc/cron.hourly)

Ar tai ką sako? Čia beveik visos eilutės iš to logo.

Ar tai ką sako? Čia beveik visos eilutės iš to logo.

Nea nieko nesako cronjobai cia pagrinde.

Nu nzn net siaip man visos problemos issipresdavo jas aptikus error loguose.

Gali perziuret kernelio logus ir panasiai.

Siaip net nzn, kad HDD butu ant ribos cia ir abejotina. Sunku cia pasakyt kur gali problema but.

Pabandyk isntaliuot koki wordpress pazek kaip sukas viskas.

Daugiau testavimo reikia.

Gal pabandyk pro mysql tuneri pratraukt?

wget -O tune.pl http://mysqltuner.pl
chmod +x tune.pl
./tune.pl

Ivedi prasomus duomenis ir lauki rezultatu. Gali but kad kanors "nedakonfiguruoto" aptiks.

Gal pabandyk pro mysql tuneri pratraukt?

wget -O tune.pl http://mysqltuner.pl
chmod +x tune.pl
./tune.pl

 

Ivedi prasomus duomenis ir lauki rezultatu. Gali but kad kanors "nedakonfiguruoto" aptiks.

Gal būtų konkrečių patarimų?

-------- General Statistics --------------------------------------------------

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.1.61-0ubuntu0.10.10.1

[OK] Operating on 32-bit architecture with less than 2GB RAM

 

-------- Storage Engine Statistics -------------------------------------------

[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster

[--] Data in MyISAM tables: 77M (Tables: 38)

[!!] InnoDB is enabled but isn't being used

[!!] Total fragmented tables: 5

 

-------- Security Recommendations -------------------------------------------

[OK] All database users have passwords assigned

 

-------- Performance Metrics -------------------------------------------------

[--] Up for: 18h 14m 32s (1M q [19.882 qps], 65K conn, TX: 608M, RX: 170M)

[--] Reads / Writes: 46% / 54%

[--] Total buffers: 58.0M global + 2.7M per thread (151 max threads)

[OK] Maximum possible memory usage: 463.8M (22% of installed RAM)

[OK] Slow queries: 0% (10K/1M)

[OK] Highest usage of available connections: 7% (11/151)

[OK] Key buffer size / total MyISAM indexes: 16.0M/31.4M

[OK] Key buffer hit rate: 100.0% (24B cached / 432K reads)

[OK] Query cache efficiency: 37.8% (239K cached / 633K selects)

[!!] Query cache prunes per day: 28654

[OK] Sorts requiring temporary tables: 0% (74 temp sorts / 114K sorts)

[OK] Temporary tables created on disk: 0% (72 on disk / 98K total)

[OK] Thread cache hit rate: 99% (19 created / 65K connections)

[!!] Table cache hit rate: 0% (64 open / 122K opened)

[OK] Open file limit used: 12% (123/1K)

[OK] Table locks acquired immediately: 99% (799K immediate / 800K locks)

[!!] Connections aborted: 34%

 

-------- Recommendations -----------------------------------------------------

General recommendations:

Add skip-innodb to MySQL configuration to disable InnoDB

Run OPTIMIZE TABLE to defragment tables for better performance

MySQL started within last 24 hours - recommendations may be inaccurate

Enable the slow query log to troubleshoot bad queries

Increase table_cache gradually to avoid file descriptor limits

Your applications are not closing MySQL connections properly

Variables to adjust:

query_cache_size (> 16M)

table_cache (> 64)

Gal dar galima mysql nustatymus pamatyt? Turetu buti cia - /etc/mysql/my.cnf

Ir dar prie to paties, ka raso

ulimit -a

?

#

# The MySQL database server configuration file.

#

# You can copy this to one of:

# - "/etc/mysql/my.cnf" to set global options,

# - "~/.my.cnf" to set user-specific options.

#

# One can use all long options that the program supports.

# Run program with --help to get a list of available options and with

# --print-defaults to see which it would actually understand and use.

#

# For explanations see

# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

 

# This will be passed to all mysql clients

# It has been reported that passwords should be enclosed with ticks/quotes

# escpecially if they contain "#" chars...

# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

[client]

port = 3306

socket = /var/run/mysqld/mysqld.sock

 

# Here is entries for some specific programs

# The following values assume you have at least 32M ram

 

# This was formally known as [safe_mysqld]. Both versions are currently parsed.

[mysqld_safe]

socket = /var/run/mysqld/mysqld.sock

nice = 0

 

[mysqld]

#

# * Basic Settings

#

 

#

# * IMPORTANT

# If you make changes to these settings and your system uses apparmor, you may

# also need to also adjust /etc/apparmor.d/usr.sbin.mysqld.

#

 

user = mysql

socket = /var/run/mysqld/mysqld.sock

port = 3306

basedir = /usr

datadir = /var/lib/mysql

tmpdir = /tmp

skip-external-locking

#

# Instead of skip-networking the default is now to listen only on

# localhost which is more compatible and is not less secure.

bind-address = 127.0.0.1

#

# * Fine Tuning

#

key_buffer = 16M

max_allowed_packet = 16M

thread_stack = 192K

thread_cache_size = 8

# This replaces the startup script and checks MyISAM tables if needed

# the first time they are touched

myisam-recover = BACKUP

#max_connections = 100

#table_cache = 64

#thread_concurrency = 10

#

# * Query Cache Configuration

#

query_cache_limit = 1M

query_cache_size = 16M

#

# * Logging and Replication

#

# Both location gets rotated by the cronjob.

# Be aware that this log type is a performance killer.

# As of 5.1 you can enable the log at runtime!

#general_log_file = /var/log/mysql/mysql.log

#general_log = 1

 

log_error = /var/log/mysql/error.log

 

# Here you can see queries with especially long duration

#log_slow_queries = /var/log/mysql/mysql-slow.log

#long_query_time = 2

#log-queries-not-using-indexes

#

# The following can be used as easy to replay backup logs or for replication.

# note: if you are setting up a replication slave, see README.Debian about

# other settings you may need to change.

#server-id = 1

#log_bin = /var/log/mysql/mysql-bin.log

expire_logs_days = 10

max_binlog_size = 100M

#binlog_do_db = include_database_name

#binlog_ignore_db = include_database_name

#

# * InnoDB

#

# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.

# Read the manual for more InnoDB related options. There are many!

#

# * Security Features

#

# Read the manual, too, if you want chroot!

# chroot = /var/lib/mysql/

#

# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".

#

# ssl-ca=/etc/mysql/cacert.pem

# ssl-cert=/etc/mysql/server-cert.pem

# ssl-key=/etc/mysql/server-key.pem

 

 

 

[mysqldump]

quick

quote-names

max_allowed_packet = 16M

 

[mysql]

#no-auto-rehash # faster start of mysql but no tab completition

 

[isamchk]

key_buffer = 16M

 

#

# * IMPORTANT: Additional settings that can override those from this file!

# The files must end with '.cnf', otherwise they'll be ignored.

#

!includedir /etc/mysql/conf.d/

Čia nustatymai.

O pagal komandą rašo:

root@server:~# ulimit -a

core file size (blocks, -c) 0

data seg size (kbytes, -d) unlimited

scheduling priority (-e) 20

file size (blocks, -f) unlimited

pending signals (-i) 16382

max locked memory (kbytes, -l) 64

max memory size (kbytes, -m) unlimited

open files (-n) 1024

pipe size (512 bytes, -p) 8

POSIX message queues (bytes, -q) 819200

real-time priority (-r) 0

stack size (kbytes, -s) 8192

cpu time (seconds, -t) unlimited

max user processes (-u) unlimited

virtual memory (kbytes, -v) unlimited

file locks (-x) unlimited

P.S. Šiuo metu kada kopinu duomenis, bandau dar pažaisti su mysql, todėl gali būt kad apkrovos ar kiti rodikliai bus pasikeitę, nei kad buvo problemos metu.

Atnaujinu informaciją, patikrinus tiek 80 tiek 3306 portus, rodo kad abu uždaryti. Dėl ko gali tai nutikti?