how to enable slow-query logging in MySQL ? Im using mysql version 4.1.20
well i just added following line in my.cnf can you please tell me is it correct way or not?
Code:
log-slow-queries=/tmp/slow_queries.log
log-update = /var/lib/mysql/updates.log
We have a dedicated server and we host 4 news portals based on e107 and joomla.
My partner on Sunday has upgraded mysql from version 4 to 5. Now the server experiences very high CPU usage and query time on portals has been increased very much.
We used to have query times lower than 1 sec for the main page of [url](which is a site that receives about 2000 visits/day) and now query times are 5-15sec !
Does anybody know if this is makes sense ?
Below is the server Status from WHM :
-------------------------------------
cpdavd up
cpsrvd up
exim (exim-4.69-15.1_cpanel_maildir) up
ftpd up
httpd (2.2.11 (Unix)) up
imap up
interval up
mysql (5.0.67-community-log) up
named (9.3.4) up
pop up
rsyslogd up
spamd up
syslogd up
Server Load 6.97 (1 cpu)
Memory Used 49.4 %
Swap Used 0.23 %
Disk /dev/hda3 (/tmp) DMA+ 70 %
Disk /dev/hda5 (/) DMA+ 22 %
Disk /dev/hda1 (/boot) DMA+ 14 %
Below is the Server Information from WHM :
------------------------------------------
Processor #1 Vendor: GenuineIntel
Processor #1 Name: Intel(R) Core(TM)2 Duo CPU E4600 @ 2.40GHz
Processor #1 speed: 2394.068 MHz
Processor #1 cache size: 2048 KB
Memory Information
Memory: 2064420k/2087424k available (2091k kernel code, 21752k reserved, 846k data, 236k init, 1169920k highmem)
System Information
Linux server.aktoweb.biz 2.6.20-1.2319.fc5 #1 Mon May 21 19:52:13 EDT 2007 i686 i686 i386 GNU/Linux
Physical Disks
hda: WDC WD3200AAJB-00TYA0, ATA DISK drive
hda: hw_config=600d
hda: hw_config=600d
hda: max request size: 512KiB
hda: 625142448 sectors (320072 MB) w/8192KiB Cache, CHS=38913/255/63<6>hda: hw_config=600d
hda: cache flushes supported
Current Memory Usage
total used free shared buffers cached
Mem: 2066428 1756324 310104 0 339576 486636
-/+ buffers/cache: 930112 1136316
Swap: 1052248 2428 1049820
Total: 3118676 1758752 1359924
Current Disk Usage
Filesystem Size Used Avail Use% Mounted on
/dev/hda5 288G 60G 213G 22% /
/dev/hda1 99M 13M 82M 14% /boot
tmpfs 1009M 0 1009M 0% /dev/shm
/dev/hda3 494M 215M 254M 46% /tmp
Below is the CPU/Memmory/Mysql Usage from WHM :
-------------------------------------------------
mysql42.707.300.0
Top Process%CPU 42.7/usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/server.aktoweb.biz.pid --skip-external-locking
Top Process%CPU 42.6/usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/server.aktoweb.biz.pid --skip-external-locking
giaprakigiapraki.com2.7012.111.7
Top Process%CPU 1.3httpd [urll] [/e107_plugins/pm/pm.php?inbox]
Top Process%CPU 1.1httpd [url] [/e107_plugins/forum/forum_viewforum.php?87]
Top Process%CPU 1.0httpd [url] [/e107_plugins/chatbox_menu/chatbox_iframe.php]
adsnetadsnet.gr1.177.900.7
Top Process%CPU 0.5httpd [adsnet.gr] [/www/delivery/lg.php?bannerid16&campaignid4&zoneid5&chan]
Top Process%CPU 0.4httpd [adsnet.gr] [/www/delivery/lg.php?bannerid50&campaignid6&zoneid15&cha]
Top Process%CPU 0.3httpd [adsnet.gr] [/www/delivery/lg.php?bannerid183&campaignid77&zoneid1&ch]
r40r40.gr1.073.340.0
Top Process%CPU 1.1httpd [r40.gr] [/index.php?optioncom_fireboard&Itemid188&funcshowcat&cat]
Top Process%CPU 0.5httpd [r40.gr] [/images/fbfiles/avatars/s_64.png]
Top Process%CPU 0.3httpd [r40.gr] [/index2.php?optioncom_jim&taskxml&no_html1]
root0.278.532.0
Top Process%CPU 0.2[kjournald]
alex123alexandroupoli.net0.030.270.0
Top Process%CPU 0.1httpd [alexandroupoli.net] [/e107_images/banners/banner468x60-alexandroupolinet.gif]
galfasgalfas.gr0.030.270.0
Top Process%CPU 0.1httpd [galfas.gr] [/racing/images/galfas_R40_banner.jpg]
eximstats0.000.000.3
nobody0.001.100.0
mailnull0.000.000.0
unauthenticated0.000.000.0
named0.000.10
Is it possible to log only SQL queries/transactions for a single account (domain)?
I can easily enable logging for the entire server, but I really only need it for a single domain.
MySQL version 5.0.67
I've got 25 domains on a Virtuozzo/Plesk8.6/CentOS5 VPS. Each domain has one up-to-date install of WordPress, most have very little traffic (average 200mb per month), maybe 2 domains get 5-7gb traffic per month.
I monitor port 80 connections and rarely see more than 10 at a time.
That should in my opinion be no problem at all for a VPS with 768mb guaranteed ram and 2.4ghz cpu. I've got 30gb hard drive spare too.
But.... about 8 or 10 times a day it grinds to a complete halt: server load at 500-1000%, sites timing out, plesk takes 3mins to load, often I can't even connect with SSH, and the plesk web server, apache
INSERT INTO module_watchdog_sys_stat (time, type, value, service_id) VALUES(FROM_UNIXTIME(1226404705), 'MAINMEM_USAGE', 17472, 11);
80 seconds sounds like a huge amount of time for a MySQL insert to me! Does anyone know if this is likely to be the cause of my trouble? Some problem with Plesk and the database? Or could it be something else?
Does anyone have snort logging to a mysql database?
I am trying to get that to work so I can view the alerts with BASE.
I followed the instructions but snort is not logging to the DB,
Our local authority is looking into using MySQL database for some internal applications and possibly our website.
Do we need a commercial or enterprise license for this particular use or can we use a free version?
I have the files .myd .myi and .frm files in a archive .tar.gz
My host do not provide shell access.
Is the any way that i can convert the files into .sql queries
Or is there any method other that using shell access.
Or is there any software to convert the database files into .sql query in my desktop computer so that i can restore it using phpmyadmin
My site has been down from several days
What is the good value to set query_cache_size. I have set it to 60 M and here
is the status. Is it good? I am not sure how to check this? Can anyone explain?
query_cache_size=60M
mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 5 |
| Qcache_free_memory | 62666440 |
| Qcache_hits | 922 |
| Qcache_inserts | 381 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 565 |
| Qcache_queries_in_cache | 182 |
| Qcache_total_blocks | 400 |
+-------------------------+----------+
8 rows in set (0.00 sec)
some vBulletin customers facing to that error message, with session table with attachment only. Other just work fine. It's not because of server load. The load is just around 0 - 0.1
I have increased wait_timeout to 200 and max_allowed_packet to 64MB. Each attachment file in those vBulletin is just around 1-3MB.
Is there any idea why and how to fix?
I want to run flush query cache, saying once a week. Is there anyway to schedule it, so system can do it automatically? Currently, I have to login server, and run it manually
View 1 Replies View Relatedhow to add a mysql access host by command line? or or how to add a mysql access host through an sql query?
View 3 Replies View RelatedI've got a problem with an SQL query that is behaving badly. Its probably really obvious, but I can't see the problem!
I have a table with 3 fields:
Code:
money bigint(20)
income int(12)
userID mediumint(7)
I have a row that has a negative money and a value of 0 for income. When I update the money value to add "0" to it, nothing happens (as expected). When I update the money field to add the income field (which is equal to 0) to it, MySQL flips the sign to make the money field positive.
The following queries show the problem:
Code:
mysql> SELECT money, income from users where userID=327961;
+----------------------+--------+
| money | income |
+----------------------+--------+
| -9223372036854775807 | 0 |
+----------------------+--------+
1 row in set (0.00 sec)
mysql> UPDATE `users` SET money = money + 0 WHERE userID =327961;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> SELECT money, income from users where userID=327961;
+----------------------+--------+
| money | income |
+----------------------+--------+
| -9223372036854775807 | 0 |
+----------------------+--------+
1 row in set (0.00 sec)
mysql> UPDATE `users` SET money = money + income WHERE userID =327961;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> SELECT money, income from users where userID=327961;
+---------------------+--------+
| money | income |
+---------------------+--------+
| 9223372036854775807 | 0 |
+---------------------+--------+
1 row in set (0.00 sec)
mysql>
Notice that there is no negative sign after the third select!!
So, we are saying that executing an update where 0 is specified in the query results in the correct action but if you take the 0 from another field it takes the negative sign away...
This for various reasons is a rather urgent problem. This problem has only appeared after upgrading from MySQL 4.0 to MySQl 5.1. This problem does not occur in MySQL 4.1.
I was wondering how to re-enable the root user for mysql?
My webhost "disabled it by default"
I am not very pleased with this, and if you are an owner of a webhost company reading this, please note that.
I'm running a forum system on my server. Loading pages that don't connect to the forum database load normal. Whenever I browse any page that connects to the forum database on my forum, the page either takes ages to load or times out loading. I never exceed using 14% of one of my two cores, I'm using a quarter of my 1.5GB ram. It's only been loading slow like this since I've switched from running Windows to Ubuntu Server 8.10. I enabled logging slow queries on SQL, but nothing's been recorded. I've installed the server packages through Webmin,
when I access the server through its local IP address it's still very slow.
I added the following to my.cnf and restarted mysql
log_slow_queries=/var/log/mysql/mysql_slow-queries.log
I saw at leat 53 slow queries, however, there is no such file in that folder.
Mysql server always busy and slow (in my VPS).
How I can know which website/account uses mysql very heavely?
or just any monitoring Idea?
I want to know which user is responsible of that overuse?
Modernbill v5 is extremely slow. It takes about 2-3 seconds to get to the next page or refresh. It took 5 seconds after clicking 'reports' tab.
In order process, it takes 3-5 seconds just to show the add-ons or update them after hitting "save add-ons". This is unbelievable. I'm using Firefox.
I'm on a cable connection
Server specs:
Dual Xeon 3.4GHz HT
4GB Memory
MySql 5
PHP 5.2.5
Apache 2.2
I applied these optimizations as suggested by an MB tech
Quote:
At the ModernBill level, We have the following custom configurations in our /lib-modernbill/include/config/config.php file:
$CONF["dbType"] = "mysqli";
$CONF["forceCLICurl"] = 0;
$CONF["enableSharedMemoryLocaleCache"] = true;
$CONF["enableSharedMemoryAdminCache"] = true;
$CONF["adminCacheSeconds"] = 300;
$CONF["enableSharedMemoryMBPathCache"] = true;
$CONF["smarty"]["enableCaching"] = true;
$CONF["smarty"]["cacheLifetime"] = 0;
$CONF["smarty"]["enableCompileChecking"] = false;
At the PHP level, we have the following in our php.ini (some of these were recommended in our documentation to start with):
max_execution_time = 300
memory_limit = 128M
error_reporting = 2039
display_errors = Off
register_globals = Off
/etc/my.cnf
[mysqld]
local-infile=0
datadir=/var/lib/mysql
skip-locking
skip-innodb
skip-networking
safe-show-database
query_cache_limit=1M
query_cache_size=32M ## 32MB for every 1GB of RAM
query_cache_type=1
max_user_connections=500
max_connections=1500
interactive_timeout=10
wait_timeout=28800
connect_timeout=20
thread_cache_size=128
key_buffer=512M ## 128MB for every 1GB of RAM
join_buffer=4M
max_connect_errors=20
max_allowed_packet=16M
table_cache=1024
record_buffer=4M
sort_buffer_size=4M ## 1MB for every 1GB of RAM
read_buffer_size=4M ## 1MB for every 1GB of RAM
read_rnd_buffer_size=4M ## 1MB for every 1GB of RAM
thread_concurrency=8 ## Number of CPUs x 2
myisam_sort_buffer_size=64M
server-id=1
collation-server=latin1_general_ci
[mysql.server]
user=mysql
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
open_files_limit=8192
[mysqldump]
quick
max_allowed_packet=16M
[mysql]
no-auto-rehash
#safe-updates
[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[mysqlhotcopy]
interactive-timeout
What I did here was create /var/log/mysqld.slow.log for queries that are taking one second or longer. After around 36 hours, here is what I came up with. Since the file size of /var/log/mysqld.slow.log was 219KB, posting it in its entirety it not practical. Therefore, I used a program, the perl script known as mysql_slow_log_parser found on www.retards.org, to parse the mysqld.slow.log to make it easier to interpret and read. The result was that the original filesize of 219KB was converted to the smaller size of 14KB....
View 7 Replies View RelatedI'm hosting about 6 private websites on my server, and some have problems.
My biggest website is a vb forum with 30.000 members and 1.5 million posts.
When i browse with firefox i see that vb forum loads fast, but phpads which is integrated on the top and footer loads very slow. When i browse with IE, it takes like 10 seconds before the whole site is loaded and sometimes IE even crashes.
When i try to login on another website / admin panel (oscommerce), i get a mysql gone away error, that means timeout. But why does it show that? I ran mysqlreport and can't find anything why it should display mysql gone away.
# ./mysqlreport
MySQL 5.0.15-standard uptime 3 19:36:47 Mon Feb 5 11:08:10 2007
__ Key _________________________________________________________________
Buffer usage 78.94M of 96.00M %Used: 82.23
Write ratio 0.60
Read ratio 0.00
__ Questions ___________________________________________________________
Total 19.61M 59.47/s
Slow 9 0.00/s %Total: 0.00 %DMS: 0.00
DMS 7.17M 21.74/s 36.56
__ Table Locks _________________________________________________________
Waited 7.57k 0.02/s %Total: 0.05
Immediate 16.50M 50.03/s
__ Tables ______________________________________________________________
Open 1.27k of 1800 %Cache: 70.56
Opened 1.82k 0.01/s
__ Connections _________________________________________________________
Max used 47 of 500 %Max: 9.40
Total 2.62M 7.95/s
__ Created Temp ________________________________________________________
Disk table 6.77k 0.02/s
Table 635.25k 1.93/s
File 50.37k 0.15/s
I can't find any problems on the server which may lead to this strange problem.
The server load is normal, around 1 to 1.5. No weird errors in the apache error log or system log.
This strange behaviour started 2 weeks ago, and appears 2 to 3 time a week.
The system is running:
centos 4.3 64-bit
kernel 2.6.16.17 #1 SMP
directadmin 1.29.0
mysql 5.0.15
php 5.2
apache 2
Zend optimizer 3.2.0
Eaccelerator 1.95
One thing i noticed is that every night when the directadmin cron finished, the server acts normal again.
The directadmin cronjobs are:
* * * * * root /usr/local/directadmin/dataskq
2 0-23/6 * * * root echo 'action=vacation&value=all' >> /usr/local/directadmin/data/task.queue;
#5 5 * * 0 root /sbin/quotaoff -a; /sbin/quotacheck -augm; /sbin/quotaon -a;
20 4 1 * * root echo 'action=reset&value=all' >> /usr/local/directadmin/data/task.queue
0 4 * * * root echo 'action=check&value=license' >> /usr/local/directadmin/data/task.queue
10 0 * * * root /usr/local/directadmin/plugins/awstats/hooks/cgi-bin/awstats_updateall.pl now -awstatsprog=/usr/local/directadmin/plugins/awstats/hooks/cgi-bin/awstats.pl && echo 'action=tally&value=all' >> /usr/local/directadmin/data/task.queue
I have windows server 2012 and Plesk v12.0.18 #30 - I noticed speed problem.
When I try to connect to mySQL with:
$conn = new mysqli('localhost', 'joomla_***', '*****', 'joomla_test');
Connection takes 1 second!!!When I try to connect with '127.0.0.1' instead of 'localhost' connection takes 0,009 seconds.
1. I tried solution to add line '127.0.0.1 localhost' to c:windowssystem32driversetshosts - but no luck.
2. I tried to edit C:Program Files (x86)ParallelsPleskMySQLDatamy.ini - adding lines
bind-address=127.0.0.1
skip-name-resolve
then restarted mySql but 1 second lag time on connection was still there.
3. I even tried to disable IPv6 but still no luck
I guess all users windows windows server 2012 has the same problem??!?!...
I freshly installed Plesk 12.0 on Ubuntu 14.04. Everything works accept the MySQL database creation.
When I try to create one, i get this error :
error: the test connection to the database server has failed because of network problems: connection refused...
Firstly this question is not related to running a web hosting business, but to dedicated servers hosting our own websites.
I was just wondering, other than security reasons, what is the importance (if any) of keeping Apache/WWW logs? Are there legal reasons?
If it is best to keep Apache logs, how should we go about this? If we don't look after them properly there will be massive amounts of logs being kept - what is the best way to store them? I know they can be "rolled over" and kept for a certain amount of time, which is the best utility to do this?
The OS is Linux (CentOS) and the web servers are Apache and Lighttpd.
and should proxy/gateway/cache server logs be kept?
I just bought a new dedicated server and have got Lxadmin on it. Now lxadmin , automatically uses a different loggin system than I would want it to use. It creates a seperate log file for each doamin.
They are located in /home/httpd/EXAMPLE.com/stats
EXAMPLE.com-error_log
EXAMPLE.com-custom_log
Now the problem is that I have literally hundreds of domains and I want it to instead log at a single place and not at these hundreds of seperate places.
This is the root cause of my problem
# The location and format of the access logfile (Common Logfile Format).
# If you do not define any access logfiles within a <VirtualHost>
# container, they will be logged here. Contrariwise, if you *do*
# define per-<VirtualHost> access logfiles, transactions will be
# logged therein and *not* in this file.
I am unable to have any kind of centralised logging command because of the individual entries for each Virtual host.
Even if i can manually remove those entries once, and new domain created by lxadmin will have that same problem. Morevoer, any kind of update of lxadmin which rebuilds the database will also ruin all the hardwork that I put in.
Is there any suggestion / solution to this problem? Have centralised data at one place in a single file?
Who here uses remote logging?
If yes state what you are using after voting on the poll.
