600MB Database Optimise

Jadmanx · 2013-03-10 11:42:39

i need some tipps for optimise the database. my server gets down whan 20 user at the same time on its.
my database is real big more than 600MB i have much videos and much Description for ech movie.

Cache Driver:file
Cache Lifetime:630min
Cache Templates:yes

Session Driver:none
Session Driver:10min

My server:
Processor Name Intel(R) Xeon(R) CPU E31260L @ 2.40GHz
Vendor ID GenuineIntel
Processor Speed (MHz) 1600.000
Total Memory 4033600 kB
Free Memory 728868 kB
Total Swap Memory 6094840 kB
Free Swap Memory 5820592 kB

top - 11:25:02 up 43 days, 17:50, 0 users, load average: 19.06, 11.07, 6.38
Tasks: 299 total, 1 running, 297 sleeping, 0 stopped, 1 zombie
Cpu(s): 5.8%us, 2.0%sy, 0.0%ni, 91.3%id, 0.9%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 4033600k total, 3458828k used, 574772k free, 174476k buffers
Swap: 6094840k total, 325156k used, 5769684k free, 1383768k cached

Last edited by Jadmanx (2013-03-18 15:27:55)

phseven · 2013-03-10 18:58:37

Hey

looks like the problem is on server load, 19.06 peak and 11.07 for a xeon is on high side

if you don´t have high volume of users , check if you have any open MYSQL PID´s

Jadmanx · 2013-03-10 19:47:10

downt know whats "open MYSQL PID´s" are.

phseven · 2013-03-11 11:07:46

you can check that via yoru admin panel or SSH
Under ssh just write TOP , you shouls see open mysql requests, they are usually on top of the list

under direct admin, you need admin access

Go to admin tools , Process monitor, check for any mysql request that is not close.

Question: do you have script + mysql + videos in 1 server? if so how many daily users you have.

Jadmanx · 2013-03-11 11:58:05

top - 07:30:21 up 45 days, 12:55, 0 users, load average: 1.18, 1.82, 3.03
Tasks: 277 total, 1 running, 275 sleeping, 0 stopped, 1 zombie
Cpu(s): 6.3%us, 2.1%sy, 0.0%ni, 90.6%id, 1.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 4033600k total, 3258196k used, 775404k free, 176852k buffers
Swap: 6094840k total, 206688k used, 5888152k free, 1560636k cached

31626 mysql 15 0 2581m 1.0g 4508 S 33.4 25.4 34703:11 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/hosted-by.al.com.err --open-files-limit=8192 --pid-file=/var/lib/mysql/hosted-by.al.com.pid
16465 apache 16 0 248m 46m 25m S 2.0 1.2 0:00.29 /usr/sbin/httpd -k start -DSSL
16592 apache 16 0 236m 13m 5236 S 2.0 0.3 0:00.01 /usr/sbin/httpd -k start -DSSL
1 root 15 0 10364 584 548 S 0.0 0.0 0:04.56 init [3]

you mean this ?
i have 3000 user a day

symtab · 2013-03-11 15:50:09

Are you using Track Views in the Admin -> Video -> Config -> Advanced? Do not use cache, for 3000 visitors / day, cache will create load not save load.

Jadmanx · 2013-03-11 16:29:42

no Track Views is disable

phseven · 2013-03-11 18:03:26

top - 07:30:21 up 45 days, 12:55, 0 users, load average: 1.18, 1.82, 3.03

looks fine for 3 000 visits daily

the peaks you have is probably someone ripping you videos from wget or something

Jadmanx · 2013-03-11 23:43:50

at this time i have 16 user on my page and the httpd service use 6 GB and my server get real slow.
i downt know its a bug of adultscript ?
some one know what i have to doo ?

Last edited by Jadmanx (2013-03-11 23:49:03)

symtab · 2013-03-12 17:14:19

Are you using cgi/fgi/fpm or mod_php? Are there active connections in netstat -taunwp?

symtab · 2013-03-12 17:15:31

Which table use the most space?

Jadmanx · 2013-03-12 21:33:54

here the bigges tables...

webcam_video_tags 2,991,602 MyISAM utf8_general_ci 195.5 MiB -
webcam_video    273,028 MyISAM utf8_general_ci 186.7 MiB -
webcam_guest    1,790,210 MyISAM utf8_general_ci 84.7 MiB -
webcam_link_in    581,484 MyISAM utf8_general_ci 63.1 MiB -
webcam_video_history    877,675 MyISAM utf8_general_ci 39.8 MiB -

symtab · 2013-03-12 22:19:16

How many videos do you have? 273.028?

phseven · 2013-03-13 07:48:22

this is mine

but I have around 120K daily visitors, building now new server for load balancing

top - 03:38:19 up 24 days, 21:13, 0 users, load average: 7.51, 4.66, 4.14
Tasks: 317 total, 1 running, 316 sleeping, 0 stopped, 0 zombie
Cpu(s): 1.1%us, 0.4%sy, 0.0%ni, 96.8%id, 1.7%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 8180852k total, 5289928k used, 2890924k free, 632020k buffers
Swap: 8385920k total, 152k used, 8385768k free, 3343876k cached

Jadmanx · 2013-03-13 08:29:29

yes 273028 videos,
what webcam_video_history,webcam_link_in and webcam_guest ?
can i slim them ?

symtab · 2013-03-13 14:12:03

You can delete all the rows in those tables, but i dont think mysql is the problem. When you do a top, does mysql appear at the top of the process list non-stop? Also do you have slow queries enabled?

Jadmanx · 2013-03-17 11:12:46

slow log queries is enabled in the server.
did i have do disable it ?

Last edited by Jadmanx (2013-03-17 11:16:56)

Jadmanx · 2013-03-17 14:02:45

thats my hosting service say:
We have monitored your server and we could see your server is consuming more server resources which causes server down.
Please contact your developer and optimize your scripts to resolve this issues.

symtab · 2013-03-17 19:53:02

No. Are there any slow queries?

Jadmanx · 2013-03-19 13:54:37

Uptime: 173331 Threads: 10 Questions: 5646410 Slow queries: 20886 Opens: 8880 Flush tables: 1 Open tables: 1024 Queries per second avg: 32.575
[root@hosted-by ~]#

symtab · 2013-03-19 18:55:45

Those slow queries are the problem. Please email me ssh root access and i will check the queries.

Jadmanx · 2013-03-21 10:04:02

i have mail you, did you found someting ?

symtab · 2013-03-21 16:27:13

I'm checking today and will let you know.

Jadmanx · 2013-04-20 16:41:26

i must make my sql slim.

what make webcam_guest , webcam_link_in ? can i clear it ?

webcam_video_history is for analytics ? can i clear it and deaktivate ?

thanks

webcam_video_tags 2,991,602 MyISAM utf8_general_ci 195.5 MiB -
webcam_video 273,028 MyISAM utf8_general_ci 186.7 MiB -
webcam_guest 1,790,210 MyISAM utf8_general_ci 84.7 MiB -
webcam_link_in 581,484 MyISAM utf8_general_ci 63.1 MiB -
webcam_video_history 877,675 MyISAM utf8_general_ci 39.8 MiB -

symtab · 2013-04-22 07:13:57

You can clear all those tables. You can deactivate video_history.

Adult Script Pro Community Forums

#1 2013-03-10 11:42:39

600MB Database Optimise

#2 2013-03-10 18:58:37

Re: 600MB Database Optimise

#3 2013-03-10 19:47:10

Re: 600MB Database Optimise

#4 2013-03-11 11:07:46

Re: 600MB Database Optimise

#5 2013-03-11 11:58:05

Re: 600MB Database Optimise

#6 2013-03-11 15:50:09

Re: 600MB Database Optimise

#7 2013-03-11 16:29:42

Re: 600MB Database Optimise

#8 2013-03-11 18:03:26

Re: 600MB Database Optimise

#9 2013-03-11 23:43:50

Re: 600MB Database Optimise

#10 2013-03-12 17:14:19

Re: 600MB Database Optimise

#11 2013-03-12 17:15:31

Re: 600MB Database Optimise

#12 2013-03-12 21:33:54

Re: 600MB Database Optimise

#13 2013-03-12 22:19:16

Re: 600MB Database Optimise

#14 2013-03-13 07:48:22

Re: 600MB Database Optimise

#15 2013-03-13 08:29:29

Re: 600MB Database Optimise

#16 2013-03-13 14:12:03

Re: 600MB Database Optimise

#17 2013-03-17 11:12:46

Re: 600MB Database Optimise

#18 2013-03-17 14:02:45

Re: 600MB Database Optimise

#19 2013-03-17 19:53:02

Re: 600MB Database Optimise

#20 2013-03-19 13:54:37

Re: 600MB Database Optimise

#21 2013-03-19 18:55:45

Re: 600MB Database Optimise

#22 2013-03-21 10:04:02

Re: 600MB Database Optimise

#23 2013-03-21 16:27:13

Re: 600MB Database Optimise

#24 2013-04-20 16:41:26

Re: 600MB Database Optimise

#25 2013-04-22 07:13:57

Re: 600MB Database Optimise

Board footer