Hello.
Sometimes, after an upgrade it is necessary to rebuild indexes
in the tables (I'm not sure if it always produces an error if
you haven't done it). In general - usual recommendations for
optimizing queries work in your case. Use the slow query log
to find slow queries. Check if indexes are used properly.
"Benjamin" <bfabre@stripped> wrote:
> Hi,
>
> I host a big french website including forum (phpbb), search engine (mnogo),
> album (smartor), blogs... around 780Mo in MySQL
>
> Everything work fine with my old configuration :
> - One SQL/mail server bi-Xeon, 2.6Ghz, 3Go :
> ........FC4, MySQL 4.0, Postfix-Mysql, Courier-imap Mysql
> - One front server vi-Xeon, 2.2Gh, 2Go :
> .......FC2, Apache 1.33, php 4.4.0
>
> Yersterday, I decided to update from mysql 4.0 to mysql 4.1.
> I simply use "rpm -Uv MySQL-server....rpm"
>
> Since this update, I see many mysql_too_many_connection.
> So I deciede to increase this setting in my my.cnf from 500 to 1000.
>
> But I don't know why Mysql is so slow to return query.
> The server keep a low load average around 1-2 and CPU's usage around 20%.
>
> I also try to use Fedora RPM, But problem is still present.
>
> Please help me, my visitors become crazy ;-)
>
> Below, I paste my different data :
> Thanks.
> Ben
>
>
> ### /etc/my.cnf
> [mysqld]
> port = 3306
> socket = /var/lib/mysql/mysql.sock
> skip-locking
> key_buffer = 384M
> max_allowed_packet = 1M
> table_cache = 512
> sort_buffer_size = 2M
> read_buffer_size = 2M
> read_rnd_buffer_size = 8M
> myisam_sort_buffer_size = 64M
> thread_cache = 60
> query_cache_size = 32M
> # Try number of CPU's*2 for thread_concurrency
> thread_concurrency = 8
> #log=/home/logs/mysql_log
> max_connections = 1200
> log_error = /var/lib/mysql/mysql_error_log
> old_passwords=1
>
>
> ### mysqlreport
> MySQL 4.1.12 uptime 0 0:47:27 Tue Sep 13 00:06:40 2005
>
> __ Key _________________________________________________________________
> Buffer usage 18.51M of 384.00M %Used: 4.82
> Write ratio 0.42
> Read ratio 0.00
>
> __ Questions ___________________________________________________________
> Total 2.39M 838.96/s
> Slow 0 0.00/s %Total: 0.00 %DMS: 0.00
> DMS 381.82k 134.11/s 15.99
>
> __ Table Locks _________________________________________________________
> Waited 18.69k 6.57/s %Total: 3.88
> Immediate 462.66k 162.51/s
>
> __ Tables ______________________________________________________________
> Open 512 of 512 %Cache: 100.00
> Opened 895 0.31/s
>
> __ Connections _________________________________________________________
> Max used 704 of 1200 %Max: 58.67
> Total 275.89k 96.91/s
>
> __ Created Temp ________________________________________________________
> Disk table 5.98k 2.10/s
> Table 17.80k 6.25/s
> File 7 0.00/s
>
>
> ### ./mysqlreport -dms -com 5
> MySQL 4.1.12 uptime 0 9:29:17 Tue Sep 13 08:48:30 2005
>
> __ Key _________________________________________________________________
> Buffer usage 29.91M of 384.00M %Used: 7.79
> Write ratio 0.33
> Read ratio 0.00
>
> __ Questions ___________________________________________________________
> Total 11.72M 343.26/s
> Slow 0 0.00/s %Total: 0.00 %DMS: 0.00
> DMS 1.80M 52.80/s 15.38
> UPDATE 997.17k 29.19/s 8.50 55.29
> SELECT 750.53k 21.97/s 6.40 41.61
> INSERT 33.51k 0.98/s 0.29 1.86
> DELETE 22.35k 0.65/s 0.19 1.24
> REPLACE 0 0.00/s 0.00 0.00
> Com_ 1.37M 39.98/s 11.65
> change_db 1.36M 39.92/s 11.63
> set_option 437 0.01/s 0.00
> show_status 280 0.01/s 0.00
> begin 274 0.01/s 0.00
> commit 274 0.01/s 0.00
>
> __ Table Locks _________________________________________________________
> Waited 36.09k 1.06/s %Total: 1.58
> Immediate 2.25M 65.85/s
>
> __ Tables ______________________________________________________________
> Open 512 of 512 %Cache: 100.00
> Opened 1.06k 0.03/s
>
> __ Connections _________________________________________________________
> Max used 704 of 1200 %Max: 58.67
> Total 1.28M 37.46/s
>
> __ Created Temp ________________________________________________________
> Disk table 28.27k 0.83/s
> Table 87.37k 2.56/s
> File 19 0.00/s
>
>
> ### top
> top - 00:02:46 up 5:38, 3 users, load average: 1.84, 1.71, 1.50
> Tasks: 165 total, 1 running, 164 sleeping, 0 stopped, 0 zombie
> Cpu(s): 9.8% us, 2.4% sy, 0.0% ni, 87.0% id, 0.4% wa, 0.1% hi, 0.3% si
> Mem: 3115044k total, 3036040k used, 79004k free, 545064k buffers
> Swap: 2031608k total, 0k used, 2031608k free, 620516k cached
>
>
>
>
>
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@stripped
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
