Results 1 to 21 of 21
Thread: MySQL taking up all resources
-
06-03-2011, 09:36 PM #1Newbie
- Join Date
- Sep 2004
- Posts
- 15
MySQL taking up all resources
My Server was running smoothly for about two years. I have one huge genealogy site there but suddenly MySQL started to eat up all resources. Without changing the queries or program running the data base.
I started tweaking my.cnf which didn't make things better, then since the utility tuning-primer.sh suggested increasing memory I decided to buy an extra 4 Gb after the database is big. No improvement. Following the suggestions in the utility made things only worse, mysqltuner.pl didn't give any right answers either.
Now my.cnf looks like
------------
[mysqld]
local-infile=0
datadir=/var/lib/mysql
skip-locking
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=200
max_connections=2000
interactive_timeout=10
wait_timeout=600
max_allowed_packet = 64M
connect_timeout=20
thread_cache_size=128
thread_cache_size=128
key_buffer=512M ## 128MB for every 1GB of RAM
join_buffer=1M
max_connect_errors=20
max_allowed_packet=16M
table_cache=2613
query_cache_limit = 4M
record_buffer=1M
sort_buffer_size=4M ## 1MB for every 1GB of RAM
read_buffer_size=4M ## 1MB for every 1GB of RAM
read_rnd_buffer_size=1M ## 1MB for every 1GB of RAM
thread_concurrency=4 ## 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
----------------
Anybody any ideas?
-
06-03-2011, 09:42 PM #2Newbie
- Join Date
- Sep 2004
- Posts
- 15
I added more but the board didn't allow me to post everything at one time, so here is additional information
mysqltuner gives the following output:
----------------
>> MySQLTuner 1.0.0 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.57
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 505M (Tables: 2135)
[--] Data in InnoDB tables: 59M (Tables: 277)
[--] Data in MEMORY tables: 6K (Tables: 2)
[!!] Total fragmented tables: 287
-------- Performance Metrics -------------------------------------------------
[--] Up for: 16h 10m 3s (190K q [3.270 qps], 9K conn, TX: 2B, RX: 39M)
[--] Reads / Writes: 84% / 16%
[--] Total buffers: 570.0M global + 10.2M per thread (2000 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 20.5G (258% of installed RAM)
[OK] Slow queries: 0% (66/190K)
[OK] Highest usage of available connections: 2% (59/2000)
[OK] Key buffer size / total MyISAM indexes: 512.0M/229.5M
[OK] Key buffer hit rate: 99.2% (86M cached / 691K reads)
[OK] Query cache efficiency: 51.4% (71K cached / 139K selects)
[!!] Query cache prunes per day: 12640
[OK] Sorts requiring temporary tables: 0% (93 temp sorts / 15K sorts)
[!!] Temporary tables created on disk: 41% (8K on disk / 20K total)
[OK] Thread cache hit rate: 99% (59 created / 9K connections)
[OK] Table cache hit rate: 24% (2K open / 10K opened)
[OK] Open file limit used: 44% (4K/10K)
[OK] Table locks acquired immediately: 99% (104K immediate / 104K locks)
[!!] InnoDB data size / buffer pool: 59.3M/8.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
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
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (> 32M)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
innodb_buffer_pool_size (>= 59M)
---------------
and tuning-primer.sh gives the following:
---------------
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -
MySQL Version 5.1.57 i686
Uptime = 0 days 16 hrs 11 min 3 sec
Avg. qps = 3
Total Questions = 190369
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:
http://dev.mysql.com/doc/refman/5.1/...variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service
SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10.000000 sec.
You have 66 out of 190390 that take longer than 10.000000 sec. to complete
Your long_query_time seems to be fine
BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.1/...-recovery.html
WORKER THREADS
Current thread_cache_size = 128
Current threads_cached = 58
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 = 59
The number of used connections is 2% 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
INNODB STATUS
Current InnoDB index space = 16 M
Current InnoDB data space = 59 M
Current InnoDB buffer pool free = 0 %
Current innodb_buffer_pool_size = 8 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory
MEMORY USAGE
Max Memory Ever Allocated : 1.12 G
Configured Max Per-thread Buffers : 19.89 G
Configured Max Global Buffers : 554 M
Configured Max Memory Limit : 20.43 G
Physical Memory : 7.91 G
nMax memory limit exceeds 90% of physical memory
KEY BUFFER
Current MyISAM index space = 212 M
Current key_buffer_size = 512 M
Key cache miss rate is 1 : 124
Key buffer free ratio = 86 %
Your key_buffer_size seems to be fine
QUERY CACHE
Query cache is enabled
Current query_cache_size = 32 M
Current query_cache_used = 30 M
Current query_cache_limit = 4 M
Current Query cache Memory fill ratio = 94.37 %
Current query_cache_min_res_unit = 4 K
However, 8515 queries have been removed from the query cache due to lack of memory
Perhaps you should raise query_cache_size
MySQL won't cache query results that are larger than query_cache_limit in size
SORT OPERATIONS
Current sort_buffer_size = 4 M
Current read_rnd_buffer_size = 1 M
Sort buffer seems to be fine
JOINS
Current join_buffer_size = 1.00 M
You have had 130 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.
Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.
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_open_cache = 2613 tables
Current table_definition_cache = 256 tables
You have a total of 2613 tables
You have 2608 open tables.
Current table_cache hit rate is 24%
, while 99% of your table cache is in use
You should probably increase your table_cache
You should probably increase your table_definition_cache value.
TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 16 M
Of 12238 temp tables, 41% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.
TABLE SCANS
Current read_buffer_size = 4 M
Current table scan ratio = 28164 : 1
read_buffer_size seems to be fine
TABLE LOCKING
Current Lock Wait ratio = 1 : 817
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'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.
--------------
However certain actions which ran fine before don't work. Simple searches editing and adding data goes relatively fine but actions with a lot of joins don't.
-
06-04-2011, 04:50 PM #3Web Hosting Master
- Join Date
- Oct 2009
- Posts
- 865
For one, your buffer sizes are waaay too large to use with your current max connection count. Potentially, the DBMS can use up to sum(buffer_sizes) * max_connections bytes of RAM - the buffers are allocated if needed for each thread - which on your server is almost three times the available RAM. Which can leads to swapping and horrid performance.
Typically, if it "suddenly" starts slowing down when nothing has changed, it's because the database reached a certain threshold size where the active part can no longer fully fit in RAM, making it go to disk - which tends to overload them for reasonably busy databases. You don't mention much in terms of server capacity, but try installing sysstat and then run:
iostat -x 1
to analyze the current IO activity.
-
06-04-2011, 10:43 PM #4Newbie
- Join Date
- Sep 2004
- Posts
- 15
Thank you for your answer. Problem is that when most of my users (and when the server is too busy) I am sleeping (they are on the other side of the world)
fiddling around with my.cnf is also way over my head. I barely understand what I am doing and just follow the suggestions done by the two programs I mentioned above
If I start one of the queries which keeps on running forever (and slows down the server)
iostat -x 1
Gives the following
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdb1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
avg-cpu: %user %nice %system %iowait %steal %idle
29.35 0.00 20.40 0.00 0.00 50.25
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 25.00 0.00 123.00 0.00 1184.00 9.63 2.89 23.51 0.25 3.10
sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda2 0.00 0.00 0.00 2.00 0.00 16.00 8.00 0.04 18.00 9.00 1.80
sda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda5 0.00 25.00 0.00 121.00 0.00 1168.00 9.65 2.86 23.60 0.26 3.10
sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdb1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
avg-cpu: %user %nice %system %iowait %steal %idle
29.50 0.00 20.50 0.00 0.00 50.00
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdb1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
avg-cpu: %user %nice %system %iowait %steal %idle
30.35 0.00 19.90 0.00 0.00 49.75
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdb1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
avg-cpu: %user %nice %system %iowait %steal %idle
28.00 0.00 22.00 0.00 0.00 50.00
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 13.00 0.00 2.00 0.00 120.00 60.00 0.00 0.00 0.00 0.00
sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda5 0.00 13.00 0.00 2.00 0.00 120.00 60.00 0.00 0.00 0.00 0.00
sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdb1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
The specifications of the server are:
100Mbps port
Core2 Duo 3.0GHz (1333MHz FSB)
8 GB RAM
3 160GB SATA2
Linux - CentOS - 64 bits
Plesk Unlimited
Actually I am willing to pay someone to speed it up again.
-
06-05-2011, 06:42 AM #5Newbie
- Join Date
- Sep 2004
- Posts
- 15
I followed the instructions here
http://everythingmysql.ning.com/prof...-mysqls-tmpdir
and your suggestions to change the buffer size
That works for the normal queries but not for the queries which keep on going and make other queries "sleep"
If I take a look in the mysqltmp directory I see the following
[root@plesk mysqltmp]# ls -l
total 4
-rw-rw---- 1 mysql mysql 0 Jun 5 06:23 #sql_2a4f_0.MYD
-rw-rw---- 1 mysql mysql 1024 Jun 5 06:23 #sql_2a4f_0.MYI
I allocated 4G for the mysqltmp "disk"
my.cnf now looks like
[mysqld]
log_queries_not_using_indexes
big_tables
max_heap_table_size=32M
tmp_table_size=32M
table_definition_cache = 2613
local-infile=0
datadir=/var/lib/mysql
skip-locking
skip-networking
safe-show-database
query_cache_limit=32M
query_cache_size=32M ## 32MB for every 1GB of RAM
query_cache_type=1
max_user_connections=200
max_connections=200
interactive_timeout=10
wait_timeout=600
max_allowed_packet = 64M
connect_timeout=20
thread_cache_size=128
thread_cache_size=128
key_buffer=512M ## 128MB for every 1GB of RAM
join_buffer=1M
max_connect_errors=20
max_allowed_packet=16M
table_cache=2613
query_cache_limit = 4M
record_buffer=1M
sort_buffer_size=4M ## 1MB for every 1GB of RAM
read_buffer_size=4M ## 1MB for every 1GB of RAM
read_rnd_buffer_size=1M ## 1MB for every 1GB of RAM
thread_concurrency=4 ## Number of CPUs x 2
myisam_sort_buffer_size=64M
server-id=1
#collation-server=latin1_general_ci
tmpdir=/tmp/mysqltmp/
[mysql.server]
user=mysql
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
open_files_limit=8192
table_open_cache=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
-
06-06-2011, 06:15 AM #6Aspiring Evangelist
- Join Date
- Aug 2010
- Location
- Prague, Czech Republic
- Posts
- 404
You can also use mysqlreport, dstat and top to get more information about the issue.
Also it would be useful to switch to 64 bit operation system, enable slow logs and analyze it. Besides, 'show processlist' might help.█ Supportex.Net server management, full range of services. EU-based outsourced company. Since 1998.
█ Outstanding quality for high performance projects; clustering and high-availability solutions, DDoS protection.
█ Cisco/Juniper network management & deployment assistance. Network design and monitoring.
-
06-06-2011, 07:57 AM #7Newbie
- Join Date
- Sep 2004
- Posts
- 15
As far as I know I DO have a 64 bit operation system even though the utility mentions to upgrade. mysqlreport won't run, I tried. I enabled slow logs, but they are empty.
If I run one of the queries which doesn't complete, it keeps on running, show processlist just gives me
| 1344 | gebruikers | localhost | nieuws | Query | 31 | Copying to tmp table | SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, p.gedcom, p.changedate, p.changed |
| 1346 | gebruikers | localhost | nieuws | Sleep | 9 | | NULL |
| 1347 | admin | localhost | NULL | Query | 0 | NULL | show processlist
Nothing more.
-
06-06-2011, 07:59 AM #8Newbie
- Join Date
- Sep 2004
- Posts
- 15
If I keep on using show processlist all the other queries obviously go to sleep.
+------+------------+-----------+--------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------------+-----------+--------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 1344 | gebruikers | localhost | nieuws | Query | 118 | Copying to tmp table | SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, p.gedcom, p.changedate, p.changed |
| 1346 | gebruikers | localhost | nieuws | Sleep | 96 | | NULL |
| 1351 | gebruikers | localhost | nieuws | Sleep | 67 | | NULL |
| 1355 | gebruikers | localhost | nieuws | Sleep | 36 | | NULL |
| 1359 | gebruikers | localhost | nieuws | Sleep | 7 | | NULL |
| 1360 | admin | localhost | NULL | Query | 0 | NULL | show processlist
-
06-06-2011, 08:19 AM #9Disabled
- Join Date
- Mar 2009
- Location
- Israel
- Posts
- 1,212
first of all - optimize and repair the databases
[!!] Total fragmented tables: 287
to check if you have a 64bit system, type :
beastserv ~ # uname -i
x86_64
it should return x86_64
-
06-06-2011, 08:46 AM #10Newbie
- Join Date
- Sep 2004
- Posts
- 15
That's a good one, I LEASED a 64 bit system but I seem to have
uname -i
i386
Total fragmented tables: 287 that's what I don't understand. I use:
els --mysqloptimizedb
regularly
and especially the biggest database is optimized three times per day. but it keeps on telling me that there are 287 fragmented tables. If I check with MySQL the big database is optimized. SO the slowness can't come from there. There are a bunch of databases in use by other users (customers) but they don't play a role in the slowness of mysql. (I checked that) They are not in use a lot maybe once a day.
-
06-06-2011, 11:10 AM #11Disabled
- Join Date
- Mar 2009
- Location
- Israel
- Posts
- 1,212
-
06-06-2011, 11:19 AM #12Web Hosting Master
- Join Date
- Mar 2004
- Posts
- 822
HalfDedi.com • Half Dedicated Half Price
We provide affordable VPS hosting solution Singapore datacenter
-
06-06-2011, 01:06 PM #13Newbie
- Join Date
- Sep 2004
- Posts
- 15
Alright, thanks both of you. I think I will have to change to the 64 bit OS first. I complained to the server centre and let's see how long it takes before they react and change.
I think the system is using the cache fine but I am not sure. I will keep you posted once the OS is changed.
-
06-06-2011, 01:39 PM #14Web Hosting Guru
- Join Date
- Oct 2010
- Location
- Copenhagen
- Posts
- 252
You might take off some load by tuning the "query_cache_size=32M" parameter.
"However, 8515 queries have been removed from the query cache due to lack of memory Perhaps you should raise query_cache_size"
Fixing the ram issue first would be a good idea though.
Enabling the slow log, as others have recommended, is also a good idea.
-
06-19-2011, 09:33 PM #15Newbie
- Join Date
- Sep 2004
- Posts
- 15
After upgrading the operating system and resolving tons of problems that caused (The server center just pushed a new disk inside with a new operating system and plugged the old disk in as backup while I specifically asked them to restore the old configuration), and a lot of testing, I finally found the solution: I added indexes to the database. Not that I knew which ones to add, but someone obviously took the effort to suggest indexes for all tables for that particular piece of software, I added them all and damn, I am back in business. Everything runs quickly now, quicker than it ever did before.
Thanks for all of you who took the efforts of thinking along with me.
-
06-19-2011, 10:06 PM #16Web Hosting Master
- Join Date
- Jul 2010
- Posts
- 797
hey!
this is a nice thread..
mine to paste your my.cnf here ?
what is ur apache max_connections? is it 2000 ?Looking for shared or reseller or VPS Hosting ?
Try our service at https://www.sosys.net!
Singapore - Indonesia - Malaysia
-
06-19-2011, 11:13 PM #17Newbie
- Join Date
- Sep 2004
- Posts
- 15
Here is the my.cnf
[mysqld]
delayed_insert_timeout=600
flush_time=14400
log-slow-queries=/var/log/mysql-slow.log
long_query_time = 10
big_tables
max_heap_table_size=200M
tmp_table_size=128M
table_definition_cache = 6240
local-infile=0
datadir=/var/lib/mysql
skip-locking
skip-networking
safe-show-database
query_cache_limit=64M
query_cache_size=64M ## 32MB for every 1GB of RAM
query_cache_type=1
log-queries-not-using-indexes=/var/log/no-indexes.log
max_user_connections=100
max_connections=300
interactive_timeout=20
wait_timeout=600
max_allowed_packet = 64M
connect_timeout=20
thread_cache_size=128
key_buffer=512M ## 128MB for every 1GB of RAM
join_buffer=2M
key_buffer_size=128M
local-infile=0
max_connect_errors=20
max_allowed_packet=16M
table_cache=6240
record_buffer=1M
sort_buffer_size=4M ## 1MB for every 1GB of RAM
read_buffer_size=4M ## 1MB for every 1GB of RAM
read_rnd_buffer_size=1M ## 1MB for every 1GB of RAM
thread_concurrency=4 ## Number of CPUs x 2
myisam_sort_buffer_size=6M
server-id=1
#collation-server=latin1_general_ci
tmpdir=/tmp/mysqltmp/
[mysql.server]
user=mysql
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
open_files_limit=8192
table_open_cache=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
I have no idea where to find the max_connections of Apache, could you enlighten me?Last edited by HennySavenije; 06-19-2011 at 11:15 PM. Reason: small correction
-
06-19-2011, 11:42 PM #18Web Hosting Master
- Join Date
- Jul 2010
- Posts
- 797
the max_connections should be located at /usr/local/apache/conf/httpd.conf.
sort_buffer_size=4M ## 1MB for every 1GB of RAM
read_buffer_size=4M ## 1MB for every 1GB of RAM
read_rnd_buffer_size=1M ## 1MB for every 1GB of RAM
Is there any reference for this comment that you put ? like 1 MB for 1 GB ?
or 128MB for 1GB ram ?
tahnksLooking for shared or reseller or VPS Hosting ?
Try our service at https://www.sosys.net!
Singapore - Indonesia - Malaysia
-
06-19-2011, 11:57 PM #19Newbie
- Join Date
- Sep 2004
- Posts
- 15
There is no max_connections in httpd.conf, so I assume there is a standard setting somewhere.
The comments come from myhuge.cnf. I have been tweaking a lot but as I said, adding the indexes to the database did the final trick. One query basically locked the whole database (and didn't release it, not even after several hours) now it takes 30 seconds to run the same query.
-
06-20-2011, 12:10 AM #20Junior Guru Wannabe
- Join Date
- Jun 2011
- Posts
- 66
same is happening with me too
-
06-20-2011, 11:59 AM #21Junior Guru Wannabe
- Join Date
- Jun 2011
- Posts
- 61
This is interesting from a case-study perspective:
1) things got slow (and appeared to do so all of a sudden) and hog resources
2) the slow-queries log wasn't enabled
3) it was indexes after all - so looking @ the slow-queries might have shown that they were on non-indexed tables (or didn't use an indexed column). .
A good place to start in the future ;-)WholesaleBackup.com
Online Backup Reseller
Your Brand, Our Technology: Total Success.
+1.800-624-9561
Similar Threads
-
How to check how much resources my site is taking?
By jani in forum Web HostingReplies: 19Last Post: 01-24-2010, 08:13 AM -
SQL Taking Up Too Much Resources
By forcium in forum Hosting Security and TechnologyReplies: 6Last Post: 05-24-2008, 03:24 PM -
MRTG taking up resources ?
By ashish1987 in forum Hosting Security and TechnologyReplies: 1Last Post: 10-12-2007, 06:06 PM -
Nobody User Taking up CPU Resources
By Matt Holme in forum Hosting Security and TechnologyReplies: 11Last Post: 09-21-2006, 10:05 AM -
SQL taking up huge resources
By TWD-Tony in forum Dedicated ServerReplies: 10Last Post: 10-26-2005, 05:09 PM