Well, do note that if I recall well (it's been a while since I did DB work) COUNT(*) queries without a WHERE clause on innodb tables are notoriously slower than on MyISAM and Memory tables.
Also, is this by any chance a Xen DomU?
What is the frontend language? If PHP, is it using MySQL or MySQLi? Are they using persistent connections?
You have not mentionned the underlying operating system, but in the case of Linux I would start by staring at the output of free -m, paying special attention to the last two lines to see if memory is tight overall.
[0:504] callisto:cyanotype $ free -m
total used free shared buffers cached
Mem: 3961 3816 144 0 184 1454
-/+ buffers/cache: 2177 1784
Swap: 2898 0 2898
Here we have a system that's healthy (it is my workstation). The second column excludes buffers and cache, so I am in fact using 2177mb of memory, and have 1784 megabytes readily available.
Last line shows that I don't use swap at all so far.
Then giving vmstat(8), to see if your system is trashing like mad would be useful, too.
[0:505] callisto:cyanotype $ vmstat 5 10
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 0 0 134116 189828 1499948 0 0 11 3 44 49 1 1 98 0
0 0 0 143112 189836 1489688 0 0 0 6 526 2177 1 1 98 0
0 0 0 139268 190504 1491864 0 0 512 4 663 4704 2 1 96 1
2 0 0 136688 191084 1493484 0 0 473 5 641 3039 1 1 97 1
0 0 0 52636 191712 1518620 0 0 5066 4 1321 6600 8 2 86 4
5 0 0 72992 193264 1377324 0 0 10742 31 1602 7441 12 3 80 5
2 1 0 84036 193896 1202012 0 0 10126 43 2621 4305 31 2 57 10
3 0 0 42456 195812 1060904 0 0 3970 75 55327 9806 43 5 41 10
8 1 0 34620 197040 942940 0 0 3554 64 50892 12531 43 6 44 6
^C
[0:506] callisto:cyanotype $
(My desktop really isn't doing all that much here, sorry. What a waste of 8 perfectly good cores)
If you see a lot of process spending time in the 'b' column, that means they are blocked, waiting for something. Often that is IO. The important columns here are si and so. Check if they're populated with high values. If so, this may be your problem -- something is consuming a lot of memory, more than you can actually affort. Using top(4) and ordering the columns by memory % (shift+m while in top) might show the culprit(s).
It's not impossible that your system is trashing to and from swap, and saturating the disks, causing blocked threads and processes.The tool iostat(8)(part of package sysstat, usually) should be given a whirl to see if you have processes that are blocked, stuck on IO_WAIT. A saturated disk can spell bad news for the whole system under high load, especially if the system is swapping a lot.
You might run iostat with extended stats, every five seconds, for instance:
[0:508] callisto:cyanotype $ iostat -x 5
Linux 2.6.35-23-generic (callisto) 2010-11-30 _x86_64_ (8 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
16,55 0,12 2,70 2,60 0,00 78,02
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sdc 0,00 2,00 1,00 0,80 27,20 22,40 27,56 0,01 3,33 3,33 0,60
sdd 0,00 12,60 67,60 4,80 4222,40 139,20 60,24 0,62 8,62 3,29 23,80
sde 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00
sdf 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00
avg-cpu: %user %nice %system %iowait %steal %idle
32,02 0,10 1,83 0,44 0,00 65,61
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sdc 0,60 3,20 11,00 0,80 265,60 32,00 25,22 0,05 3,90 2,88 3,40
sdd 0,00 8,20 0,00 3,00 0,00 89,60 29,87 0,02 8,00 7,33 2,20
sde 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00
sdf 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00
avg-cpu: %user %nice %system %iowait %steal %idle
49,26 0,22 3,12 0,12 0,00 47,28
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sdc 6,20 3,00 7,40 3,80 208,00 54,40 23,43 0,09 7,86 2,50 2,80
sdd 0,00 15,20 0,20 4,00 1,60 152,00 36,57 0,03 6,67 6,19 2,60
sde 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00
sdf 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00
avg-cpu: %user %nice %system %iowait %steal %idle
16,00 0,54 1,05 1,07 0,00 81,35
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sdc 4,20 0,00 31,40 0,00 3204,80 0,00 102,06 0,17 4,90 2,68 8,40
sdd 0,00 28,20 0,20 2,60 1,60 246,40 88,57 0,02 7,14 7,14 2,00
sde 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00
sdf 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00
^C
This should allow you to easily see if your volumes are being saturated. For instance here, you can see that my disks are terribly underutilized, that the system spends most of its cpu cycles idling, etc etc.
If that percentage is mostly in the % IOWAIT column, well you have an IO bottleneck here. You probably already know all this, but just covering all the bases to make sure.
The idea is that your config file changed, and you have no history of it (putting your config files under version control is a great idea for that very reason) -- and it is not impossible the size of a buffer suddendly changed thus making expensive queries like COUNT(*) without SELECT suddendly start to gobble up ressources.
Based on what you have learned from the previous use of the tools abive -- you should probably inspect the configuration file (being the only thing that changed, it is very likely the culprit) to see if the buffer values are sane for your average load.
How large are the buffers, like the query_cache_size value, and especially the sort_buffer sizes? (If that doesn't fit in memory, it will performed on disk, at a massive cost as I'm sure you can imagine).
How large is the innodb_buffer_pool_size?
How large is the table_cacheand most importantly, does that value fits within the system limits for file handles? (both open-files-limit in [mysqld] and at the OS level).
Also, I don't remember off the top of my head if this is still true, but I'm fairly certain that innodb actually locks the entire table whenever it has to commit an auto-increment fields. I googled and I could not find if that was still true or not.
You could also use innotop(1) to see what's going on more in detail, too.
I hope this helps somehow or gives you a starting point :)
What is Active Directory?
Active Directory Domain Services is Microsoft's Directory Server. It provides authentication and authorization mechanisms as well as a framework within which other related services can be deployed (AD Certificate Services, AD Federated Services, etc). It is an LDAP compliant database that contains objects. The most commonly used objects are users, computers, and groups. These objects can be organized into organizational units (OUs) by any number of logical or business needs. Group Policy Objects (GPOs) can then be linked to OUs to centralize the settings for various users or computers across an organization.
When people say "Active Directory" they typically are referring to "Active Directory Domain Services." It is important to note that there are other Active Directory roles/products such as Certificate Services, Federation Services, Lightweight Directory Services, Rights Management Services, etc. This answer refers specifically to Active Directory Domain Services.
What is a domain and what is a forest?
A forest is a security boundary. Objects in separate forests are not able to interact with each other, unless the administrators of each separate forest create a trust between them. For example, an Enterprise Administrator account for domain1.com, which is normally the most privileged account of a forest, will have, no permissions at all in a second forest named domain2.com, even if those forests exist within the same LAN, unless there is a trust in place.
If you have multiple disjoint business units or have the need for separate security boundaries, you need multiple forests.
A domain is a management boundary. Domains are part of a forest. The first domain in a forest is known as the forest root domain. In many small and medium organizations (and even some large ones), you will only find a single domain in a single forest. The forest root domain defines the default namespace for the forest. For example, if the first domain in a new forest is named domain1.com, then that is the forest root domain. If you have a business need for a child domain, for example - a branch office in Chicago, you might name the child domain chi. The FQDN of the child domain would be chi.domain1.com. You can see that the child domain's name was prepended forest root domain's name. This is typically how it works. You can have disjoint namespaces in the same forest, but that's a whole separate can of worms for a different time.
In most cases, you'll want to try and do everything possible to have a single AD domain. It simplifies management, and modern versions of AD make it very easy to delegate control based on OU, which lessens the need for child domains.
I can name my domain whatever I want, right?
Not really. dcpromo.exe, the tool that handles the promotion of a server to a DC isn't idiot-proof. It does let you make bad decisions with your naming, so pay attention to this section if you are unsure. (Edit: dcpromo is deprecated in Server 2012. Use the Install-ADDSForest PowerShell cmdlet or install AD DS from Server Manager.)
First of all, don't use made up TLDs like .local, .lan, .corp, or any of that other crap. Those TLDs are not reserved. ICANN is selling TLDs now, so your mycompany.corp that you're using today could actually belong to someone tomorrow. If you own mycompany.com, then the smart thing to do is use something like internal.mycompany.com or ad.mycompany.com for your internal AD name. If you use mycompany.com as an externally resolvable website, you should avoid using that as your internal AD name as well, since you'll end up with a split-brain DNS.
Domain Controllers and Global Catalogs
A server that responds to authentication or authorization requests is a Domain Controller (DC). In most cases, a Domain Controller will hold a copy of the Global Catalog. A Global Catalog (GC) is a partial set of objects in all domains in a forest. It is directly searchable, which means that cross-domain queries can usually be performed on a GC without needing a referral to a DC in the target domain. If a DC is queried on port 3268 (3269 if using SSL), then the GC is being queried. If port 389 (636 if using SSL) is queried, then a standard LDAP query is being used and objects existing in other domains may require a referral.
When a user tries to log in to a computer that is joined to AD using their AD credentials, the salted and hashed username and password combination are sent to the DC for both the user account and the computer account that are logging in. Yes, the computer logs in too. This is important, because if something happens to the computer account in AD, like someone resets the account or deletes it, you may get an error that say that a trust relationship doesn't exist between the computer and the domain. Even though your network credentials are fine, the computer is no longer trusted to log into the domain.
Domain Controller Availability Concerns
I hear "I have a Primary Domain Controller (PDC) and want to install a Backup Domain Controller (BDC)" much more frequently that I would like to believe. The concept of PDCs and BDCs died with Windows NT4. The last bastion for PDCs was in a Windows 2000 transitional mixed mode AD when you still had NT4 DCs around. Basically, unless you're supporting a 15+ year old
install that has never been upgraded, you really don't have a PDC or a BDC, you just have two domain controllers.
Multiple DCs are capable of answering authentication requests from different users and computers simultaneously. If one fails, then the others will continue to offer authentication services without having to make one "primary" like you would have had to do in the NT4 days. It is best practice to have at least two DCs per domain. These DCs should both hold a copy of the GC and should both be DNS servers that hold a copy of the Active Directory Integrated DNS zones for your domain as well.
FSMO Roles
"So, if there are no PDCs, why is there a PDC role that only a single DC can have?"
I hear this a lot. There is a PDC Emulator role. It's different than being a PDC. In fact, there are 5 Flexible Single Master Operations roles (FSMO). These are also called Operations Master roles as well. The two terms are interchangeable. What are they and what do they do? Good question! The 5 roles and their function are:
Domain Naming Master - There is only one Domain Naming Master per forest. The Domain Naming Master makes sure that when a new domain is added to a forest that it is unique. If the server holding this role is offline, you won't be able to make changes to the AD namespace, which includes things like adding new child domains.
Schema Master - There is only one Schema Operations Master in a forest. It is responsible for updating the Active Directory Schema. Tasks that require this, such as preparing AD for a new version of Windows Server functioning as a DC or the installation of Exchange, require Schema modifications. These modifications must be done from the Schema Master.
Infrastructure Master - There is one Infrastructure Master per domain. If you only have a single domain in your forest, you don't really need to worry about it. If you have multiple forests, then you should make sure that this role is not held by a server that is also a GC holder unless every DC in the forest is a GC. The infrastructure master is responsible for making sure that cross-domain references are handled properly. If a user in one domain is added to a group in another domain, the infrastructure master for the domains in question make sure that it is handled properly. This role will not function correctly if it is on a global catalog.
RID Master - The Relative ID Master (RID Master) is responsible for issuing RID pools to DCs. There is one RID master per domain. Any object in an AD domain has a unique Security Identifier (SID). This is made up of a combination of the domain identifier and a relative identifier. Every object in a given domain has the same domain identifier, so the relative identifier is what makes objects unique. Each DC has a pool of relative IDs to use, so when that DC creates a new object, it appends a RID that it hasn't used yet. Since DCs are issued non-overlapping pools, each RID should remain unique for the duration of the life of the domain. When a DC gets to ~100 RIDs left in its pool, it requests a new pool from the RID master. If the RID master is offline for an extended period of time, object creation may fail.
PDC Emulator - Finally, we get to the most widely misunderstood role of them all, the PDC Emulator role. There is one PDC Emulator per domain. If there is a failed authentication attempt, it is forwarded to the PDC Emulator. The PDC Emulator functions as the "tie-breaker" if a password was updated on one DC and hasn't yet replicated to the others. The PDC Emulator is also the server that controls time sync across the domain. All other DCs sync their time from the PDC Emulator. All clients sync their time from the DC that they logged in to. It's important that everything remain within 5 minutes of each other, otherwise Kerberos breaks and when that happens, everyone cries.
The important thing to remember is that the servers that these roles run on is not set in stone. It's usually trivial to move these roles around, so while some DCs do slightly more than others, if they go down for short periods of time, everything will usually function normally. If they're down for a long time, it's easy to transparently transfer the roles. It's much nicer than the NT4 PDC/BDC days, so please stop calling your DCs by those old names. :)
So, um...how do the DCs share information if they can function independently of each other?
Replication, of course. By default, DCs belonging to the same domain in the same site will replicate their data to each other at 15 second intervals. This makes sure that everything is relatively up to date.
There are some "urgent" events that trigger immediate replication. These events are: An account is locked out for too many failed logins, a change is made to the domain password or lockout policies, the LSA secret is changed, the password is changed on a DC's computer account, or the RID Master role is transferred to a new DC. Any of these events will trigger an immediate replication event.
Password changes fall somewhere between urgent and non-urgent and are handled uniquely. If a user's password is changed on DC01 and a user tries to log into a computer that is authenticating against DC02 before replication occurs, you'd expect this to fail, right? Fortunately that doesn't happen. Assume that there is also a third DC here called DC03 that holds the PDC Emulator role. When DC01 is updated with the user's new password, that change is immediately replicated to DC03 also. When thee authentication attempt on DC02 fails, DC02 then forwards that authentication attempt to DC03, which verifies that it is, indeed, good, and the logon is allowed.
Let's talk about DNS
DNS is critical to a properly functioning AD. The official Microsoft party line is that any DNS server can be used if it is set up properly. If you try and use BIND to host your AD zones, you're high. Seriously. Stick with using AD Integrated DNS zones and use conditional or global forwarders for other zones if you must. Your clients should all be configured to use your AD DNS servers, so it's important to have redundancy here. If you have two DCs, have them both run DNS and configure your clients to use both of them for name resolution.
Also, you're going to want to make sure that if you have more than one DC, that they don't list themselves first for DNS resolution. This can lead to a situation where they are on a "replication island" where they are disconnected from the rest of the AD replication topology and cannot recover. If you have two servers DC01 - 10.1.1.1 and DC02 - 10.1.1.2, then their DNS server list should be configured like this:
Server: DC01 (10.1.1.1)
Primary DNS - 10.1.1.2
Secondary DNS - 127.0.0.1
Server: DC02 (10.1.1.2)
Primary DNS - 10.1.1.1
Secondary DNS - 127.0.0.1
OK, this seems complicated. Why do I want to use AD at all?
Because once you know what you're doing, you life becomes infinitely better. AD allows for the centralization of user and computer management, as well as the centralization of resource access and usage. Imagine a situation where you have 50 users in an office. If you wanted each user to have their own login to each computer, you'd have to configure 50 local user accounts on each PC. With AD, you only have to made the user account once and it can log into any PC on the domain by default. If you wanted to harden security, you'd have to do it 50 times. Sort of a nightmare, right? Also imagine that you have a file share that you only want half of those people to get to. If you're not using AD, you'd either need to replicate their username and passwords by hand on the server to give seemless access, or you'd have to make a shared account and give each user the username and password. One way means that you know (and have to constantly update) users' passwords. The other way means that you have no audit trail. Not good, right?
You also get the ability to use Group Policy when you have AD set up. Group Policy is a set of objects that are linked to OUs that define settings for users and/or computers in those OUs. For example, if you want to make it so that "Shutdown" isn't on the start menu for 500 lab PCs, you can do that in one setting in Group Policy. Instead of spending hours or days configuring the proper registry entries by hand, you create a Group Policy Object once, link it to the correct OU or OUs, and never have to think about it again. There are hundreds of GPOs that can be configured, and the flexibility of Group Policy is one of the major reasons that Microsoft is so dominant in the enterprise market.
Best Answer
How about
Measure-Command {get-aduser -filter * -properties *|select $_.givenname,$_.sn,$_.mail, $_.mailnickname}or something similar (depending on what attributes etc your app queries)?