MySQL Server/DB Optimization

Awarded

note you will be working on an adult website.

Issue:

- We have a client with large video sharing website running AVS script. Average users online 500.

- We notice there was lag in website speed when there in 2000 users online at peak time (11AM-2PM EST)

- The issue appears not with web servers or loadbalancer however mysql server shows sign of high cpu usage when this happens.

- During peak time 4-6CPU cores being used by mysql and load goes up to 200. We have recently upgraded the mysql server to 2 X Intel(R) Xeon(R) CPU E5-2620, 16GB ram, SSD drive and the issue still there. The issue appears not I/O wait or memory usage. Highest mysql connection peak at 900 at a given time.

- MYSQL tuning script statics below

-------- General Statistics --------------------------------------------------

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.0.95-log

[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------

[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster

[--] Data in MyISAM tables: 44M (Tables: 69)

[!!] Total fragmented tables: 7

-------- Security Recommendations -------------------------------------------

[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------

[--] Up for: 1d 21h 21m 54s (72M q [[url removed, login to view] qps], 5M conn, TX: 46B, RX: 7B)

[--] Reads / Writes: 78% / 22%

[--] Total buffers: [url removed, login to view] global + [url removed, login to view] per thread (1500 max threads)

[!!] Maximum possible memory usage: [url removed, login to view] (494% of installed RAM)

[OK] Slow queries: 5% (3M/72M)

[OK] Highest usage of available connections: 62% (937/1500)

[OK] Key buffer size / total MyISAM indexes: [url removed, login to view]

[OK] Key buffer hit rate: 100.0% (1B cached / 19K reads)

[OK] Query cache efficiency: 40.4% (19M cached / 48M selects)

[OK] Query cache prunes per day: 0

[OK] Sorts requiring temporary tables: 0% (4K temp sorts / 13M sorts)

[OK] Temporary tables created on disk: 2% (11K on disk / 449K total)

[OK] Thread cache hit rate: 99% (4K created / 5M connections)

[OK] Table cache hit rate: 99% (1K open / 1K opened)

[OK] Open file limit used: 16% (1K/7K)

[!!] Table locks acquired immediately: 90%

-------- Recommendations -----------------------------------------------------

General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance

Reduce your overall MySQL memory footprint for system stability

Optimize queries and/or use InnoDB to reduce lock wait

Project Goal: The website navigation speed shouldn't lag and should have same speed when the online user is 1000 or 2000. The hardware is not an issue here or the web server. Website growing daily and should be able to handle the growth without a beat.

Project Requirement:

- Login to mysql server during peak time (11AM-2PM EST) few days to observe the load spike and compare the navigation speed

- Mysql server does not have any control panel, you should be able to work through command only via ssh

- You must be well versed in linux,mysql, php as you may have to re-write the mysql query that script uses to reduce load or table locks

- Downtime is not acceptable, if you want to make changes to sql server can only be done during off peak hours (6PM - 9PM EST) and must be communicated in advance of how long you need

- All changes must be documented and presented to us once completed, we will need this when we move to another sql server.

- If you have any questions about the infrastructure design, ask before bidding

- Project goal should be met (min 90%)

Project Budget: $100

Please bid only if you can meet the project requirements!!!

Skills: Graphic Design, HTML, PHP, Website Design

See more: working for 3m, web design tuning, web design day rate, web design cores, up design 99, temp website design, statistics archive, r-rx, php and mysql connection, peak memory, peak design, online disk, online architecture design work, mysql in memory engine, memory engine, log in design 99, disk design online, design web cores, design online 99, design 99 log in, check online website security, check for website security online, 99 design web design, 99 design sign in

Project ID: #4023998

6 freelancers are bidding on average $565 for this job

USAGraphicPR0

********************************************.. YOUR SEARCH ENDS HERE .. ********************************************** GET IT DONE RIGHT THE FIRST TIME! Hello Sir/Ma'am, I look forward to talking to you further and get More

$1000 USD in 8 days
(118 Reviews)
8.0
WebBizSolution

I run European website design firm with years of design experience personally developing over 300+ websites. I can also provide a list of references from past customers if you'd like. I'm not the cheapest you'll find, More

$888 USD in 7 days
(64 Reviews)
7.1
stels88

I can help you!

$250 USD in 2 days
(118 Reviews)
6.1
zelyonka

Hi, yudaprawira4y! Please, see our offer in PMB.

$500 USD in 7 days
(4 Reviews)
3.9
reynaldoegr

I have read your complete details, I can work via ssh only there is no problem, I have optimized large sites that use to go down at peak time: (http://tao24open.com - currently running i7 quadcore 8gb ram) average 1500 More

$250 USD in 2 days
(8 Reviews)
3.3
DuK84EJ2s

We are freelance software developers. If you contact me I can give a quote for your project and we can discuss the details. <b><i>Removed by Admin</i></b>

$500 USD in 1 day
(0 Reviews)
0.0
thomasbhy

we will provide you complete Solution as per your need. For more detail please check our message where you can find our previous work too

$250 USD in 4 days
(0 Reviews)
0.0