Mysql Tuning

Dec 19, 2008

I have Linux server with WHM/Cpanel with 2000 domains now my problem is.Mysql is using 90-100% CPU usage and 1500-2000 queries are running at a time so please guide me how can i optimize it and how can i tune mysql server so it doesn't go high.

I have configure my.cnf file as --->
max_allowed_packet = 4M
set-variable = max_connections=100
safe-show-database
query_cache_limit=1M
query_cache_size=128M
query_cache_type=1
key_buffer_size=256M
long_query_time = 3
table_cache=9092

how can i do this and any changes is require ?

View 2 Replies


ADVERTISEMENT

Tuning MySQL

Apr 4, 2007

I've noticed I'm getting high numbers for Handler_read_rnd_next.

How can I dig deeper to find out which tables/queries are causing this?

View 3 Replies View Related

Tuning Sites For MySQL

Jun 16, 2008

What have you found to be the best tuning sites for MySQL?

I'm getting into a bit of trouble. We have a weather site, and with all of the traffic, we're getting a little tapped out. When the loads hit between 134 and 160, the mail clients start to time out. Apache is still pretty fast, although it takes a little longer once you cross loads of 80, 5 second page loads, but when it hits between 130 and 160, I'm seeing 15-20 second page loads. DA is impossible above 80 but SSH is still very workable. Apache is tweaked to the max. I've kicked up some of the sizes in MySQL several weeks ago, and that did it them. However, we're taking on about 22,000 to 25,000 uniques an hour now. We normally can handle that no problem, but people are asking for maps a lot more now with the flooding and all. That requires a lot of MySQL lookups and the CPU creating a lot more maps. The maps I already cache for the duration, which is 15 minutes. The only horse I have left to whip is MySQL. After that, it will probably be a move to FreeBSD 7, but I'd like to throw in a few tweaks yet before we do that.

View 8 Replies View Related

About Tuning Apache

Jun 7, 2009

Can anybody show me how to tune the prefork module I don't know where to begin.

View 4 Replies View Related

Kernel Tuning

Apr 24, 2009

as part of a project I have lately been looking into various aspects of kernel tuning. Most notably lately tuning the TCP stack for more efficient memory usage/throughput.

Thought I would start this thread to mention some of the tools I'd found for doing testing and see what anyone else had to recommend.

So far my favorite of the bunch is nuttcp. Its easy to use and gives a very good idea of how much of your bandwidth you are able to utilize.

A few interesting web pages are as follows for anyone interested in the topic:

[url]- Tuning TCP for High Bandwidth Delay networks

[url]- TCP Tuning Cook book, some interesting information in there as well

[url]...formanceTuning - Performance Tuning TWiki. Has a list of useful tools, flags for existing tools and ways to monitor network performance from a system level, along with some suggestions of things to correct

View 0 Replies View Related

Disk Tuning

May 5, 2009

shared hosting env?

/etc/sysctl.conf:
vm.dirty_background_ratio = 20
vm.dirty_ratio = 60

blockdev --setra 16384 /dev/sda

View 3 Replies View Related

Tcp/ip Tuning For Better Speed

Apr 11, 2008

i have this couple of windows 2003 servers, colocated in data center, i need to improve download speeds to our customers who are at least 200ms away, the end user is not using download accelarator,

is there a way that any settings to be done on server so that per thread speed can be increased, this case the server and client both have the ability to make a connection at more than a megabit speed. i did some search but all the articles point to end user and not the server saying to increase tcp window size etc.. not sure if those articles relate to server side changes.

View 14 Replies View Related

Tuning-primer.sh Results, How To Act On Them

Nov 22, 2007

Quote:

MySQL Version 4.1.22-standard i686

Uptime = 0 days 0 hrs 4 min 15 sec
Avg. qps = 17
Total Questions = 4479
Threads Connected = 1

Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
[url]

SLOW QUERIES
Current long_query_time = 10 sec.
You have 1 out of 4491 that take longer than 10 sec. to complete
The slow query log is NOT enabled.
Your long_query_time may be too high, I typically set this under 5 sec.

WORKER THREADS
Current thread_cache_size = 128
Current threads_cached = 6
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 2000
Current threads_connected = 1
Historic max_used_connections = 7
The number of used connections is 0% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating

MEMORY USAGE
Max Memory Ever Allocated : 96 M
Configured Max Per-thread Buffers : 10 G
Configured Max Global Buffers : 58 M
Configured Max Memory Limit : 10 G
Total System Memory : 3.95 G

Max memory limit exceeds 85% of total system memory

KEY BUFFER
Current MyISAM index space = 78 M
Current key_buffer_size = 16 M
Key cache miss rate is 1 : 735
Key buffer fill ratio = 8.00 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere

QUERY CACHE
Query cache is enabled
Current query_cache_size = 32 M
Current query_cache_used = 4 M
Current query_cach_limit = 1 M
Current Query cache fill ratio = 14.83 %
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current record/read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 1.00 M
You have had 0 queries where a join could not use an index properly
Your joins seem to be using indexes properly

OPEN FILES LIMIT
Current open_files_limit = 10000 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_cache value = 1024 tables
You have a total of 721 tables
You have 93 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 32 M
Of 212 temp tables, 0% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 1 M
Current table scan ratio = 17754 : 1
You have a high ratio of sequential access requests to SELECTs
You may benefit from raising read_buffer_size and/or improving your use of indexes.

TABLE LOCKING
Current Lock Wait ratio = 1 : 76
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'

how to make the changes in red? My server works good for awhile, but then gets REALLY REALLY slow.

View 1 Replies View Related

Tuning A FreeBSD VPS For Network Performance

Jul 13, 2009

I have a VPS system on the west coast of the US, and access it from the east coast. Sometimes I can get 1Mbyte/sec downloads, and other times it is as bad a 250KB/sec.

I have done some pings, and have not seen any packet loss. I've experimented with sysctl and changed some parameters to hopefully help, but really haven't seen much of a difference.

Does anyone have a recommendation as to what I could do different to squeeze a little more speed out of the connection? The problem is that from both sides of the US, I see ping times (depending on different ISPs on the east coast) from 80ms-120ms.

Here is what I've tuned so far:

kern.ipc.nmbclusters=65535
net.inet.tcp.sendbuf_max=16777216
net.inet.tcp.recvbuf_max=16777216
net.inet.tcp.sendspace=78840
net.inet.tcp.recvspace=78840
net.inet.tcp.mssdflt=1460
net.inet.tcp.slowstart_flightsize=52
net.inet.tcp.inflight.min=29200
net.inet.tcp.sendbuf_inc=8760
net.inet.tcp.recvbuf_inc=8760
net.inet.tcp.sendbuf_auto=1
net.inet.tcp.recvbuf_auto=1
net.inet.tcp.delayed_ack=1
net.inet.tcp.delacktime=150
net.inet.tcp.inflight.enable=1

View 1 Replies View Related

I Moved Mysql From /var To /backup/mysql, Mysql Is No Longer Working

May 8, 2009

Since my /var partition is full, so I moved /var/lib/mysql to /backup/mysql/.

Seems all the files are copied and I changed my.cnf reboot mysql, but all the web sites using db is not working anymore..

View 4 Replies View Related

Installed MySQL In /usr/local/mysql But Error In /var/lib/mysql

Jan 21, 2007

I installed the MySQL binary packages in /usr/local/mysql/ after removing the MySQL RPM package. MySQL is functioning when I executed /usr/local/mysql/bin/safe_mysqld. I reinstalled MySQL before I installed PHP. When I used a PHP script to access a MySQL database, it outputs an error:

Code:
Warning: mysqli::mysqli() [function.mysqli-mysqli]: (HY000/2002): can't connect to local mysql server through socket /var/lib/mysql/mysql.sock in index.php on line 2
However, I installed MySQL in /usr/local/mysql, not in /var/lib/mysql. How do I fix MySQL?

View 1 Replies View Related

Mysql.sock Gone And Mysql Restart Doesn't Create It

Jun 16, 2008

For some reason mysql wont start, i have tried restarting mysql but it wont, it says FAILED. The mysql.sock file seems to have disappeared and i cannot find it anywhere.

View 5 Replies View Related

How To Restore Mysql Databases From /var/lib/mysql/user_database/ Files Only

Feb 11, 2008

I recently had a harddrive failure and luckliy I can still access certain directories on this failed drive. I can still access the /var/lib/mysql/ directory which holds all the users databases and have backed all these up separately using tar.

Now what I need to know is how do you restore these database files to another server? I tried simply untar'ing one of these to the new servers /var/lib/mysql/ direcotry and it stuffed Mysql up - it went offline. I had to get a cpanel tech to bring Mysql back online.

how can I get these database files to fully work on a new server?

View 2 Replies View Related

Knowledge Of Mysql Exploit Or Mysql Injection?

Jan 17, 2008

Can someone recommended me some one with knowledge of mysql exploit or mysql injection, it seem to our VB forum have issue with database load..

View 5 Replies View Related

MySQL Monitor Shows MySQL As Offline When It's Not

Jan 15, 2007

I've been racking my brains with this problem for the last couple of months and have made zero progress. I've asked a lot of people if they know what might be wrong here, but none of them have any idea.

Basically, the problem is that any sort of service monitor I put on my server shows MySQL as being down/offline, even when I know for sure it's up and running perfectly fine. All other services report a green light.

The mysqld service is running on port 3306, which is open both inbound and outbound on my APF config, so as far as I can tell it's nothing to do with the firewall (I won't rule that out though).

The other odd thing is that MySQL shows a green light within WHM, but not anywhere else.

Does anyone know what might be wrong here? Am I missing something entirely fundamental and obvious?

View 11 Replies View Related

Can I Move /var/lib/mysql To /home/mysql

Apr 15, 2009

my /var partition is full,

im not sure if any files i can remove to get more free space on /var partition.

because there are more free space on /home,

i think if i can move all the /var/lib/mysql to /home/mysql,

if yes,howcan i move it and do any change,

can let the sql data do not lose,

andrun well in the feature ?

View 14 Replies View Related

Uninstall MySQL 3.23 And Install MySQL 5.0

Mar 28, 2007

I've rented a dedicated server that comes with Red Hat Enterprise Linux 4 and MySQL 3.23 but I need at least MySQL 4.1 or higher. My knowledge about GNU/Linux is very limited, so I've read some documents at mysql.com and now I think I have to follow these steps:

1. Uninstall MySQL 3.23 with this command line:
shell> rpm -e mysql-*.rpm

2. Install the server and client RPMs of MySQL 5.0:
shell> rpm -i MySQL-server-VERSION.i386.rpm
shell> rpm -i MySQL-client-VERSION.i386.rpm

View 2 Replies View Related

Easy To Upgrade From MySQL 4.1 To MySQL 5

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

MySQL Error : MySQL Server Has Gone Away

Dec 18, 2007

I keep getting this error :
MySQL Error : MySQL server has gone away

How do I fix it?

my.cnf contents

Quote:

[mysqld]
safe-show-database
old_passwords
back_log = 75
skip-innodb
max_connections = 800
key_buffer = 48M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 3000
thread_cache_size = 128
wait_timeout = 90
connect_timeout = 30
tmp_table_size = 128M
max_heap_table_size = 64M
max_allowed_packet = 256M
net_buffer_length = 16384
max_connect_errors = 10
thread_concurrency = 4
read_rnd_buffer_size = 786432
bulk_insert_buffer_size = 8M
query_cache_limit = 3M
query_cache_size = 48M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = MyISAM
max_user_connections = 500

[mysqld_safe]
nice = -5
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]
interactive-timeout

View 5 Replies View Related

Your PHP MySQL Library Version 5.0.75 Differs From Your MySQL Server Version

Jun 27, 2009

I have recently upgraded mysql server from 5.0.75 to 5.1.31 on my Ubuntu server 9.04 32 bit. After that when I am running phpmyadmin it is printing a warning :

Your PHP MySQL library version 5.0.75 differs from your MySQL server version 5.1.31.

View 2 Replies View Related

Upgrade From MySQL 4 To MySQL 5

Jun 3, 2008

I'm trying to upgrade from MySQL 4 to MySQL 5 for performance reasons. MySQL 4 was working great so I decided to just go ahead and upgrade to MySQL 5 via CPanel. The upgrade appeared to go fine however I now get this error when trying to start MySQL.

/usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)' Check that mysqld is running and that the socket: '/var/lib/mysql/mysql.sock' exists! mysql has failed, please contact the sysadmin (result was "Warning, no valid mysql.sock file found.mysql has failed").

I went ahead and did a forced re-install of MySQL 5, but that didn't help. Apache has been updated as well since then, but that did no good, and the MySQL process isn't starting at all.

Its CENTOS with PHP5.

View 1 Replies View Related

Difference Between --with-mysql And --with-mysql=dir

Jun 21, 2007

I am not sure what difference between --with-mysql and --with-mysql=/usr to compile php 4.4.7

because I get error message when I use --with-mysql=/usr

the error message is like Mysql header can not be found or similar thing.

it ok when I use --with-mysql

so, I am afraid that can I use php and mysql without problem by using --with-mysql?

View 1 Replies View Related

Upgrade Mysql MySQL 4.1 To 5

Jan 20, 2007

I'd like to upgrade my current mysql 4.1 version to mysl 5, but i can't find a good tutorial or explenation! I've been searching for hours now without any good results.
I've downloaded the lastest stable mysql 5 .rpm, and tried to install it with rpm -i mysql.version.rpm, but it says the following:

# rpm -i MySQL-server-standard-5.0.27-0.rhel4.i386.rpm
warning: MySQL-server-standard-5.0.27-0.rhel4.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
error: Failed dependencies:
MySQL conflicts with mysql-4.1.20-1.RHEL4.1.i386
MySQL-server conflicts with mysql-server-4.1.20-1.RHEL4.1.i386

I guess that means i have to uninstall mysql 4.1 first.

ok...

# rpm -e mysql-server-4.1.20-1.RHEL4.1.i386
warning: /var/log/mysqld.log saved as /var/log/mysqld.log.rpmsave

Let's try to install it now.

# rpm -i MySQL-server-standard-5.0.27-0.rhel4.i386.rpm
warning: MySQL-server-standard-5.0.27-0.rhel4.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
error: Failed dependencies:
MySQL conflicts with mysql-4.1.20-1.RHEL4.1.i386

Ok, let's remove that.

# rpm -e mysql-4.1.20-1.RHEL4.1.i386
error: Failed dependencies:
libmysqlclient.so.14 is needed by (installed) perl-DBD-MySQL-2.9004-3.1.i386
libmysqlclient.so.14 is needed by (installed) dovecot-0.99.11-4.EL4.i386
libmysqlclient.so.14 is needed by (installed) php-mysql-4.3.9-3.22.i386
libmysqlclient.so.14(libmysqlclient_14) is needed by (installed) dovecot-0.99.11-4.EL4.i386
libmysqlclient.so.14(libmysqlclient_14) is needed by (installed) php-mysql-4.3.9-3.22.i386
mysql = 4.1.20-1.RHEL4.1 is needed by (installed) mysql-devel-4.1.20-1.RHEL4.1.i386

Does upgrade work?

# rpm -Uvh MySQL-server-standard-5.0.27-0.rhel4.i386.rpm warning: MySQL-server-standard-5.0.27-0.rhel4.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
error: Failed dependencies:
libmysqlclient.so.14 is needed by (installed) perl-DBD-MySQL-2.9004-3.1.i386
libmysqlclient.so.14 is needed by (installed) dovecot-0.99.11-4.EL4.i386
libmysqlclient.so.14 is needed by (installed) php-mysql-4.3.9-3.22.i386
libmysqlclient.so.14(libmysqlclient_14) is needed by (installed) dovecot-0.99.11-4.EL4.i386
libmysqlclient.so.14(libmysqlclient_14) is needed by (installed) php-mysql-4.3.9-3.22.i386

Clearly not.

How do i upgrade mysql?

View 1 Replies View Related

Ugrading From Mysql 4.0.x To Mysql 5.0.x

Feb 16, 2007

I have a big issue importing 4.0 databases with mysql 5.0.27.

I did a mysqldump -pxxxxx --force --all-databases > file.sql on server with mysql 4.0

But when I tried to import the file on server with mysql 5.0, this is what I got:

ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1

I even tried to force import and I got:
ERROR 1046 (3D000) at line x: No database selected for every database.
I checked sql file and CREATE DATABASE xx and USE xx were both added.

I tried this with a single database and worked but with all not

View 6 Replies View Related

Mysql And Php: What If "Client API Version" Doesn't Match Mysql Version

Jun 1, 2007

loading a phpinfo() shows that the mysql variable "Client API version" on my server doesn't match the version of mysql installed ( 4.0.20 when mysql version is 4.1.21).

Can this cause trouble and php or mysql errors on scripts i'm running?

View 0 Replies View Related

What In /var/lib/mysql/

Nov 28, 2008

What are the files in this directory - /var/lib/mysql/

Files like
#sql_2a6c_0.MYD
#sql_2a6c_2.MYD
etc
etc

I have a stack of them all 200MB plus in size

I need to cleanup /var as its 99% full, what else can I clean up?

View 11 Replies View Related

MySQL 5.1 GA

Nov 28, 2008

MySQL 5.1 GA

Has anyone tried to migrate already?

View 6 Replies View Related

MySQL Down

Apr 15, 2008

Mysql usage is very high now and most time server load is high.

Where is issue and how can resolve this?

View 11 Replies View Related

MySQL = How Much RAM

Oct 22, 2008

I'm trying to understand, generally, on a Hybrid VPS with 1gig guaranteed/4gig burst- for small sites that might be installing stuff like Wordpress, a Forum, Joomla!, etc., how many MySQL databases this system can support.

I realize traffic would be a major factor but for discussion sake lets assume an average of 50 users for each db with a peak of 100 users.

Just trying to get a 'rule of thumb'. Wondering also, how much RAM (resources?) an idle db consumes.

View 10 Replies View Related

Mysql 4.1 Or 5

Feb 25, 2008

i've been using 4.1 without problems but now i'm switching servers and i am wondering whether its a good idea to go for ver 5. How do they compare in terms of performance/security etc? Whats recommended for web use?

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved