Reducing MySQL memory usage on Ubuntu / Debian Linux

If you are running your services on a low end virtual hosting every byte of memory you can save is important. The memory is often the limiting factor of how many applications you can run on VPS: CPUs are shared, memory not, on the same physical host.

  • Low-end VPS come with 512 MB memory or less
  • Front front-end server Apache / Nginx / Varnish takes > 100 MB +  min. 20 MB for each child process
  • Memecached takes its toll
  • MySQL takes 200 – 400 MB
  • Each Python / PHP process takes at least 15 MB and you need parallel processes for paraller HTTP requests (FCGI, pre-fork, others… )
  • Operating system processes need some memory (SSH, cron, sendmail)

As you can see it gets very crowded in 512 MB.

It’s especially troublesome since the memory is allocated lazily and the memory usage builds up slowly. In some point caches are no longer caches, but swapped to a disk – virtual memory usage grows beyond available RAM. To keep the server response, everything time critical should fit to RAM once and if the processes themselves don’t know how to release memory in this situation you need to tune a memory cap for them.

1. MySQL memory consumption

MySQL can be a greedy bastard what comes to memory consumption. Here on this server MySQL seems to take 417M virtual memory which seems to be little excessive for just running two WordPress instances and one Django / Python application:

1310 mysql     20   0  417M 21100  2776 S  0.0  1.2  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/v

After some tuning I was able to bring it down a bit

3354 mysql     20   0  276m  19m 2848 S    0  1.2   3:41.19 mysqld

A reduction of 130 MB, or 1/4 of the server total memory. Not bad.

Use mtop to monitor running MySQL, its querieries, etc. so you know what’s going on. As you can see this MySQL has very good cache rate meaning that basically it is keeping everything in memory. If the content of the sites is less than 10 MBytes total, 400 MB contains plenty of space to cache the content:

load average: 0.05, 0.08, 0.16 mysqld 5.0.51a-3ubuntu5.8-log up 1 day(s), 19:47 hrs                                                             
2 threads: 1 running, 6 cached. Queries/slow: 187.1K/0 Cache Hit: 99.39%

2. What eats memory

I am not an expert on MySQL, so I hope someone with more insight could post comments regarding how to tune MySQL for low memory situations and how it is expected to behave.

Some ideas I run through my head

  • MySQL default cache settings are not too tight on Ubuntu/Debian, making it suitable for moderate loads, not low loads. If you don’t have much content, everything is just kept in memory (even if not needed)
  • MySQL uses round robin for connections and if there is 100 max connections it will allocate a thread stack for each connection (someone please confirm this – I found contracting infos).

3. Configuring MySQL

Here are listed some methods how to reduce the memory usage. This is what I done on this little box

MySQL is mostly configured in /etc/mysql/my.cnf on Ubuntu / Debian.

The final adjustments

key_buffer              = 8M
max_connections         = 30
query_cache_size        = 8M
query_cache_limit       = 512K
thread_stack            = 128

source from : http://opensourcehacker.com/2011/03/31/reducing-mysql-memory-usage-on-ubuntu-debian-linux/
Advertisements

Installing LDAP support in PHP on Ubuntu server

LDAP support in PHP is not enabled by default. To enable LDAP support on an existing Ubuntu Apache web server you need to install php5-ldap package.

Install php5-lda:
sudo apt-get install php5-ldap
Reboot apache
/etc/init.d/apache2 restart

Be sure to enable LDAP support within PHP. Make sure that you have installed the necessary packages for your distro.

  • RedHat EL based distro (CentOS 4.3):
    yum install php-ldap
  • Make sure that /etc/php.d/ldap.ini contains
    extension=ldap.so
  • Ubuntu 6.06.1 (Dapper Drake) and others:
    sudo apt-get install php-ldap
    or possibly:
    sudo apt-get install php5-ldap
  • Other distros (/etc/php5/apache2):
    Modify php.ini, and uncomment the line:
     ;extension=php_ldap.so
    change to:
    extension=php_ldap.so
  • Windows:
    Modify php.ini, and uncomment the line:
     ;extension=php_ldap.dll
    change to:
    extension=php_ldap.dll

Linux log files location and how do I view logs files?

Go to /var/logs directory:# cd /var/logsView common log file /var/log/messages using any one of the following command:# tail -f /var/log/messages

# less /var/log/messages
# more -f /var/log/messages
# vi /var/log/messages
Output:

Jul 17 22:04:25 router  dnsprobe[276]: dns query failed
Jul 17 22:04:29 router last message repeated 2 times
Jul 17 22:04:29 router  dnsprobe[276]: Primary DNS server Is Down... Switching To Secondary DNS server
Jul 17 22:05:08 router  dnsprobe[276]: Switching Back To Primary DNS server
Jul 17 22:26:11 debian -- MARK --
Jul 17 22:46:11 debian -- MARK --
Jul 17 22:47:36 router  -- MARK --
Jul 17 22:47:36 router  dnsprobe[276]: dns query failed
Jul 17 22:47:38  debian kernel: rtc: lost some interrupts at 1024Hz.
Jun 17 22:47:39  debian kernel: IN=eth0 OUT= MAC=00:0f:ea:91:04:07:00:08:5c:00:00:01:08:00 SRC=61.4.218.24 DST=192.168.1.100 LEN=60 TOS=0x00 PREC=0x00 TTL=46 ID=21599 DF PROTO=TCP SPT=59297 DPT=22 WINDOW=5840 RES=0x00 SYN URGP=0

Common Linux log files name and usage

  • /var/log/message: General message and system related stuff
  • /var/log/auth.log: Authenication logs
  • /var/log/kern.log: Kernel logs
  • /var/log/cron.log: Crond logs (cron job)
  • /var/log/maillog: Mail server logs
  • /var/log/qmail/ : Qmail log directory (more files inside this directory)
  • /var/log/httpd/: Apache access and error logs directory
  • /var/log/lighttpd: Lighttpd access and error logs directory
  • /var/log/boot.log : System boot log
  • /var/log/mysqld.log: MySQL database server log file
  • /var/log/secure: Authentication log
  • /var/log/utmp or /var/log/wtmp : Login records file
  • /var/log/yum.log: Yum log files

In short /var/log is the location where you should find all Linux logs file. However some applications such as httpd have a directory within /var/log/ for their own log files. You can rotate log file using logrotate software and monitor logs files using logwatch software.

 

source from : http://www.cyberciti.biz

Aside

“Reported Attack Page!” – Google Malware Detected Gan… !!!! Huaaa

 

Malem-malem tiba2 gua dapet info.. beberapa website yang gua kelola ga bisa diakses. Pas gua cek ternyata sub domainnya semua juga sama ada peringatan dari Google bahwa situs yang kita buka mengarahkan ke website berbahaya, Di IE ini ga kejadian loh ya.. hebring emang Google detected nya :p . Woow… sempet ribet juga, sambil browsing2 sana-sini gua menemukan beberapa solusi buat mengatasi Report Attack Page ini.

1. Coba lihat koding dari website pada file index,pada source code apakah telah di sisipi kode “” yang oleh google dianggap badware/malware, jika ya hapus kode itu atau periksa semua file di folder website termasuk file .*htaccess. Ini penting pastiin semua file website lo udah bener2 bersih dari Malware ( cek script anehnya satu per satu, kalo ga nemu tanya riri hahaha).

2. Melaporkan ke google bahwa anda sudah menghapusnya, caranya kunjungi http://www.google.com/webmasters/tools kemudian login menggunakan account anda, pada menu dashboard pilih request review setelah mengklik web site anda lalu ikuti petunjuknya.

3. Segera ubah password cPanel dan juga data login admin web elo (jika ada).

Selanjutnya elo bisa melakukan langkah ini ke Googlewebmaster, yaitu :

1. http://www.google.com/webmasters/
2. sign in into webmastertool, gunakan email yang sudah Anda daftarkan jika belum lakukan sign up, misalkan : adminweb@gmail.com. WAJIB GMAIL.
3. Add site
4. pilih Upload an HTML file to your server
5. Download  file ini “this HTML verification file”  [googled66ee5f847dc5b68.html]
6. Upload ke /public_html hosting yang terkena report attack, misalkan mametrockafella.com
7. Klik Confirm successful upload by visiting http://mametrockafella.com/googled66ee5f847dc5b68.html in your browser.
8. Klik tombol verify.

Sekarang tinggal nunggu konfirmasi dari Google…

 

Cara cek file penyusup mengarahkan kemana bisa ke situs http://urlquery.net

Webmin dengan LAMPP

LAMPP yang digunakan di Server linux, bisa diintegrasi ke WEBMIN di server tsb, terutama aplikasi Apache dan MySQL nya. Langkah-langkah konfigurasinya adalah sebagai berikut:

Webmin – Apache Webserver – Module Config – (System configuration)

1. Apache server root directory : /opt/lampp

2. Path to httpd executable : /opt/lampp/bin/httpd

3. Command to start apache : /opt/lampp/lampp startapache

4. Command to stop apache : /opt/lampp/lampp stopapache

5. Command to apply configuration : /opt/lampp/lampp restart

Webmin – MySQL Database Server – Module Config – (System configuration)

1. Path to mysqlshow command : /opt/lampp/bin/mysqlshow

2. Path to mysqladmin command : /opt/lampp/bin/mysqladmin

3. Path to mysql command : /opt/lampp/bin/mysql

4. Path to mysqldump command : /opt/lampp/bin/mysqldump

5. Path to mysqlimport command : /opt/lampp/bin/mysqlimport

6. Command to start MySQL server : /opt/lampp/lampp startmysql

7. Command to stop MySQL server : /opt/lampp/lampp stopmysql

8. Path to MySQL databases directory : /opt/lampp/var/mysql

9. MySQL configuration file : /opt/lampp/etc/my.cnf

XML Parsing Error: undefined entity

XML Parsing Error: undefined entity
Location: jar:file:

///usr/lib/firefox-3.6.18/chrome/toolkit.jar!/content/global/netError.xhtml
Line Number 60, Column 12: <title>&loadError.label;</title>
———–^

 

Open a terminal by hitting CTRL + ALT + T
Enter the following command:

sudo iptables -F

Reboot and see if something changed.

 

If this not resolve, open a terminal and run this:

sudo iptables -F
sudo iptables -X
sudo iptables-save

Reboot

Error Mounting

Wooosaaa…. leptop gua mati tiba2… CRASH!!… anjritt.. bikin panik aja… :C nya ga bisa kebuka…,, Kebetulan gua dual boot Windows sama Ubuntu, biasanya sih gua santai2 aja klo pake Ubuntu klo CRASH masih bisa masuk lewat Live CD atau Dual booting nya Ubuntu.

Pas gua masuk sistem :C lewat Ubuntu gini nih.. Sistem Windows nya Corrupt… jadi pas booting selalu gagal masuk Windows…

Unable to mount 105 GB Filesystem

Error mounting: mount exited with exit code 13: $MFTMirr does not match $MFT (record 0).
Failed to mount ‘/dev/sda2’: Input/output error
NTFS is either inconsistent, or there is a hardware fault, or it’s a
SoftRAID/FakeRAID hardware. In the first case run chkdsk /f on Windows
then reboot into Windows twice. The usage of the /f parameter is very
important! If the device is a SoftRAID/FakeRAID then first activate
it and mount a different device under the /dev/mapper/ directory, (e.g.
/dev/mapper/nvidia_eahaabcc1). Please see the ‘dmraid’ documentation
for more details.

Tanya-tanya om Google , masuk lewat live cd ubuntu atau dual boot.. trus jalanin terminal…

  • sudo apt-get install ntfsprogs

root@mamet-laptop:/home/mamet# sudo apt-get install ntfsprogs
Reading package lists… Done
Building dependency tree
Reading state information… Done
ntfsprogs is already the newest version.
0 upgraded, 0 newly installed, 0 to remove and 0 not upgraded.

  • sudo ntfsfix /dev/sdXY

root@mamet-laptop:/home/mamet# sudo ntfsfix /dev/sdXY
Failed to determine whether /dev/sdXY is mounted: No such file or directory.
Mounting volume… Error opening partition device: No such file or directory.
Failed to startup volume: No such file or directory.
FAILED
Attempting to correct errors… Error opening partition device: No such file or directory.
FAILED
Failed to startup volume: No such file or directory.
Volume is corrupt. You should run chkdsk.

  • sudo ntfsfix /dev/sda2

root@mamet-laptop:/home/mamet# sudo ntfsfix /dev/sda2
Mounting volume… FAILED
Attempting to correct errors…
Processing $MFT and $MFTMirr…
Reading $MFT… OK
Reading $MFTMirr… OK
Comparing $MFTMirr to $MFT… FAILED
Correcting differences in $MFTMirr record 0…OK
Correcting differences in $MFTMirr record 1…OK
Correcting differences in $MFTMirr record 2…OK
Processing of $MFT and $MFTMirr completed successfully.
Setting required flags on partition… OK
Going to empty the journal ($LogFile)… OK
NTFS volume version is 3.1.
NTFS partition /dev/sda2 was processed successfully.

Yaa.. seengaknya masih bisa selametin datanya dulu yaa… hahaa… baru ngebantai si Windows… :p

Itu masih blom selesai… pas lagi proses backup data tiba2 leptop gua mati.. alhasil hardsik eksternal gua ikutan error, sadisss… edannnn… How to Back My Data… di hardisk ekternal itu semua data2 penting gua… ini emang leptop gua udh uzur kali udah ga bisa diajakin tempur.. gua utak-atik tetep ga balik tuh data ada 2 hari. Akhirnya software Recovery Data gua pake sebagai pamungkas. Ada 2 aplikasi yg gua coba Recuva sama GetDataBack… setelah scanning hardisk eksternal 1 Tera selama 2 hari, percobaan pertama gagal.. tiba2 programnya close dengan sendirinya. Gua scan lagi 1 hari akhirnya data gua balik lagi… horeyyyyy…. 🙂

Kalo pas ngopy data gede pastiin hardware lo bener2 tangguh deh kalo ga.. resiko keilangan data ntar hehe….