# Slow site during peak times... help!

**URL:** <https://forums.percona.com/t/slow-site-during-peak-times-help/466>\
**Category:** Other MySQL® Questions\
**Created:** [September 26, 2007, 2:08am UTC](https://forums.percona.com/t/slow-site-during-peak-times-help/466 "2007-09-26T02:08:54Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![DDJCS](https://avatars.discourse-cdn.com/v4/letter/d/8e7dd6/32.png) [@DDJCS](https://forums.percona.com/u/DDJCS)\
**Post date:** [September 26, 2007, 2:08am UTC](https://forums.percona.com/t/slow-site-during-peak-times-help/466/1 "2007-09-26T02:08:54Z")

</div>

Hello, I run a chat/community site which has grown a lot lately and now, during peak times, there are about 400 simultaneous users. Good? I think so but this is also where the problems begin: during peak times in fact the site’s response time is pretty slow and this is becoming really a big issue (the site is slow to navigate and it’s slow to send/receive chat messages ( ).

My current configuration is a linux Server (CentOS 4) with Xeon CPUs (dual dual cores, 8 CPUs 2,66ghz), 8gb of RAM, Apache 1.3.39, PHP 5.2.4 and MySQL 5.0.45. The PHP code is very optimized and so are all of my queries (I optimized every single query using the EXPLAIN command and now they are fast and efficient, no filesort, no temp tables, etc.). The CPU usage during peak times is also very low (around 35%) and the RAM seems to be ok.

I’m pretty sure that my problems lie in some MySQL parameters bad set and I hope that some “mysql guru” here can help me to figure out the problems… (

Being a chat community, I’m using InnoDB tables for almost all of my tables since my site does a _terrific_ use of insert/updates/deletes and every seconds there are hundreds of them. I also use persistent connections as I heard they help a lot if you have thousands of mysql connections per second. The site runs fine until there are about 300 simultaneous users then it starts to slow down a lot when it grows more (370+)!

Here’s my current my.cnf file. Please note that I disabled the “query cache” because I noticed that it slowed down things even more since it was caching thousands of similar queries (almost all of my queries are dynamic and I read that query caching is not helpful in these cases but can be a big bottleneck…):

[mysqld]skip-lockingskip-bdblog-binkey\_buffer\_size = 32Mjoin\_buffer\_size = 2Mread\_buffer\_size = 2Mread\_rnd\_buffer\_size = 3Msort\_buffer\_size = 6Mmyisam\_sort\_buffer\_size = 32Mtmp\_table\_size = 32Mtable\_cache = 1024thread\_cache\_size = 128thread\_concurrency = 16max\_allowed\_packet = 16Mmax\_connect\_errors = 10max\_connections = 800max\_user\_connections = 800connect\_timeout = 10interactive\_timeout = 30wait\_timeout = 30server\_id = 1long\_query\_time = 2log\_slow\_queries = /var/log/mysqld.slow.log#InnoDB settingsinnodb\_data\_home\_dir = /var/lib/mysql/innodb\_data\_file\_path = ibdata1:100M:autoextendinnodb\_buffer\_pool\_size = 1800Minnodb\_additional\_mem\_pool\_size = 20Minnodb\_thread\_concurrency = 16innodb\_flush\_log\_at\_trx\_commit = 0innodb\_lock\_wait\_timeout = 30innodb\_log\_files\_in\_group = 2innodb\_log\_file\_size = 512Minnodb\_log\_buffer\_size = 16M[safe\_mysqld]open\_files\_limit = 8192err-log = /var/log/mysqld.log[mysqldump]quickmax\_allowed\_packet = 16M[mysql]no-auto-rehash[isamchk]key\_buffer = 64Msort\_buffer = 64Mread\_buffer = 16Mwrite\_buffer = 16M[myisamchk]key\_buffer = 64Msort\_buffer = 64Mread\_buffer = 16Mwrite\_buffer = 16M[mysqlhotcopy]interactive-timeout

I re-started the mysql service 7 hours ago. What I can notice right now is that phpMyAdmin is showing me in red color the following lines (and I suppose that “red” means “bad”, isn’t it?):

- Innodb\_buffer\_pool\_pages\_dirty 89
- Innodb\_buffer\_pool\_reads 3,896
- Innodb\_row\_lock\_time\_avg 17
- Innodb\_row\_lock\_time\_max 208
- Innodb\_row\_lock\_waits 34
- Handler\_read\_rnd 165 k
- Handler\_read\_rnd\_next 7,112 k
- Opened\_tables 219

Here’s my “show status”

Aborted\_clients 1806Aborted\_connects 0Binlog\_cache\_disk\_use 0Binlog\_cache\_use 371629Bytes\_received 493Bytes\_sent 7930Com\_admin\_commands 0Com\_alter\_db 0Com\_alter\_table 0Com\_analyze 0Com\_backup\_table 0Com\_begin 0Com\_call\_procedure 0Com\_change\_db 1Com\_change\_master 0Com\_check 0Com\_checksum 0Com\_commit 0Com\_create\_db 0Com\_create\_function 0Com\_create\_index 0Com\_create\_table 0Com\_create\_user 0Com\_dealloc\_sql 0Com\_delete 0Com\_delete\_multi 0Com\_do 0Com\_drop\_db 0Com\_drop\_function 0Com\_drop\_index 0Com\_drop\_table 0Com\_drop\_user 0Com\_execute\_sql 0Com\_flush 0Com\_grant 0Com\_ha\_close 0Com\_ha\_open 0Com\_ha\_read 0Com\_help 0Com\_insert 0Com\_insert\_select 0Com\_kill 0Com\_load 0Com\_load\_master\_data 0Com\_load\_master\_table 0Com\_lock\_tables 0Com\_optimize 0Com\_preload\_keys 0Com\_prepare\_sql 0Com\_purge 0Com\_purge\_before\_date 0Com\_rename\_table 0Com\_repair 0Com\_replace 0Com\_replace\_select 0Com\_reset 0Com\_restore\_table 0Com\_revoke 0Com\_revoke\_all 0Com\_rollback 0Com\_savepoint 0Com\_select 2Com\_set\_option 4Com\_show\_binlog\_events 0Com\_show\_binlogs 0Com\_show\_charsets 1Com\_show\_collations 1Com\_show\_column\_types 0Com\_show\_create\_db 0Com\_show\_create\_table 0Com\_show\_databases 1Com\_show\_errors 0Com\_show\_fields 0Com\_show\_grants 1Com\_show\_innodb\_status 0Com\_show\_keys 0Com\_show\_logs 0Com\_show\_master\_status 0Com\_show\_ndb\_status 0Com\_show\_new\_master 0Com\_show\_open\_tables 0Com\_show\_privileges 0Com\_show\_processlist 0Com\_show\_slave\_hosts 0Com\_show\_slave\_status 0Com\_show\_status 1Com\_show\_storage\_engines 0Com\_show\_tables 0Com\_show\_triggers 0Com\_show\_variables 2Com\_show\_warnings 0Com\_slave\_start 0Com\_slave\_stop 0Com\_stmt\_close 0Com\_stmt\_execute 0Com\_stmt\_fetch 0Com\_stmt\_prepare 0Com\_stmt\_reset 0Com\_stmt\_send\_long\_data 0Com\_truncate 0Variable\_name ValueCom\_unlock\_tables 0Com\_update 0Com\_update\_multi 0Com\_xa\_commit 0Com\_xa\_end 0Com\_xa\_prepare 0Com\_xa\_recover 0Com\_xa\_rollback 0Com\_xa\_start 0Compression OFFConnections 6126Created\_tmp\_disk\_tables 0Created\_tmp\_files 5Created\_tmp\_tables 6Delayed\_errors 0Delayed\_insert\_threads 0Delayed\_writes 0Flush\_commands 1Handler\_commit 0Handler\_delete 0Handler\_discover 0Handler\_prepare 0Handler\_read\_first 0Handler\_read\_key 0Handler\_read\_next 0Handler\_read\_prev 0Handler\_read\_rnd 0Handler\_read\_rnd\_next 172Handler\_rollback 0Handler\_savepoint 0Handler\_savepoint\_rollback 0Handler\_update 0Handler\_write 299Innodb\_buffer\_pool\_pages\_data 5285Innodb\_buffer\_pool\_pages\_dirty 96Innodb\_buffer\_pool\_pages\_flushed 225639Innodb\_buffer\_pool\_pages\_free 109573Innodb\_buffer\_pool\_pages\_latched 0Innodb\_buffer\_pool\_pages\_misc 342Innodb\_buffer\_pool\_pages\_total 115200Innodb\_buffer\_pool\_read\_ahead\_rnd 7Innodb\_buffer\_pool\_read\_ahead\_seq 42Innodb\_buffer\_pool\_read\_requests 1480925552Innodb\_buffer\_pool\_reads 3896Innodb\_buffer\_pool\_wait\_free 0Innodb\_buffer\_pool\_write\_requests 4158714Innodb\_data\_fsyncs 43869Innodb\_data\_pending\_fsyncs 0Innodb\_data\_pending\_reads 0Innodb\_data\_pending\_writes 0Innodb\_data\_read 83382272Innodb\_data\_reads 4319Innodb\_data\_writes 195833Innodb\_data\_written 3377671680Innodb\_dblwr\_pages\_written 225639Innodb\_dblwr\_writes 4985Innodb\_log\_waits 0Innodb\_log\_write\_requests 713163Innodb\_log\_writes 31431Innodb\_os\_log\_fsyncs 33894Innodb\_os\_log\_pending\_fsyncs 0Innodb\_os\_log\_pending\_writes 0Innodb\_os\_log\_written 277638656Innodb\_page\_size 16384Innodb\_pages\_created 329Innodb\_pages\_read 4956Innodb\_pages\_written 225639Innodb\_row\_lock\_current\_waits 0Innodb\_row\_lock\_time 607Innodb\_row\_lock\_time\_avg 17Innodb\_row\_lock\_time\_max 208Innodb\_row\_lock\_waits 35Innodb\_rows\_deleted 77092Innodb\_rows\_inserted 93587Innodb\_rows\_read 476763718Innodb\_rows\_updated 192109Key\_blocks\_not\_flushed 0Key\_blocks\_unused 28992Key\_blocks\_used 3Key\_read\_requests 26434Key\_reads 45Key\_write\_requests 2100Key\_writes 42Last\_query\_cost 10.499000Max\_used\_connections 572Ndb\_cluster\_node\_id 0Ndb\_config\_from\_host Ndb\_config\_from\_port 0Ndb\_number\_of\_data\_nodes 0Not\_flushed\_delayed\_rows 0Open\_files 20Open\_streams 0Open\_tables 196Opened\_tables 0Prepared\_stmt\_count 0Qcache\_free\_blocks 0Qcache\_free\_memory 0Qcache\_hits 0Qcache\_inserts 0Qcache\_lowmem\_prunes 0Variable\_name ValueQcache\_not\_cached 0Qcache\_queries\_in\_cache 0Qcache\_total\_blocks 0Questions 17392379Rpl\_status NULLSelect\_full\_join 0Select\_full\_range\_join 0Select\_range 0Select\_range\_check 0Select\_scan 6Slave\_open\_temp\_tables 0Slave\_retried\_transactions 0Slave\_running OFFSlow\_launch\_threads 0Slow\_queries 0Sort\_merge\_passes 0Sort\_range 0Sort\_rows 0Sort\_scan 0Table\_locks\_immediate 18202418Table\_locks\_waited 0Tc\_log\_max\_pages\_used 0Tc\_log\_page\_size 0Tc\_log\_page\_waits 0Threads\_cached 47Threads\_connected 525Threads\_created 1284Threads\_running 1Uptime 25531Uptime\_since\_flush\_status 25531

Here’s my “show variables”

auto\_increment\_increment 1auto\_increment\_offset 1automatic\_sp\_privileges ONback\_log 50basedir /binlog\_cache\_size 32768bulk\_insert\_buffer\_size 8388608character\_set\_client utf8character\_set\_connection utf8character\_set\_database latin1character\_set\_filesystem binarycharacter\_set\_results utf8character\_set\_server latin1character\_set\_system utf8character\_sets\_dir /usr/share/mysql/charsets/collation\_connection utf8\_unicode\_cicollation\_database latin1\_swedish\_cicollation\_server latin1\_swedish\_cicompletion\_type 0concurrent\_insert 1connect\_timeout 10datadir /var/lib/mysql/date\_format %Y-%m-%ddatetime\_format %Y-%m-%d %H:%i:%sdefault\_week\_format 0delay\_key\_write ONdelayed\_insert\_limit 100delayed\_insert\_timeout 300delayed\_queue\_size 1000div\_precision\_increment 4engine\_condition\_pushdown OFFexpire\_logs\_days 0flush OFFflush\_time 0ft\_boolean\_syntax + -\>\<()~\*:“”&|ft\_max\_word\_len 84ft\_min\_word\_len 4ft\_query\_expansion\_limit 20ft\_stopword\_file (built-in)group\_concat\_max\_len 1024have\_archive YEShave\_bdb NOhave\_blackhole\_engine YEShave\_compress YEShave\_crypt YEShave\_csv YEShave\_dynamic\_loading NOhave\_example\_engine YEShave\_federated\_engine YEShave\_geometry YEShave\_innodb YEShave\_isam NOhave\_merge\_engine YEShave\_ndbcluster DISABLEDhave\_openssl NOhave\_ssl NOhave\_query\_cache YEShave\_raid NOhave\_rtree\_keys YEShave\_symlink YESinit\_connect init\_file init\_slave innodb\_additional\_mem\_pool\_size 20971520innodb\_autoextend\_increment 8innodb\_buffer\_pool\_awe\_mem\_mb 0innodb\_buffer\_pool\_size 1887436800innodb\_checksums ONinnodb\_commit\_concurrency 0innodb\_concurrency\_tickets 500innodb\_data\_file\_path ibdata1:100M:autoextendinnodb\_data\_home\_dir /var/lib/mysql/innodb\_doublewrite ONinnodb\_fast\_shutdown 1innodb\_file\_io\_threads 4innodb\_file\_per\_table OFFinnodb\_flush\_log\_at\_trx\_commit 0innodb\_flush\_method innodb\_force\_recovery 0innodb\_lock\_wait\_timeout 30innodb\_locks\_unsafe\_for\_binlog OFFinnodb\_log\_arch\_dir innodb\_log\_archive OFFinnodb\_log\_buffer\_size 16777216innodb\_log\_file\_size 536870912innodb\_log\_files\_in\_group 2innodb\_log\_group\_home\_dir ./innodb\_max\_dirty\_pages\_pct 90innodb\_max\_purge\_lag 0innodb\_mirrored\_log\_groups 1innodb\_open\_files 300innodb\_rollback\_on\_timeout OFFinnodb\_support\_xa ONinnodb\_sync\_spin\_loops 20innodb\_table\_locks ONinnodb\_thread\_concurrency 16innodb\_thread\_sleep\_delay 10000interactive\_timeout 30join\_buffer\_size 2093056Variable\_name Valuekey\_buffer\_size 33554432key\_cache\_age\_threshold 300key\_cache\_block\_size 1024key\_cache\_division\_limit 100language /usr/share/mysql/english/large\_files\_support ONlarge\_page\_size 0large\_pages OFFlc\_time\_names en\_USlicense GPLlocal\_infile ONlocked\_in\_memory OFFlog OFFlog\_bin ONlog\_bin\_trust\_function\_creators OFFlog\_error log\_queries\_not\_using\_indexes OFFlog\_slave\_updates OFFlog\_slow\_queries ONlog\_warnings 1long\_query\_time 2low\_priority\_updates OFFlower\_case\_file\_system OFFlower\_case\_table\_names 0max\_allowed\_packet 16776192max\_binlog\_cache\_size 4294967295max\_binlog\_size 1073741824max\_connect\_errors 10max\_connections 800max\_delayed\_threads 20max\_error\_count 64max\_heap\_table\_size 16777216max\_insert\_delayed\_threads 20max\_join\_size 18446744073709551615max\_length\_for\_sort\_data 1024max\_prepared\_stmt\_count 16382max\_relay\_log\_size 0max\_seeks\_for\_key 4294967295max\_sort\_length 1024max\_sp\_recursion\_depth 0max\_tmp\_tables 32max\_user\_connections 800max\_write\_lock\_count 4294967295multi\_range\_count 256myisam\_data\_pointer\_size 6myisam\_max\_sort\_file\_size 2147483647myisam\_recover\_options OFFmyisam\_repair\_threads 1myisam\_sort\_buffer\_size 33554432myisam\_stats\_method nulls\_unequalndb\_autoincrement\_prefetch\_sz 32ndb\_force\_send ONndb\_use\_exact\_count ONndb\_use\_transactions ONndb\_cache\_check\_time 0ndb\_connectstring net\_buffer\_length 16384net\_read\_timeout 30net\_retry\_count 10net\_write\_timeout 60new OFFold\_passwords OFFopen\_files\_limit 4000optimizer\_prune\_level 1optimizer\_search\_depth 62port 3306preload\_buffer\_size 32768profiling OFFprofiling\_history\_size 15protocol\_version 10query\_alloc\_block\_size 8192query\_cache\_limit 1048576query\_cache\_min\_res\_unit 4096query\_cache\_size 0query\_cache\_type ONquery\_cache\_wlock\_invalidate OFFquery\_prealloc\_size 8192range\_alloc\_block\_size 2048read\_buffer\_size 2093056read\_only OFFread\_rnd\_buffer\_size 3141632relay\_log\_purge ONrelay\_log\_space\_limit 0rpl\_recovery\_rank 0secure\_auth OFFsecure\_file\_priv server\_id 1skip\_external\_locking ONskip\_networking OFFskip\_show\_database OFFslave\_compressed\_protocol OFFslave\_load\_tmpdir /tmp/slave\_net\_timeout 3600slave\_skip\_errors OFFslave\_transaction\_retries 10slow\_launch\_time 2socket /var/lib/mysql/mysql.socksort\_buffer\_size 6291448sql\_big\_selects ONVariable\_name Valuesql\_mode sql\_notes ONsql\_warnings OFFssl\_ca ssl\_capath ssl\_cert ssl\_cipher ssl\_key storage\_engine MyISAMsync\_binlog 0sync\_frm ONsystem\_time\_zone CESTtable\_cache 1024table\_lock\_wait\_timeout 50table\_type MyISAMthread\_cache\_size 128thread\_stack 126976time\_format %H:%i:%stime\_zone SYSTEMtimed\_mutexes OFFtmp\_table\_size 33554432tmpdir /tmp/transaction\_alloc\_block\_size 8192transaction\_prealloc\_size 4096tx\_isolation REPEATABLE-READupdatable\_views\_with\_limit YESversion 5.0.45-community-logversion\_comment MySQL Community Edition (GPL)version\_compile\_machine i686version\_compile\_os pc-linux-gnuwait\_timeout 30

Thank you very much to anyone who can help me!

–  
Best Regards,  
Marco

---

<div class="post-metadata">

**Author:** ![DDJCS](https://avatars.discourse-cdn.com/v4/letter/d/8e7dd6/32.png) [@DDJCS](https://forums.percona.com/u/DDJCS)\
**Post date:** [September 27, 2007, 4:20am UTC](https://forums.percona.com/t/slow-site-during-peak-times-help/466/2 "2007-09-27T04:20:03Z")

</div>

… anyone? (

---

<div class="post-metadata">

**Author:** ![scoundrel](https://avatars.discourse-cdn.com/v4/letter/s/3ec8ea/32.png) [@scoundrel](https://forums.percona.com/u/scoundrel)\
**Post date:** [September 27, 2007, 7:19pm UTC](https://forums.percona.com/t/slow-site-during-peak-times-help/466/3 "2007-09-27T19:19:47Z")

</div>

1. Do you use some PHP opcode cache (like APC, eAccelerator, xcaxche)?

2. Try to move away from permanent connections because whey could cause described problems when you have too many simultaneous connections.

---

<div class="post-metadata">

**Author:** ![mikec](https://avatars.discourse-cdn.com/v4/letter/m/977dab/32.png) [@mikec](https://forums.percona.com/u/mikec)\
**Post date:** [October 1, 2007, 1:49am UTC](https://forums.percona.com/t/slow-site-during-peak-times-help/466/4 "2007-10-01T01:49:14Z")

</div>

“every seconds there are hundreds of them”

need to do an iostat -xn 3 (options may be different on your os)  
and pay attention to %b %w, asvc\_t and actv

%b = how busy the disk is  
%w = how many waits  
asvc\_t = average service time  
actv = average io’s waiting

%w should be very small less than 1-3% or so  
%b shouldn’t be large less than about 60% or so  
asvc\_t should be less than 10 milliseconds  
actv should be less than 5 or so

If you are doing hundreds of them a second, chances are the disk  
is overloaded. The best remedy is to fix the application  
so it isn’t doin so much DML.

---

<div class="post-metadata">

**Author:** ![mikec](https://avatars.discourse-cdn.com/v4/letter/m/977dab/32.png) [@mikec](https://forums.percona.com/u/mikec)\
**Post date:** [October 1, 2007, 2:18am UTC](https://forums.percona.com/t/slow-site-during-peak-times-help/466/5 "2007-10-01T02:18:32Z")

</div>

Run iostat during peak time and  
also run top to make sure you aren’t swapping.

---

<div class="post-metadata">

**Author:** ![DDJCS](https://avatars.discourse-cdn.com/v4/letter/d/8e7dd6/32.png) [@DDJCS](https://forums.percona.com/u/DDJCS)\
**Post date:** [October 2, 2007, 1:56am UTC](https://forums.percona.com/t/slow-site-during-peak-times-help/466/6 "2007-10-02T01:56:22Z")

</div>

First of all thank you for replying me!

Well… what do you mean by saying “fix the application”? Unfortunately I can’t do much to fix it or optimize it further:( It’s just the typology of my site (a community site with hundreds of chat messages every minute, photo galleries, ecc.) which makes it so heavy. A community site needs a lot of resources (apache / php / mysql) and the queries are a lot because they need to be a lot. I’ve already done a lot of optimizations removing some not needed queries and/or speeding up them! Do you think any hardware upgrade (i.e. buying a second server to use exclusively for MySQL) could help me? What would you upgrade in my Server to gain more performance? Thank you very much!

---

<div class="post-metadata">

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\
**Post date:** [October 2, 2007, 4:01am UTC](https://forums.percona.com/t/slow-site-during-peak-times-help/466/7 "2007-10-02T04:01:27Z")

</div>

As posted before, run iostat or top during peak time to find out more if your problem is CPU or I/O.

Bit since you wrote somewhere that you had about 30% cpu load which should indicate that the problem is I/O I’m going to assume that that is the problem.

And if it is IO then you basically have these two options:  
1.  
Increase cache  
I noticed that you have 8GB RAM but are only using 1800MB for InnoDB and my guess is that you are using a 32bit mysql version.  
Change to a 64bit version and increase the innodb buffer pool to about 6GB.

1. 

Buy faster disks  
Depending on what kind of disks you have now look at trying to buy even faster ones.

But if you need to do large things like this anyway, then aim for buying and installing a separate new DB server with 64bit platform with more RAM than now, install a 64bit OS and 64bit version of mysql.

That way you can perform all these installations without any downtime for your site.  
Then when the time is write, you bring down your current webserver and mysql server, copy the db over to the new server.  
Fire the new server up.  
Change the php login settings to use the new servers ip instead of localhost and start the website again.  
Leaving the old server to only handle the webserver/php and the new server handling all DB.

---

<div class="post-metadata">

**Author:** ![mikec](https://avatars.discourse-cdn.com/v4/letter/m/977dab/32.png) [@mikec](https://forums.percona.com/u/mikec)\
**Post date:** [October 2, 2007, 9:00pm UTC](https://forums.percona.com/t/slow-site-during-peak-times-help/466/8 "2007-10-02T21:00:44Z")

</div>

Hi,

First I’d do what Sterin suggests…

If this isn’t enough, I’d look at upgrading to an external RAID  
storage array dedicated to the db server alone.

Something like this:

[http://www.dell.com/content/products/productdetails.aspx/pva](http://www.dell.com/content/products/productdetails.aspx/pva) ul\_md3000?c=us&l=en&s=bsd&cs=04

or the Apple XServe RAID.

Basically you want write cache and lots of spindles like these  
products offer so that writes happen very quickly. You must  
also think about a UPS with these products and turn  
off write caching on the disks to preserve data integrity.

Switch from internal disk to a RAID array and you will be  
amazed at the performance improvement.

---

<div class="post-metadata">

**Author:** ![DDJCS](https://avatars.discourse-cdn.com/v4/letter/d/8e7dd6/32.png) [@DDJCS](https://forums.percona.com/u/DDJCS)\
**Post date:** [October 4, 2007, 4:08am UTC](https://forums.percona.com/t/slow-site-during-peak-times-help/466/9 "2007-10-04T04:08:46Z")

</div>

Thank you all! I bought and I’m going to install another dedicated Server just for the DB (mysql 5) to balance the load. In this way I hope to get a good speed improvement…

---

<div class="post-metadata">

**Author:** ![esudnik](https://avatars.discourse-cdn.com/v4/letter/e/96bed5/32.png) [@esudnik](https://forums.percona.com/u/esudnik)\
**Post date:** [October 8, 2007, 8:29am UTC](https://forums.percona.com/t/slow-site-during-peak-times-help/466/10 "2007-10-08T08:29:07Z")

</div>

How many DB queries do you have per second?  
What is your avg. page generation time?

What you can do:

- Create a mysql replication to other server and read from slave server
- Implement full page caching for X seconds (when it is possible)

---

<div class="post-metadata">

**Author:** ![DDJCS](https://avatars.discourse-cdn.com/v4/letter/d/8e7dd6/32.png) [@DDJCS](https://forums.percona.com/u/DDJCS)\
**Post date:** [October 17, 2007, 8:42am UTC](https://forums.percona.com/t/slow-site-during-peak-times-help/466/11 "2007-10-17T08:42:14Z")

</div>

| [B]sterin wrote on Tue, 02 October 2007 05:31[/B] |
| 

But if you need to do large things like this anyway, then aim for buying and installing a separate new DB server with 64bit platform with more RAM than now, install a 64bit OS and 64bit version of mysql.

 |

Here we go, as suggested by sterin I bought a second dedicated Server (a dual core machine with 8gb of RAM, faster hard disks, etc.) and I installed on it Centos and MySQL, both 64bit versions. I changed some variables in the “my.cnf” file following the suggestions that you people gave me (i.e. innodb\_buffer\_pool\_size = 6000M) and I must say that the performance boost has been really great and the site is now pretty fast! )

I have only a few doubts though… Using the “tuning-primer.sh” tool to check if my “my.cnf” variables were correct (I read good things about this tool so I decided to give it a try) I’ve noticed something wrong in the “Memory usage” section… here it is:

MEMORY USAGEMax Memory Ever Allocated : 12 GConfigured Max Per-thread Buffers : 14 GConfigured Max Global Buffers : 5 GConfigured Max Memory Limit : 20 GTotal System Memory : 9.73 GMax memory limit exceeds 85% of total system memory

Hmmm… is maybe a value of 6000M for “innodb\_buffer\_pool\_size” too much? I don’t know but according to that tool it looks like I’m using too much mysql memory (even if, I repeat, everything seems to run great). Here’s the current “my.cnf” file which I’m using on the new dedicated DB Server:

[mysqld]skip-lockingskip-bdblog-binkey\_buffer\_size = 32Mjoin\_buffer\_size = 2Mread\_buffer\_size = 2Mread\_rnd\_buffer\_size = 2Msort\_buffer\_size = 6Mmyisam\_sort\_buffer\_size = 32Mtmp\_table\_size = 32Mtable\_cache = 1024thread\_cache\_size = 128thread\_concurrency = 16max\_allowed\_packet = 16Mmax\_connect\_errors = 10max\_connections = 1200max\_user\_connections = 1200connect\_timeout = 10interactive\_timeout = 30wait\_timeout = 30server\_id = 1long\_query\_time = 2#log\_queries\_not\_using\_indexes = 1log\_slow\_queries = /var/log/mysqld.slow.log#InnoDB settingsinnodb\_data\_home\_dir = /var/lib/mysql/innodb\_data\_file\_path = ibdata1:100M:autoextendinnodb\_buffer\_pool\_size = 6000Minnodb\_additional\_mem\_pool\_size = 20Minnodb\_thread\_concurrency = 16innodb\_flush\_log\_at\_trx\_commit = 0innodb\_lock\_wait\_timeout = 30innodb\_log\_files\_in\_group = 2innodb\_log\_file\_size = 1000Minnodb\_log\_buffer\_size = 16M[safe\_mysqld]open\_files\_limit = 8192err-log = /var/log/mysqld.log[mysqldump]quickmax\_allowed\_packet = 16M[mysql]no-auto-rehash[isamchk]key\_buffer = 64Msort\_buffer = 64Mread\_buffer = 16Mwrite\_buffer = 16M[myisamchk]key\_buffer = 64Msort\_buffer = 64Mread\_buffer = 16Mwrite\_buffer = 16M[mysqlhotcopy]interactive-timeout

Do you guys suggest me to change anything in the “my.cnf” file (I have only one MyISAM table, a very small one. Everything else is InnoDB)? Thank you very much for any help you can give!

---

<div class="post-metadata">

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\
**Post date:** [October 17, 2007, 1:35pm UTC](https://forums.percona.com/t/slow-site-during-peak-times-help/466/12 "2007-10-17T13:35:28Z")

</div>

The reason you get that high figures from tuning-primer is because you allow a maximum of 1200 connections and some parameters affect on a per thread level.  
Paramters like:  
join\_buffer\_size = 2M  
read\_buffer\_size = 2M  
read\_rnd\_buffer\_size = 2M  
sort\_buffer\_size = 6M  
etc.

And if you take these and multiply them with 1200 you get a very high figure.

But since this is the _worst_ case scenario which most probably will never happen it is still pretty safe to run with these settings although tuning-primer is complaining.

So if you see that during normal working periods the server is not swapping then the settings are fine.

---

<div class="post-metadata">

**Author:** ![DDJCS](https://avatars.discourse-cdn.com/v4/letter/d/8e7dd6/32.png) [@DDJCS](https://forums.percona.com/u/DDJCS)\
**Post date:** [October 17, 2007, 1:53pm UTC](https://forums.percona.com/t/slow-site-during-peak-times-help/466/13 "2007-10-17T13:53:33Z")

</div>

Thanks sterin for the answers! Another thing: do you suggest me to use persistent connections? I have hundreds and hundreds of mysql connections per second and googling around I read that persistent connections are recommended in some situations and above all when you have an external DB Server (I have no clue why…). I know that persistent connections are “memory eaters” but maybe in my case hundreds of mysql connections are going to eat memory even more… or not? What do you think?

---

<div class="post-metadata">

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\
**Post date:** [October 17, 2007, 4:11pm UTC](https://forums.percona.com/t/slow-site-during-peak-times-help/466/14 "2007-10-17T16:11:39Z")

</div>

Persistent connections is recommended because you avoid the overhead to set up a new connection.

But at the same time MySQL is _very_ fast compared to other DBMS to set up a new connection. Which means that you very often can run mysql applications where you perform new connections all the time.

And that said a lot of people that try to use persistent connections has had problems with it. Where the connections on the mysql server is piling up so they need to set a short timeout value to kill the stale connections so that they don’t block new connections with queries.