Showing posts with label performance boosting. Show all posts
Showing posts with label performance boosting. Show all posts

Jun 16, 2011

What should I do when MySQL storage files are getting too large?

As MySQL is the internet and cloud de-facto database, we dedicate another post regarding what should you do before getting to the money time.

Some methods including stored data compression and splitting the InnoDB storage file are covered in this post.

Compressing the File
InnoDB data compression is possible in a way that is very similar to disk compression with the pros and cons of it. This method is defined in the table declaration statement and explained in the MySQL documentation. Will it improve MySQL performance? Fortunately, the answer is yes! a recent post by MySQL@Facebook team shown a 10% boost in response time and improvement in the cache hit ratio.

Splitting the Storage File
The default InnoDB configuration is using a single file named ibdata1. This file stores both data and configuration. The bad news is that this file always expands and cannot be shrinked.
Usually people notice this fact when the database is getting too large and they have some storage or backup issues. Usually in these cases, the system is already in production and cannot suffer downtime. Therefore, you should take care of splitting this file in the early days of the system rather when you are in the money time.

What to do?
  1. Configure the MySQL to save the data in various files, one per table. Please notice that this will work only to tables that will be created from now on. Perform that by adding for following lines to the my.cnf file:
    1. [mysqld]
    2. innodb_file_per_table
  2. Stop the server for maintenance and perform one of the following three methods:
    1. Convert all InnoDB tables to MyISAM and back
    2. Export only InnoDB tables, drop them, delete ibdata1 and import InnoDB tables.
    3. Export all databases, delete ibdata1 and import everything back.
  3. I recommends you to choose option 2 and perform it according to the following procedure: export-delete-import:
    1. mysqldump to the whole database.
    2. Stop the MySQL daemon.
    3. Delete the ibdata + ilog files.
    4. Delete the database folders (all except for MySQL).
    5. Change my.cnf file if needed.
    6. Restart the MySQL daemon.
    7. Import the dumped file back to the system.
Bottom Line
Some times, it wise to perform tasks earlier in order to avoid complex issues in the future.

Keep Performing,

Feb 21, 2010

Lecture: Extract The Traffic from the DB

A few days ago I had a presentation in the AlphaGeeks meetup in Tel Aviv, presenting NoSQL, Memcached, CouchDB, Sharding and other buzzwords that help you extract the traffic from the database and boost your system performance. If you were not there, you can take a look at the presentation (English) or the recorded video (Hebrew).

The Presentation (English)
The Video (Hebrew)

Keep Performing,
Moshe Kaplan

Dec 20, 2009

Expect for the Best. Be Prepared for the Worse.

A major issue in preparing for the disaster is getting clear visibility of your system. Especially if we are talking about distributed multi server and multi data center system.
Paul Venezia from InfoWorld provided a clear and concise description of the major open source tools (yes, free ones), that can give your clear visibility of your system.These tools can help you better know:
  1. When does your CPU utilization get close to 100%?
  2. When should you add more hardware or make an effort to boost your system performance?
  3. What are your high traffic sources?
  4. What is the root cause analysis for system poor performance?
  5. What are the long term treadlines?
 Among the described tools you can find:
  1.  Cacti - System Monitoring Web GUI that provides you graphs to every system metric that can be exposed in SNMP. Most recommended tool.
  2.  Nagios - Network Monitoring tool.
  3. NeDi - Physical network mapping tool (to which port your server is connected). Useful moslty if you are in the on-premise business rather than the cloud business.
  4. Ntop - Traffic analysis tool that can help find what are the traffic sources, and which one keeps you bill so high.
  5. Pancho - Configuring and backup Cisco routers. Again for the on-premise guys.
Using at least some of these tools you can better prepare for the disaster, and many times avoid it.

Keep Performing,
Moshe Kaplan. Performance Expert.

Nov 26, 2009

What if I have been in NYC, London and SF in the same day?

When you analyzing a website performance, it's always a mystery what is the performance issue root cause (well at least till we dive in and reveal it :-).

A common issue in this analysis is what is the network round trip time (RTT) in the play, and who is to blame for. Usually we start with following measures:
  1. Analyze the website from our offices. This test includes both Wireshark analysis to analyze the network RTT and FireBug to understand website behavior.
  2. Analyze the website performance in the hosting environment by running a terminal services on a server (or the appropriate measure in Linux or UNIX based environment), were RTT should be zero.
  3. Finally, we usually connect the site from another site around the world. This is done usually by connecting a server in another hosting environment (booting a server for a hour using Amazon AWS is a great solution for that).


    These tests are usually a good method in order to evaluate the communication layer play in the performance issue. However, testing from remote sites is not always accurate and sufficient. In these cases we recommend using a service name browsermob.com, that provides you a FireBug like analysis from several sites around the world.  

    Keep Performing!
    Moshe Kaplan. 9AFW6C8B3GKN

    Sep 17, 2009

    The Basics of SQL Server Performance

    When you first hit a SQL Server that cannot server even another single hit you should start with following basic methods:
    1. Analyze the application and understand who are the most massive business processes, and analyze their performance. High odds that the problem lies there.
    2. Detect open connections using the Activity Monitor
    3. Check objects execution time using the SQL Server reports (of course assuming  you are lucky enough and all SQL Server access is done using stored procedures).
    4. Implement profiling and check the queries are most frequent and  most time consuming.
    5. Detect dead locks (a good sign for that this is the case is low CPU utilization).
    6. Detect slowdown source by using the following query  (thanks to Henk van der Valk):
    -- Uncomment the reset or capture the wait stats before you start the batch to investigate:
    -- DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR)
    -- DBCC SQLPERF('sys.dm_os_latch_stats', CLEAR)

    -- show the sql waitstatistics:
    SELECT wait_type
    , SUM(waiting_tasks_count) AS waiting_tasks_count
    , SUM(wait_time_ms) AS wait_time_ms
    , SUM(max_wait_time_ms) AS max_wait_time_ms
    , SUM(signal_wait_time_ms) AS signal_wait_time_ms
    , SUM(wait_time_ms/NULLIF(waiting_tasks_count,0)) as 'Avg_wait_time_ms per waittype req.'

    FROM sys.dm_os_wait_stats
    WHERE wait_type NOT IN
    (
    'SQLTRACE_BUFFER_FLUSH'
    , 'LAZYWRITER_SLEEP'
    , 'CHECKPOINT_QUEUE'
    , 'BROKER_TO_FLUSH'
    )
    AND wait_type NOT LIKE 'XE%'
    AND wait_type NOT LIKE 'PREEMPT%'
    AND wait_type NOT LIKE 'SLEEP%'
    AND wait_type NOT LIKE '%REQ%DEAD%'
    AND waiting_tasks_count > 0
    GROUP BY wait_type
    WITH ROLLUP
    ORDER BY wait_time_ms DESC

    After doing these initial steps, you finally gathhered enough data to solve the issue...

    Keep Performing,
    Moshe Kaplan. RockeTier. The Performance Experts.
    moshe at rocketier.com

    Aug 22, 2009

    ITIL, Performance Boosting and Presentations

    Hi,

    In the last past months RockeTier worked (and keep working) with several large organizations in several aspects: 1) design new systems which are capable to do more based on commodity hardware ; 2) boosting existing software performance (lean projects) and 3) establish processes in large organization that support the life cycle of performance from event management, problem management to establishing a continues performance boosting to the organization systems from RFI to production (ITIL oriented projects).

    We decided to share with you a presentation to a large telecom oriented company that is considering these days getting into the performance boosting and Lean projects for its main product lines.


    Keep Performing,
    Moshe Kaplan. RockeTier. The performance and cloud experts.

    May 13, 2009

    Cloud Slam 09': The Pareto Illusion

    A few weeks ago we presented in Cloud Slam 09' cloud services cost analysis, and how it can be minimized using performance boosting. We also discussed in this presentation databases in the cloud and Sharding based on participants questions.
    Feel free to take a look at the recorded presentation as well as the abstract, which are available at Cloud Slam site as well.

    The Pareto Illusion - Why we end up paying too much for cloud services and what can we do about it? Moshe Kaplan and Ayal Baron

    Video of the session:



    Presentation Abstract:
    Cloud computing is a whole new game: we are no longer talking about equity and CapEx, but regarding OpEx.

    So why are you still paying so much every month for your cloud services? How can it be reduced? What are the industry state of art methodologies to gain more out of your cloud service provider? How to do more with same, and how to gain a better ROI for your project? and last but not least, how to help saving our world.

    In this presentation we'll discuss how can you make your software more efficient, and how to focus on core components of the system and achieving 80% boost in 20% effort.

    Keep Performing,
    Moshe Kaplan. RockeTier. The Cloud and Performance Experts

    Mar 14, 2009

    Interview: Crictor and the RockeTier 5 Steps Performance Boosting Methodology

    Hi,

    A week ago Crictor published an interview with me regarding RockeTier 5 steps performance boosting methodology (Hebrew). The interview includes issues regarding our team, the methodology and our success stories.



    Crictor is an online channel focused in the Israeli Hi Tech industry.

    Keep Performing
    Moshe Kaplan. RockeTier. The Performance Experts

    Feb 21, 2009

    Case Study: Handle 1 Billion Events Per Day Using a Memory Grid

    Hi,

    We just published our case study regarding affiliate marketing billing system performance boosting, and we got a great post from Todd Hoff from HighScalability.com regarding it.

    The case study main highlights are:
    1. How to grow from 1 million events per day system to 1 billion events per day
    2. How to keep cost low and avoid millions of USD equity investments
    3. How to grow fast keeping close with the business objectives while designing the road map

    Please feel free to read our performance boosting case study and comment regarding it in this blog.

    Best,
    Moshe Kaplan
    RockeTier. The Performance Experts.

    Update #1: Answers to questions I received through the email:

    How do we provide HA?

    We usually deploy the systems in a active/active configuration.

    What about crash recovery? If counters are kept in memory only there is a window time where a crash will loose the updated counters? Is the client OK with loosing some updates or do you address it someway?
    First of all, many of our clients prefer to avoid data loss and risk the lose of data saved in the memory. This is based on simple arithmetic. If you lose a 1 minute of business operation in single server that is about 400 events/second and in case every 1000 events generates you a revenue of few dozens cents, you will prefer losing these 30 bucks than replicating every part of the system.

    How do you make sure that the other request to the failed server are still getting answer?
    The load balancer is smart enough to detect the server failure, change the rotation algorithm, and making sure an alternative server will take care of the processing.

    How do we support Multi datacenter HA?
    Multi datacenter HA can be achieved using geo clustering

    What about customers that require zero loss of data?
    Other customer that require zero lose of data, get an answer using 1) Gigaspaces XAP, which supports data synchronization on the fly between two servers, keeping the two servers synchronized to the last operation and 2) using RDMA.

    Feb 2, 2009

    The Mystery of System Calls

    Hi,

    It always a pleasure to have real life contributions from colleagues in the industry. This time, Rubi Dagan, a system architect and senior team leader at Metacafe, one of the world's largest video sites ww, shares with us "the mystery of system calls".

    Background
    Metacafe's software system had many calls to time(), and during stress it was felt much stronger. For example, in the figure 1 you can see that 35% of the syscall time was wasted on time().



    However, when taking a look on several other servers, it was found that all requests are being processes without calls to time at all!!! (see figure 2). Hint: use strace -cp `ps ax | grep [h]ttpd | awk '{ print $1 }' | tr '\n' ',' | sed 's/,/ -p /g'` -f to get this information








    Solving the mystery...

    The solution of it is based on the BIOS, in an option named HPET – High Precision Event Timer which when enabled, the kernel do fast lookups without a need to use the time() syscall. This method is able to track the time instead of the kernel. Please notice that this function should be enabled on the kernel.

    That’s it, instead of Apache or other program deals with time the HPET mechanism do that. The bottom line is reduce time system calls. See also the thread in StackOverflow.

    Bottom Line
    This new configuraion reduced syscalls time by ~30% and more which is being translated to a great performance impact on Metacafe servers.

    P.S We'll be glad to expose here other cases from the industry. Don't be shame to submit your case and contribute the community.

    Best Regards,
    Moshe Kaplan. RockeTier. The Performance Experts

    ShareThis

    Intense Debate Comments

    Ratings and Recommendations