Previous Thread
Next Thread
Print Thread
Rate Thread
#241969 04/02/2003 8:41 PM
Joined: Feb 2002
Posts: 950
Hacker
Hacker
Offline
Joined: Feb 2002
Posts: 950
My mysqld processes are running at about 80MB each.

My settings:

[mysqld]
datadir = /home/mySQL
socket = /home/mySQL/mysql.sock
skip-locking
set-variable = max_connections=200
set-variable = key_buffer=64M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=4M
set-variable = table_cache=1024
set-variable = thread_cache_size=512
set-variable = wait_timeout=9600
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10

# Try number of CPU's*2 for thread_concurrency
set-variable = thread_concurrency=2

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open-files-limit=8192

[mysqlhotcopy]
interactive-timeout

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[myisamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=16M
set-variable = write_buffer=16M

I run an average over just over 1,000 queries per minute over a 6 month period with some large peaks (over 6,000 q/m).

mysql-3.23.54a-3.72

Sponsored Links
Joined: Feb 2002
Posts: 1,759
Addict
Addict
Offline
Joined: Feb 2002
Posts: 1,759
hmm...

sorry this doesn't help, but just curious. What is the command to check how many queries per minute?

Joined: Feb 2002
Posts: 1,759
Addict
Addict
Offline
Joined: Feb 2002
Posts: 1,759
Hmm.. nevermind, I think this does what I want it to:

Code
<?php<br />$link = mysql_connect('localhost', "mysql_user", "mysql_password");<br />$status = explode('  ',mysql_stat($link));<br />print_r($status);<br />?><br /> 

Joined: Feb 2002
Posts: 950
Hacker
Hacker
Offline
Joined: Feb 2002
Posts: 950

Joined: Jan 2003
Posts: 36
User
User
Offline
Joined: Jan 2003
Posts: 36
i FEEL your pain!
it took a mere 3 hours for mysqld to hit 68M today on the site i try to admin. with linux 7.3, mysql 3.23.56 and 1.5 gig of ram on a 2ghz p4. slow was not a slow enough word to describe it.

my /etc/my.cnf is VERY close to yours.

i made a few tweaks and now (at 2:35 of uptime) my daemons are only 42meg in size.

i can only offer a few small suggestions:
1) make your table_cache size smaller (use "mysqladmin extended-service" to show how many have been opened) i would bet a number closer to 300 or 400 would be better for you.

2) change ALL of your myisamchk variables to 1/4th the size they are now. 64->16M, 16->4M

3) comment out (#) the thread_cache_size

right now i'm doing 155,000 queries in 155 minutes.

Sponsored Links
Joined: Feb 2002
Posts: 950
Hacker
Hacker
Offline
Joined: Feb 2002
Posts: 950
Using the above table, except that I set max_connections to 500, here's my results:

Aborted_clients 51113 (<- is this bad?)
Aborted_connects 4
Bytes_received 3376166447
Bytes_sent 3405372917
Com_admin_commands 0
Com_alter_table 30
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_change_db 2021507
Com_change_master 0
Com_check 0
Com_commit 0
Com_create_db 4
Com_create_function 0
Com_create_index 0
Com_create_table 39
Com_delete 200047
Com_drop_db 9
Com_drop_function 0
Com_drop_index 0
Com_drop_table 12
Com_flush 0
Com_grant 0
Com_insert 237163
Com_insert_select 45
Com_kill 0
Com_load 0
Com_load_master_table 0
Com_lock_tables 22
Com_optimize 3
Com_purge 0
Com_rename_table 0
Com_repair 0
Com_replace 332505
Com_replace_select 11
Com_reset 0
Com_restore_table 0
Com_revoke 0
Com_rollback 0
Com_select 21723894
Com_set_option 1885
Com_show_binlogs 0
Com_show_create 645
Com_show_databases 175
Com_show_fields 726
Com_show_grants 0
Com_show_keys 142
Com_show_logs 0
Com_show_master_status 0
Com_show_open_tables 0
Com_show_processlist 0
Com_show_slave_status 0
Com_show_status 231111
Com_show_innodb_status 0
Com_show_tables 3092
Com_show_variables 132
Com_slave_start 0
Com_slave_stop 0
Com_truncate 0
Com_unlock_tables 0
Com_update 1163994
Connections 1821203
Created_tmp_disk_tables 78593
Created_tmp_tables 1292305
Created_tmp_files 0
Delayed_insert_threads 0
Delayed_writes 0
Delayed_errors 0
Flush_commands 1
Handler_delete 238813
Handler_read_first 407784
Handler_read_key 1284799277
Handler_read_next 866748024
Handler_read_prev 30484
Handler_read_rnd 227865353
Handler_read_rnd_next 2412233576
Handler_update 1248649039
Handler_write 124971966
Key_blocks_used 62341
Key_read_requests 158577939
Key_reads 8497
Key_write_requests 1673918
Key_writes 728877
Max_used_connections 202
Not_flushed_key_blocks 0
Not_flushed_delayed_rows 0
Open_tables 501
Open_files 916
Open_streams 0
Opened_tables 838
Questions 27460721
Select_full_join 3070
Select_full_range_join 1
Select_range 1895690
Select_range_check 0
Select_scan 2913644
Slave_running OFF
Slave_open_temp_tables 0
Slow_launch_threads 0
Variable_name Value
Slow_queries 17
Sort_merge_passes 0
Sort_range 2490289
Sort_rows 321864749
Sort_scan 1759843
Table_locks_immediate 25094330
Table_locks_waited 5539
Threads_cached 119
Threads_created 203
Threads_connected 84
Threads_running 1
Uptime 983392

Per your suggestions, I set:

[mysqld]
datadir = /home/mySQL
socket = /home/mySQL/mysql.sock
skip-locking
skip-innodb
set-variable = max_connections=500
set-variable = key_buffer=64M
set-variable = join_buffer=4M
set-variable = record_buffer=1M
set-variable = sort_buffer=8M
set-variable = table_cache=600
set-variable = wait_timeout=1000
set-variable = connect_timeout=3
set-variable = max_allowed_packet=4M
set-variable = max_connect_errors=10

# Try number of CPU's*2 for thread_concurrency
set-variable = thread_concurrency=2

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open-files-limit=4096

[mysqlhotcopy]
interactive-timeout

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[myisamchk]
set-variable = key_buffer=16M
set-variable = sort_buffer=16M
set-variable = read_buffer=4M
set-variable = write_buffer=4M

Joined: Jan 2003
Posts: 338
Enthusiast
Enthusiast
Offline
Joined: Jan 2003
Posts: 338
In which file on disk I could find simmilar contents ?

Joined: Jan 2003
Posts: 36
User
User
Offline
Joined: Jan 2003
Posts: 36
so how is it running now?

i found a NEW defination of slow.
mysqld hit 116meg for each instance.
now, put 15 of them in memory.
and httpd and the rest of the os.
guess what?
swap hell!!! it was ugly to say the least.

i still think you have to many connections and the table cache is too big. try more like 300 or 200 connections and a table cache of 525.
how many users are hitting the forums at the same time?

Joined: Apr 2003
Posts: 10
Newbie
Newbie
Offline
Joined: Apr 2003
Posts: 10
> mysqld hit 116meg for each instance.
If you saw this in 'top', you should know, that this includes the shared memory of all mysqld processes on linux systems.

This is not really bad ;-)

It is critical if mysqld uses as much memory as the size of the key buffer.

So long...

Indy

Last edited by indy; 05/19/2003 3:13 PM.
Joined: Feb 2002
Posts: 950
Hacker
Hacker
Offline
Joined: Feb 2002
Posts: 950
[]If you saw this in 'top', you should know, that this includes the shared memory of all mysqld processes on linux systems. [/]

Now that is interesting! How can I tell how many processes are running?

Sponsored Links
Joined: Oct 2000
Posts: 2,223
Veteran
Veteran
Offline
Joined: Oct 2000
Posts: 2,223
ps -auxf and count the mysqld proceses. Most of which will be sound asleep. You could get really fancy and pipe that to grep for mysql then send it to word count

When you see 114 megs that's for all of them, not each.


Picture perfect penmanship here.
Joined: May 1999
Posts: 3,039
Guru
Guru
Offline
Joined: May 1999
Posts: 3,039
Yeah, like:

ps auxw | grep -c mysqld


UBB.threads Developer
Joined: Jan 2003
Posts: 36
User
User
Offline
Joined: Jan 2003
Posts: 36
i beg to differ.
because only SOME of them were 114meg.
others were at 110meg and others were at 106meg.
so was the 106 shared or was it 110 or 114?
there were around 16 instances of mysqld running.

i can watch it right now and see sizes of 38, 41 and 43meg for different mysqld processes.

there ARE other ways of viewing system stats without using top.

Joined: Feb 2002
Posts: 950
Hacker
Hacker
Offline
Joined: Feb 2002
Posts: 950
ps auxw | grep -c mysqld
209

Does that seem right?

Joined: Apr 2003
Posts: 10
Newbie
Newbie
Offline
Joined: Apr 2003
Posts: 10
You can use some of the extended views of top.
Press f or F and then mark the following:

k, l, n, o

Then you will see a lot of informations.
Including the one, that your mysqld is not swapping out any memory ;-)

So long...

Indy

Joined: Jan 2003
Posts: 36
User
User
Offline
Joined: Jan 2003
Posts: 36
this is at 13+ hours of runtime.

with k,l,n,o

9444 mysql 17 0 3171 4055M 36236 35M 1616 R 27.8 2.3 0:03 mysql
9521 mysql 17 0 3171 4055M 36236 35M 1616 R 26.0 2.3 0:00 mysql
9429 mysql 18 0 3171 4055M 36236 35M 1616 R 14.3 2.3 0:04 mysql
9519 mysql 17 0 3171 4055M 36236 35M 1616 R 12.5 2.3 0:00 mysql
9519 mysql 16 0 3171 4057M 36200 35M 1616 R 10.6 2.3 0:00 mysql
9444 mysql 16 0 3171 4062M 38200 37M 1616 R 9.9 2.4 0:03 mysql
9328 mysql 15 0 3171 4062M 38200 37M 1616 S 4.2 2.4 0:25 mysql
8829 mysql 15 0 3171 4062M 38200 37M 1616 S 3.5 2.4 0:57 mysql
8857 mysql 15 0 3171 4062M 38200 37M 1616 S 2.1 2.4 0:51 mysql
9270 mysql 16 0 3171 4062M 38200 37M 1616 R 2.1 2.4 0:24 mysql
9515 mysql 16 0 3171 4057M 36200 35M 1616 S 1.4 2.3 0:00 mysql

in 2 more days, these will be closing in on 100meg each and swapping will ensue. right now it is not swapping, because it all fits (under 500meg in size), but soon (36 hours) it won't fit in 1.5gig.

Joined: Apr 2003
Posts: 10
Newbie
Newbie
Offline
Joined: Apr 2003
Posts: 10
What about your apache daemon?
I told you, that it is a bad idea, running mysql under heavy load and an apache on the same machine.

Did you take a look in your slow queries logfile?
If you calculate your cache miss rate, you will see, it is _very_ low. But, if you think about, it is not so low because of not having enough memory. It is so low because of the missing indices.
Please note your "Select Scan" and your "Select full Join" values. They are to high!
Your problem are not the buffers of MySQL, you should optimize the database structure.

So long...

Indy

Joined: Jan 2003
Posts: 36
User
User
Offline
Joined: Jan 2003
Posts: 36
and here is mysql swapping.

2:02pm up 2 days, 9:41, 1 user, load average: 0.71, 0.66, 0.77
104 processes: 98 sleeping, 6 running, 0 zombie, 0 stopped
CPU states: 64.4% user, 36.4% system, 0.0% nice, 0.0% idle
Mem: 1548256K av, 1467428K used, 80828K free, 0K shrd, 165056K buff
Swap: 1020116K av, 1600K used, 1018516K free 1110256K cached

PID USER PRI NI TSIZE DSIZE SIZE TRS SWAP RSS SHARE STAT %CPU %MEM
6306 mysql 19 0 3171 4071M 62364 1380 8 60M 1624 R 28.6 4.0
6318 mysql 18 0 3171 4071M 62364 1380 8 60M 1624 S 21.9 4.0
3440 mysql 16 0 3171 4071M 62364 1380 8 60M 1624 R 13.5 4.0
6765 root 16 0 26 3969M 1100 40 0 1100 868 R 10.9 0.0
6133 mysql 15 0 3171 4071M 62364 1380 8 60M 1624 S 3.3 4.0
29170 mysql 16 0 3171 4071M 62364 1380 8 60M 1624 R 1.6 4.0
1 root 15 0 23 3969M 484 28 0 484 420 S 0.0 0.0
2 root 15 0 0 0 0 0 0 0 0 SW 0.0 0.0
3 root 15 0 0 0 0 0 0 0 0 SW 0.0 0.0
4 root 34 19 0 0 0 0 0 0 0 SWN 0.0 0.0
5 root 15 0 0 0 0 0 0 0 0 SW 0.0 0.0
6 root 25 0 0 0 0 0 0 0 0 SW 0.0 0.0
7 root 15 0 0 0 0 0 0 0 0 SW 0.0 0.0

Joined: Jan 2003
Posts: 36
User
User
Offline
Joined: Jan 2003
Posts: 36
swap swap swap goes mysql

3:42pm up 2 days, 11:21, 1 user, load average: 2.27, 1.81, 1.56
147 processes: 124 sleeping, 23 running, 0 zombie, 0 stopped
CPU states: 61.7% user, 38.2% system, 0.0% nice, 0.0% idle
Mem: 1548256K av, 1495656K used, 52600K free, 0K shrd, 165096K buff
Swap: 1020116K av, 3292K used, 1016824K free 1095628K cached

PID USER PRI NI SIZE SWAP RSS SHARE STAT %CPU %MEM TIME COMMAND
3853 mysql 17 0 62776 392 60M 1476 R 16.4 4.0 3:04 mysqld
7988 mysql 15 0 62808 392 60M 1476 S 16.4 4.0 0:20 mysqld
8424 root 16 0 1124 0 1124 868 R 13.3 0.0 0:08 top
7244 mysql 16 0 62776 392 60M 1476 R 10.1 4.0 0:40 mysqld
7243 mysql 16 0 62776 392 60M 1476 R 3.9 4.0 0:39 mysqld
8412 httpd 15 0 9148 0 9148 7872 S 3.1 0.5 0:00 httpd
8446 httpd 15 0 9196 0 9196 7804 S 3.1 0.5 0:00 httpd
7238 mysql 25 0 62776 392 60M 1476 R 2.3 4.0 1:02 mysqld
7241 mysql 16 0 62776 392 60M 1476 R 2.3 4.0 0:56 mysqld
8436 httpd 16 0 9436 0 9436 7712 R 2.3 0.6 0:00 httpd
8440 httpd 15 0 9204 0 9204 7804 S 2.3 0.5 0:00 httpd
8445 httpd 15 0 9492 0 9492 7812 S 2.3 0.6 0:00 httpd
7249 mysql 16 0 62776 392 60M 1476 R 1.5 4.0 0:38 mysqld
8361 mysql 16 0 62808 392 60M 1476 S 1.5 4.0 0:01 mysqld
7195 root 16 0 7684 0 7684 7528 S 0.7 0.4 0:01 httpd
7202 httpd 15 0 10444 0 10M 7868 S 0.7 0.6 0:08 httpd
7208 httpd 15 0 10544 0 10M 7776 S 0.7 0.6 0:08 httpd


only took 1 hour 40 minutes more to really take it into swap h3ll.
tsk, tsk, tsk.
i think this clearly shows that mysqld CAN and WILL take a system to its knees if allowed to grow in size to the point that it starts churning swap.
no more needs to be said really.

Joined: Jan 2003
Posts: 36
User
User
Offline
Joined: Jan 2003
Posts: 36
more swap h3ll.

4:10pm up 3 days, 4:13, 1 user, load average: 3.93, 2.41, 2.45
129 processes: 111 sleeping, 18 running, 0 zombie, 0 stopped
CPU states: 60.3% user, 39.6% system, 0.0% nice, 0.0% idle
Mem: 1548284K av, 1295932K used, 252352K free, 0K shrd, 147280K buff
Swap: 1020116K av, 3188K used, 1016928K free 898156K cached

PID USER PRI NI SIZE SWAP RSS SHARE STAT %CPU %MEM TIME COMMAND
16016 mysql 14 0 99404 120 96M 1632 R 18.8 6.4 0:27 mysqld
16050 mysql 20 0 99404 120 96M 1632 R 10.8 6.4 0:19 mysqld
15712 mysql 19 0 99404 120 96M 1632 R 9.8 6.4 1:02 mysqld
6 root 13 0 0 0 0 0 SW 8.9 0.0 54:40 kscand
15940 mysql 16 0 99404 120 96M 1632 R 6.9 6.4 0:34 mysqld
15727 mysql 14 0 99404 120 96M 1632 R 4.9 6.4 0:16 mysqld
16014 mysql 13 0 99404 120 96M 1632 R 3.9 6.4 0:44 mysqld
14922 httpd 12 0 10724 0 10M 5292 R 2.9 0.6 0:11 httpd
14991 httpd 12 0 10924 0 10M 5296 R 2.9 0.7 0:11 httpd
15944 mysql 12 0 99404 120 96M 1632 R 2.9 6.4 0:19 mysqld
15947 mysql 12 0 99404 120 96M 1632 R 2.9 6.4 0:50 mysqld
16013 mysql 12 0 99404 120 96M 1632 R 2.9 6.4 0:42 mysqld
16020 mysql 12 0 99404 120 96M 1632 R 2.9 6.4 0:23 mysqld
16504 mysql 12 0 99404 120 96M 1632 R 2.9 6.4 0:14 mysqld
16564 mysql 12 0 99404 120 96M 1632 R 2.9 6.4 0:10 mysqld
14937 httpd 11 0 10300 0 10M 5292 S 1.9 0.6 0:11 httpd
16680 httpd 11 0 9644 0 9644 5276 R 1.9 0.6 0:01 httpd

mysqld at 96meg. and into swap after only 3 days.
tsk tsk

Joined: Feb 2002
Posts: 950
Hacker
Hacker
Offline
Joined: Feb 2002
Posts: 950
Mem: 1548284K av, 1295932K used, 252352K free, 0K shrd, 147280K buff
Swap: 1020116K av, 3188K used, 1016928K free 898156K cached


Doesnt seem like much swapping going on.... You have 252352K of free memory - am I missing something?

Joined: Apr 2003
Posts: 10
Newbie
Newbie
Offline
Joined: Apr 2003
Posts: 10
The load average is to high and the cpu time used by the system is to high, too.
In this case it there are no more possibilities make MySQL running faster with tuning buffer values. There are some other
indices needed.
A few more indices and a key buffer with an eqal size - will make it perfect.
The swap space is used every time, a MySQL process is to "old": Linux thinks it could be swapped out.
If the MySQL processes have a shorter life time, because of the faster answering behaviour to the queries, there is no more need to swap out for Linux.


Link Copied to Clipboard
Donate Today!
Donate via PayPal

Donate to UBBDev today to help aid in Operational, Server and Script Maintenance, and Development costs.

Please also see our parent organization VNC Web Services if you're in the need of a new UBB.threads Install or Upgrade, Site/Server Migrations, or Security and Coding Services.
Recommended Hosts
We have personally worked with and recommend the following Web Hosts:
Stable Host
bluehost
InterServer
Visit us on Facebook
Member Spotlight
JAISP
JAISP
PA
Posts: 449
Joined: February 2008
Forum Statistics
Forums63
Topics37,573
Posts293,925
Members13,849
Most Online5,166
Sep 15th, 2019
Today's Statistics
Currently Online
Topics Created
Posts Made
Users Online
Birthdays
Top Posters
AllenAyres 21,079
JoshPet 10,369
LK 7,394
Lord Dexter 6,708
Gizmo 5,833
Greg Hard 4,625
Top Posters(30 Days)
Top Likes Received
isaac 82
Gizmo 20
Brett 7
WebGuy 2
Morgan 2
Top Likes Received (30 Days)
None yet
The UBB.Developers Network (UBB.Dev/Threads.Dev) is ©2000-2024 VNC Web Services

 
Powered by UBB.threads™ PHP Forum Software 8.0.0
(Preview build 20221218)