Mysql Delayed Writes Performance Boost, Worth The Risk
Jul 19, 2007
During my poking around performance tips I found the DELAY_KEY_WRITE option (and innodb_flush_log_at_trx_commit = 0 for innodb)
which supposedly for mysql will disable the immediate disk flush for every transaction written and instead update only once every second at most?
One thing I've never had to restart on my vps is mysql, it's been great. So is this safe to turn on? Am I risking corruption? Will the performance gain be worth it with only a 16M cache?
I just moved my web server from ISP A to ISP B. There is one thing confusing me.
My real time bandwidth in ISP A shows 1M-1.5M bit per second at peak time before I moved, but after moving, my real time bandwidth report in ISP B shows 5M-7M bit per second. I don't think my traffic grows so fast in just 2 days. After talked to the technician, I realize my monthly bandwidth now grows to about 2000G based on my situation. But, I know my monthly bandwidth was just about 300G just 2 days ago.
I installed iptraf and it shows same report as my real time bandwidth report shows. My site is not downloading site. I also checked server load, it's just less than 0.05, not like a ddos.
I am considering to upgrade to MySQL 5.0. I am using 4.1 at present. Now I wonder if it will really improve performance... I really have some busy databases... Also wonder if 5.0 is fully downwards compatible to 4.1
I have a site that runs from a MySQL database. The database isn't big, it has only 16.1 MB on 17 tables with 103,978 records, and all are properly indexed.
My problem is that my MySQL has a particular problem with this database because I'll have to wait around 5-10 minutes to receive the query results.
I've tuned MySQL, I've tuned Apache and in the daily usage i have usually low Load Averages of 0.19 0.22 0.40. My server specs are Intel P4 at 3GHz with HT, 2Gb of RAM (in dual Chanel), 2 Hdd of 250Gb (backup) and 300Gb (main hdd), both with 16 mb cache. I'm running Debian 3.1 with 2.6 kernel and there is no swapping to disk as i had previous on the 2.4 kernel.
I will post my.conf below and maybe you'll give me a suggestion.
Code: # # The MySQL database server configuration file. # # You can copy this to one of: # - "/etc/mysql/my.cnf" to set global options, # - "/var/lib/mysql/my.cnf" to set server-specific options or # - "~/.my.cnf" to set user-specific options. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # [url]
# This will be passed to all mysql clients # It has been reported that passwords should be enclosed with ticks/quotes # escpecially if they contain "#" chars... # Remember to edit /etc/mysql/debian.cnf when changing the socket location. [client] port= 3306 socket= /var/run/mysqld/mysqld.sock
# Here is entries for some specific programs # The following values assume you have at least 32M ram
# This was formally known as [safe_mysqld]. Both versions are currently parsed. [mysqld_safe] socket= /var/run/mysqld/mysqld.sock nice= 0
[mysqld] # # * Basic Settings # user= mysql pid-file= /var/run/mysqld/mysqld.pid socket= /var/run/mysqld/mysqld.sock port= 3306 basedir= /usr datadir= /var/lib/mysql tmpdir= /tmp language= /usr/share/mysql/english skip-external-locking # # For compatibility to other Debian packages that still use # libmysqlclient10 and libmysqlclient12. old_passwords= 1 # # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. bind-address= 127.0.0.1 # # * Fine Tuning # key_buffer= 32M key_buffer_size= 32M max_allowed_packet= 16M myisam_sort_buffer_size = 32M table_cache= 3072 sort_buffer_size= 4M read_buffer_size= 4M read_rnd_buffer_size= 4M join_buffer_size= 2M thread_stack= 512K wait_timeout= 300 max_connection = 60 max_connect_errors= 10 thread_cache_size= 100 long_query_time= 2 max_user_connections= 50 interactive_timeout= 100 connect_timeout= 15 tmp_table_size= 64M open_files_limit= 3072 max_heap_table_size = 64M # # * Query Cache Configuration # query_cache_limit= 2M query_cache_size = 64M query_cache_type = 1 # # * Logging and Replication # # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. #log= /var/log/mysql.log #log= /var/log/mysql/mysql.log # # Error logging goes to syslog. This is a Debian improvement :) # # Here you can see queries with especially long duration log-slow-queries= /var/log/mysql/mysql-slow.log log-queries-not-using-indexes = /var/log/mysql/mysql-index.log # The following can be used as easy to replay backup logs or for replication. #server-id= 1 log-bin= /var/log/mysql/mysql-bin.log # See /etc/mysql/debian-log-rotate.conf for the number of files kept. max_binlog_size = 104857600 #binlog-do-db= include_database_name #binlog-ignore-db= include_database_name # # * BerkeleyDB # # According to an MySQL employee the use of BerkeleyDB is now discouraged # and support for it will probably cease in the next versions. skip-bdb # # * InnoDB # # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. # Read the manual for more InnoDB related options. There are many! # # * Security Features # # Read the manual, too, if you want chroot! # chroot = /var/lib/mysql/ # # If you want to enable SSL support (recommended) read the manual or my # HOWTO in /usr/share/doc/mysql-server/SSL-MINI-HOWTO.txt.gz # ssl-ca=/etc/mysql/cacert.pem # ssl-cert=/etc/mysql/server-cert.pem # ssl-key=/etc/mysql/server-key.pem
I thought that i might need to start rewriting my code in order to be able to fix things but i need at least another opinion from someone who knows more.
I tried setting up my web server with the user's home directories as a NFS and everything seemed to be working except for one thing I found. When I used php write which I have a couple scripts that do, it can't use fopen with the option "w" which means to trunicate the length to 0 and point at the beginning of the file.
It allows me to use "a" which just writes at the bottom of the file. This is weird because when I login to shell I can delete the files, edit the files, and make files.
It gives me this error....[function.fopen]: failed to open stream: Operation not permitted
httpd 2.0.59, php 4.4.6, userspace nfs, using no_root_squash,sync,no_all_squash,rw in the exports.
Right now i have a busy running forum Running on a Single Xeon 5335 with 4GB of RAM,
single 73GB SCSI 15K. And the site seems running fine most of the time except at peak.
The load sometime goes up to 8 for about 1 hour. So i am looking to upgrade my server.
The next server i am thinking about is
Single C2Q 9300, 8GB of RAM, 1x750GB SATAII as primary drive for for webserver, 1x150GB Raptor 10K to serve MYSQL only.
I wonder if the HDD performance on my current server server with future server be the same of better? Since the future server has better CPU and RAM, the only thing i worry is the HDD performance.
So in short, Single SCSI 15K V.S Combination of SATA + RAPTOR. What do you guy think?
i am using Litespeed as webserver and i also will be using litespeed on future server.
I have the hatchling plan on HostGator. Is there a PHP and MySQL open-source application where I can put up there to test its performance on running PHP and MySql applications? I want to see if there is any performance issue on this host before migrating a large ZenCart app to it.
What are people's experience on HostGator running PHP and MySQL?
I'm going to be rolling out a php/mysql driven site soon and I'm pretty much resigned to the fact that the mysql performance dreamhost has given me isn't going to cut it, its probably too oversold. Simple one table one column selects can take 30 seconds or time out depending on how badly the server is being hammered. HTTP requests are usually snappy, but the mysql performance is bogus.
What is a good host for me to launch this site with? Storage wouldn't need to be too terribly high, at least initially. I'm tempted by MediaTemple's slick marketing, but I've seen on here that some people have had poor sql performance (contrary to what some personal friends have experienced, so I'm torn). I was reading about downtownhost on here, but their load times seemed slow when I hit a couple domains listed on here that are hosted by them.
This host needs to be located in the US. Honestly, I like dreamhost and their panel, save for the sql sluggishness I'm getting.
I have just recently moved a site over to my VPS, everything is working fine except for Paypal IPN Emails which arrive in my inbox 2 days after the sent timestamp, I have had 5 which have come through on time, this has led me to think it may be a problem with my VPS settings, however the host SolarVPS have checked the logs and can see nothing wrong, and have asked me to speak with Paypal as they are unable to explain it. I just wondered if anyone here has seen a problem like this, where it only affects one sender.
I have looked to see if others have had this from Paypal before, and seen many issues but not this one in particular.
This is the only message I receive when I send an email to myself from Hotmail to customerdomain.com
Delivery Status Notification (Delay)þ From: postmaster@mail.hotmail.com Sent: Thu 1/17/08 10:33 AM To: user@hotmail.com ________________________________________
This is an automatically generated Delivery Status Notification.
THIS IS A WARNING MESSAGE ONLY.
YOU DO NOT NEED TO RESEND YOUR MESSAGE.
Delivery to the following recipients has been delayed.
user@customerdomain.com
--Forwarded Message Attachment-- From: user@hotmail.com To: user@customerdomain.com Subject: Date: Wed, 16 Jan 2008 22:32:48 +0000
********************
Why would it be delayed? I'm using cpanel / centos
Possibly too much mail in queue or something else?
I'm running into a problem with a relatively new (2 months old) server. I have just a few accounts on it, and I'm already noticing unusual loads, for the... load. After some benchmarking with bonnie++ (and plain old "dd") there is clearly a problem.
Isn't a write-speed over 7MB/s reasonable to expect? Also look at the low CPU times...
Anyway running the same test on a similar but older AND busier server showed much better results than this. In fact dd'ing a 1GB file from /dev/zero "finished" in about 10 seconds but then pegged the server at 99% iowait (wa) for a full three minutes (until done being written from cache I assume), bringing load to 15.00
That's all the info I have so far... the data center just replaced the card (which gave no errors) with no effect. Running these benchmark tests is about the extend of my hardware experience.
I have a windows 2003 VPS and I'm not satisfied with the HD I/O speed, and before I mention what speeds I am getting I just want to see what people say should be plausible. I want to know if I am thinking I should get more out of the VPS then I really should.
Anyway, I used this program from ATTO to benchmark: short-media.com/download.php?d=108 Called ATTO Disk Benchmark
If anyone is willing, run it on your VPS and post a screenshot. It's a standalone .exe, no install needed.
It is currently with company A who charge quite a lot to keep it there. I want to move it to company B who are my hosts and with whom I get 1 free domain name.
One added complication is that the domain is in a friends name, but I have logon and can change name to my own any time I want.
Company B said "After it is on our registrar, you will be the only one that can renew it as long as it doesn't expire for longer than 90 days."
This has me worried that because I haven't renewed it withing 90 days that it can be stolen from me. Have I misunderstood or is this a risk?
If so would I be better advised to renew it in my friends name wth company A?
I'm a Windows guy and can little or nothing about Linux. How big risk do I take if I'm using a Linux VPS and never update/patch the kernel?
I'm using CentOS 5 and LxAdmin. I can update the control panel, but I can not update/patch the kernel since I have no knowledge how I do that.
I'm using a unmanaged plan, so no help there.
Some of my sites are running Wordpress, but I'm always using the lates WP installation. I not using any other plugins that WG2, Gallery2, and remove max width.
Nobody except me have access to the VPS, and I have no other FTP accounts or something like that on the VPS.
I have no other scripts or any kind of dynamic pages on my VPS.
What kind of risk do I have here?
I'm currently having plans to cancel my second VPS that's using Win2003, and only use Linux in the future. I can cut my monthly expensive with 50% that way, but do I take a big risk doing it that way?
I am using dreamhost host 3 of my web sites and 1 blog. Dreamhost is great, offers alot space and bandwidth.
but I think they are oversellling their space, sometimes it gets really slow. (overselling ? ok, I dont really know, but sometimes its really slow, and most my asian readers said need to refresh to load the page. I am wondering if theres a way to check if they are overselling or not.)
I am thinking about buying vps, even tho, I still got 5 month left with dreamhost.
I found 2 vps companies are highly recommanded on this forum, JaguarPC and LiquidWeb.
theres already a post compared both companies in terms of price and service. I say I will pick JagarPc, cuz, its basic plan just 20 USD, and htey got promotion now, its even cheaper. and basic Liquidweb vps plan is 60 bucks.
I am wondering why Jagarpc is so cheap , are they overselling? how can we check if they are overselling.
I found a few posts saying how good jaguarPc is. and they are not overselling, but those members just signed up this month, and only have 1-3 posts. I cannot really trust those new members.
Can someone share their experience with JaguarPC? compare JaguarPc performance and liquidweb performance. antoher question is switch from dreamhost to JaguarPC basic vPS plan, will performance gets better?
last question: VPS account allows 3 IP, 3ip = 3 domains? if not, how many domains can I have?
At present I run SSH on a different port then normal to protect root. This has worked for two years, but with discovering that cPanel finally support SFTP without shell access needed, I want to finally turn off FTP and require SFTP. The problem is the port I am using. Since it's a random port I have been secured against root attacks (well nothing has shown up). I am with LiquidWeb which is fully managed. So I guess they take care of allot of prevention.
This is what I am thinking of doing. move SSH back to port 22 (I only host a few friends sites and want to be hosting 20 accounts by end of year to cover my costs). Then disabled root password and require SSH keys. Would this be strong as secure as running SSH on a high #port or am I fooling myself.
I could also add in for good measure restricting root SSH/SFTP (yes I prefer SFTP for file management as I am legally blind and using Transmit+BBEdit is allot easier for me for editing files). The problem with restricting to certain IP's, is that Shaw charges $30/month more for a static IP and I also am at my moms 25% of the time (and she is also with Shaw). I think the XXXX.vs.shawcable.net is static but I am not 100% sure.
I really do want to kill FTP so that only port 80 is the only non SSL port open.
There are always people who would like to know what the php settings are on the server. Is it a security risk to share the phpinfo.php file on a website, with anybody who visits that website, able to view it?
A friend of mine owns a hosting company and a client of his asked to have mbstring and mysqli installed. What he wants to know is , is there any security risks if he does install that on his server?
Also, he wants to know, if there is not, what how does he go about installing that on the server?
I did a quick search on this and could not see it as already being posted
It seems quite a clever but simple idea - remove a lot of the oxygen from the air to help reduce the risk of fire. What do those of you operating your own facilities make of this? Is anyone already doing this? [url]
