Very Slow Query Time After Upgrade To Mysql 5
Apr 8, 2009
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
View 8 Replies
ADVERTISEMENT
Apr 23, 2007
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
View 6 Replies
View Related
Nov 11, 2008
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?
View 3 Replies
View Related
Nov 17, 2008
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?
View 2 Replies
View Related
Apr 30, 2008
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
View 2 Replies
View Related
Jan 4, 2007
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)
View 2 Replies
View Related
Aug 12, 2007
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?
View 1 Replies
View Related
Dec 5, 2007
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 Related
Jun 9, 2009
I have a dedicated server that I am renting that appears to randomly respond very slowly for some reason (pings are between 90-4000ms with about 75% packet loss).
This has been happening lately (about once a week for the past 4 weeks) and I thought I had originally found the cause of the problem but it appears I haven't.
Originally, when this problem was happening we were getting an abnormally high about of traffic but a reboot always fixed the issue. I've checked every where I can think of but I cannot find anything out of the ordinary.
ight now we're averaging less than 2Mbps in traffic, the CPU load is less than 0.75, the memory usage is at 55% with no swap being used, Apache connections are normal, no active SSH, FTP, or mail connections, I've restarted all services which had no impact, I've checked the access and error logs but can't see anything out of the ordinary.
I have a ticket open with the data center but I'm trying to figure this out on my own to fix whatever is causing this so I can move my data over to a new server we just got setup over the weekend in a local data center.
View 7 Replies
View Related
May 3, 2008
I ordered a server few days ago and it still has not been setup i had verify my information with them and they still have not setup my server I tried talking to them but nothing... Should I wait some more or cancel and move to Softlayer?
Offtopic - Which network is better SF or The Planet?
View 9 Replies
View Related
Oct 6, 2008
I recently made a shift to FDC for one of my servers that I use for downloadable content.
I went for a $199 server with a 100mbps shared bandwidth port. Now, the only problem I seem to be having with it is that downloads appear to be, I would say recognized, very slowly by the server before they start, however, once the download starts I get a full speed. This usually happens during peak times.
I'm using about 10/15mbps on the server according to my cPanel statistics. I just wanted to ask here prior to contacting FDC on this, whether this slow server response time has something to do with the bandwidth port, or perhaps the server's RAM, CPU, etc? Given I'm using quite minimal bandwidth on the port, should I consider having them fix this or upgrade the server instead?
And if I had to upgrade, would I need to upgrade the port or the RAM, or both for this?
View 3 Replies
View Related
Apr 30, 2009
how 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 Related
Jan 10, 2007
I'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.
View 1 Replies
View Related
Sep 20, 2008
Hosting: VPS Hosting with 320 MB min and 1024 MB burst.
Websites: All the websites (about 20+) on my current vps hosting are very database heavy. They include blogs, ecommerce sites and a few small forums.
Before: In the beginning, when I had less than 10 websites on my hosting, the web hosting worked great. No hiccups and all websites worked flawlessly.
Now: Now that I'm hosting 20+ websites, I find that websites sometimes tend to timeout or not show up in my browser as well as browsers in other locations.
Do you think I need more resources? Do you think that upgrading to Hybrid Server hosting will rectify the situation?
View 10 Replies
View Related
Jun 13, 2007
I have a number of web sites on a dedicated server. Some of these web sites are a few years old. Are there any issues upgrading from MySQL 4.1 to 5.x? Are there web sites that may have compatibility issues?
View 6 Replies
View Related
Dec 28, 2008
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.
View 13 Replies
View Related
Feb 25, 2007
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.
View 9 Replies
View Related
Sep 23, 2008
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?
View 7 Replies
View Related
Dec 29, 2007
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
View 1 Replies
View Related
Jun 18, 2009
we have cpanel server and how to upgrade from mysql 5.0.45 to 5.0.77?
View 2 Replies
View Related
May 23, 2008
I am running the op Centos and also have got Cpanel installed on my VPS.
Yesterday I just upgraded MYSQL from 4 to 5 using the Cpanel script. And when I run mysql -v it says that I am running Mysql 5.
When I run phpinfo it displays that I am still running mysql 4.
I have tired rebuilding php 5 by running:-
PHP Code:
./configure --with-mysqli --with-mysql=/usr
Make
But I am keeping on getting the follow error message:-
gcc: /usr/lib/mysql/libmysqlclient.so: No such file or directory
When I check in the direct, all I have got is libmysqlclient.a, libmysqlclient.la*, libmysqlclient_r.a, libmysqlclient_r.la
So how can I check extactly what has been installed for MYSQL 5 and where it been installed and see what could be missing? Because all I did was to run it from the cpanel mysql 5 upgrade.
View 4 Replies
View Related
Mar 19, 2007
Do you recommend MYSQL upgrade from 4 to 5?
Note that the server Contain about 200 accounts and alot of databases
View 4 Replies
View Related
Apr 10, 2007
Currently I have:
Quote:
PHP 4.3.9
Âåðñèÿ MySQL 4.1.20
Shall I upgrade either MySQL or PHP to boost performance of vbulletin forum?
View 8 Replies
View Related
Sep 16, 2007
i wanted LXADMIN HIAB they had to install CENTOS 4.5 32bit for me
so i ended up with older versions of mysql and php.
my php wasnt even compiled with GD support
mysql: 4.1.20
php: 4.3.9
how come lxadmin is running on lighttpd while all the contents of my website shows its running on apache 2.0.52?
View 2 Replies
View Related
Jul 18, 2007
my VPS only has php version 4.3.9 and i would like php 5. also it only a mysql 4 and i would like mysql .
The os is CentOS 4.4
How do i upgrade it?
View 10 Replies
View Related
Oct 26, 2007
how to upgrade php and mysql in ssh ?
View 12 Replies
View Related
Jun 5, 2008
Mysql is restarting all the time, but when I look at the log file, I got the following error message. Anyone has any idea about this issue?
080604 22:48:45 InnoDB: Started; log sequence number 0 1304279005
080604 22:48:45 [Warning] mysql.user table is not updated to new password format; Disabling new password usage until mysql_fix_privilege_tables is run
080604 22:48:45 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.45-community' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition (GPL)
080604 22:48:46 [Note] /usr/sbin/mysqld: Normal shutdown
080604 22:48:46 InnoDB: Starting shutdown...
080604 22:48:49 InnoDB: Shutdown completed; log sequence number 0 1304279005
080604 22:48:49 [Note] /usr/sbin/mysqld: Shutdown complete
View 11 Replies
View Related
